Using DBI, need to send MySQL commands through bind

Forum Moderators: coopster & phranque

Message Too Old, No Replies

Using DBI, need to send MySQL commands through bind

csdude55

7:26 am on Apr 26, 2019 (gmt 0)

This is my code:

my $rows = $dbh->do("INSERT INTO table (colA, colB, colC) VALUES (?, ?, ?)", undef,
 $foo, MD5($bar), CURRENT_TIMESTAMP) or die "Couldn't execute INSERT INTO table: " . $dbh->errstr;

The problem I'm having is that Perl is trying to find a Perl function of MD5, instead of just sending "MD5($bar)" and letting MySQL do the MD5() work. And even though it's not showing up in the error log, I suspect that "CURRENT_TIMESTAMP" will have the same problem once I get MD5() straight.

I know that I could just plug CURRENT_TIMESTAMP in without using bind, but how do I send the MD5() when I need to escape $bar? I really don't want to load a module for it when MySQL does it just fine.

phranque

7:54 am on Apr 26, 2019 (gmt 0)

i would try this:

my $rows = $dbh->do("INSERT INTO table (colA, colB, colC) VALUES (?, ?, ?)", undef,
 $foo, 'MD5(' . $bar. ')', CURRENT_TIMESTAMP) or die "Couldn't execute INSERT INTO table: " . $dbh->errstr;

Brett_Tabke

1:34 pm on Apr 26, 2019 (gmt 0)

That looks like it should work Phranque. I just tried it and it went through find.

csdude55

3:04 am on Apr 27, 2019 (gmt 0)

Looks like it worked great! Thanks, phranque :-D

I was getting myself in to an alternate that I think would have worked if I'd kept on, but I didn't like it AT ALL:

$sth = $dbh->do("INSERT INTO table (colA, colB, colC) VALUES (
 $dbh->quote($foo),
 'MD5(' . $dbh->quote($bar) . ')',
 CURRENT_TIMESTAMP)") or die "Couldn't execute INSERT: " . $dbh->errstr;

In theory, using $dbh->quote() would be the same as using bind, but it was getting a lot harder to keep up with.

phranque

3:21 am on Apr 27, 2019 (gmt 0)

single quote-delimited strings are treated literally and without interpolation.

source: https://perldoc.perl.org/perlop.html#Quote-and-Quote-like-Operators

csdude55

5:10 am on Apr 27, 2019 (gmt 0)

Wait, I spoke too soon! It was actually storing a literal "MD5(whatever)" instead of running the MD5() function. And CURRENT_TIMESTAMP was giving me the same problem.

But I did find a solution:

my $rows = $dbh->do("INSERT INTO table (colA, colB, colC) VALUES (?, MD5(?), CURRENT_TIMESTAMP)", undef,
 $foo, $bar) or die "Couldn't execute INSERT INTO table: " . $dbh->errstr;

Short answer, move the functions to the query instead of the wildcards.