Hello Monks,

I'm writing a a bit of code to preform an extraction on an XML document. That bit isn't really relevant as I know the XML is being extracted appropriately and when printed to a text file is exactly what I want it to be. Suffice it to say I know I'm getting the data I need.

Because I'm dealing with a database with multiple tables and a variable number of columns within those tables, I want to be able to insert a variable number of items into each row. To this end, I've written the following code. I've excluded the object/methods that extract the data and compile it into a miniature database structure. This is the code for the extraction from the hash and the insert into the database.

use strict;
use warnings;
use utf8;
use diagnostics;
use DBI;
use xml::twig;

        our %mirror;
        our %referential;
        our $table;
        our $rows;
        our $columns;

our  $dbh= connect_to_db() || die "Can't connect: $DBI::errstr\n";


         
my  $file ="poorlySTRUCTURED.xml";

my $twig=XML::Twig->new(
     twig_handlers=>
        {lots of handlers that call the elements I need});
        
        $twig->parsefile("$file");
        

        
        
sub connect_to_db
  { my $driver = "mysql";
      my $drh = DBI->install_driver($driver);
         my $dsn = "DBI:$driver:database=united;host=somehost;port=any
+portinastorm";
    my $dbh = DBI->connect($dsn, "root", "password", {AutoCommit=>1};
$dbh->{RaiseError}=1;);
    
    return( $dbh);
  }
        sub insertSKU{
    for my $Itemnumber (keys %mirror){
        for $table (keys %{$mirror{$Itemnumber}}){
            if ($table=~/skugroup/){
    $columns= (join ', ', keys %{$mirror{"$Itemnumber"}{"$table"}});
    $rows= (join ', ', values %{$mirror{"$Itemnumber"}{"$table"}}); 

#Now we sanitize the data for MySQL syntax.
    $columns =~s/\.|;|\/|\\|\|//g;
    $rows =~s/\.|;|\/|\\|\|//g;
    my $insert= $dbh->prepare("INSERT INTO $table ($columns) VALUES ($
+rows)GO");
    $insert->execute() || die "Insert failed \n $columns \n\n $rows";
        
        }                            }
    }
}
[download]

The error I get back is

"You have an error in your SQL syntax; check the manual that correspon
+ds to your MySQL server version for the right syntax use near 'acetat
+e cover protectsdocument, Distinguished leatherette frame is accented
+ wit'
[download]

Any commas present in the data are removed when the value is extracted and I've verified that. I avoid using the bind_param method because I need to insert into multiple tables with a variable number of values. At this point I'm wondering if when using DBI to insert values into a database the values need to be quoted. I don't think I've missed any escape characters in sanitizing the data, and the MySQL website lists this as appropriate syntax for inserting multiple values with a single insert command. So what am I doing wrong? Do you need more information to help out?