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 will be a string or otherwise?

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

Answer 2

Consider two ways of doing the same thing:

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

Or

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

Answer 3

I guess it will be a string. But the input parameters will be sent to the database & appropriate cast/conversions will be applied prior to creating an actual SQL statement.

To give you an example, it might try and see if the CAST/Conversion works.
If it works, it could create a final statement out of it.

   SELECT * From MyTable WHERE param = CAST('10; DROP TABLE Other' AS varchar(30))

Try an example with a SQL statement accepting a numeric parameter.
Now, try passing a string variable (with numeric content that is acceptable as numeric parameter). Does it raise any error?

Now, try passing a string variable (with content that is not acceptable as numeric parameter). See what happens?

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