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

I am facing error "ORA-00911: invalid character" while invoking below function. If I use SQL query with hardcoded values (as of now it is commented out in below code snippet) then I am getting db records in JSON response in postman without any issue. So, looks like I am doing something wrong with the arguments. FYI, I am using "github.com/sijms/go-ora/v2" package to connect to oracle db. Also, 'DashboardRecordsRequest' struct is in datamodel package but I have pasted it in below code snippet for reference. Please note that as I am doing POC and we will be using stored procedure(s) to interact with oracle.

Postman Request Payload:

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

Go 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
}

Requests Table Structure:

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

Error:

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"

Solution

The immediate problem is that you are using the JDBC-style ? bind placeholders, rather than the expected :var form. From the documentation for the go-ora package you said you are using:

parameters in oracle should start with : for example :pr1

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 anythng except variables, so it won't allow you to have the sortorder as a variable at all, and if you just remove that and do:

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

that will appear to work but even that won't quite do what you want, as the ordering will be by the literal column name, not it's value; so it would be run as the equivalent of ORDER BY 'requestnumber' rather than ORDER BY requestnumber. And ordering by that constant string won't achieve anything.

You would need to embed the ordering criteria in the statement:

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

db<>fiddle using a PL/SQL dynamic cursor as a simplified equivalent, showing the three versions - one erroring, one not ordering as expected, and finally ordered properly.

But you then also need to sanitise those inputs to protect again SQL injection.