Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change

Re^2: DBH Insert of Binary Data

by Hero Zzyzzx (Curate)
on Mar 18, 2005 at 20:40 UTC ( #440799=note: print w/replies, xml ) Need Help??

in reply to Re: DBH Insert of Binary Data
in thread DBH Insert of Binary Data

My understanding was that explicitly using $dbh->quote was pretty much the same as using placeholders, in terms of escaping characters that would break SQL.

Put another way, shouldn't the two statements above be pretty much equivalent, except that yours is reusable?

-Any sufficiently advanced technology is
indistinguishable from doubletalk.

My Biz

Replies are listed 'Best First'.
Re^3: DBH Insert of Binary Data
by jZed (Prior) on Mar 18, 2005 at 20:56 UTC
    NO! In most DBDs, the quote method escapes things that need to be escaped and the result is put into a SQL string. The parser needs to then parse that string. A placeholder is different because a) there is no escaping of the value (binary values may not like being escaped) and b) the value is *never* put back into the SQL string, it is sent separately to the RDBMS which means that there is no opportunity for SQL injection since the placeholder values is clearly known to be a value, not part of a SQL string and it also means that the parser has no work - it never sees the placeholder value. Think of it like this:
       1)  SELECT x FROM FROM y WHERE $myquoted_value =z
       2a) SELECT x FROM y WHERE ? = 1
       2b) $value_to_insert
    With #1, the RDBMS sees everything at once and has to separate out $myquoted_value, possibly being tricked about what to spearate if $myquoted_value contains SQL injection. With #2, the SQL statement and the value are passed sparately and the RDBMS does not need to separate them again. Placeholders are better for security and often better for performance.
      I mostly agree, but AFAIK, the $dbh->quote() method is (or should be) implemented by the specific DBD driver and should always escape correctly. Now, there might be situations or database where you can't just insert a quoted string in a BLOB, but SQL injection should not be possible with a $dbh->quote()d string.

      The top post should remove the quotes around the quoted string, though, as $dbh->quote already provides them. Never mind, there aren't any.

        Try this with your favorite DBD. The DBD and/or the RDBMS may prevent the injection at some later time, but the quote method has little to do with it.
            my $val = $dbh->quote(q{Boston;DELETE FROM myTable});
        As for the quote method not messing up a binary, you're probably correct in most cases, but in cases where the DBD supports several escaping methods (e.g. both '' and \') it's possible to have problems. And even where it doesn't cause problems, you're adding three different steps to the process - quoting the Blob, unquoting the Blob, and parsing the Blob as a value within the SQL string.

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (3)
As of 2017-07-22 17:09 GMT
Find Nodes?
    Voting Booth?
    I came, I saw, I ...

    Results (340 votes). Check out past polls.