为什么我的INSERT有时会因“没有这样的字段”而失败？

I've been using the following snippet in developements for years. Now all of a sudden I get a DB Error: no such field warning

多年来我一直在开发中使用以下代码片段。现在突然间我得到一个DB错误:没有这样的字段警告

$process = "process";
$create = $connection->query
(
    "INSERT INTO summery (process) VALUES($process)"
);
if (DB::isError($create)) die($create->getMessage($create));

but it's fine if I use numerics

但如果我使用数字,那就没问题

$process = "12345";
$create = $connection->query
(
    "INSERT INTO summery (process) VALUES($process)"
);
if (DB::isError($create)) die($create->getMessage($create));

or write the value directly into the expression

或者将值直接写入表达式

$create = $connection->query
(
    "INSERT INTO summery (process) VALUES('process')"
);
if (DB::isError($create)) die($create->getMessage($create));

I'm really confused ... any suggestions?

我真的很困惑......有什么建议吗?

2 个解决方案

#1

It's always better to use prepared queries and parameter placeholders. Like this in Perl DBI:

使用准备好的查询和参数占位符总是更好。像Perl DBI中的这样:

my $process=1234;
my $ins_process = $dbh->prepare("INSERT INTO summary (process) values(?)");
$ins_process->execute($process);

For best performance, prepare all your often-used queries right after opening the database connection. Many database engines will store them on the server during the session, much like small temporary stored procedures.

为获得最佳性能,请在打开数据库连接后立即准备所有常用查询。许多数据库引擎会在会话期间将它们存储在服务器上,就像小型临时存储过程一样。

Its also very good for security. Writing the value into an insert string yourself means that you must write the correct escape code at each SQL statement. Using a prepare and execute style means that only one place (execute) needs to know about escaping, if escaping is even necessary.

它也非常有利于安全。将值写入插入字符串本身意味着必须在每个SQL语句中编写正确的转义码。使用prepare和execute样式意味着只有一个地方(执行)需要知道转义,如果甚至需要转义。

#2

Ditto what Zan Lynx said about placeholders. But you may still be wondering why your code failed.

同样Zan Lynx对占位符所说的话。但是你可能仍然想知道为什么你的代码失败了。

It appears that you forgot a crucial detail from the previous code that worked for you for years: quotes.

您似乎忘记了以前代码中对您有用的关键细节多年:引号。

This (tested) code works fine:

这(测试)代码工作正常:

my $thing = 'abcde';
my $sth = $dbh->prepare("INSERT INTO table1 (id,field1)
                              VALUES (3,'$thing')");
$sth->execute;

But this next code (lacking the quotation marks in the VALUES field just as your first example does) produces the error you report because VALUES (3,$thing) resolves to VALUES (3,abcde) causing your SQL server to look for a field called abcde and there is no field by that name.

但是下一个代码(在VALUES字段中缺少引号就像你的第一个例子那样)会产生你报告的错误,因为VALUES(3,$ thing)解析为VALUES(3,abcde)导致你的SQL服务器查找字段叫做abcde,那个名字没有字段。

my $thing = 'abcde';
my $sth = $dbh->prepare("INSERT INTO table1 (id,field1)
                              VALUES (3,$thing)");
$sth->execute;

All of this assumes that your first example is not a direct quote of code that failed as you describe and therefore not what you intended. It resolves to:

所有这些都假定您的第一个示例不是直接引用代码,因为您描述的代码失败,因此不是您的意图。它解决了:

"INSERT INTO summery (process) VALUES(process)"

which, as mentioned above causes your SQL server to read the item in the VALUES set as another field name. As given, this actually runs on MySQL without complaint and will fill the field called 'process' with NULL because that's what the field called 'process' contained when MySQL looked there for a value as it created the new record.

如上所述,这会导致您的SQL服务器将VALUES中的项目设置为另一个字段名称。如上所述,这实际上在没有抱怨的情况下在MySQL上运行,并且将填充名为'process'的字段为NULL,因为当MySQL在创建新记录时查找值时,该字段称为'process'。

I do use this style for quick throw-away hacks involving known, secure data (e.g. a value supplied within the program itself). But for anything involving data that comes from outside the program or that might possibly contain other than [0-9a-zA-Z] it will save you grief to use placeholders.

我确实使用这种风格来快速丢弃涉及已知安全数据的黑客攻击(例如程序本身提供的值)。但是对于任何涉及来自程序外部或可能包含[0-9a-zA-Z]之外的数据的内容,它将使您免于使用占位符。

#1