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 two parameters represented by the “?” parameter markers. The parameter values can be set at runtime by calling SQLSetParameter.

Advantages of Using Parameterized Queries

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 gain 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 provides a performance gain when the same query is executed multiple times on the server. With prepared execution, a query is prepared once and then executed multiple times with different parameters, allowing the SQL engine to create an execution plan once that can be reused on subsequent executions.

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

Example of a Parameterized 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 ? 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 cannot be executed as code.

Example of a Parameterized Query with Direct Execution

// This procedure inserts a new record in the customer table.
Procedure DemoParaQryInsert tCustomer NewCustomer
    Handle  hdbc
    Handle  hstmt
    String  sSQLQuery

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