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 ?

sorry for my previous wrong answer (removed). Seems they works. look at this post : http://stackoverflow.com/questions/1812891/java-escape-string-to-prevent-sql-injection — Heisenbug, Aug 19 '11 at 11:11
Preventing SQL injection by proper escaping is one of the reasons prepared statements were created. — Jacob, Aug 19 '11 at 11:13
possible duplicate of [Can I avoid all SQL-injection attacks by using parameters?](http://stackoverflow.com/questions/3736831/can-i-avoid-all-sql-injection-attacks-by-using-parameters) — Thilo, Aug 19 '11 at 11:15
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? — Thilo, Aug 19 '11 at 11:18

score 4 · Accepted Answer · answered Aug 19 '11 at 11:12

4

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.

answered Aug 19 '11 at 11:12

Thomas

87,414
12
119
157

score 3 · Answer 2 · answered Aug 19 '11 at 11:21

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/

score 0 · Answer 3 · answered Aug 19 '11 at 11:15

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.

Will prepared statements prevent sql injection attacks?

3 Answers3