Will prepared statements prevent sql injection attacks?

Question

Consider a hypothetical case where I have to retrieve some details from the database based on the userId and the sample code is given below

private String getpassword(String username) {
PreparedStatement statement = null;
ResultSet resultSet = null;
Connection conn = null;
final String selectQuery = "SELECT password FROM " + "users WHERE username=?";
try {
 conn = dataSource.getConnection();
 statement = conn.prepareStatement(selectQuery);
 statement.setString(1, username);
 resultSet = statement.executeQuery();
 if (resultSet.next()) {
 }
} catch (SQLException e) {
 // log it
}
//return
}

This username is actually coming from the client side and the user can tamper the data (if he wants to). So will preparedStatements prevent from accepting quotes and send only the filtered form of SQL to the database.

For eg: I can provide username= ' or 1=1 and it will be a valid SQL statement. But if the driver escapes the quotes from user inputs, then they would prevent sql injections.

What is the general understanding of the same ?

• Heisenbug: sorry for my previous wrong answer (removed). Seems they works. look at this post : stackoverflow.com/questions/1812891/…
• Jacob: Preventing SQL injection by proper escaping is one of the reasons prepared statements were created.
• Thilo: possible duplicate of Can I avoid all SQL-injection attacks by using parameters?
• Thilo: Has nothing to do with your question, but that password is actually just a salted hash, and you have finally blocks in place to close Connection, Statement, and ResultSet, right?
• nibin012: its a hypothetical method.. :)

Answer 1

According to this, yes: http://en.wikipedia.org/wiki/SQL_injection

In that case the statement is already compiled and injected code would not be interpreted (and thus not be executed) again.

Answer 2

Using parameters and a prepared statement does prevent SQL injection attacks, i.e. passing "' or 1=1" will not result in unintended data returned. However, if at any stage you display the data back to the user, you need to ensure that the HTML that is produced cannot be affected by the user input that comes back from the database

For example, if your web page displays:

Hello, ${username}

if the username is

<script>alert('I could have been more malicious')</script>

can lead to XSS or CSRF attacks.

N.B.

Hello, ${fn:escapeXml(username)}

would be safer (JSP code).

A good reference is:

• http://www.sans.org/top25-software-errors/

Answer 3

The username and the query will be sent to the database as two separate things, and the database-engine will be responsible for putting the two back together. The query is already compiled by the engine by the time the parameter is read, so the two are never considered part of the same statement.