MySQL Data Insertion Errors w/ Perl

ingsoc747 has asked for the wisdom of the Perl Monks concerning the following question:

OK, here goes...

I'm building an eCommerce site utilizing the open source osCommerce project. Our company also uses Quickbookbs for inventory management, billing, etc. In order to effectively update the osCommerce site with the current inventory, I needed some sort of script or program to do this, and I found a solution in the osCommerce contributions section (http://www.oscommerce.com/community/contributions,2194/category,all/search,quickbooks).

Now, I was able to get all the data sources and MySQL up and running (I'm runnin 2k over here, please, no beatdowns ;). I installed and am able to compile the perl script. It works fine except for certain inventory items, with which i get the following error message:

DBD::mysql::db do failed: You have an error in your SQL syntax.  Check
+ the manual that corresponds to your MySQL server version for the rig
+ht syntax to use near '')' at line 1 at qb2oscommerce.pl line 246.
[download]

When I check the items that produce errors, it seems like the item descriptions contain single quotes (used, in out descriptions, to indicate foot measurements). I'm wondering how to modify the perl script to force MySQL to accept these descriptions. (I'm assuming that it is an INSERTION problem, since MySQL is the DB that I'm inserting into)

Unfortunately, I am not very adept at Perl. I'm pretty good at C++ and assembly, though, so I'm not totally programming ignorant. I will quote the code below for reference. Thanks for your help. (BTW, I marked line 246 in the code)

#!run it like this 'perl qb2oscommerce.pl'

use Win32::ODBC;
use Data::Dumper;

use DBI;

# Debug settings
my $debug = 1;
$Data::Dumper::Indent = 3;
my $count = 0;

# Mysql server settings
#my $mysqldsn = 'DBI:mysql:oscommerce:hostname';
#my $mysqldsn = 'DBI:mysql:oscommrce:localhost';
my $mysqldsn = 'dbi:mysql:oscommerce:localhost:3306';
my $db_user_name = '...';
my $db_password = '...';

# Show items that we dont have stock of?
my $show_no_stock = true;

# Quickbooks QODBC settings
$DSN = "QODBCDSN";

if (!($db = new Win32::ODBC($DSN))){
    print "Error connecting to $DSN\n";
    print "Error: " . Win32::ODBC::Error() . "\n";
    exit;
}

# Setup and execute query to list all Items from quickbooks.
$SqlStatement = "SELECT * FROM ItemInventory WHERE IsActive = 1 ORDER 
+by FullName";
if ($db->Sql($SqlStatement)){
    print "SQL failed.\n";
    print "Error: " . $db->Error() . "\n";
    $db->Close();
    exit;
}

# Connect to the Mysql server.
my $dbh = DBI->connect($mysqldsn, $db_user_name, $db_password);

#if ($debug > 2) {
#    $dbh->do("DELETE FROM categories");
#    $dbh->do("DELETE FROM categories_description");
#    $dbh->do("DELETE FROM products");
#    $dbh->do("DELETE FROM products_description");
#    $dbh->do("DELETE FROM products_to_categories");
#}

# Loop through all Items
while ($db->FetchRow()){
    undef %Data;
    %Data = $db->DataHash();

    # Skip stuff we dont want on the web
    next if ($Data{'PurchaseDesc'} =~ /AMR/i);

    # Tell the user what we are doing
    print $Data{'Name'} . " ";
    
    # print debug messages if we are in debug mode;
    print Dumper(\%Data) . "\n" if $debug > 2;

    # If this is a category, then lets add/update the category
    # entry.

    $product_id = find_product_id($Data);

    if ($Data{'PurchaseDesc'} =~ /category/i) {
        print "Category ";

        # Was there any rows returned? (Does the category exist?)
        # If it does, setup an update query, otherwise setup an insert
        if (defined($product_id)) {
            $id = $product_id;
            $query = "UPDATE categories SET " .
                "date_added = '" . $Data{'TimeCreated'} . "', " .
                "sort_order = '1' " .
                "WHERE categories_id = '" . $id . "'";
            print "Updating Category: " . $query . "\n" if $debug > 1;
            print "Update\n";
        } else {
            $query = ("INSERT INTO categories " .
                      "(categories_id, date_added, sort_order) " . 
                      "VALUES " .
                      "(NULL, " .
                      "'" . $Data{'TimeCreated'} . "', '1')");
            print "Inserting Category: " . $query . "\n" if $debug > 1
+;
            print "New\n";
            $id = 'NULL';
        }

        # Do the insert/update query 
        $dbh->do($query);

        # If this was a new entry, get the mysql servers automatically
        # assigned number for the new record and setup the insert quer
+y
        # if if wasn't new, they setup and update query.
        if ($id eq 'NULL') {
            $id = $dbh->{'mysql_insertid'};
            $query = ("INSERT INTO categories_description " .
                      "(categories_id, categories_name) " .
                      "VALUES " .
                      "('" . $id . "', '" . 
                      $Data{'FullName'} . "')");
            print "Inserting Description: " . $query . "\n" if $debug 
+> 1;
        } else {
            $query = "UPDATE categories_description SET " .
                "categories_name = '" . $Data{'FullName'} . "' " .
                "WHERE categories_id = '" . $id . "'";
            print "Updating Description: " . $query . "\n" if $debug >
+ 1;
        }

        # Do the insert/update.
        $dbh->do($query);
    # If this isnt a category, then its an item, and needs to be added
    # to the products table.
    } else {
        print "Item " if $debug > 0;
        # Set the stock flag
        if ($Data{'QuantityOnHand'} <= 0) {
            if ($show_no_stock) {
                $status = 1;
            } else {
                $status = 0;
            }
        } else {
            $status = 1;
        }

        # Determine the Category id
        $query = ("SELECT categories_id from categories_description " 
+.
                  "WHERE categories_name = '" . 
                  $Data{'ParentRefFullName'} . "'");
        my $sth = $dbh->prepare($query);
        $sth->execute();
        
        # Were there any rows returned? 
        if ($sth->rows > 0) {
            $ref = $sth->fetchrow_hashref();
            $cat_id = $$ref{'categories_id'};
        } else {
            $cat_id = 0;
        }

        # Try and work out the product id
        $products_id = find_product_id($Data);

        # Work out the manufacturers id
        if (defined($Data{'CustomFieldManufacturer'})) {
            $query = ("SELECT manufacturers_id " .
                      "FROM manufacturers " .
                      "WHERE manufacturers_name = '" . 
                      $Data{'CustomFieldManufacturer'} . "'");
            my $sth = $dbh->prepare($query);
            $sth->execute();
            if ($sth->rows > 0) {
                $ref = $sth->fetchrow_hashref();
                $manufacturers_id = $$ref{'manufacturers_id'};
            } else {
                $query = ("INSERT INTO manufacturers " .
                          "(manufacturers_id, manufacturers_name) " .
                          "VALUES " .
                          "(NULL, '" .
                          $Data{'CustomFieldManufacturer'} . "')");
                $dbh->do($query);
                $manufacturers_id = $dbh->{'mysql_insertid'};
            }
        } else {
            $manufacturers_id = 0;
        }

        # Work out the price
        if ($Data{'AmountIncludesVAT'} == 1) {
            $price = $Data{'SalesPrice'} - ($Data{'SalesPrice'} / 11);
        } else {
            $price = $Data{'SalesPrice'};
        }

        printf(" \$ %10.2f ", $price);

        # If quickbooks has data in the web products id, then use it
        # Rather than adding the product again
        if (defined($products_id)) {
            
            $query = ("SELECT * FROM specials WHERE products_id = '" .
+ $products_id . "'");
            my $sth = $dbh->prepare($query);
            $sth->execute();
            if ($sth->rows > 0) {
                $priceupdate = "";
                print "Update - No Price\n";
            } else {
                $priceupdate = "products_price = '" . $price . "', ";
                print "Update - New Price\n";
            }

            # Add the product to the products table
            $query = "UPDATE products SET " .
                "products_quantity = '" . $Data{'QuantityOnHand'} . "'
+, " .
                "products_model = '" . $Data{'Name'} . "', " .
                "products_date_added = '" . $Data{'TimeCreated'} . "',
+ " .
                "products_status = '" . $status . "', " .
                "products_tax_class_id = '1', " .
                $priceupdate . 
                "manufacturers_id = '" . $manufacturers_id . "' " .
                "WHERE products_id = '" . $products_id . "'";
            $dbh->do($query);

            # Next, update the products description
            $query = "UPDATE products_description SET " .
                "products_name = '" . $Data{'Name'} . "', " .
                "products_description = '" . $Data{'SalesDesc'} . "' "
+ .
                "WHERE products_id = '" . $products_id . "', " .
            $dbh->do($query);
            
            # Finally, update the products category association
            $query = "UPDATE products_to_categories SET " .
                "categories_id = '" . $cat_id . "' " .
                "WHERE products_id = '" . $products_id . "'";
            $dbh->do($query);
        } else {
            print "New\n";
            # Add the product to the products table
            $query = ("INSERT INTO products " .
                      "(products_id, products_quantity, products_model
+, products_price, products_date_added, products_status, products_tax_
+class_id, manufacturers_id) " .
                      "VALUES " .
                      "(NULL, '" . 
                      $Data{'QuantityOnHand'} . "', '" .
                      $Data{'Name'} . "', '" .
                      $price . "', '" .
                      $Data{'TimeCreated'} . "', '" .
                      $status . "','1','" .
                      $manufacturers_id . "')");
            $dbh->do($query);
            $id = $dbh->{'mysql_insertid'};
            
            # Next, add the products description
            $query = ("INSERT INTO products_description " .
                      "(products_id, products_name, products_descripti
+on) " .
                      "VALUES " .
                      "('" . $id . "', '" .
                      $Data{'Name'} . "', '" .
                      $Data{'SalesDesc'} . "')");
#-----line 246            $dbh->do($query);
            
            # Finally, add the products category association
            $query = ("INSERT INTO products_to_categories " .
                      "(products_id, categories_id) " .
                      "VALUES " .
                      "('" . $id . "', '" . $cat_id . "')");
            $dbh->do($query);

            # Update quickbooks with the product id that was assigned
            push(@qb_update, ("UPDATE ItemInventory " .
                              "SET Barcode = '" . $id . "' " .
                              "WHERE ListID = '" . $Data{'ListID'} . "
+'"));

        }
    }
    $count++;
    last if (($debug > 2) && ($count > 10));
}

# Now do the quickbooks updates
#
# This doesn't work until Custom fields are supported in QODBC
#
#foreach $line (@qb_update) {
#    print $line . "\n";
#    if ($db->Sql($line)) {
#        print "SQL failed.\n";
#        print "Error: " . $db->Error() . "\n";
#        $db->Close();
#        exit;
#    }
#}

# Close the quickbooks link
$db->Close();
undef $db;
exit;

$SqlStatement = "SELECT * FROM ItemInventoryAssembly ORDER by FullName
+";
if ($db->Sql($SqlStatement)){
    print "SQL failed.\n";
    print "Error: " . $db->Error() . "\n";
    $db->Close();
    exit;
}

#my $dbh = DBI->connect($mysqldsn, $db_user_name, $db_password);

while($db->FetchRow()){
#$db->FetchRow();
    undef %Data;
    %Data = $db->DataHash();
#    print Dumper(\%Data) . "\n";
#    print $Data{'FullName'} . "\t" . 
#        $Data{'PurchaseCost'} . "\t" . 
#        $Data{'SalesPrice'} . "\n";
#    if (($Data{'ParentRefFullName'} eq 'WS') or
#        ($Data{'Name'} eq 'WS')) {
        print Dumper(\%Data);
    if ($Data{'PurchaseDesc'} =~ /category/i) {
        print $Data{'PurchaseDesc'} . "\n";
    } else {
        $query = ("INSERT INTO products (products_id, products_quantit
+y, products_model, products_price, products_date_added, products_stat
+us, products_tax_class_id) VALUES(NULL, '" . 
                  $Data{'QuantityOnHand'} . "', '" .
                  $Data{'FullName'} . "', '" .
                  $Data{'SalesPrice'} . "', '" .
                  $Data{'TimeCreated'} . "', '" .
                  "1', '1')");
        print $query . "\n";
#        $dbh->do($query);
        }
#    }
}

$db->Close();

sub find_product_id {
    ($Data) = @_;
    my $sth;

    if (defined($Data{'CustomFieldWebProductId'})) {
        $products_id = $Data{'CustomFieldWebProductId'};
        print "ID: " . $products_id if $debug > 0;
    } else {
        if ($Data{'PurchaseDesc'} =~ /category/i) {
            $query = ("SELECT categories_id " .
                      "FROM categories_description " .
                      "WHERE categories_name = '" . $Data{'FullName'} 
+. "'");
            $sth = $dbh->prepare($query);
            $sth->execute();
            
            undef $products_id;
            
            # Were there any rows returned?
            if ($sth->rows > 0) {
                $ref = $sth->fetchrow_hashref();
                $products_id = $$ref{'categories_id'};
                print "CID: "; # . $products_id if $debug > 0;
            }
            $sth->finish();
        } else {
            $query = ("SELECT products_id " .
                      "FROM products_description " .
                      "WHERE products_name = '" . $Data{'Name'} . "'")
+;
            $sth = $dbh->prepare($query);
            $sth->execute();
            
            undef $products_id;
            
            # Were there any rows returned?
            if ($sth->rows > 0) {
                $ref = $sth->fetchrow_hashref();
                $products_id = $$ref{'products_id'};
                print "PID: "; # . $products_id if $debug > 0;
            }
            $sth->finish();
        }
    }

    return $products_id;
}
[download]

Considered by DrHyde: use a url shortener so as not to geborkle page layouts (Keep/Edit/Delete vote = 16/16/0).
Unconsidered by davido: No consensus in vote. Please use clear considerations.

Comment on MySQL Data Insertion Errors w/ Perl Select or Download Code

Replies are listed 'Best First'.
Re: MySQL Data Insertion Errors w/ Perl by dragonchild (Archbishop) on Nov 19, 2004 at 15:51 UTC
Lookup up placeholders in the DBI documentation. It's a quoting issue and placeholders solves it for you. You're saying your company paid for that script? If so, then you might want to think about suing them for breach of security and lack of competence. Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply]
Re^2: MySQL Data Insertion Errors w/ Perl by ingsoc747 (Acolyte) on Nov 19, 2004 at 15:57 UTC
No, we didn't pay for it--it was a quick perl hack contribution I found on the osCommerce Community site. Thanks for your help!	[reply]
Re: MySQL Data Insertion Errors w/ Perl by Yendor (Pilgrim) on Nov 19, 2004 at 16:22 UTC
When you point out line 246, the first thing that I noticed is that this piece of code that you've downloaded does not use SQL placeholders. Read more about SQL placeholders. Since this script `use`s `DBI`, the DBI module should take care of the single-quote problem that you explain. Also, since you state that you are "not very adept at Perl", I have a suggestion for you. First would be to stop using this script in favor of one that has better security (lack of SQL placeholders is considered a high security risk if end users are allowed to enter values that are going into the SQL. Super Search or Google on "SQL injection attack". Alternately, you can get more adept at Perl -- or hire someone who already is -- if you're set on continuing usage of this script. -Yendor IWeThey HOPE Ride	[reply]
Re^2: MySQL Data Insertion Errors w/ Perl by ingsoc747 (Acolyte) on Nov 19, 2004 at 16:43 UTC
I understand the risk of SQL injection attacks; however, I don't think that it is really a problem in this situation because this script will be used only by me to update the inventory daily. It will not be hosted on the website. I am, however, attempting to update the script to use placeholders. In addition to its advantages, it will help me to learn Perl a little bit better :) Thanks.	[reply]
Re^3: MySQL Data Insertion Errors w/ Perl by DrHyde (Prior) on Nov 22, 2004 at 09:47 UTC
OK, so you can trust the user of the script. Fair enough. Do you trust the data that you are feeding to it? Your data is, effectively, executable code.	[reply]
Re: MySQL Data Insertion Errors w/ Perl by trammell (Priest) on Nov 19, 2004 at 16:50 UTC
Save yourself much wailing and gnashing of teeth: `use strict; use warnings;` [download]	[reply] [d/l]
Re^2: MySQL Data Insertion Errors w/ Perl by ingsoc747 (Acolyte) on Nov 19, 2004 at 17:26 UTC
OK... but what do these do?	[reply]
Re^3: MySQL Data Insertion Errors w/ Perl by trammell (Priest) on Nov 19, 2004 at 17:50 UTC
In a nutshell, they catch many mistakes commonly made in Perl. For more details, your local Perl install has the full scoop: `% perldoc strict % perldoc warnings` [download] Update: see also Some things that will make your life easier as a Perl coder	[reply] [d/l]
Re^4: MySQL Data Insertion Errors w/ Perl by ingsoc747 (Acolyte) on Nov 19, 2004 at 18:39 UTC
Re: MySQL Data Insertion Errors w/ Perl by freddo411 (Chaplain) on Nov 19, 2004 at 18:46 UTC
From the tone and content of your post, I can tell you are thoughtful and serious about this work. The tone of the replies seem a bit harsh, but I'm sure that is because we are shocked, shocked I say, by the code in the script. ;-) Don't take the tone too personally, but do try to address the issues brought up. In particular, using place holders isn't hard at all Read up on DBI recipes for detailed help. Since you are new to Perl, it might not seem easy, but keep at it and it will. good luck ------------------------------------- Nothing is too wonderful to be true -- Michael Faraday	[reply] [d/l]
Re^2: MySQL Data Insertion Errors w/ Perl by ingsoc747 (Acolyte) on Nov 19, 2004 at 18:57 UTC
Thanks for the encouragement... I'm just happy that I can admit that I'm not the original programmer behind this code! Unfortunately, there aren't any other easy solutions to this particular need, so I'm kind of stuck using it for the time being. Thankfully, you guys have been a big help. BTW, I've gotten those problem items to insert using placeholders... now it's time to go about and renovate the rest of the code, so to speak.	[reply]