MySQL 如何使用 Perl 的 DBI 防止 SQL 注入攻击？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2300765/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I protect against SQL injection attacks using Perl's DBI?
提问by cskwrd
Is there a function i can use in Perl to sanitize input before putting it into a MySQL db? I don't know regex very well so before I make my own function i was wondering if there was already one made.
在将输入放入 MySQL 数据库之前，我可以在 Perl 中使用它来清理输入吗？我不太了解正则表达式，所以在我制作自己的函数之前，我想知道是否已经制作了一个。
回答by friedo
The proper way to sanitize data for insertion into your database is to use placeholdersfor all variables to be inserted into your SQL strings. In other words, NEVER do this:
清理数据以插入数据库的正确方法是为所有要插入 SQL 字符串的变量使用占位符。换句话说，永远不要这样做：
my $sql = "INSERT INTO foo (bar, baz) VALUES ( $bar, $baz )";
Instead, use ?placeholders:
相反，使用?占位符：
my $sql = "INSERT INTO foo (bar, baz) VALUES ( ?, ? )";
And then pass the variables to be replaced when you execute the query:
然后在执行查询时传递要替换的变量：
my $sth = $dbh->prepare( $sql );
$sth->execute( $bar, $baz );
You can combine these operations with some of the DBI convenience methods; the above can also be written:
您可以将这些操作与一些 DBI 便利方法结合起来；上面也可以写成：
$dbh->do( $sql, undef, $bar, $baz );
See the DBI docsfor more information.
有关更多信息，请参阅DBI 文档。
回答by Dave Sherohman
Minor (and admittedly pedantic) addendum to the "use placeholders" answers: Parametrized queries are not, strictly speaking, "sanitizing". They do not modify the data in any way to make it safe. Instead, they protect against SQL injection by sending the query structure (commands) and the data by separate channels.
“使用占位符”答案的次要（并且公认的迂腐）附录：严格来说，参数化查询不是“清理”。他们不会以任何方式修改数据以使其安全。相反，它们通过单独的通道发送查询结构（命令）和数据来防止 SQL 注入。
The reason I feel this distinction is significant is because treating sanitizing/quoting/escaping your data and using parametrized queries as the same thing implies that they are interchangeable or, at best, that parameters are just a better way to quote dangerous characters, so it's no big deal if you stick with quoting instead of bothering to figure out that placeholder stuff.
我觉得这种区别很重要的原因是因为将清理/引用/转义数据和使用参数化查询视为同一件事意味着它们是可互换的，或者充其量，参数只是引用危险字符的更好方法，所以它是如果你坚持引用而不是费心去弄清楚占位符的东西，那没什么大不了的。
In truth, they are completely different techniques with completely different levels of reliability. Quoting can provide excellentprotection against injection, but there is always the chance that a determined attacker could find some corner case which will break or slip through your quoting algorithm and allow them to perform a successful SQL injection. Parametrized queries, on the other hand, provide absoluteprotection against SQL injection. Because the commands and data are sent separately, there is no waythat the database engine can be tricked into executing data as a command.
事实上，它们是完全不同的技术，具有完全不同的可靠性水平。引用可以提供出色的注入保护，但是坚定的攻击者总是有可能找到一些极端情况，这些情况会破坏或绕过您的引用算法并允许他们成功执行 SQL 注入。另一方面，参数化查询提供了针对 SQL 注入的绝对保护。因为命令和数据被分开发送的，有没有办法数据库引擎可以被欺骗执行数据为命令。
Unless you're in a case where your language or database engine won't allow you to use a parameter in your query, neverquote/escape/sanitize user input as protection against SQL injection. Alwaysuse parametrized queries for this purpose if you are able to do so.
除非您的语言或数据库引擎不允许您在查询中使用参数，否则永远不要引用/转义/清理用户输入以防止 SQL 注入。 如果可以，请始终为此目的使用参数化查询。
And the obligatory link: http://bobby-tables.com/has examples of how to use parametrized queries in several different languages, including Perl.
强制性链接：http: //bobby-tables.com/提供了如何在多种不同语言（包括 Perl）中使用参数化查询的示例。
回答by codeholic
In very rare casesyou're not able to use placeholders, as described in other answers. But even in such rare case you shouldn't tamper with data by yourself, since it makes a place for a potential bug. It's better to use DBI's quoteand quote_identifiermethods. Also it makes your code less dependent on a particular RDBMS.
在极少数情况下，您无法使用占位符，如其他答案中所述。但即使在这种罕见的情况下，您也不应该自己篡改数据，因为它为潜在的错误提供了空间。最好使用 DBIquote和quote_identifier方法。此外，它使您的代码对特定 RDBMS 的依赖程度降低。
Disclaimer.The following is a dummy example and is not meant to illustrate the very rare caseI mentioned.
免责声明。以下是一个虚拟示例，并不打算说明我提到的非常罕见的情况。
$dbh->do('INSERT INTO ' . $dbh->quote_identifier($table) . ' (id, name) VALUES '
'(NULL, ' . $dbh->quote($name) . ')');
回答by Mikael S
How do you call the database?
怎么调用数据库？
DBIhas support for prepared statements using placeholders. Both DBIx::Classand Rose::DB::Objectsanitize values automatically, if you use the "find" method provided by each library.
DBI支持使用占位符的预处理语句。无论DBIx ::类和 玫瑰:: DB ::对象自动的sanitize值，如果您使用的每个库提供的“发现”的方法。
回答by Yasiru G
Answer: Use SQL placeholders (?).
答案：使用 SQL 占位符 (?)。
Why: The structure of the SQL statement and the data values represented by the placeholders are sent to the database completely separately. so there is absolutely no way that the data values can be interpreted as SQL commands.
为什么：SQL 语句的结构和占位符表示的数据值是完全分开发送到数据库的。所以绝对没有办法将数据值解释为 SQL 命令。
