如何在SQL预处理语句中转义单引号和双引号？

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.

4 个解决方案

#1

You can either use the ->quote method (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);

#2

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中会引发安全漏洞。

#3

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使用true查询参数,它会将参数值与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的人)担心如何做到这一点。

#4

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...!

似乎对我来说工作正常......!

#1