Re: [fw-mvc] Zend_Db quote backslashes issue

Viktor,

It appears that it's likely incorrect only in the case of LIKE comparisons
due to MySQL treating LIKE differently than other operations. Changing it
(Zend Framework) to double escape in any circumstance would likely break it
for everything but LIKE comparisons.

Could you do something like:

$path = "C:\some\path\with\backslahes"
; // in reality the value comes from a config.ini
$this->fetchAll($this->select()->where('path LIKE ?', addslashes($path)));

as a work around? I think the fix would involve ZF inspecting the first
parameter to where() to determine if a like was being used and then doing
the escaping differently, or requiring a different way of handling likes
outside of the normal where() flow.

I verified running a query with = against paths and indeed, if = is used and
the backslashes are double escaped, it will not work, but single escaped
(ie, c:\\) does work.

David

On Thu, Dec 30, 2010 at 1:33 PM, Viktor Grandgeorg <info@intelligibel.de>wrote:

>
> Hello,
>
> > The query Zend generates looks right to me. What makes you think it
> > should be producing something with groups of four slashes?
>
> it might look right to you, but is definitely wrong, as you can see in the
> Note in the MySQL Reference Manual
> under:
> http://dev.mysql.com/doc/refman/5.5/en/string-comparison-functions.html
>
> It's what it says, that makes me think it should be producing something
> with groups of four slashes:
>
> 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.
>
> So there it is:
> To search for "\", specify it as "\\\\";
>
>
> > What does the data actually look like when you select it out of the
> > DB? It should have only one slash (without you needing to do
> > stripslashes).
>
> Of course it is coming out of the db like:
> C:\some\path\with\backslahes
>
> Regards
> Viktor
>
>
>
> > -----Original Message-----
> > From: Paul McGarry [mailto:paul@paulmcgarry.com]
> > Sent: Wednesday, December 29, 2010 10:24 PM
> > To: info@intelligibel.de
> > Cc: fw-mvc@lists.zend.com
> > Subject: Re: [fw-mvc] Zend_Db quote backslashes issue
> >
> > On Thu, Dec 30, 2010 at 2:27 AM, Viktor Grandgeorg
> > <info@intelligibel.de> wrote:
> > > Hello,
> > >
> > > it seems that Zend_Db does not escape backslashes right -
> > or I'm doing something wrong:
> > >
> > > in my db table "dir" I have a column named "path" with the
> > folowing entry:
> > >
> > > C:\some\path\with\backslahes
> > >
> > > In my DbTable class I'm doing something like:
> > >
> > > $path = "C:\some\path\with\backslahes"; // in reality the
> > value comes from a config.ini
> > > $this->fetchAll(
> > > $this->select()->where('path LIKE ?', $path));
> > >
> > > Now Zend_Db generates the following query:
> > >
> > > SELECT `dir`.* FROM `dir` WHERE (path LIKE
> > 'C:\\some\\path\\with\\backslahes')
> > >
> > > This is wrong and the corresponding row never gets matched.
> > > It must be the following SQL query to match the right row(s):
> > >
> > > SELECT `dir`.* FROM `dir` WHERE (path LIKE
> > 'C:\\\\some\\\\path\\\\with\\\\backslahes')
> > >
> > > Can somebody confirm this issue?
> >
> > The query Zend generates looks right to me. What makes you think it
> > should be producing something with groups of four slashes?
> >
> > What does the data actually look like when you select it out of the
> > DB? It should have only one slash (without you needing to do
> > stripslashes).
> > If it is coming out of the db like:
> > C:\\some\\path\\with\\backslahes
> > then it has been escaped too many times when you inserted it.
> >
> >
> > Paul
> >
>
>