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 db之前，我是否可以使用Perl来对输入进行清理?我不太了解regex，所以在我创建自己的函数之前，我想知道是否已经生成了一个。
5 个解决方案
#1
66
The proper way to sanitize data for insertion into your database is to use placeholders for 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 docs for more information.
更多信息请参见DBI文档。
#2
20
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 excellent protection 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 absolute protection against SQL injection. Because the commands and data are sent separately, there is no way that 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, never quote/escape/sanitize user input as protection against SQL injection. Always use 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://bobbytables .com/有如何在不同语言(包括Perl)中使用参数化查询的示例。
#3
7
In very rare cases you'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 quote and quote_identifier methods. Also it makes your code less dependent on a particular RDBMS.
在非常罕见的情况下，您不能使用占位符，如其他答案所述。但是，即使是在如此罕见的情况下，您也不应该自己篡改数据，因为它为潜在的bug提供了位置。最好使用DBI的quote和quote_identifier方法。它还可以减少代码对特定RDBMS的依赖。
Disclaimer. The following is a dummy example and is not meant to illustrate the very rare case I mentioned.
免责声明。下面是一个伪示例，并不是要说明我提到的非常罕见的情况。
$dbh->do('INSERT INTO ' . $dbh->quote_identifier($table) . ' (id, name) VALUES '
'(NULL, ' . $dbh->quote($name) . ')');
#4
7
How do you call the database?
如何调用数据库?
DBI has support for prepared statements using placeholders. Both DBIx::Class and Rose::DB::Object sanitize values automatically, if you use the "find" method provided by each library.
DBI支持使用占位符的准备语句。如果您使用每个库提供的“查找”方法，那么DBIx::Class和Rose::DB::对象都会自动对值进行清理。
#5
0
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命令。
#1
66
The proper way to sanitize data for insertion into your database is to use placeholders for 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 docs for more information.
更多信息请参见DBI文档。
#2
20
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 excellent protection 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 absolute protection against SQL injection. Because the commands and data are sent separately, there is no way that 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, never quote/escape/sanitize user input as protection against SQL injection. Always use 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://bobbytables .com/有如何在不同语言(包括Perl)中使用参数化查询的示例。
#3
7
In very rare cases you'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 quote and quote_identifier methods. Also it makes your code less dependent on a particular RDBMS.
在非常罕见的情况下，您不能使用占位符，如其他答案所述。但是，即使是在如此罕见的情况下，您也不应该自己篡改数据，因为它为潜在的bug提供了位置。最好使用DBI的quote和quote_identifier方法。它还可以减少代码对特定RDBMS的依赖。
Disclaimer. The following is a dummy example and is not meant to illustrate the very rare case I mentioned.
免责声明。下面是一个伪示例，并不是要说明我提到的非常罕见的情况。
$dbh->do('INSERT INTO ' . $dbh->quote_identifier($table) . ' (id, name) VALUES '
'(NULL, ' . $dbh->quote($name) . ')');
#4
7
How do you call the database?
如何调用数据库?
DBI has support for prepared statements using placeholders. Both DBIx::Class and Rose::DB::Object sanitize values automatically, if you use the "find" method provided by each library.
DBI支持使用占位符的准备语句。如果您使用每个库提供的“查找”方法，那么DBIx::Class和Rose::DB::对象都会自动对值进行清理。
#5
0
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命令。