in reply to Problem with DBI and MySQL

As pointed out by many others, you may want to use placeholders, especially if you will be executing the same query several times with different data during the lifespan of the database connection.

That said, placeholders do not necessarily have a place in once-off queries. What you absolutely MUST do when placing your data inline like this, is quote your data using the $dbh->quote() method. Make it a habit, even when you trust your variables, because you may reuse the code later.

Not only does ->quote() place quotation marks around your strings (failure to do so will make the query fail) but it will also escape potentially harmful characters that could be injected by a hacker. 

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

[download]
This script won't insert anything, but if you are logged into MySQL with sufficient privileges it will ruin your day. Protecting yourself is easy: 
my $name = "; DROP DATABASE mysql;";
my $sth = $dbh->prepare("INSERT INTO names (name) values (".$dbh->quot
+e($name).")");
$sth->execute;

[download]
Now the evil name will be inserted into the table as expected.

Google "SQL Injection".

-- Time flies when you don't know what you're doing

Replies are listed 'Best First'.
Re^2: Problem with DBI and MySQL
by roboticus (Chancellor) on Aug 23, 2010 at 11:12 UTC

    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

[reply]
[d/l]
[select]
      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]

In Section Seekers of Perl Wisdom