如何在 SQL 准备好的语句中转义单引号和双引号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2003572/
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 escape single and double quotes in SQL prepared statement?
提问by Sam
I have a SQL statement similar to the one shown below in Perl:
我在 Perl 中有一个类似于下面显示的 SQL 语句：
my $sql="abc..TableName '$a','$b' ";
The $a is free text which can contain anything including single quotes, double quotes, back- and front-slash characters, etc.
$a 是自由文本，可以包含任何内容，包括单引号、双引号、反斜杠和正斜杠字符等。
How can these characters be escaped to make the SQL statement work?
如何转义这些字符以使 SQL 语句起作用？
Thanks.
谢谢。
回答by mopoke
You can either use the ->quotemethod (assuming you're using DBI):
您可以使用该->quote方法（假设您使用的是 DBI）：
my $oldValue = $dbh->quote('oldValue');
my $newValue = $dbh->quote('newValue');
$dbh->do("UPDATE myTable SET myValue=$newValue where myValue=$oldValue");
Better still, the best practice is to use bind values:
更好的是，最佳实践是使用绑定值：
my $sth = $dbh->prepare('UPDATE myTable SET myValue=? WHERE myValue=?');
$sth->execute('newValue','oldValue');
This should also work for stored procedure calls, assuming the statement once the strings have been expanded is valid SQL. This may be driver/DB specific so YMMV.
这也应该适用于存储过程调用，假设字符串展开后的语句是有效的 SQL。这可能是特定于驱动程序/数据库的，所以 YMMV。
my $sth = $dbh->prepare("DBName..ProcName ?,? ");
$sth->execute($a, $b);
回答by mopoke
Use a prepared statement. Replace the variable with a ?. To crib an example from DBI manpages:
使用准备好的语句。用 ? 替换变量。要从 DBI 联机帮助页中获取示例：
$sql = 'SELECT * FROM people WHERE lastname = ?';
$sth = $dbh->prepare($sql);
$sth->execute($user_input_here);
Interpolating user input into your SQL is asking for security holes.
将用户输入插入到您的 SQL 中是在寻找安全漏洞。
回答by Bill Karwin
If you use query parameter placeholders, you don't have to escape the content of the strings.
如果使用查询参数占位符，则不必转义字符串的内容。
my $sql="DBName..ProcName ?, ?";
$sth = $dbh->prepare($sql);
$sth->execute($a, $b);
If the DBI is using true query parameters, it sends the parameter values to the RDBMS separately from the SQL statement. The values are never combined with the SQL statement string, therefore the values never have an opportunity to cause SQL injection.
如果 DBI 使用的是真正的查询参数，它会将参数值与 SQL 语句分开发送到 RDBMS。这些值永远不会与 SQL 语句字符串组合，因此这些值永远不会导致 SQL 注入。
If the DBI is "emulating" prepared statements by interpolating the variables into the query string, then DBI should handle the correct escaping logic so you don't have to. Let the experts (those who write and test DBI) worry about how to do it.
如果 DBI 通过将变量插入到查询字符串中来“模拟”准备好的语句，那么 DBI 应该处理正确的转义逻辑，因此您不必这样做。让专家（编写和测试 DBI 的人）担心如何去做。
回答by Chris Denman
If you don't want to use ->quote (for some reason, this function doesn't run on my version of DBI) then try this:
如果您不想使用 ->quote（出于某种原因，此功能无法在我的 DBI 版本上运行），请尝试以下操作：
$query=~s/\"/\\"/g;
I tend to do the same with single quotes and commas too just to be safe.
为了安全起见，我也倾向于对单引号和逗号做同样的事情。
Seems to work fine for me...!
对我来说似乎工作正常......！
