SQL injections with prepared statements?

Question

If I remember correctly, I think Jeff has mentioned in the Stack Overflow podcast a possible weakness in SQL prepared statements. I'm wondering what kind(s) of weakness(es) did he refer to? Was it possibly just about inappropriate usage thereof, or something more sinister?

The podcast, to my remembering, didn't go deeper into the subject, it was just a pass-by-remark.

Answer 1

I think what he said was that, when you use Prepared Statements, SQL server could cache your query execution plan, so, even if you modify some of the parameters on the executing query, the server could pick the wrong (probably cached) execution plan that would perform very badly.

He also mentioned a new feature of SQL Server 2008 to force the engine to re-evaluate execution plans that he used to overcome this situation.

With Prepared Statements, the only issue I have is this. Consider the following Java Code:

String sql = "select * from table where name like ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "PATTERN%");
ResultSet rs = pstmt.executeQuery();

Here you would expect that, if you have an index on table(name) it will be used by the query plan. Well, it won't. Because PraparedStatement must precompile and expect the worst: '%PATTERN%', for example. So, it won't optimize. It took me a while to figure this one out. It was causing my database to suffer. :(

Hope it helps.

Answer 2

Beyond the normal sql injection (what we might call first order) attack there are secondary levels. For example its not uncommon to have stored procedures use string concatenation to build a query which it then executes. If result of retrieved field values are included in such a concatenation then there is a danger of injection.