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.

If your DBA was correct (which they aren't), a SQL injection attack *attempt* against your query *might be something like* `FNAME=SMITH' OR CUST_ID=10040 OR 'X'='X` — Jeffrey Kemp, Apr 06 '17 at 04:07
@AlexPoole, my DBA says that he read this somewhere, but has to find specifically where he read it. — FearlessFuture, Apr 06 '17 at 15:30

score 7 · Answer 1 · answered Apr 05 '17 at 15:19

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.

Bill Karwin · Answer 2 · 2017-04-05T15:38:13.180

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

While this is true and good information, I'm not quite sure how it relates to his specific question of "How is it possible?" This does a good job explaining "When *can't* I use parameters?", but that wasn't what was asked. — Siyual, Apr 05 '17 at 15:18
@Siyual, point taken. I added an intro paragraph at the top of my answer. Does that help? — Bill Karwin, Apr 05 '17 at 15:38

score 1 · Answer 3 · edited May 23 '17 at 12:02

Speaking of regular queries, there are two known edge cases for Mysql/PDO both unrelated to Oracle.

Speaking of the stored procedures, there of course could be a problem in the stored procedure itself but that I would consider as either an irrelevant case or inconsistent use of prepared statements.

So most likely your DBA simply heard some rumor somewhere but don't have a good idea on what he is talking about.

Marmite Bomber · Accepted Answer · 2022-01-26T21:08:25.003

0

Strictly speaking is SQL injection indeed possible when using bind variables. The query below use BV and can be subject of SQL injection in case the parameter column_list is manipulated.

 'select' + column_list + ' from T where col :1'

So what is missing is using bind variables in a statical query and avoiding statement concatination using parameters e.g. for column list.

edited Jan 26 '22 at 21:08

answered Apr 05 '17 at 19:50

Marmite Bomber

19,886
4
26
53

1

This is what my DBA was saying. – FearlessFuture Apr 24 '17 at 18:32

score -3 · Answer 5 · answered Apr 05 '17 at 15:20

-3

If you want a wild speculation of how such a thing could be possible, here's one:

You insert into a table, using bind parameters
DBA has created an insert trigger on that table
That trigger takes the inserted value and uses it in an execute immediate

... or basically any other code that involves using dynamic SQL with the query string made up of user-supplied data. Even bind parameters won't help you there.

answered Apr 05 '17 at 15:20

Jiri Tousek

12,211
5
29
43

1

Can you give a code example for this case, since bind variables can only be used for constant values? Also, how is this affected by SQL injection, since the process you mentioned is pre-existing. – FearlessFuture Apr 05 '17 at 15:37
1

You mean if the inserted value is itself a complete SQL statement, which is then executed dynamically? That's not really SQL injection, if so... – Alex Poole Apr 05 '17 at 15:58
`execute immediate` is an extreme form of SQL injection. Of course, when you avoid SQL injection in one place, but still have it somewhere else, you keep having the vulnerability. This is not really what the question is about. – trincot Jun 30 '22 at 18:06

How is SQL Injection Possible When Using Bind Variables?

5 Answers5