带预备语句的SQL注入? [英] SQL injections with prepared statements?

问题描述

如果我没记错的话，我认为Jeff在Stack Overflow播客中提到了SQL预准备语句中可能存在的弱点.我想知道他指的是哪种弱点?可能只是使用不当，还是更险恶? >

根据我的记忆，播客并没有深入探讨该主题，只是一个旁白.

解决方案

我认为他说的是，当您使用Prepared Statements时，SQL Server可以缓存您的查询执行计划，因此，即使您修改了一些参数在正在执行的查询上，服务器可能会选择错误的(可能是已缓存的)执行计划，而执行计划会非常糟糕.

他还提到了SQL Server 2008的一项新功能，该功能迫使引擎重新评估他用来克服这种情况的执行计划.

对于准备好的语句，我唯一的问题就是这个.考虑以下Java代码:

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

在这里，您希望，如果表(名称)上有索引，则查询计划将使用该索引.好吧，不会.因为PraparedStatement必须预编译并预料到最坏的情况，例如'％PATTERN％'.因此，它不会优化.我花了一些时间才弄清楚这一点.这导致我的数据库受苦. :(

希望有帮助.

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.

解决方案

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.

这篇关于带预备语句的SQL注入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！