Is CallableStatement really immune to SQL injection?

Question

We have a Java application that communicates with multiple SQL Server databases on the same box. The number of and names of these databases vary. By and large, we use almost exclusively stored procedures with CallableStatement to access the databases. We are extremely good about avoiding SQL injection and using bind variables.

The only area of concern is that the database name itself is concatenated into the SQL that we pass to the CallableStatement as such:

"{call [" + dbName + ".dbo." + procName + "(?, ?, ?)}"

procName is hard-coded into child classes using a template method pattern, so that string is guaranteed safe.

dbName is defined externally. I have tried setting dbName to all sorts of patterns to escape the syntax and exploit this on my development environment and have been unsuccessful.

I have set it to the following to produce the following SQL calls (table and proc names changed to protect the innocent):

securitytest].nx_proc()};delete from poor_victim_table;

becomes

{call [securitytest].nx_proc()};delete from poor_victim_table;].dbo.proper_proc_name()}

and

securitytest].nx_proc()};exec('delete from poor_victim_table');

becomes

{call [securitytest].nx_proc()};exec('delete from poor_victim_table');].dbo.proper_proc_name(?,?,?,?,?,?,?)}

Results in Incorrect syntax near ')'. and poor_victim_table still has rows. I have used truncate table, drop table and drop database and when they didn't work, I switched to simple delete to rule out security settings.

If I use a proc that takes bind parameters, I always get a mismatch between the number of expected parameters and supplied parameters such as The index 1 is out of range..

securitytest]};exec('delete from poor_victim_table');

becomes

{call [securitytest]};exec('delete from poor_victim_table');].dbo.proper_proc_name(?,?,?,?,?,?,?)}

All roads seem to lead to a runtime error and the SQL does not execute. Of course, this is great. But I want to make sure it's failing because it cannot succeed and not failing because I am failing to try the right combination.

The popular opinion / urban myth is that using a stored procedure makes you immune to SQL injection, but I prefer to not trust absolute statements like that when it comes to security.

After researching this for a while, the best I came up with is this stackoverflow question: SQL injection - no danger on stored procedure call (on iSeries)?. It seems to support using CallableStatement because it protects you from SQL injection unless your proc code itself makes dynamic SQL out of an input parameter.

So, my question to the community is, assuming the SQL code in a proc is safe, does using CallableStatement in JDBC really prevent SQL injection? Or does the SQL Server driver parse the string in a way that prevents it, but other drivers may not? Or am I not trying hard enough?

If it is safe, how is that guarantee made? Is it due to the abstract syntax of using { call blah(?) } which is not real SQL, but gets translated to SQL?

Answer 1

You should be safe, but just like you, i wouldn't trust that, especially if you're interfacing to different databases using different JDBC drivers etc.

If i were you, before the statement you posted, i'd make sure to check if the dbName contains anything but letters, digits, and possibly an underscore. This should allow all valid dbNames, and prevent all kinds of messing with it.

Answer 2

Check your db connection url, i think that is refer static db, so if you write db name in callable statement this will generate problem whenever db name changed, your code like die (most places to change), so don't use db name in query but you can create different objects for different db connection or different helper classes for that.

Answer 3

The popular opinion / urban myth is that using a stored procedure makes you immune to SQL injection, but I prefer to not trust absolute statements like that when it comes to security.

And you do well in not trusting such a blanket statement. However, the statement needs to be qualified/qualifyable.

See, a stored procedure is immune to SQL injection when it is well written. It allows you to control what kind of SQL statements are permitted. You can configure your production database so that it doesn't allow execution of DMLs, only stored procedure (thus saving you from someone executing a horrible cartesian joint, by accident or on purpose.)

But beyond a stored procedure being well written, the caller has also the onus to validate and sanitize input, and JDBC provides you a way to do that via "bind" parameters.

A CallableStatement inherits from PreparedStatement, which provides methods for binding parameters. The binding methods escape the incoming parameter values so as to make injection pretty much impossible.

Think of stored procedures (and callable and prepared statements) as a hammer. You can use it well to hammer a nail or to crush your thumb.