在不使用Prepared语句的情况下摆脱SQL注入 [英] Getting rid of SQL injection without using Prepared statement
问题描述
我的应用程序有很多使用Statement编写的JDBC查询，因此容易受到SQL注入的攻击.不幸的是，此应用程序是在大约10年前开发的，开发人员可能不了解Prepared语句.
My application has a lot of JDBC queries written using Statement and is thus vulnerable to SQL injection. Unfortunately this application was developed around 10 years back and probably developers didn't know about Prepared statement.
我知道解决此问题的最佳方法是使用PreparedStatement，但是在整个应用程序中进行转换非常繁琐.此外，为SQL注入编写任何形式的Patten匹配都可能非常棘手，因为诸如Select，insert，union等关键字都是英文单词，这些单词也可能出现在用户键入的文本字段中.
I know that the best way to solve this problem is to use PreparedStatement but it is very tedious to convert it throughout the application. Also, writing any sort of Patten matching for SQL injection could be very tricky as keywords like Select, insert, union, etc are all English words which could also appear in a text field keyed in by the user.
有没有一种更聪明的方法来避免在不使用Prepared语句的情况下进行SQL注入.如果这是一个重复的问题，请给我一个答案很好的问题链接.感谢您的帮助.
Is there a smarter way to avoid SQL injection without using Prepared statement. If this is a duplicate question, please give me the link to question which has a good answer. Thanks for the help.
推荐答案
H.不幸的是，它几乎总是取决于货币和管理决策的适当性，但是非常乏味"通常不被认为是有效的 engineering 问题-它只是适当重构代码的借口
Hah. Unfortunately, it almost always depends on the monetary and managerial decision for what's appropriate, but "it is very tedious" is not generally considered a valid engineering concern -- it is but an excuse to appropriately refactor the code.
与此同时，该问题要求使用非PreparedStatement方法:简而言之，如果您无法将工作卸载到库中(例如，准备好的语句)，那么唯一的其他方法是您自己为每个注入的"项目完成此操作.无论哪种方式，都必须执行检查输入的工作.唯一的问题是在哪里完成，以及哪些程序员的专业知识制作了输入验证代码.
Meanwhile, the question requests non-PreparedStatement methods: in short, if you cannot offload the work to a library (e.g. prepared statements), then the only other method is to do it yourself for each "injected" item. Either way, the work of checking the input must be performed. The sole question is where it is done, and what programmer's expertise made the input validation code.
例如，考虑一个简单的SELECT语句:
For example, consider a simple SELECT statement:
sql = "SELECT * FROM mytable WHERE id = " + untrustedVar;
为完整性起见，我们可以假设注入示例，其中untrustedVar是类似于1 OR 1或1; DROP TABLE mytable;的字符串.显然，这将导致不想要的行为，与返回给调用方的所有行或现在缺少数据库表:
For completeness, we might assume the injection example where untrustedVar is a string like 1 OR 1 or 1; DROP TABLE mytable; Obviously this would result in unwanted behavior, vis-a-vis all rows returned to the caller, or a now missing database table:
SELECT * FROM mytable WHERE id = 1;
DROP mytable;
在这种情况下，您可以让语言语义至少确保unstrustedVar是整数，也许是在函数定义中:
In this case, you could let the language semantics at least ensure that unstrustedVar is an integer, perhaps in your function definition:
String[] selectRowById ( int untrustedVar ) { ...
或者，如果它是字符串，则可以使用如下正则表达式来实现:
Or if it's a string, you might do this with a regex like:
Pattern valid_id_re = Pattern.compile('^\d{1,10}$'); // ensure id is between 1 and 10 billion
Matcher m = valid_id_re.matcher( unstrustedVar );
if ( ! m.matches() )
return null;
但是，如果您输入的内容较长且没有任何语法或结构保证(例如，Web表单文本区域)，那么您将需要进行较低级别的字符替换，以避开潜在的不良字符.每条陈述.每个变量.每个数据库版本(PostgreSQL，Oracle，MySQL，SQLite等).这是一罐蠕虫.
But if you have longer inputs that do not have any grammar or structure guarantees (e.g., a web form textarea), then you will need to do the lower-level character replacements to escape the potentially bad characters. Per statement. Per variable. Per database flavor (PostgreSQL, Oracle, MySQL, SQLite, etc.). This ... is a can of worms.
当然，好处是，如果您没有使用准备好的语句，并且还没有人为避免对应用程序进行SQL注入攻击而进行任何工作，那么您将无处可去.
The upside, of course, is that if you are not using prepared statements, and no one has yet done the work to otherwise avoid SQL injection attacks for your application, you have no where to go but up.
与此同时，我敦促，敦促，敦促您重新考虑您对由于原因我们不能使用准备好的陈述"的立场.更重要的是，正如戈德·汤普森(Gord Thompson)在下面的评论中正确指出的那样:无论如何，这将是大量的工作，所以为什么不正确地做到这一点呢?"
Meanwhile, I urge, urge, urge you to reconsider your stance on "We can't use prepared statements because reasons." More to the point, and as Gord Thompson correctly points out in the comments below, "it will be a considerable amount of work in any case, so why not just do it right and be done with it?"
在编写了上面的文章之后，我想到有些人可能会认为仅编写准备好的语句就可以保证更好的安全性.实际上，它是带有绑定参数的准备好的语句 ，从而提高了安全性.例如，可以这样写:
After writing the above, it occurred to me that some might think that merely writing a prepared statement = better security. Actually, it's prepared statements with bound parameters that ups the security. For example, one could write this:
String sql = "SELECT * FROM mytable WHERE id = " + untrustedVar;
PreparedStatment pstmt = dbh.prepareStatement( sql );
return pstmt.executeQuery();
在这一点上，您所做的仅是准备一条已经注入了恶意代码的语句.相反，请考虑参数的实际绑定:
At this point, you've done little more than prepare a statement that has already been injected with malicious code. Instead, consider the actual binding of parameters:
String sql = "SELECT * FROM mytable WHERE id = ?"; // Raw string; never touched by "tainted" variable
PreparedStatment pstmt = dbh.prepareStatement( sql );
pstmt.setObject(1, p); // Perform the actual binding.
return pstmt.executeQuery();
后一个示例做了两件事.它首先创建一个已知的安全格式，然后将该格式发送到数据库进行准备.然后，之后，数据库返回了准备好的语句的句柄，我们是否绑定变量，最后执行该语句.
This latter example does two things. It first creates a known safe format, and sends that to the DB for preparation. Then, after the DB has returned a handle to the prepared statement, do we bind the variables, and finally execute the statement.
这篇关于在不使用Prepared语句的情况下摆脱SQL注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
