Using placeholders. See this tutorial

Indeed, psini is correct—placeholders should be your new best friend. Not only will they solve your quoting woes, more importantly they will provide a secure method of writing to a database, preventing such things as injection attacks.

However, it might be good to see an example of how you can more concisely deal with a hash of data that you want to write to the database using DBI. This clever piece of code (that I borrowed from someone in the Monastery years ago) builds your query using the keys and values of your hash of data. Note the ? placeholders.

my $stmt = qq/INSERT INTO books ( / . join(',', keys %hash) . qq/) 
                VALUES (/ . join(',', ('?') x keys %hash) . qq/)/;
my $sth = $dbh->prepare($stmt);
$sth->execute(values %hash);
[download]

Here's a simplified example (I use CGI::Application in my own work) of an entire subroutine I place in a common module

sub write_to_db {
   my $table   = shift; #pass in table name
   my $hash    = shift; #pass in hash ref to data
   my ($stmt, $sth);
   
   #subroutine to connect to db
   my $dbh = dbconnect(server =>'master', db => 'member');
   
   my %hash = %{ $hash }; #deref data

   if ($query->param('update')) {  #set a hidden value in html form if
+ updating
      $stmt = qq/UPDATE $table SET updated_on = NOW(), / . join(' = ?,
+', keys %hash) . qq/ = ? 
                 WHERE id = ?/;
      $sth = $dbh->prepare($stmt);
      $sth->execute(values %hash, $query->param('id')); #set a hidden 
+value
      
   } else {
      my $stmt = qq/INSERT INTO $table (created_on, / . join(',', keys
+ %hash) . qq/) 
                  VALUES (NOW(),/ . join(',', ('?') x keys %hash) . qq
+/)/;
      my $sth = $dbh->prepare($stmt);
      $sth->execute(values %hash);   
   }
}
[download]

<shameless plug> you should take a look at this short tutorial for CGI::Application. CGI::App is a great habit to get into, and will simplify your life, or coding</shameless plug>

Keep in mind in this case that you cannot delete nor add any keys into the hash between the keys %hash and values %hash! If you can't guarantee that you'd rather do something like:

my @keys = keys %hash;
my $stmt = qq/INSERT INTO books ( / . join(',', @keys) . qq/) 
                VALUES (/ . join(',', ('?') x @keys) . qq/)/;
my $sth = $dbh->prepare($stmt);
#...
$sth->execute(@hash{@keys});
[download]

Jenda


Support Denmark! Defend the free world!

Or maybe-

use SQL::Abstract;
my $sql = SQL::Abstract->new;

my $book_data =  {}; # your data from above

my ( $stmt, @bind ) = $sql->insert("books", $book_data);

print $stmt, "\n\n";
print join(" : ", @bind), "\n";

my $sth = $dbh->prepare($stmt);
my $rv = $sth->execute(@bind);
die "FAIL! DBI, ur doin it rong!" unless $rv == 1;
[download]

SQL::Abstract is really quite nice and will save you from all kinds of weird contortions if you try to roll your own version.

(Sidenote: unless something has changed in the Amazon dev agreement in the last couple years, it's against their TOS to save the data on your side in any persistent way.)

VALUES (
        NULL,
        " . $dbh->quote($hash{$key}{'title'}) . ", 
        " . $dbh->quote($hash{$key}{'authors'}) . ",  
        " . $dbh->quote($hash{$key}{'isbn'}) . ",  
        " . $dbh->quote($hash{$key}{'pages'}) . ",  
        " . $dbh->quote($hash{$key}{'publisher'}) . ",  
        " . $dbh->quote($hash{$key}{'publication_date'}) . ",
        " . $dbh->quote($hash{$key}{'image_small'}) . ",  
        " . $dbh->quote($hash{$key}{'image_medium'}) . ",  
        " . $dbh->quote($hash{$key}{'url_amazon'}) . ",   
        NULL)";
[download]

that code is pretty ugly and unnecessarily complicated. You can use placeholders as suggested before...

It's not how I would write it, but the OP was having problems with quoting strings.

It's no more complicated than the original code, and solves the quoting issues using the preferred DBI quoting interface. Placeholders aren't the one and only method of handling quoting issues and it's not my place to tell the OP how to code.