Parameterized Queries

Parameterized queries are SQL queries that contain parameters that can be set at runtime.

For example:

Select Order_Number, Customer_Number, SalesPerson_ID, Order_Date from OrderHea Where SalesPerson_ID = ? And Order_Date > ?

This query has 2 parameters represented by the “?” parameter markers. The parameters' values can be set at runtime by calling SQLSetParameter.

The advantages of using parameterized queries are:

Protection against SQL Injection

Parameterized queries protect against SQL Injection attacks. The query itself and the parameter values are sent to the SQL server separately, which enhances security..

Queries without parameters are usually built by string concatenation of the (executable) SQL code and data values. If the data values are provided by user input, an attacker can enter executable SQL code as data and get unauthorized access to a database.

By using parameters, data is sent to the server separately from the SQL code, thereby making SQL injection impossible.

Prepared Execution

Prepared execution gives a performance gain when the same query gets executed multiple times on the server. With prepared execution, a query is prepared once and then executed multiple times with different parameters and the SQL engine only has to create an execution plan once that can be re-used on subsequent executions.

Parameterized queries can also be used for queries that are only executed once (SQLExecDirect).

An example of a parametrized query with prepared execution:

    Procedure DemoParaQryPrepared

        Handle  hdbc

        Handle  hstmt

        String  sSQLQuery

        Integer iParamNum

        String  sSalesPersonID

        Date    dOrderDate

        String[][] sResultSet

        Move "Select Order_Number, Customer_Number, SalesPerson_ID, Order_Date from OrderHea Where SalesPerson_ID = ? And Order_Date > ? " to sSQLQuery

        Get SQLConnectionId of ghoConnection "MssqlOrderID" to hdbc        

        If (hdbc <> 0) Begin

            Get SQLOpen of hdbc to hstmt

            If (hstmt <> 0) Begin

                Send SQLPrepare of hstmt sSQLQuery

                // First execution of prepared statement

                Move 1 to iParamNum

                Move 'MM' to sSalesPersonID

                Send SqlSetParameter of hstmt iParamnum sSalesPersonID typeString

                Move 2 to iParamNum

                Move (DateSet(2015, 12, 25)) to dOrderDate

                Send SqlSetParameter of hstmt iParamnum dOrderDate typeDate

                Send SQLExecute of hstmt

                Get SQLFetchResultSetValues of hstmt to sResultSet

                // Second execution of prepared statement

                Move 1 to iParamNum

                Move 'BS' to sSalesPersonID

                Send SqlSetParameter of hstmt iParamnum sSalesPersonID typeString

                Move 2 to iParamNum

                Move (DateSet(2015, 10, 31)) to dOrderDate

                Send SqlSetParameter of hstmt iParamnum dOrderDate typeDate

                Send SQLExecute of hstmt

                Get SQLFetchResultSetValues of hstmt to sResultSet

                Send SQLClose of hStmt

            End

            Send SQLDisconnect of hDbc

        End

    End_Procedure

• In the above program, the parameterized query that is executed is:

Select Order_Number, Customer_Number, SalesPerson_ID, Order_Date from OrderHea Where SalesPerson_ID = ? And Order_Date > ?

• The two ?'s in the query are parameter markers.
• Parameter markers can be used in the Where clause of a SELECT statement and in INSERT or UPDATE statements. For example:

INSERT INTO "dbo"."OrderHea" ("Order_Number", "Customer_Number", "Order_Date", "Terms")  VALUES (?, ?, ?, ?)

• The parameterized query is prepared once and can be executed multiple times.
• SQLPrepare sends the query to the server, where the query is compiled (an Execution plan is generated). The compiled query is saved on the server.
• SQLSetParameter sets the parameter values.
• SQLExecute will then execute the compiled query with the passed parameter values.
• Since the query itself and the parameter data are sent in separate calls, it protects against SQL injection. Even when the parameter values contain injected SQL code it can not be executed as code.

An example of a parametrized query with direct execution:

// This procedure inserts a new record in the customer table.

    Procedure DemoParaQryInsert tCustomer NewCustomer        

        Handle  hdbc

        Handle  hstmt

        String  sSQLQuery

// INSERT INTO [dbo].[Customer]

// ([Customer_Number],[Name],[Address],[City],[State],[Zip],[Credit_Limit][Comments],[Status])

// VALUES (?,?.?,?,?,?,?,?,?)

        Move (sSQLQuery + "INSERT INTO [dbo].[Customer] ") to sSQLQuery

        Move (sSQLQuery + "([Customer_Number],[Name],[Address],[City],[State],[Zip],[Credit_Limit],[Comments],[Status])") to sSQLQuery

        Move (sSQLQuery + "VALUES (?,?,?,?,?,?,?,?,?)") to sSQLQuery

        Get SQLConnectionId of ghoConnection "MssqlOrderID" to hdbc        

        If (hdbc <> 0) Begin

            Get SQLOpen of hdbc to hstmt

            If (hstmt <> 0) Begin

                Send SqlSetNextParameter of hstmt NewCustomer.iCustomerNumber typeNumber

                Send SqlSetNextParameter of hstmt NewCustomer.sName           typeString

                Send SqlSetNextParameter of hstmt NewCustomer.sAddress        typeString

                Send SqlSetNextParameter of hstmt NewCustomer.sCity           typeString

                Send SqlSetNextParameter of hstmt NewCustomer.sState          typeString

                Send SqlSetNextParameter of hstmt NewCustomer.sZip            typeString

                Send SqlSetNextParameter of hstmt NewCustomer.nCredit_Limt    typeNumber

                Send SqlSetNextParameter of hstmt NewCustomer.sComments       typeString

                Send SqlSetNextParameter of hstmt NewCustomer.sStatus         typeString

                Send SQLExecDirect of hstmt sSQLQuery

                Send SQLClose of hStmt

            End

            Send SQLDisconnect of hDbc

        End

    End_Procedure

New Procedures in cSQLStatement

SQLSetParameter

SQLSetNextParameter