From the OWASP page on Preventing SQL Injection in Java:
Variables passed as arguments to prepared statements will automatically be escaped by the JDBC driver.
I understand how prepared statements seperate user input to be handled as parameter content and not as part of SQL command. But I stumbled across the quoted sentence above and I am wondering what’s all the escaping about. How does that help prevent the injection attack?
Suppose your statement is
"select * from foo where name = '" + name + "'";
Now if the name variable happens to be O'Reilly, you end up with the following SQL query, which is invalid:
select * from foo where name = 'O'Reilly'
Use a prepared statement instead:
"select * from foo where name = ?"
The driver will then bind the parameter correctly as a string, and the single quote in O'Reilly won't be interpreted as the end of the string started at 'O.
In this simple case, not using a prepared statement will "only" lead to an exception in your app. But if someone uses a name like
' or 1 = 1 or name <> '
the query will become
select * from foo where name = '' or 1 = 1 or name <> ''
and the query will thus load every single row of the table. That's what SQL injection is all about.
