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