SQL injection vulnerability thru preparedStatement

An Statement like this

String query = "SELECT * FROM users WHERE userid ='"+ userid + "'" + " AND password='" + password + "'";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(query);

is eligible for SQL injection. But how does PreparedStatement helps in preventing SQL injection? Consider the following scenario:

PreparedStatement stmt = connection.prepareStatement("SELECT * FROM users WHERE userid=? AND password=?");
stmt.setString(1, userid);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();

what if someone enters the userId = "abc" and password = "1=1", as this will also be considered as valid String...

Jon Skeet
people
quotationmark

The exact way in which this protects you is up to the database, but there are two obvious options:

  • The database driver could perform string interpolation and generate a SQL statement which ensures that all parameters are properly escaped.
  • The database driver could pass the SQL exactly as you've specified it to the database, and pass the values for the parameters through an entirely separate channel, which doesn't need any escaping, because it just contains the values.

The latter is a more sensible solution in my view, as it allows the database to cache query plans really simply, recognizing that two queries are exactly the same other than the parameter values. I'd expect any modern database to support this in its native communication protocol.

people

See more on this question at Stackoverflow