Are prepared statements 100% safe against SQL injection?

Question

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?

Answer 1

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].