[interchange-bugs] [rt.icdevgroup.org #337] DBI does not quote column names -> reserved words will break query
Peter
peter at pajamian.dhs.org
Wed Feb 24 23:43:45 UTC 2010
On 24/02/10 17:39, David Christensen via RT wrote:
> <URL: http://rt.icdevgroup.org/Ticket/Display.html?id=337 >
>
> On Tue Jan 05 09:09:12 2010, thunder wrote:
>> If a column name turns out to be (or become) a reserved word then
>> queries in Interchange will break (ie set_slice etc) .. This is
>> because column names are not quoted.
>>
>> (pj: the DBI method quote_identifier() is specifically for that
>> purpose.)
>>
>> Example:
>> Mysql 4 -> 'condition' would be a column name without a problem
>> Mysql 5 -> 'condition' is a reserved word and needs to be quoted
>>
>
>>From lib/Vend/Table/DBI.pm
> 1265 }
> 1266 }
> 1267
> 1268 $tkey = $s->quote($key, $s->[$KEY]) if defined $key;
> 1269 #::logDebug("tkey now $tkey");
> 1270
> 1271 my $force_insert =
>
> Would this be sufficient to change 1268 to:
>
> 1268 $tkey = $s->[$DBI]->quote_identifier($key, $s->[$KEY]) if defined $key;
No, you're confusing the quoting of data with the quoting of
identifiers. IC currently quotes data just fine (and that's what line
1268 does here). If you change that to quote_identifier then it will
put the wrong type of quoting on the data and cause mysql to spit the
dummy (so to speak).
What you're looking for are the various $db methods in DBI.pm where SQL
queries are formed. Identifiers (ie column names) should be quoted as
per the following example with set_slice:
Change the following lines:
unshift @$fary, $s->[$KEY];
unshift @$vary, $key;
my $fstring = join ",", @$fary;
my $vstring = join ",", map {"?"} @$vary;
$sql = "insert into $s->[$TABLE] ($fstring) VALUES
($vstring)";
To this:
unshift @$fary, $s->[$KEY];
unshift @$vary, $key;
foreach (@$fary) { $_ = $s->[$DBI]->quote_identifier($_) }
my $fstring = join ",", @$fary;
my $vstring = join ",", map {"?"} @$vary;
$sql = "insert into $s->[$TABLE] ($fstring) VALUES
($vstring)";
You'll want to do something like that for all the methods in DBI.pm
Peter
More information about the interchange-bugs
mailing list