(Here’s a re-post from my other blog.)No, I’m not going to say the obvious: use placeholders or positional arguments to avoid SQL injection. All developers should know that. Except that it’s not entirely true. Yes, you read it right. Let me explain…Placeholders are most commonly used in conjunction with prepared SQL statements, and the primary purpose of this is not to avoid SQL injection, but to make applications more efficient.

Take the following example:

PreparedStatement stmt = connection.prepareStatement("insert into foo values (?,?,?)");

This might creates an object, stmt, that can be called multiple times with different parameters. Depending on the implementation of the database driver, it might do some special pre-compiling of the statement, making your code not only cleaner but also more efficient. F.ex.:

while( more data ) {
   Data[] newData = readData();
   stmt.execute(newData[0],newData[1],newData[2);
}

Given an API without placeholders, the alternative might be something as follows:

while( more data ) {
  Data[] newData = readData();
  connection.executeStatement("insert into foo values ("+ connection.escape(newData[0])
    +","+ connection.escape(newData[1]) +","+ connection.escape(newData[0]) +")" );
}

The first example is cleaner, and might be more efficient as well. String concatenation, like in the latter example, opens up for SQL injection if parameters are not escaped properly, as any good developer knows. But how can placeholders be subject to SQL injection?Well, at my previous job I was asked if our application was vulnerable to SQL injection, and my immediate answer was no… We were using placeholders, after all.

However, I started digging around a little deeper, searching for SQL injection problems related to the JDBC drivers we used, and I actually found a bug in the PostgreSQL JDBC driver that made it vulnerable to SQL injection in a very specific instance.

Granted, that was a very specific instance, and not a big deal. But it got worse … Another JDBC driver I looked at, implemented placeholders by inserting parameters into the SQL string! I don’t remember which JDBC driver it was, but I believe it was some early version for some open source RDBMS. I doubt many do it like this, though…

The point here, is that database drivers are made by developers like you and me, and they can make mistakes like everyone else. Although I would assume that most drivers are good enough, they can contain bugs as any other piece of software can. Should you panic? No. Should you trust database drivers to do the right thing? Probably, but there are no guarantees. Good luck!