Are prepared statements 100% safe against SQL injection?

  • 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?

    Kernel, database, and other components are not invulnerable to all possible attacks. Security teams contracted by finance, government, infrastructure, military, and critical business systems frequently require multiple security layers. Peruse a few bug lists and find holes that were patched ... after unspecified damage was done. To assume they "got'em all" is to deny statistical realities of software. It is not possible, with the finite test suite of a component, to test all possible attacks to the overall system containing that component. Safety is not an absolute 0.0 or 1.0 probability.

  • dr jimbob

    dr jimbob Correct answer

    9 years ago

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

    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!

    Exactly what I'd figured, then. As close to 100% as you can guarantee, ignoring implementation exploits.

    @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).

    Sorry, I'm a bit dense today. How does Lateral SQL Injection (http://www.databasesecurity.com/dbsec/lateral-sql-injection.pdf) fit into this?

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

    @drjimbob That might actually be one of the most informative comments I've ever read on this site. Now, I'm off to go read about the implementation of SQL parsers...

    @Polynomial, the existence of a kernel, database, or other bug is always > 0.0. (BruceEdiger's comment that SQL injection CAN'T HAPPEN is actually counterproductive. Peruse the bug list of the open source software in question and you will find security holes that have been patched. The bug report will not mention the attacks that successfully destroyed or acquired critical data prior to the release of the bug fix. Experienced security teams require at least two levels of defense for critical systems. Validation and/or sanitation is a common (and prudent) first level.

    I'd like to add, if you're using stored procedures for all of your sql, wouldn't the execution plans already be optimized? So if you're using good database practices, prepared statements wouldn't really reduce your query's efficiency by much.

    "With bound parameters, your SQL query execution plan is figured out..." - no its not. PHP's MySQL extension uses client side binding. Oracle (by default) will delay plan generation until it sees the parameters. Prepared statements are good for security if they incorporate privilege seperation, or some other means to close off bypass of the prepared statement.

    @symcbean - Agreed. The answer slightly oversimplified things by stating the RDBMS prepares the execution plan before looking at parameters. This is often done, but not always. However, prepared statements/bound parameters should still be safe as the DB has enough information when constructing the query plan to clearly delineate between the SQL statement and variables (that an attacker could try to inject SQL commands into, but the DB knows to interpret as data not SQL keywords).

License under CC-BY-SA with attribution


Content dated before 7/24/2021 11:53 AM