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?

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

EDIT:  Responding to two criticisms (that are a tad too long for comments):

First, as others noted yes every relational database supporting prepared statements and bound parameters doesn't necessarily pre-compile the prepared statement without looking at the value of the bound parameters.  Many databases customarily do this, but its also possible for databases to look at the values of the bound parameters when figuring out the execution plan.  This isn't a problem, as the structure of the prepared statement with separated bound parameters, makes it easy for the database to cleanly differentiate the SQL statement (including SQL keywords) from data in bound parameters (where nothing in a bound parameter will be interpreted as an SQL keyword).  This is not possible when constructing SQL statements from string concatenation where variables and SQL keywords would get intermixed.

Second, as the other answer points out, using bound parameters when calling an SQL statement at one point in a program will safely prevent SQL injection when making that top-level call.  However, if you have SQL injection vulnerabilities elsewhere in the application (e.g., in user-defined functions you stored and run in your database that you unsafely wrote to construct SQL queries by string concatenation).

For example, if in your application you wrote pseudo-code like:

sql_stmt = "SELECT create_new_user(?, ?)"
params = (email_str, hashed_pw_str)
db_conn.execute_with_params(sql_stmt, params)


There can be no SQL injection when running this SQL statement at the application level.  However if the user-defined database function was written unsafely (using PL/pgSQL syntax):

CREATE FUNCTION create_new_user(email_str, hashed_pw_str) RETURNS VOID AS
$$
DECLARE
   sql_str TEXT;
BEGIN
     sql_str := 'INSERT INTO users VALUES (' || email_str || ', ' || hashed_pw_str || ');'
     EXECUTE sql_str;
END;
$$
LANGUAGE plpgsql;


then you would be vulnerable to SQL injection attacks, because it executes an SQL statement constructed via string concatenation that mixes the SQL statement with strings containing the values of user defined variables.

That said unless you were trying to be unsafe (constructing SQL statements via string concatenation), it would be more natural to write the user-defined in a safe way like:

CREATE FUNCTION create_new_user(email_str, hashed_pw_str) RETURNS VOID AS
$$
BEGIN
     INSERT INTO users VALUES (email_str, hashed_pw_str);
END;
$$
LANGUAGE plpgsql;


Further, if you really felt the need to compose an SQL statement from a string in a user defined function, you can still separate data variables from the SQL statement in the same way as stored_procedures/bound parameters even within a user defined function.  For example in PL/pgSQL:

CREATE FUNCTION create_new_user(email_str, hashed_pw_str) RETURNS VOID AS
$$
DECLARE
   sql_str TEXT;
BEGIN
     sql_str := 'INSERT INTO users VALUES($1, $2)'
     EXECUTE sql_str USING email_str, hashed_pw_str;
END;
$$
LANGUAGE plpgsql;


So using prepared statements is safe from SQL injection, as long as you aren't just doing unsafe things elsewhere (that is constructing SQL statements by string concatenation).
