---
title: Using parameterized queries in DataFlex embedded SQL calls
description: Update your DataFlex application's embedded SQL calls to use parameterized queries and create templated calls as part of your application. Parameterized queries also limit your application’s vulnerability to SQL injection attacks, as values passed in through parameters are inherently sanitized.
---

[Mertech Blog ](https://www.mertech.com/blog)

# [Using parameterized queries in DataFlex embedded SQL calls](https://www.mertech.com/blog/using-parameterized-queries-dataflex-embedded-sql)

 Written by [Matt Ledger](https://www.mertech.com/blog/author/matt-ledger) | Jun 11, 2018 4:38:29 PM

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_stmtMove (SFormat(sql_stmt, cust_name, cust_num)) to sql_stmtSend AppendTextLn of oTextEdit1 sql_stmtsql_set_stmt to sql_stmtsql_prepare_stmtsql_execute_stmtSql_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_stmtSend AppendTextLn of oTextEdit1 sql_stmtsql_set_stmt to sql_stmtsql_set_query_parameter 1 to cust_namesql_set_query_parameter 2 to cust_numsql_prepare_stmtsql_execute_stmtsql_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_stmtSend AppendTextLn of oTextEdit1 sql_stmtsql_set_stmt to sql_stmtsql_set_query_parameter 1 to cust_namesql_set_query_parameter 2 to cust_numsql_prepare_stmtsql_execute_stmtsql_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_stmtSend AppendTextLn of oTextEdit1 sql_stmtsql_set_stmt to sql_stmtsql_set_query_parameter "cname" to cust_namesql_set_query_parameter "cnum" to cust_numsql_prepare_stmtsql_execute_stmtsql_cancel_query_stmt
```

 

To find out more about the advantages of using this and other new features in Flex2SQL v15, [watch our webinar](https://www.youtube.com/watch?v=o3RB278UdcI).

 

[View full post](https://www.mertech.com/blog/using-parameterized-queries-dataflex-embedded-sql)

```json
{
  "@context" : "http://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Matt Ledger"
  },
  "dateModified" : "2018-06-19T20:32:21.350Z",
  "datePublished" : "2018-06-11T16:38:29Z",
  "headline" : "Using parameterized queries in DataFlex embedded SQL calls",
  "image" : {
    "@type" : "ImageObject",
    "height" : 548,
    "url" : "https://info.mertech.com/hubfs/parameterized%20queries%20%281%29.png",
    "width" : 1024
  },
  "mainEntityOfPage" : "https://www.mertech.com/blog/using-parameterized-queries-dataflex-embedded-sql",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "height" : 60,
      "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
      "width" : 60
    },
    "name" : "Blog"
  }
}
```