How does a PreparedStatement avoid or prevent SQL injection?

java sql jdbc prepared-statement sql-injection
Prabhu R picture Prabhu R · Oct 17, 2009 · Viewed 109.1k times · Source

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

Paul Tomblin picture Paul Tomblin · Oct 17, 2009

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.

Related questions

How can I get the SQL of a PreparedStatement?

I have a general Java method with the following method signature: private static ResultSet runSQLResultSet(String sql, Object... queryParams) It opens a connection, builds a PreparedStatement using the sql statement and the parameters in the queryParams variable length array, runs …

Read More
Using setDate in PreparedStatement

In order to make our code more standard, we were asked to change all the places where we hardcoded our SQL variables to prepared statements and bind the variables instead. I am however facing a problem with the setDate(). Here …

Read More
Variable column names using prepared statements

I was wondering if there was anyway to specify returned column names using prepared statements. I am using MySQL and Java. When I try it: String columnNames="d,e,f"; //Actually from the user... String name = "some_table"; //From user... …

Read More