Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Re^2: Problem with DBI and MySQL

by roboticus (Canon)
on Aug 23, 2010 at 11:12 UTC ( #856678=note: print w/ replies, xml ) Need Help??


in reply to Re: Problem with DBI and MySQL
in thread 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);

[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
Re^3: Problem with DBI and MySQL
by FloydATC (Chaplain) 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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://856678]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (9)
As of 2014-11-28 09:04 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred Perl binaries come from:














    Results (194 votes), past polls