Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling

Re^2: Problem with DBI and MySQL

by roboticus (Chancellor)
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


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.


[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.


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

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://856678]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (6)
As of 2018-02-23 20:35 GMT
Find Nodes?
    Voting Booth?
    When it is dark outside I am happiest to see ...

    Results (310 votes). Check out past polls.