Welcome to WebmasterWorld Guest from 34.229.126.29

Forum Moderators: coopster & jatar k & phranque

Using DBI, need to send MySQL commands through bind

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

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1095
votes: 103


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.
7:54 am on Apr 26, 2019 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:11682
votes: 205


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;
1:34 pm on Apr 26, 2019 (gmt 0)

Administrator from US 

WebmasterWorld Administrator brett_tabke is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Sept 21, 1999
posts:38246
votes: 108


That looks like it should work Phranque. I just tried it and it went through find.
3:04 am on Apr 27, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1095
votes: 103


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.
3:21 am on Apr 27, 2019 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:11682
votes: 205


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

source: https://perldoc.perl.org/perlop.html#Quote-and-Quote-like-Operators
5:10 am on Apr 27, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1095
votes: 103


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.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members