如何解决Golang调用Oracle时的ORA-00911无效字符错误

调用GetDashboardActiveRequestRecords函数时触发ORA-00911错误，硬编码SQL可正常返回数据，但使用参数化查询时出错，使用github.com/sijms/go-ora/v2连接Oracle数据库。

Postman请求Payload #

{
    "username": "UserABC",
    "startindex": 0,
    "pagesize": 10,
    "sortby": "requestnumber",
    "sortorder": "DESC"
}

Go代码实现 #

type DashboardRecordsRequest struct {
    Username                string `json:"username"`    
    StartIndex              int    `json:"startindex"`
    PageSize                int    `json:"pagesize"`
    SortBy                  string `json:"sortby"`
    SortOrder               string `json:"sortorder"`
}

func GetDashboardActiveRequestRecords(request datamodel.DashboardRecordsRequest) ([]datamodel.ActiveRequestRecord, error) {
    sortby := request.SortBy
    sortorder := request.SortOrder
    startindex := request.StartIndex
    pagesize := request.PageSize
    activerecords := []datamodel.ActiveRequestRecord{}

    slog.Info("Verify values", slog.String("sortby", sortby), slog.String("sortorder", sortorder), slog.Int("startindex", startindex), slog.Int("pagesize", pagesize))

    dbconn, err := getDBConnection()
    if err != nil {
        logger.Error("Could not connect to database")
        return activerecords, err
    }
    stmt, err := dbconn.Prepare("SELECT requestnumber, requeststatus, NVL(requestor, 'N/A'), NVL(pendingwith, 'N/A'), NVL(processtype, 'N/A'), actiondate FROM requests WHERE requeststatus = 'PENDINGAPPROVAL' ORDER BY ? ? OFFSET ? ROWS FETCH NEXT ? ROWS ONLY")
    /*stmt, err := dbconn.Prepare("SELECT requestnumber, requeststatus, NVL(requestor, 'N/A'), NVL(pendingwith, 'N/A'), NVL(processtype, 'N/A'), actiondate FROM requests WHERE requeststatus = 'PENDINGAPPROVAL' ORDER BY requestnumber DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY")*/
    if err != nil {
        logger.Error("Error while building prepared statement for retrieving dashboard active records", slog.String("Error", err.Error()))
        return activerecords, err
    }
    rows, err := stmt.Query(sortby, sortorder, startindex, pagesize)
    //rows, err := stmt.Query()
    if err != nil {
        logger.Error("Error while executing prepared statement for retrieving dashboard active records", slog.String("Error", err.Error()))
        return activerecords, err
    }
    defer rows.Close()

    for rows.Next() {
        var rec datamodel.ActiveRequestRecord
        err = rows.Scan(&rec.RequestNumber, &rec.RequestStatus, &rec.RequestorName, &rec.PendingWith, &rec.ProcessType, &rec.LastActionDate)
        if err != nil {
            logger.Error("Error while processing database resultset for dashboard active records", slog.String("Error", err.Error()))
            return activerecords, err
        }
        activerecords = append(activerecords, rec)
    }
    return activerecords, err
}

Requests表结构 #

CREATE TABLE "REQUESTS" (
    "REQUESTNUMBER"          VARCHAR2(64 CHAR) NOT NULL ENABLE,
    "REQUESTSTATUS"          VARCHAR2(128 CHAR) NOT NULL ENABLE,
    "SUBMISSIONDATE"         TIMESTAMP(6),
    "PROCESSTYPE"            VARCHAR2(256 CHAR),
    "SUBMITTER"              VARCHAR2(256 CHAR) NOT NULL ENABLE,
    "REQUESTOR"              VARCHAR2(512 CHAR),
    "PENDINGWITH"            VARCHAR2(512 CHAR),
    "ACTIONDATE"             TIMESTAMP(6),
    "RESUBMISSIONDATE"       TIMESTAMP(6),
    PRIMARY KEY ( "REQUESTNUMBER" ),
    FOREIGN KEY ( "SUBMITTER" )
        REFERENCES "SUBMITTERS" ( "USERNAME" )
)

错误日志 #

time=2023-10-04T06:43:06.304Z level=INFO source=C:/code/tutorials/myapp/internal/storage/dashboard.go:19 msg="Verify values" sortby=requestnumber sortorder=DESC startindex=0 pagesize=10
time=2023-10-04T06:43:06.603Z level=ERROR source=C:/code/tutorials/myapp/internal/storage/dashboard.go:34 msg="Error while executing prepared statement for retrieving dashboard active records" Error="ORA-00911: invalid character\n"

问题原因与解决方案 #

原因 #

Oracle预编译语句仅支持绑定数据值，不支持将列名、排序方向（ASC/DESC）这类SQL语法元素作为参数。当你尝试把sortby和sortorder作为参数传入时，Oracle会将它们解析为字符串常量，导致SQL语法错误（比如变成ORDER BY 'requestnumber' 'DESC'），触发ORA-00911。

解决方案 #

1. 白名单校验：先对sortby和sortorder做合法性校验，仅允许预定义的合法列名和排序方向，避免SQL注入。
2. 动态拼接SQL：校验通过后，将合法的排序字段和方向直接拼入SQL，分页参数（startindex、pagesize）仍用参数化绑定。

修改后的代码示例：

import "strings"
import "fmt"

func GetDashboardActiveRequestRecords(request datamodel.DashboardRecordsRequest) ([]datamodel.ActiveRequestRecord, error) {
    sortby := request.SortBy
    sortorder := request.SortOrder
    startindex := request.StartIndex
    pagesize := request.PageSize
    activerecords := []datamodel.ActiveRequestRecord{}

    slog.Info("Verify values", slog.String("sortby", sortby), slog.String("sortorder", sortorder), slog.Int("startindex", startindex), slog.Int("pagesize", pagesize))

    // 白名单校验排序字段
    validSortColumns := map[string]bool{
        "requestnumber": true,
        "requeststatus": true,
        "actiondate":    true,
        // 按需添加其他允许的列
    }
    if !validSortColumns[sortby] {
        sortby = "requestnumber" // 设置默认排序字段
    }

    // 校验排序方向
    sortorder = strings.ToUpper(sortorder)
    if sortorder != "ASC" && sortorder != "DESC" {
        sortorder = "DESC" // 设置默认排序方向
    }

    dbconn, err := getDBConnection()
    if err != nil {
        logger.Error("Could not connect to database")
        return activerecords, err
    }

    // 动态拼接合法的排序逻辑，参数化绑定分页参数
    sqlStr := fmt.Sprintf(`SELECT requestnumber, requeststatus, NVL(requestor, 'N/A'), NVL(pendingwith, 'N/A'), NVL(processtype, 'N/A'), actiondate 
                           FROM requests WHERE requeststatus = 'PENDINGAPPROVAL' 
                           ORDER BY %s %s OFFSET ? ROWS FETCH NEXT ? ROWS ONLY`, sortby, sortorder)
    stmt, err := dbconn.Prepare(sqlStr)
    if err != nil {
        logger.Error("Error while building prepared statement", slog.String("Error", err.Error()))
        return activerecords, err
    }
    rows, err := stmt.Query(startindex, pagesize)
    if err != nil {
        logger.Error("Error while executing prepared statement", slog.String("Error", err.Error()))
        return activerecords, err
    }
    defer rows.Close()

    for rows.Next() {
        var rec datamodel.ActiveRequestRecord
        err = rows.Scan(&rec.RequestNumber, &rec.RequestStatus, &rec.RequestorName, &rec.PendingWith, &rec.ProcessType, &rec.LastActionDate)
        if err != nil {
            logger.Error("Error while processing resultset", slog.String("Error", err.Error()))
            return activerecords, err
        }
        activerecords = append(activerecords, rec)
    }
    return activerecords, err
}

补充说明 #

当前处于POC阶段，后续切换为存储交互时，同样不能直接将列名作为参数绑定，需在存储过程内部通过分支逻辑处理合法排序字段，或使用动态SQL（需做好SQL注入防护）。

内容的提问来源于stack exchange，提问作者Ranjeet