ZF-53: quote() quotes numeric values inappropriately.

Description

I notice the quote() method quotes even numeric values which is not necessary.

Its strange this problem is manifest only with scalars. With an array as an argument, it works as expected: quote( '123' ); RESULT: '123'

quote( array( '1', 2, 3, 'a', 'b' )); RESULT: 1, 2, 3, 'a', 'b'

the problem is only with the quote(numeric-scalar). Doing an is_numeric() validation in the quote() method should do the trick.

Comments

05/22/06 11:52:28: Modified by jayson

* component changed from Documentation to Zend_Db.

05/22/06 15:50:20: Modified by rob.nospam@akrabat.com

For the PDO drivers, if this is a bug, then it is in PDO rather than in the framework code.

Zend_Db_Adapter_Pdo_Abstract::quote() calls the PDO drivers quote() function to do the work.

http://uk.php.net/manual/en/function.pdo-quote.php clearly shows that it takes a string, not a mixed, so I suspect that it is by design.

Having said that, the manual page also says: "PDO::quote() places quotes around the input string (if required) and escapes special characters within the input string, using a quoting style appropriate to the underlying driver." The "if required" bit makes me wonder if the PDO drivers are supposed to be cleverer than they are.

Incidentally, I'm seeing quoted numbers in all cases for PDO drivers:


        // test numeric
        $value = $this->_db->quote('1');
        $this->assertEquals("'1'", $value);

        $value = $this->_db->quote(1);
        $this->assertEquals("'1'", $value);

        $value = $this->_db->quote(array(1,'2',3));
        $this->assertEquals("'1', '2', '3'", $value);

all pass.

Yields reasonable output, as expected according to PHP's PDO documentation:


<?
$include_path = ini_get('include_path')   . PATH_SEPARATOR . "./zf/library";
ini_set('include_path',  $include_path); 

require 'zf/library/Zend/Db.php';

$adapt = Zend_Db::factory('PDO_Sqlite',array('dsnprefix'=>'sqlite','dbname'=>'foo.sq3'));
var_dump($adapt);
$adapt->beginTransaction();
var_dump($adapt);

$vals = array( '1', 2, 3, 'a', 'b' );
foreach ( $vals as &$val)

    $val = $adapt->quote($val);

var_dump(implode(', ', $vals));
?>

At some point, integers, strings, dates, and such must be converted from PHP's data types to the database's types. Usually, programmers try to minimize the number of conversions for various reasons. PDO's choice of normalizing to strings enables developers to see exactly what will be sent to the database, making it easy to test and debug.

"PDO is deliberately somewhat type agnostic in that it prefers to represent data as strings instead of converting to integer or double types. You might wonder at this, but the reasoning is quite simple: the string type is the type that is most precise and has the largest range available in PHP; prematurely converting data into integers or doubles can lead to either truncation or rounding errors. By pulling the data out as strings, PDO gives your script control over how and when the conversion happens using the usual PHP type juggling facilities (such as casting and implicitly during mathematical operations)."

This actually is an issue, for example for DB2 and MySQL.

An expression like "columnName = '12'" fails on DB2 because it has no implicit way of converting a string to an integer.

This is also an issue on MySQL, because a comparison of an integer column to a string spoils the optimizer. It can't use an index on the column because it thinks it's comparing against an expression, not a constant of the matching type. So it results in very bad performance.

Fixed in revision 3520.

Note that I used is_int(), not is_numeric(). is_int() returns true only for actual PHP integer scalars, but is_numeric() returns true for strings that contain characters valid for a numeric value. I want strings to continue to be quoted even if they appear numeric, so that strings like '000123' where the leading zeroes are significant can be inserted (this is not an unusual case).

Unit tests updated in revision 3521.

Since version 1.0.0 production release, it raise un issue on MS SQL server. When inserting data in a string field, if the data is numeric, it raise un SQL error. There should have an optional boolean parameter in the quote function wich enable or not the is_numeric test.