A Guide to SQL Bind Variables

Many SQL queries that we write need to use values provided from other areas, such as user input or an application.

There are a couple of ways to include these values in your queries, but there is one preferred way.

The preferred way is to use bind variables.

In this guide, you’ll learn all about bind variables and how to use them in Oracle, SQL Server, MySQL, and PostgreSQL.

Let’s take a look.

Providing Data to a Query

Let’s say you have a case management application, and one page in this application shows all cases assigned to a particular user.

The SQL query to retrieve data from the database could look something like this:

SELECT
case_id,
case_name,
case_status,
created_date
FROM submitted_cases
WHERE assigned_to_id = 1;

This query gets some fields from the submitted_cases table. It filters based on the assigned_to_id, which represents a user.

The specified ID is 1.

However, this value of 1 needs to change depending on which user is accessing the application or which user is being viewed.

One way to do it would be using string concatenation:

SELECT
case_id,
case_name,
case_status,
created_date
FROM submitted_cases
WHERE assigned_to_id = ' + selected_user_id  + ';'

This may be the way we are taught. However, this is not a good approach.

Concatenating strings like this leaves you open to SQL injection attacks. You can read more about them in this guide here.

So, what’s the solution?

We use a concept called “bind variables”.

While you’re here, if you want an easy-to-use list of the main features in SQL for different vendors, get my SQL Cheat Sheets here:

 

What Are Bind Variables?

A bind variable is an SQL feature that lets you turn part of your query into a parameter. You can provide this parameter to the query when you run it, and the query is constructed and executed. Bind variables, often called bind parameters or query parameters, are often used in WHERE clauses to filter data.

Instead of putting the required value into the query, or writing separate queries for each different value, or concatenating strings, you can use bind variables.

Here’s how it works.

You write a query, like the “case management” query we had earlier.

Anywhere where the values are provided to the query, you add a bind variable.

SELECT
case_id,
case_name,
case_status,
created_date
FROM submitted_cases
WHERE assigned_to_id = [your_bind_variable];

When the query is run, the database asks for the value to use instead of the bind variable. The application provides the value, and the database runs the query.

The value in the square brackets is the bind variable. This isn’t the actual syntax of a bind variable – we’ll get to that later in this guide.

Let’s understand why they are useful, and then we’ll look at some bind variable examples in Oracle, SQL Server, MySQL, and PostgreSQL.

 

Benefits of Bind Variables

There are two main benefits of using bind variables: security and performance. Jeff Atwood wrote about this back in 2005 and many others have written about it as well.

 

Bind Variables Increase Security

Using bind variables instead of other options will protect you against SQL Injection attacks.

Using string concatenation, on the other hand, will leave your code vulnerable to SQL Injection attacks. This is true even if you escape special characters or try to sanitise the inputs.

If your query uses a bind variable, the database will treat the input separate from the query, which is a more secure way of handling it.

 

Bind Variables Increase Performance

A query with bind variables in it also performs better than a query that uses concatenation.

This is because of the caching and execution plan features of each database system.

Using string concatenation or other approaches, each time a parameter is provided to a query, the database treats it as a different query. If we look at them, they are pretty much the same.

For example:

SELECT
case_id,
case_name,
case_status,
created_date
FROM submitted_cases
WHERE assigned_to_id = 1;

 

SELECT
case_id,
case_name,
case_status,
created_date
FROM submitted_cases
WHERE assigned_to_id = 2;

These two queries are treated as different queries if they are compiled using string concatenation. This means the execution plan is generated each time, which adds extra overhead to running the query.

If you use bind variables, the queries are treated as the same, even if the inputs are different. This query will be the same each time.

SELECT
case_id,
case_name,
case_status,
created_date
FROM submitted_cases
WHERE assigned_to_id = [your_bind_variable];

More details about how the execution plans are impacted and improved when using bind variables can be found in Markus Winand’s article.

So, bind variables improve performance and security. How can we implement them in our database?

 

Examples of Bind Variables

The concept of a bind variable is the same in different databases. However, the syntax is different.

Here’s a summary:

Database	Syntax
Oracle	:varname
SQL Server	@varname
MySQL	?
PostgreSQL	:varname

Let’s see some examples.

 

Oracle

To use bind variables in an Oracle SQL query, you use the colon character : to indicate a bind variable. You use : then the variable name.

Using our sample query for cases, it would look like this:

SELECT
case_id,
case_name,
case_status,
created_date
FROM submitted_cases
WHERE assigned_to_id = :user_id;

When the query is run by the application, a value needs to be provided to use in place of the user_id.

 

SQL Server

To use a bind variable in SQL Server, you use the @ symbol before the variable name.

Using our sample query for cases, it would look like this:

SELECT
case_id,
case_name,
case_status,
created_date
FROM submitted_cases
WHERE assigned_to_id = @user_id;

The user_id is provided when the query is run.

 

MySQL

A bind variable is added to a MySQL query by using the question mark ? character.

There’s no need to name the variable.

Using our sample query for cases, it would look like this:

SELECT
case_id,
case_name,
case_status,
created_date
FROM submitted_cases
WHERE assigned_to_id = ?;

When you run the query, the values are provided in the order they are specified in the query (for queries with multiple bind variables).

 

PostgreSQL

In PostgreSQL, you can use a bind variable by using the colon : character, then the name of the variable, just like in Oracle.

Using our sample query, it looks like this:

SELECT
case_id,
case_name,
case_status,
created_date
FROM submitted_cases
WHERE assigned_to_id = :user_id;

The user_id value is provided when the query is run.

 

Conclusion

Bind variables (or bind parameters, or query parameters, or parameterised queries) should be the only way that you handle parameters in your SQL queries. They perform better than alternatives and prevent SQL injection.

Hopefully, this article has been helpful to you, whether it’s understanding the concept of a bind variable or using it in your database.

While you’re here, if you want an easy-to-use list of the main features in SQL for different vendors, get my SQL Cheat Sheets here: