即使在准备好的语句中，SQL注入也是可能的 [英] Is SQL injection possible even on a prepared statement

问题描述

我在Stack Overflow上阅读了很多关于如何通过使用预准备语句来防止SQL注入的文章

I read many articles on Stack Overflow regarding how SQL injection can be prevented by using prepared statements

但即使在准备好的语句上也没有办法进行SQL注入或者100％安全吗？

But is there any way to do SQL injection even on prepared statements or is it 100% safe?

以下是我的java代码

Below is my java code

 String query = "SELECT * FROM Users WHERE username=? and password=?";

 ps=con.prepareStatement(query);  

 ps.setString(1,username);
 ps.setString(2,password);

 rs = ps.executeQuery();

 status = rs.next();

 if(status==true){
.....
}else{
....
}

我尝试了一些sql注入查询，比如

I tried some sql injection queries like

一些输入：

SELECT * FROM users WHERE username = '[e-mail]' OR 1 = 1 LIMIT 1 -- ' ] AND password = md5('1234');

SELECT * FROM users WHERE email = '[e-mail]' AND password = md5('xxx') OR 1 = 1 -- ]');

我还尝试了一些更多的查询，但因为（单引号）'被转义（/' ）没有SQL注入查询似乎工作。

I have also tried with some more queries but as the (single quote)' is escaped(/') none of the SQL injection queries seem to work.

请建议我是否有任何SQL注入查询/技术可以应用于上述代码中的SQL注入。

Kindly suggest me if there are any SQL injection queries/techniques which can be applied to do SQL injection in the above code.

推荐答案

此查询： String query =SELECT * FROM Users WHERE username =？and password = ？; 是安全的，因为无论参数是什么，它仍将作为简单的选择执行。最多，它将结束浏览整个表格。

This query : String query = "SELECT * FROM Users WHERE username=? and password=?"; is safe, because whatever the parameters can be, it will still be executed as a simple select. At most, it will end browsing a whole table.

但是准备好的声明只是一个工具而且（糟糕的）程序员可能仍然滥用它。

But prepared statement is just a tool and (bad) programmers may still misuse it.

让我们看看以下查询

String query = "SELECT id, " + paramName + " FROM Users WHERE username=? and password=?";

其中 paramName 将是参数名称。它与 paramName 一样安全，因为您直接使用变量来构建将由数据库引擎解析的字符串。这里 PreparedStatement 无法帮助，因为JDBC不允许参数化列名。

where paramName would be a parameter name. It is only as safe as paramName is, because you use directly a variable to build the string that will be parsed by the database engine. Here PreparedStatement cannot help because JDBC does not allow to parameterize a column name.

所以这里的规则是：

• 如果可以，请避免使用这样的构造！


• 如果你真的需要它，请仔细检查（正则表达式，允许的字符串列表等等， paramName 不能超出您的预期，因为该控件是针对SQL注入的唯一预防

• avoid such a construct if you can !
• if you really need it, double check (regexes, list of allowed strings, etc.) that paramName cannot be anything other than what you expect because that control is the only prevention against SQL injection

这篇关于即使在准备好的语句中，SQL注入也是可能的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！