Using parameterized queries in DataFlex embedded SQL calls

In Flex2SQL v15, we introduced the ability to use parameterized queries within your DataFlex application’s embedded SQL calls. Developers have been asking for this feature for a long time, and for good reason.

Using parameterized queries, rather than directly specifying query values, not only improves your app’s performance, but also eliminates the threat of SQL injection attacks and malicious data entry.

Parameterized queries allow developers to create queries using placeholders that accept future, variable-specified values, instead of specifying values directly using a series of concatenated strings. Prior to Flex2SQL v15, your typical, string concatenated embedded SQL call would look something like this: 

Move "update Customer set Name = ‘ABC' where Customer_Number = ‘1001’” to sql_stmt
Move (SFormat(sql_stmt, cust_name, cust_num)) to sql_stmt
Send AppendTextLn of oTextEdit1 sql_stmt
sql_set_stmt to sql_stmt
sql_prepare_stmt
sql_execute_stmt
Sql_cancel_query_stmt

 
In the above query, you explicitly specify the values for the customer’s name and number, construct the query string, and then send it to the server. While this construction is relatively straightforward, it opens the door to SQL injection attacks.

Switching to parameterized queries

As you probably already know, allowing users to enter query values and send them directly to your server provides them the opportunity to insert malicious code and assault your database’s integrity. Parameterized queries avoid this problem, because users’ entered query values are not sent directly to your server. Instead, the values are assigned to variables, which are themselves assigned to placeholders (parameters) within your query. In this way, parameterized queries sanitize users’ entered data before sending it to your server.

Each database server uses its own placeholder syntax. Microsoft SQL Server and MySQL use “?” as their placeholders, while PostgreSQL uses “$”, and Oracle uses custom variable names. The following examples show how to reconstruct the above, string concatenated query into a parameterized query for use with the four database servers that Flex2SQL supports.

Example for MS SQL and MySQL databases:

In this example, you see two “?”s which represent the placeholders for values to be used in the query. A new statement, SQL_SET_QUERY_PARAMETER, has been added to allow you to assign a variable to each placeholder: 

Move "update Customer set Name = ? where Customer_Number = ?" to sql_stmt
Send AppendTextLn of oTextEdit1 sql_stmt
sql_set_stmt to sql_stmt
sql_set_query_parameter 1 to cust_name
sql_set_query_parameter 2 to cust_num
sql_prepare_stmt
sql_execute_stmt
sql_cancel_query_stmt

Example for PostgreSQL database:

In PostgreSQL, query parameters are represented by “$1”, “$2”, and the SQL_SET_QUERY_PARAMETER statement uses numbers that correspond to the ordinal position of each parameter. In PostgreSQL, the above query would look like this: 

Move "update Customer set Name = $1 where Customer_Number = $2" to sql_stmt
Send AppendTextLn of oTextEdit1 sql_stmt
sql_set_stmt to sql_stmt
sql_set_query_parameter 1 to cust_name
sql_set_query_parameter 2 to cust_num
sql_prepare_stmt
sql_execute_stmt
sql_cancel_query_stmt

Example for Oracle database:

Oracle allows developers to name their query parameters, instead of using symbols. In Oracle, the above query would look like this: 

Move "update Customer set Name = cname where Customer_Number = cnum" to sql_stmt
Send AppendTextLn of oTextEdit1 sql_stmt
sql_set_stmt to sql_stmt
sql_set_query_parameter "cname" to cust_name
sql_set_query_parameter "cnum" to cust_num
sql_prepare_stmt
sql_execute_stmt
sql_cancel_query_stmt

To find out more about the advantages of using this and other new features in Flex2SQL v15, watch our webinar.