Re^2: Problem with DBI and MySQL

FloydATC:

If you're going to use prepare then execute for a single-use query, I still advocate using placeholders. Here's why:

The server has the opportunity^[3] to precompile the request in the form specified by the developer, rather than as-modified by the user^[1]. So maintenance programmers can easily review the SQL to see what the server will do, without having to trace the parameters to ensure that they're all properly quoted.
It appears to be simpler^[2] to use placeholders than to properly apply the quote method to build SQL commands. Relying on the DBI module and associated DBD drivers to "do the right thing" when using placeholders is much simpler than ensuring you've properly quoted all values you're interpolating into an SQL string.

NOTES:

[1] Meaning that the SQL the server may precompile is known to the developer, rather than being modified by text manipulations. I realize that the resulting parse tree will be identical with proper use of quoting, #############

[2] Converting your example to use placeholders gives us:

my $name = "; DROP DATABASE mysql;";
my $sth = $dbh->prepare("INSERT INTO names (name) values (?)");
$sth->execute($name);
[download]

[3] If the driver supports it, then the server can compile the execution plan for the statement. Afterwards, no combination of quotes, semicolons, comments, etc. can make the server drop a table (or other action) not already in the execution plan.

...roboticus

Comment on Re^2: Problem with DBI and MySQL Select or Download Code

Replies are listed 'Best First'.
Re^3: Problem with DBI and MySQL by FloydATC (Deacon) on Aug 25, 2010 at 15:52 UTC
I agree, $dbh->do() would have been much better as an example of a once-off query, and as a general rule placeholders produce code that is easier to read and debug. Where possible. Otherwise, use $dbh->quote(). -- Time flies when you don't know what you're doing	[reply]