Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

DBH Insert of Binary Data

by amt (Monk)
on Mar 18, 2005 at 19:49 UTC ( #440788=perlquestion: print w/ replies, xml ) Need Help??
amt has asked for the wisdom of the Perl Monks concerning the following question:

Gentleman,

Now that I have my binary data into scalar from an earlier post,39978, I am confused how to proceed with inserting this data into a LONGBLOB in my database. I am using
$dbh->do("INSERT INTO `table` (`blobcolumn`) VALUES (".$dbh->quote($bi +ndata).")");
This, however, brings me no joy. Is there a function that inserts binary data into a table? Should I create the row without the data first, then update the entry with the binary data.

Many thanks in advance.
amt.

perlcheat

Comment on DBH Insert of Binary Data
Download Code
Re: DBH Insert of Binary Data
by friedo (Prior) on Mar 18, 2005 at 20:02 UTC
    For this sort of thing you'll want to bind the values.

    my $sth = $dbh->prepare("INSERT INTO `table` (`blobcolumn`) VALUES ( ? )"); $sth->execute($bindata) or die $dbh->errstr;

    The "?" gets replaced by your $bindata when the query executes, and DBI takes care of everything for you. (This feature also allows you to prepare a statement handle once and execute it several times with changing values.)

      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

        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.
      Placeholders, are the way to go in mysql. In postgres (DBD::pg) you explicitly need to do something like:
      my $sth = $dbh->prepare("INSERT INTO foo (blob) VALUES (?)"); $sth->bind_param(1, $blob_data, { pg_type => DBD::Pg::PG_BYTEA });
      This is a pain in the ass, I wish postgres would just let placeholders work with binary magically like mysql does.
Re: DBH Insert of Binary Data
by blueberryCoffee (Scribe) on Mar 20, 2005 at 02:50 UTC
    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?

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (5)
As of 2014-12-27 02:50 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (176 votes), past polls