Take the 2-minute tour ×
Information Security Stack Exchange is a question and answer site for Information security professionals. It's 100% free, no registration required.

Are prepared statements actually 100% safe against SQL injection, assuming all user-provided parameters are passed as query bound parameters?

Whenever I see people using the old mysql_ functions on StackOverflow (which is, sadly, way too frequently) I generally tell people that prepared statements are the Chuck Norris (or Jon Skeet) of SQL injection security measures.

However, I've never actually seen any documentation that categorically states "this is 100% safe". My understanding of them is that they separate the query language and parameters all the way to the front door of the server, which then treats them as separate entities.

Am I correct in this assumption?

share|improve this question

2 Answers 2

up vote 18 down vote accepted

Guarantee of 100% safe from SQL injection? Not going to get it (from me).

In principle, your database (or library in your language that is interacting with the db) could implement prepared statements with bound parameters in an unsafe way susceptible to some sort of advanced attack, say exploiting buffer overflows or having null-terminating characters in user-provided strings, etc. (You could argue that these types of attacks should not be called SQL injection as they are fundamentally different; but that's just semantics).

I have never heard of any of these attacks on prepared statements on real databases in the field and strongly suggest using bound parameters to prevent SQL injection. Without bound parameters or input sanitation, its trivial to do SQL injection. With only input sanitation, its quite often possible to find an obscure loophole around the sanitation.

With bound parameters, your SQL query execution plan is figured out ahead of time without relying on user input, which should make SQL injection not possible (as any inserted quotes, comment symbols, etc are only inserted within the already compiled SQL statement).

The only argument against using prepared statements is you want your database to optimize your execution plans depending on the actual query. Most databases when given the full query are smart enough to do an optimal execution plan; e.g., if the query returns a large percentage of the table, it would want to walk through the entire table to find matches; while if its only going to get a few records you may do an index based search [1].

share|improve this answer
    
Slightly off the security side, but SQL server provides option (recompile) to force a new compilation and it will use the current parameter values when choosing a plan. Although I don't know for sure, I expect other RDBMS provide a similar function. So no excuse for not using parameterised queries! –  pipTheGeek May 21 '12 at 17:52
    
Exactly what I'd figured, then. As close to 100% as you can guarantee, ignoring implementation exploits. –  Polynomial May 21 '12 at 18:13
    
@pipTheGeek - Interesting. I use postgresql as my rdbms of choice (free software) and cannot find this option, and MySQL specifically says they do not support 'WITH RECOMPILE'. I think you may be able to do something similar in postgres if you first wrote a stored procedure and then did a prepared statement calling it--but not sure it would recompile execution plan (would need to check). –  dr jimbob May 21 '12 at 18:34
    
Sorry, I'm a bit dense today. How does Lateral SQL Injection (databasesecurity.com/dbsec/lateral-sql-injection.pdf) fit into this? –  Bruce Ediger May 22 '12 at 15:45
3  
@BruceEdiger - Lateral SQL injection in your link can't happen if you use prepared statements with bound parameters. In pseudocode, if one defines sql_str = "SELECT name FROM all_objects WHERE created = ?" and then did prepared_stmt = db_cursor.prepare(sql_str), prepared_stmt.execute_with_param(SYSDATE), you can't use SYSDATE to inject code to alter the execution plan as the execution plan was determined in the prepare() step independent of SYSDATE's value. (Unless there's another vulnerability). The lateral flaw is doing naive string processing to construct an SQL statement. –  dr jimbob May 22 '12 at 16:10

100% safe? Not even close. Bound parameters (prepared statement-wise or otherwise) effectively can prevent, 100%, one class of SQL injection vulnerability (assuming no db bugs and a sane implementation). In no way do they prevent other classes. Note that PostgreSQL (my db of choice) has an option to bind parameters to ad hoc statements which saves a round trip regarding prepared statements if you don't need certain features of these.

You have to figure that many large, complex databases are programs in themselves. The complexity of these programs varies quite a bit, and SQL injection is something that has to be looked out for inside programming routines. Such routines include triggers, user-defined functions, stored procedures, and the like. It is not always obvious how these things interact from an application level as many good dba's provide some degree of abstraction between the application access level and the storage level.

With bound parameters, the query tree is parsed, then, in PostgreSQL at least, the data is looked at in order to plan. The plan is executed. With prepared statements, the plan is saved so you can re-execute the same plan with different data over and over (this may or may not be what you want). But the point is that with bound parameters, a parameter cannot inject anything into the parse tree. So this class of SQL injection issue is properly taken care of.

But now we need to log who writes what to a table, so we add triggers and user-defined functions to encapsulate the logic of these triggers. These pose new issues. If you have any dynamic SQL in these, then you have to worry about SQL injection there. The tables they write to may have triggers of their own, and so forth. Similarly a function call might invoke another query which might invoke another function call and so forth. Each of these is independently planned of the main tree.

What this means is that if I run a query with a bound parameter like foo'; drop user postgres; -- then it cannot directly implicate the top-level query tree and cause it to add another command to drop the postgres user. However, if this query calls another function directly or not, it is possible that somewhere down the line, a function will be vulnerable and the postgres user will be dropped. The bound parameters offered no protection to secondary queries. Those secondary queries need to make sure they use bound parameters too to the extent possible, and where not, will need to use appropriate quoting routines.

The rabbit hole goes deep.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.