Re: [fw-mvc] Zend_Db quote backslashes issue

Response inline.

On 12/30/10 5:30 AM, Viktor Grandgeorg wrote:
> Hello,
>
> you know wrong, like it clearly says in the note in chapter
> 11.5.1. "String Comparison Functions" in the MySQL Reference Manual
> under:
> http://dev.mysql.com/doc/refman/5.5/en/string-comparison-functions.html
>
> Because MySQL uses C escape syntax in strings (for example, "\n" to represent a newline character), you must double any "\" that you
> use in LIKE strings. For example, to search for "\n", specify it as "\\n". To search for "\", specify it as "\\\\"; this is because
> the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched
> against.
>
> This is the same for MySQL versions< 5.5
>
> So this must be a bug in Zend_Db's quote mechanism.

No, this is not a concern of Zend_Db's quote mechanism. Zend_Db's quote
mechanism is strictly quoting strings for platform->database safety, for
nothing more.

What you are asking here is for Zend_Db to have know the context of your
query and fix it appropriately. In your example:

$this->fetchAll(
$this->select()->where('string LIKE ?', $match)
);

Zend_Db does not know you are using LIKE as it is part of the where
clause string. If LIKE has special quoting rules in Mysql, Zend_Db
cannot know about this, and would not know what special rules to apply.

Case in point, if you use = as opposed to LIKE, it works perfectly fine.
Zend_Db would have no knowledge if you used = or LIKE in that query.

Put another way, if you used REGEXP in your query, would you expect
Zend_Db to fix your regular expression?

$this->fetchAll(
$this->select()->where('string REGEXP ?', $match)
);

Basically, if you are using LIKE in your where clause, your parametrized
values must be prepared in such a contextually valid way before supply
it for parametrization. Zend_Db will only quote for safety.

-ralph