MySQL 如何在 Perl DBI 查询中插入带引号的字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/284740/
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 insert strings with quotes into Perl DBI queries?
提问by Paul Tomblin
What is the preferred way to insert strings that can contain both single and double quotes (",') into MySql using DBI? For example, $val1and $val2can contain quotes:
什么是插入可以包含单个和双引号（”“）到MySQL使用DBI例如，字符串的首选方法？$val1并且$val2可以包含引号：
my $dbh = DBI->connect( ... );
my $sql = "insert into tbl_name(col_one,col_two) values($val1, $val2)";
my $sth = $dbh->prepare($sql);
$sth->execute();
回答by Paul Tomblin
Use a bound query using
使用绑定查询
$sth = $dbh->prepare("insert into tbl_name(col_one,col_two) values(?,?)");
$sth->execute($val1, $val2);
If you use bound variables, everything is escaped for you.
如果您使用绑定变量，一切都会为您转义。
Update: Changed my example to correspond with the example edited into the question.
更新：更改了我的示例以与编辑到问题中的示例相对应。
Update: I don't know why Adam deleted his answer, but if for some reason you can't use bound variables (aka "placeholders"), you can also use $dbh->quote($var)on the variable. For example:
更新：我不知道为什么亚当删除了他的答案，但如果由于某种原因你不能使用绑定变量（又名“占位符”），你也可以$dbh->quote($var)在变量上使用。例如：
$sql = sprintf "SELECT foo FROM bar WHERE baz = %s",
$dbh->quote(q("Don't"));
回答by Adam Bellaire
Use the quote()method. It will intelligently handle the quoting for you. Example from the docs:
使用quote()方法。它将智能地为您处理报价。文档中的示例：
$sql = sprintf "SELECT foo FROM bar WHERE baz = %s",
$dbh->quote("Don't");
Slightly modified to have both types of quotes:
稍微修改为具有两种类型的引号：
$sql = sprintf "SELECT foo FROM bar WHERE baz = %s",
$dbh->quote(q("Don't"));
回答by Aquatoad
One small caveat on the bound placeholders, I build a rather large database-loading script that initially used bound placeholders in an older version of Perl/DBI and found what appears to be a memory leak in the placeholder implementation, so if you're looking at using them in a persistent process/daemon or in a high-volume context you may want to make sure process size doesn't become an issue. Switching over to building the query strings using the quote() method eliminated the issue for me.
关于绑定占位符的一个小警告，我构建了一个相当大的数据库加载脚本，该脚本最初在旧版本的 Perl/DBI 中使用绑定占位符，并发现占位符实现中似乎存在内存泄漏，所以如果您正在寻找在持久进程/守护程序或高容量上下文中使用它们时，您可能希望确保进程大小不会成为问题。切换到使用 quote() 方法构建查询字符串为我消除了这个问题。
回答by jjohn
DBI placeholders are awesome. They shine when you need to execute the same query in a loop. Consider this:
DBI 占位符很棒。当您需要在循环中执行相同的查询时，它们会发光。考虑一下：
my $dbh = DBI->connect(...);
my $name_pairs = get_csv_data("data.csv");
my $sth = $dbh->prepare("INSERT INTO t1 (first_name, last_name) VALUES (?,?)");
for my $pair (@$name_pairs) {
unless ($sth->execute(@$pair)) {
warn($sth->errstr);
}
}
In this case, having the prepared statement handle is, er, handy.
在这种情况下，拥有准备好的语句句柄非常方便。
However, barring this sort of tight-loop cases, I like to see the actual statement that was sent to the server. This is where I lean heavily on quote and frankly sprintf.
但是，除非出现这种紧密循环的情况，否则我喜欢看到发送到服务器的实际语句。这是我严重依赖引用和坦率地说 sprintf 的地方。
# Here, I am confident about the hash keys, less so about the values
$sql = sprintf("INSERT INTO t1 (%s) VALUES (%s)",
join(",", keys(%hash)),
join("," map { $dbh->quote($_) } values(%hash))
);
$sth = $dbh->prepare($sql);
unless ($sth->execute) {
warn($sth->{Statement});
}
Note that you do have to set RaiseError => 0 on $dbh so that you can see the SQL that failed, but this has helped me a great deal in the past.
请注意，您必须在 $dbh 上设置 RaiseError => 0 以便您可以看到失败的 SQL，但这在过去对我有很大帮助。
Cheers.
干杯。
