Why does my INSERT sometimes fail with "no such field"?

Question

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

$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?

Answer 1

thanks paul - that was driving me mad!

Answer 2

It's always better to use prepared queries and parameter placeholders. Like this in 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.

Answer 3

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

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.

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.

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.