[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