How is SQL Injection Possible When Using Bind Variables?

Question

How is SQL injection possible when using bind variables?

My DBA says that using bind variables doesn't fully secure one against SQL injection, but I can't find out how this can be the case, since bind variables especially for strings will usually force the injected SQL to be a string in a WHERE clause.

Example:

SELECT CUST_ID  
FROM CUST.CUSTOMER 
WHERE FIRST_NAME=:FNAME;

If FNAME="SELECT FNMAME WHERE CUST_ID=10040", the database will run the following query

SELECT CUST_ID 
FROM CUST.CUSTOMER 
WHERE FIRST_NAME="SELECT FNMAME WHERE CUST_ID=10040";

which will return 0 rows.

I scoured the Internet for an answer to this question and even this site, but I couldn't find it.

Thanks again.

Answer 1

You can be assured that SQL injection is not possible if you prepare your statement, binding all parameters to it. This is because this way of working does not inject anything into the SQL, so it is impossible to have SQL injection.

First the SQL statement is compiled, and then the parameters are passed to the database engine. At that time the SQL text plays no role any more, but the compiled version of it. The engine knows how to deal with these two pieces of information: the compiled statement, and the parameters. It does not inject the parameters into some SQL, which at that point does not play a role any more: it has already been compiled.

Answer 2

It's not possible for query parameters to "break" and allow SQL injection in the parameterized query. But it's true that parameter binding does not provide a solution for all possible dynamic queries. Perhaps that's what your DBA meant (why don't you ask her?).

Consider this query:

SELECT CUST_ID FROM CUST.CUSTOMER ORDER BY :COLUMNNAME :DIRECTION

See, we may be coding a user interface that allows the user to pick which column to sort by, and the direction, ascending versus descending.

But you can't use bound parameters this way. Bound parameters can be used to substitute for a constant value in an SQL expression, but not table names, column names, SQL keywords like ASC/DESC, or other parts of syntax. Only constant values, like quoted strings, quoted date literals, or numeric literals.

So how can you use bound parameters to protect other parts of your query that need to be dynamic?

You can't!

Other parts of your query, like identifiers, SQL keywords, or expressions, must be fixed in your query string before you prepare the query. Which means you can't use parameter placeholders for them.

There are techniques like whitelisting to make sure the variable you interpolate in your SQL query string are among a set of known values, and there's a standard way to quote identifiers, but those other methods aren't the same as bound parameters.

You might like my presentation SQL Injection Myths and Fallacies. Here's a recording of me presenting it as a webinar: https://www.youtube.com/watch?v=VldxqTejybk