[Solar-talk] MySQL type conversion bug in Solar_Sql_Adapter

[Jeff Moore]

Hi,

I've found a rather insidious bug in Solar_Sql_Adapter::quote

This function uses the php is_numeric function to shortcut using the  
driver built in quoting function, so that if a numeric value is passed  
it won't be quoted in the standard manner.  So for example, a where  
clause fragment may end up looking like:

id = 2008

rather than

id = '2008'

However, a problem occurs when this is combined with MySQL's mixed  
type comparison rules.
(http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html)

If a string and a numeric are compared, the string is converted into a  
number for the comparison.  For example, the following is TRUE in MySQL:

SELECT "2008/u-r-screwed" = 2008

While this comparison is of course false:

SELECT "2008/u-r-screwed" = "2008"

So, where you have a string column defined and you use solar to quote  
a value to compare against it and that value happens to contain a  
valid string representation of a number, then you have the potential  
to match more records than you bargained for.

Additionally, MySQL cannot use an index for the column in this type of  
comparison, which may cause your query to be inexplicably slow in the  
case where a quoted value happens to be numeric.

I'd recommend modifying Solar_Sql_Adapter::quote to remove the  
is_numeric shortcut.

Jeff
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman-mail3.webfaction.com/pipermail/solar-talk/attachments/20080310/7fa3f798/attachment.html