Parameterized Queries in SQL – A Guide

Preface

Almost every time we hear about an attack being mounted on an application, multiple questions arise – were any other parts of the application vulnerable? What data was stolen from the database? Are the clients of the company informed of the data breach? Did the company reset the passwords of their users?

These are great questions – answers to them need to be provided. The main question, however, is the attack vector itself – what do we know about the attack?

In most cases, attacks involving identity theft are mounted through injection attacks – attacks that’ve been there for as long as the Internet has been a thing, however, it’s still on the top 3 most prevalent attack vectors for web applications according to OWASP.

Injection attacks can be easily and reliably thwarted by employing parameterized queries.

What Are Parameterized Queries?

In short, parameterized queries are just that – they’re queries that specify parameters instead of values. For example, a query like so:

Would turn into a query like so (note the two parameters):

The purpose of such queries are to “disconnect” the query from the values – if a value is malicious, it will be executed separately from the query itself and won’t cause any harm to the application.

Parameterized Queries Explained

The purpose of parameterized queries is to provide parameters, then connect values to those parameters, and then execute the query. Queries executed in such a way will not be susceptible to injection attacks because the query and the parameters will be sent over for execution separately.

Do note that parameters cannot be set using SQL alone – parameterized queries only work when other programming languages (PHP, ASP.NET, etc.) are involved and as such, require a couple lines of code to be executed successfully. Here’s an example:

As you can see, the aforementioned query has two parameters attached to it: username and email. The concept is simple – we attach the username provided by the user to the “username” parameter and the email to the “email” parameter, then execute the query. The same works in other languages too, here’s an example for Java:

It’s all pretty self-explanatory too: we attach a string to the query via a parameter, then execute the query.

When and How to Parameterize

Now that you know what parameterized (or prepared) queries are in SQL, you probably wonder how and how to use parameterization to achieve the best results possible. It’s all pretty simple – whenever you provide user input to a query, pass it via parameters. It’s all there really is to it – the execution process differs depending on what programming language is in use (see examples above), but the concept is always the same: pass user input to the query via parameters and avoid providing it in a standalone fashion, and you will avoid injection attacks.

Does Parameterization Always Help? Corner-Cases

Many developers believe that to avoid injection attacks you must parameterize your queries and you will be good to go. In most cases, that’s true, but as demonstrated in a StackOverflow answer from 2012, there are specific corner cases where parameterization isn’t helpful and would still let an attacker mount a SQL injection attack. The example is based off of an 2006 example of the SQL injection while bypassing the mysql_real_escape_string function. It works like this:

1. We set the character set on the server to gbk by issuing a SET NAMES gbk query.
2. We craft a payload such that if we issue an addslashes() function on it that would insert an \ character before the “’” character, so the payload would need to start with the sequence 0xbf27.
3. Since the server things we’re using a gbk character string and the client thinks we’re still using the default one (latin1), the function mysql_real_escape_string inserts a backslash in our filtered query.

Such an approach works because the function mysql_real_escape_string knows the character set and the addslashes() function in PHP doesn’t – to avoid this, we need to use an up-to-date version of MySQL and an updated version of PHP, avoid using character sets we don’t know for connection encoding, or enable the NO_BACKSLASH_ESCAPES mode in SQL.

The Role of DbVisualizer

The role of proper SQL clients such as the one built by DbVisualizer shouldn’t be discounted either – the role your databases act within your application is huge, and they must be cared for appropriately. When choosing a SQL client, remember that they should help you take care of all three aspects related to your database – performance, availability, and security. DbVisualizer was built for navigating complecity – it has all of the tools you would need to manage all database management systems with the help of state-of-the art database technologies. From table referencing to letting you set up custom settings inside of the tool itself, DbVisualizer has everything:

And while SQL clients won’t help you parameterize your queries, they can certainly help build them – grab a free trial of DbVisualizer and figure that out yourself!

Summary

Parameterized queries in SQL are a great way to avoid injection attacks hitting your database – by parameterized queries can help separate user input from the query itself and make injection attacks unfeasible for the attacker.

Parameterized queries aren’t everything, though – to take proper care of your databases from top to bottom, consider employing SQL clients like the one built by DbVisualizer.

FAQs

What are Parameterized Queries?

Parameterized queries are SQL queries that accept parameters within themselves – those parameters help separate user input from the query itself and in turn, protect web applications from injection attacks.

How to Parameterize?

Parameterization is easy – simply replace user input with parameters, then assign values to those parameters.

Does Parameterization Work the Same Across All Programming Languages?

Yes, parameterization works the same way no matter what kind of programming language is being used. It may look a little different depending on the language, but the concept is the same – a couple of examples are provided above within this blog.

Why Should I Use SQL Clients?

Parameterization is only one piece of the puzzle – by using SQL clients such as the one built by DbVisualizer, your company can easily manage and work with all kinds of database management systems and ensure their availability, security, and performance without sacrificing the time of your developers or DBAs.