Using DBI, need to send MySQL commands through bind

[csdude55]

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]

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]

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

[csdude55]

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]

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

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

[csdude55]

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.