Issues

ZF-1984: Zend_Db_Statement->_stripQuoted() does not escape strings in regular expressions

Description

When using reserved characters like [ and ], the regular expressions in the protected function Zend_Db_Statement->_stripQuoted() throw an exception/error. This is because the strings should be quoted like that:

$q = preg_quote($q); $qe = preg_quote($qe); $d = preg_quote($d); $de = preg_quote($de);

I noticed this problem while using MS-SQL Server (odbtp). Of course, you can set QUOTED_IDENTIFIER to ON and use double quotes instead of brackets (as described in related tickets):

http://msdn2.microsoft.com/en-us/library/aa224033(SQL.80).aspx

This however doesn't change the fact, that chars inserted into a regular expression should be escaped. Delimiters in brackets can always be used, regardless of the setting of QUOTED_IDENTIFIER.

Just an idea, but isn't there an easier way of getting the escape characters from the database adapter? Functions like getIdentifierQuoteStartChar(), getIdentifierQuoteEndChar(), getQuoteStartChar() and getQuoteEndChar()? I would even recommend using public class constants for this purpose.

Comments

Assigning to [~bkarwin] to initiate issue review. Is this issue really a blocker?

Don't use brackets as identifier delimiters. This is not standard SQL, it's bogus Microsoft syntax. The Zend_Db_Adapter_Pdo_Mssql class always executes "{{SET QUOTED_IDENTIFIER ON}}" after connecting, to encourage usage of standard SQL syntax.

I don't think we should add methods to the Db Adapter interface to support non-standard syntax used by a single vendor. The better solution is to use the standard SQL identifier delimiter, which is the double-quote ({{"}}) and is the same for begin and end of a delimited identifier.