How to resolve error 'ORA-00911: invalid character' in Golang?

During the Golang development process, we sometimes encounter errors such as "ORA-00911: invalid character". This error is usually caused by using invalid characters in the SQL statement. To solve this problem, we can take some simple methods. First of all, before executing the SQL statement, we should carefully check whether there are special characters or invalid characters in the statement. Secondly, we can try to use quotes to enclose fields or values ​​that contain special characters to avoid errors. In addition, you can also use escape characters to process special characters to ensure that they are correctly recognized and processed in SQL statements. Through these methods, we can effectively solve the "ORA-00911: invalid character" problem in Golang and ensure that our program can run normally.

Question content

I encountered the error "ORA-00911: invalid character" when calling the following function. If I use a SQL query with hardcoded values ​​(as of now, it's commented out in the snippet below), then I can get the database records in a JSON response in Postman without any issues. So, it looks like I'm doing my argument wrong. FYI, I am using "github.com/sijms/go-ora/v2" package to connect to oracle db. Also, the "DashboardRecordsRequest" structure is in the data model package, but I've pasted it into the snippet below for reference. Please note that when I do the POC we will be using stored procedures to interact with Oracle.

Postman request payload:

<code>{
    "username": "UserABC",
    "startindex": 0,
    "pagesize": 10,
    "sortby": "requestnumber",
    "sortorder": "DESC"
}
</code>

Execution code:

<code>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
}
</code>

Request table structure:

<code>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" )
)
</code>

mistake:

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"

Workaround

The immediate problem is that you are using the JDBC-style ? binding placeholder instead of the expected :var form. From the documentation of the go-ora package, you say you are using:

So your stmt should be:

SELECT requestnumber, requeststatus, NVL(requestor, 'N/A'), NVL(pendingwith, 'N/A'), NVL(processtype, 'N/A'), actiondate
FROM requests WHERE requeststatus = 'PENDINGAPPROVAL'
ORDER BY :sortby :sortorder OFFSET :startindex ROWS FETCH NEXT :pagesize ROWS ONLY

But you can't bind anything other than a variable, so it won't let you have sortorder as a variable at all, if you just remove it and do:

ORDER BY :sortby OFFSET :startindex ROWS FETCH NEXT :pagesize ROWS ONLY

This seems to work, but even this doesn't do exactly what you want because the sort will sort by the literal column name rather than its value; so it will work as ORDER BY 'requestnumber'## The equivalent of # runs instead of ORDER BY requestnumber. And sorting by that constant string won't accomplish anything.

You need to embed the ordering criteria in the statement:

"... ORDER BY " + sortby + " " + sortorder + " OFFSET :startindex ROWS FETCH NEXT :pagesize ROWS ONLY"

dbfiddle Using PL/SQL dynamic cursors as a simplified equivalent, three versions are shown - one with an error, one that didn't order as expected, and finally one that ordered fine.

But you also need to sanitize these inputs to prevent SQL injection again.

The above is the detailed content of How to resolve error 'ORA-00911: invalid character' in Golang?. For more information, please follow other related articles on the PHP Chinese website!