Perl and MYSQL insert/update using quote 3

[bneese]

I have a Perl script that is running a WMI query against a PC and then populating a MYSQL DB. The problem is that if the returned result has a quote in it like "Daemon's Home" it fails when it goes to do the insert. Here is a sample of my code:
-----------------------------------------------------------
foreach my $PRINFO ( in( $WMIServices->InstancesOf( $WIN32_PR ) ) )
{
$PRNUM = ++$PRNUMCount;
$sth = $db->prepare("UPDATE win32_product set
Vendor='PRINFO->{Vendor}'
WHERE PCID = $PCID AND PRNUM = $PRNUM");
$sth->execute();
}
-----------------------------------------------------------
First, is this a Perl issue or MYSQL issue. I believe it is a MYSQL issue but I can't figure out how to get by this. 'PRINFO->{Vendor} brings back Daemon's Home but then fails in the update. Here is the error:

-----------------------------------------------------------
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'S HOME'
-----------------------------------------------------------

 

[Deleted]

If the value for PRINFO->{Vendor} is "Daemon's Home"

then the problem is the single quote Daemon's

Do this

my $update = "UPDATE win32_product set " . quote(PRINFO->{Vendor}) . " WHERE PCID = $PCID AND PRNUM = $PRNUM";
$sth = $db->prepare($update);
$sth->execute();

sub quote {
        return $dbh->quote(shift());      
}

http://unixjunky.com

 

[bneese]

That is awesome! Thanks, now I have 1 more question. What if you wanted to quote in the field in the DB? Could that be done?

 

[Deleted]

Not sure what you are asking. Please give an example.

http://unixjunky.com

 

[bneese]

what if I wanted to insert Daemon's Home into the database instead of Daemons Home?

 

[PaulTEG]

'\s


cigless ...

 

[PaulTEG]

\'s
even

cigless ...

 

[bneese]

Maybe should have explained a little better. When I query
WMI I get "Daemon's House" back as the result. This result could change depending on what record is returned from WMI. I will never know the result up front. Is there a way to take a string and tell PERL to ignore it? Hopefully I explained it OK.

 

[PaulTEG]

$string=~s/'/\\'/g;

HTH
--Paul

cigless ...

 

[ishnid]

This appears to be one of those situations where you should be using placeholders. There's a quick tutorial on using them here. Then DBI takes care of quoting any variables you're putting into your SQL statement.

 

[bneese]

Do I need to setup a subroutine for param? I am getting an error when I run the code:

Undefined subroutine &main:aram called at C:\Perl\scripts\client.pl line 178.

 

[bneese]

Here is my code:

my $Caption=param('PRINFO->{caption}');
my $Vendor=param('PRINFO->{vendor}');
my $insert=$db->prepare("UPDATE win32_product(Caption,Vendor) values(?,?)");
$sth->execute($Caption,$Vendor);
$sth->finish();

 

[bneese]

1 more thing. This script will be compiled as an exe on run as a service on Windows boxes if that makes any difference.

 

[ishnid]

The "param" function in the example I linked to is just an example of where you get the values from (in that case, it looks like the author was using CGI to get the values from a web form). In your case, you already have $PRINFO->{caption} as a variable, so trying to assign it to $Caption is redundant.

 

[bneese]

Thanks for all your help guys. This is what I finally did and all your input helped greatly.

$Caption=$PRINFO->{caption};
$Vendor=$PRINFO->{vendor};
$sth=$db->prepare("UPDATE win32_product set
Caption=?,
Vendor=?
WHERE PCID = $PCID AND PRNUM = $PRNUM");
$sth->execute($Caption,$Vendor);
$sth->finish();

 

[stevexff]

According to the DBI doc, you shouldn't need the finish call, as this isn't cursor based. As ishnid suggests, why create the extra variables? Also, if we are using two placeholders, why not all four?

my $sth = $db->prepare("UPDATE win32_product set
   Caption=?,
   Vendor=?
   WHERE PCID = ? AND PRNUM = ?");

$sth->execute($PRINFO->{'caption'}, $PRINFO->{'vendor'}, $PCID, $PRNUM);

 

[bneese]

This is what I ended up with after cleaning it up some:

foreach my $PRINFO ( in( $WMIServices->InstancesOf( $WIN32_PR ) ) )
{
$PRNUM = ++$PRNUMCount;
$sth=$db->prepare("UPDATE win32_product set
Caption=?,
Description=?,
IdentifyingNumber=?,
InstallDate=?,
InstallDate2=?,
InstallState=?,
Name=?,
PackageCache=?,
SKUNumber=?,
Vendor=?,
Version=?
WHERE PCID = $PCID AND PRNUM = $PRNUM");
$sth->execute($PRINFO->{Caption},$PRINFO->{Description},$PRINFO->{IdentifyingNumber},$PRINFO->{InstallDate},$PRINFO->{InstallDate2},$PRINFO->{InstallState},$PRINFO->{Name},$PRINFO->{PcakageCache},
$PRINFO->{SKUNumber},$PRINFO->{Vendor},$PRINFO->{Version});
}

 

[bneese]

And yes, you are right. I should use the placeholders for all 4

 

[stevexff]

Assuming you pasted it, you have a typo in PcakageCache which will fail silently if you don't have warnings on. But you do use Warnings;, right?

 

[bneese]

Actually I have never used "Use Warnings" do I need to load a new module for that or is it built in?