Sorry for asking possibly stupid questions, but why use a complicated library for something like this when you can just write the query directly and insert safed values? Something like this:
use strict;
use warnings;
### Matches negatives, decimals
sub isNum {
return $_[0] =~ m/^-?(?:\d*\.)?\d+$/;
}
### Converts non-safe characters in string
sub safeString {
my $val = $_[0];
$val =~ s/(['"\\\x00\x1a])/\\$1/g;
return $val;
}
### Determines type of value, converts string
sub safeValue {
my $val = $_[0];
return "'" . safeString($val) . "'"
if length($val) == 0 || !isNum($val);
return $val;
}
### Safes all inserted values
### Inserts strings with quotes if {key} is used
### Inserts without quotes if [key] is used
sub queryMysql {
my ($query, $args) = @_;
$query =~ s/\[(\w+)\]/safeString($args->{$1})/eg;
$query =~ s/{(\w+)}/safeValue($args->{$1})/eg;
return $query;
}
my %values = (
'table' => 'tablename',
'x' => '123',
'y' => 'alpha',
'z' => -45.67,
'colname' => 'COLB',
'from' => '20110101',
'to' => '20110131'
);
print queryMysql('
UPDATE [table]
SET x = {x}, y = {y}, z = {z}
WHERE [colname] BETWEEN {from} AND {to}',
\%values);
### UPDATE tablename
### SET x = 123, y = 'alpha', z = -45.67
### WHERE COLB BETWEEN 20110101 AND 20110131
I hacked this together years ago for situations where I don't want to bother messing with the ? ? and separate array format of DBI. Could probably take a few minutes and write a function for generating inserts and updates as well.