How does a PreparedStatement avoid or prevent SQL injection?

Question

I know that PreparedStatements avoid/prevent SQL Injection. How does it do that? Will the final form query that is constructed using PreparedStatements be a string or otherwise?

Solution 1

The problem with SQL injection is, that a user input is used as part of the SQL statement. By using prepared statements you can force the user input to be handled as the content of a parameter (and not as a part of the SQL command).

But if you don't use the user input as a parameter for your prepared statement but instead build your SQL command by joining strings together, you are still vulnerable to SQL injections even when using prepared statements.

Solution 2

Consider two ways of doing the same thing:

PreparedStatement stmt = conn.createStatement("INSERT INTO students VALUES('" + user + "')");
stmt.execute();

Or

PreparedStatement stmt = conn.prepareStatement("INSERT INTO student VALUES(?)");
stmt.setString(1, user);
stmt.execute();

If "user" came from user input and the user input was

Robert'); DROP TABLE students; --

Then in the first instance, you'd be hosed. In the second, you'd be safe and Little Bobby Tables would be registered for your school.

Solution 3

To understand how PreparedStatement prevents SQL Injection, we need to understand phases of SQL Query execution.

1. Compilation Phase. 2. Execution Phase.

Whenever SQL server engine receives a query, it has to pass through below phases,

1. Parsing and Normalization Phase: In this phase, Query is checked for syntax and semantics. It checks whether references table and columns used in query exist or not. It also has many other tasks to do, but let's not go in detail.

2. Compilation Phase: In this phase, keywords used in query like select, from, where etc are converted into format understandable by machine. This is the phase where query is interpreted and corresponding action to be taken is decided. It also has many other tasks to do, but let's not go in detail.

3. Query Optimization Plan: In this phase, Decision Tree is created for finding the ways in which query can be executed. It finds out the number of ways in which query can be executed and the cost associated with each way of executing Query. It chooses the best plan for executing a query.

4. Cache: Best plan selected in Query optimization plan is stored in cache, so that whenever next time same query comes in, it doesn't have to pass through Phase 1, Phase 2 and Phase 3 again. When next time query come in, it will be checked directly in Cache and picked up from there to execute.

5. Execution Phase: In this phase, supplied query gets executed and data is returned to user as ResultSet object.

Behaviour of PreparedStatement API on above steps

1. PreparedStatements are not complete SQL queries and contain placeholder(s), which at run time are replaced by actual user-provided data.

2. Whenever any PreparedStatment containing placeholders is passed in to SQL Server engine, It passes through below phases

   1. Parsing and Normalization Phase
   2. Compilation Phase
   3. Query Optimization Plan
   4. Cache (Compiled Query with placeholders are stored in Cache.)

UPDATE user set username=? and password=? WHERE id=?

3. Above query will get parsed, compiled with placeholders as special treatment, optimized and get Cached. Query at this stage is already compiled and converted in machine understandable format. So we can say that Query stored in cache is Pre-Compiled and only placeholders need to be replaced with user-provided data.

4. Now at run-time when user-provided data comes in, Pre-Compiled Query is picked up from Cache and placeholders are replaced with user-provided data.

(Remember, after place holders are replaced with user data, final query is not compiled/interpreted again and SQL Server engine treats user data as pure data and not a SQL that needs to be parsed or compiled again; that is the beauty of PreparedStatement.)

If the query doesn't have to go through compilation phase again, then whatever data replaced on the placeholders are treated as pure data and has no meaning to SQL Server engine and it directly executes the query.

Note: It is the compilation phase after parsing phase, that understands/interprets the query structure and gives meaningful behavior to it. In case of PreparedStatement, query is compiled only once and cached compiled query is picked up all the time to replace user data and execute.

Due to one time compilation feature of PreparedStatement, it is free of SQL Injection attack.

You can get detailed explanation with example here: https://javabypatel.blogspot.com/2015/09/how-prepared-statement-in-java-prevents-sql-injection.html

Solution 4

The SQL used in a PreparedStatement is precompiled on the driver. From that point on, the parameters are sent to the driver as literal values and not executable portions of SQL; thus no SQL can be injected using a parameter. Another beneficial side effect of PreparedStatements (precompilation + sending only parameters) is improved performance when running the statement multiple times even with different values for the parameters (assuming that the driver supports PreparedStatements) as the driver does not have to perform SQL parsing and compilation each time the parameters change.

Solution 5

SQL injection: when user has the chance to input something that could be part of the sql statement

For example:

String query = “INSERT INTO students VALUES(‘” + user + “‘)”

when user input “Robert’); DROP TABLE students; –” as the input, it causes SQL injection

How prepared statement prevents this?

String query = “INSERT INTO students VALUES(‘” + “:name” + “‘)”

parameters.addValue(“name”, user);

=> when user input again “Robert’); DROP TABLE students; –“, the input string is precompiled on the driver as literal values and I guess it may be casted like:

CAST(‘Robert’); DROP TABLE students; –‘ AS varchar(30))

So at the end, the string will be literally inserted as the name to the table.

http://blog.linguiming.com/index.php/2018/01/10/why-prepared-statement-avoids-sql-injection/

View more solutions

Comments

[Brett]

Technically the JDBC spec does not insist that there are no SQL injection flaws. I don't know of any drives that are affected.

[Pavan Manjunath]

@Jayesh I suggest adding your blog contents as an answer here. Most of the answers are just telling the differences b/w dynamic SQL query generation and prepared stmt. They aren't addressing the issue of why prepared statements work better which your blog does.

[Jayesh]

Added as answer, I hope it helps.

[Max]

So, if I got it right, the query in the second example which will be executed would actually be: INSERT INTO student VALUES("Robert'); DROP TABLE students; --") - or at least something like that. Is this true?

[Paul Tomblin]

No, in the FIRST instance, you'd get that statement. In the second one, it would insert "Robert'); DROP TABLE students;--" into the user table.

[Max]

Thats what I meant, in the second example (the "safe" one), the string Robert'); DROP TABLE students; -- will be saved into the field in the student table. Did I write something else? ;)

[Paul Tomblin]

Sorry, nesting quotes is something I try to avoid because of confusion like this. That's why I like PreparedStatements with parameters.

[Max]

Sorry for any confusion and thank you for the explanation! :)

[Brett]

It doesn't have to be implemented like that, and I believe it often isn't.

[tangens]

Sure, but you can still hardcode some or all of your parameters.

[Alkanshel]

Little Bobby Tables. XD Great reference

[david blaine]

Example please - But if you don't use the user input as a parameter for your prepared statement but instead build your SQL command by joining strings together, you are still vulnerable to SQL injections even when using prepared statements.

[beldaz]

Of course, the Bobby Tables trick only works on systems that allow muti-line statements. SQL Server 2008 allows them, but Oracle doesn't. The more frequent problem is corruption of WHERE clauses.

[beldaz]

FWIW Prepared statements aren't a JDBC thing - they are a SQL thing. You can prepare and execute prepared statements from within a SQL console. PreparedStatement just supports them from within JDBC.

[beldaz]

Actually the SQL is typically precompiled on the database. That is, an execution plan is prepared on the database. When you execute the query the plan is executed with those parameters. The extra benefit is that the same statement can be executed with different parameters without the query processor having to compile a new plan each time.

[Dheeraj Joshi]

nice explanation

[jouell]

Literally the most complete answer on the HOW it works piece

[Héctor Álvarez]

If I'm not mistaken, The part CAST(‘Robert’); from CAST(‘Robert’); DROP TABLE students; –‘ AS varchar(30)) would break, then it would proceed to drop the table if that was the case. It does stop the injection, so I believe the example is just not complete enough to explain the scenario.

[Wild Pottok]

Thank you for pointing out the importance of using parameter binding, rather than PreparedStatement alone. Your reply, however, seems to imply that using a dedicated API is necessary to protect against SQL injection. Since this is not the case, and using PreparedStatement with parameter binding also works, would you care to reformulate?

[Unknown]

It was much helpful. Thanks for the detailed explanation.

[Julien Maret]

Same answer as the selected answer with less precisions.