ZF-3448: Zend_Db_Adapter_Abstract::quote() does not properly handle ENUM values...

Issue Type: Bug Created: 2008-06-11T13:54:31.000+0000 Last Updated: 2012-08-31T09:07:49.000+0000 Status: Open Fix version(s): Reporter: David Rogers (al_the_x) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db

Related issues: Attachments:


The logic for quoting fields in Zend_Db_Adapter_Abstract::quote() is insufficient to address fields of type ENUM that employ numeric values, even when the explicit data type is passed as the second argument. For example, a field in SQL with type "ENUM('1','0','-1')" should always quote values, particularly numeric values, before assembling a query, but this never occurs for numeric values, resulting with inaccurate queries. Casting the value of the field to string within PHP is a useful workaround for this issue, as Zend_Db_Adapter_Abstract no longer treats it as a numeric value.


Posted by Ashley Kitson (akzincdig) on 2008-06-28T08:59:12.000+0000

Zend_Db doesn't appear to properly handle enumerated types at all!

In a call to db->update($table,$data,$where) any enumerated values are not written to the database even though the $data array contains values for the enumerated type fields. (the data values have been previously passed through db->quote() )

Same holds true for db->insert(), no enumerated types are written to the database

Database = MySql Driver/Adapter = Pdo_Mysql

Posted by David Rogers (al_the_x) on 2008-06-28T09:09:02.000+0000

You're right to include your comment on this issue, then... MySQL silently ignores values for fields that it doesn't understand or don't match its field definitions... Try setting a too-long value to a VARCHAR field to see what I'm talking about...

Does this only affect ENUM's that contain numeric values for you? I don't notice the same behavior with TINYINT. I have a patch to extract and submit, if it will be reviewed.

Posted by Ashley Kitson (akzincdig) on 2008-06-28T09:10:28.000+0000

Additional note. Actually a blank value is written to the database for an enumerated value. Create a table with a enum value that defaults to one of its allowed values. Insert a record to the table, without setting a value for the enum field. It will default to whatever you set it on the database. Now update the record, this time passing a value for the enum field. It will get overwritten with a blank.

Posted by Ashley Kitson (akzincdig) on 2008-06-28T09:14:56.000+0000

David said "Does this only affect ENUM's that contain numeric values for you"

I tend not to use numeric values for enums so don't experience that particular issue. I use string values e.g.

CREATE TABLE IF NOT EXISTS syst_test ( pid int(11) NOT NULL auto_increment, uVal int(11) NOT NULL, strVal varchar(30) default NULL, dateVal date default NULL, textVal text, floatVal float default NULL, enumVal enum('one','two') default NULL, rowSts enum('active','suspended','defunct') NOT NULL default 'active' COMMENT 'Row Status', rowUid int(11) NOT NULL default '0' COMMENT 'Last Edited By', rowDt timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'Edit Datetime', PRIMARY KEY (pid), UNIQUE KEY uVal (uVal) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

Posted by Ashley Kitson (akzincdig) on 2008-06-28T09:21:23.000+0000

A little further delving seems to reveal that the Zend layer is doing everything you would expect. It properly puts quotes around an enum value. It is when the pdo layer is called to do the update that the value disappears. So it my be PDO, not Zend_Db at fault. Can't debug PDO in Zend Studio, so can get no further :-(

Posted by David Rogers (al_the_x) on 2008-06-28T09:57:16.000+0000

We haven't had the issue you're describing with string values and ENUM fields at all, Ashley, only those fields that contain a numeric ENUM value. Our solution has just been to cast the number to a string value, ie :

<pre class="highlight">
-- Sample Schema in Database 'test'
'one_or_zero' ENUM('1','0') DEFAULT '1',
'true_or_false' ENUM('true','false) DEFAULT 'false',

( '1', 'true' ),
( '1', 'false' ),
( '0', 'true' ),
( '1', 'false' );

<pre class="highlight">
$Adapter = Zend_Db::factory('Pdo_Mysql', array('host' => 'localhost', 'user' => 'root', 'dbname' => 'test');

// Expected - integer: 2
    $Adapter->select()->from('enum_test', array('count' => 'COUNT(*)'))
        ->where('one_or_zero = ?', 1)
// Actual - integer: 0

// Expected - integer: 2
    $Adapter->select()->from('enum_test', array('count' => 'COUNT(*)'))
        ->where('one_or_zero = ?',  (string) 1)
// Actual - integer: 2

What you're describing - correct me if wrong - is something like the following:

<pre class="highlight">
// Same as above...

// Expected - integer 1 (number of rows affected)
    $Adapter->insert(array('true_or_false' => 'true'))
// Actual - integer 1

// Expected - integer 3 (2 originals plus newly inserted value)
    $Adapter->select()->from('enum_test', array('count' => 'COUNT(*)'))
        ->where('true_or_false = ?', 'true')
// Actual - integer 0 (query got messed up somewhere...!)

What's version of PHP you're using, as this may be the culprit instead.

Posted by Ashley Kitson (akzincdig) on 2008-06-28T10:06:24.000+0000

I had a spare minute so I checked to see if anything different happened by changing the adapter to Mysqli. It didn't - still doen't work.

However just to prove it isn't MySql, I ran the following bare bones mysql query:

    $db = mysql_connect('localhost','MyDb','MyDb');
    $sql = "update syst_test set enumVal = 'two' where pid = 10";
    $ret = mysql_db_query('MyDb',$sql,$db);

That works just fine, the enum value gets updated.

So it's not SQL. If changing adapters proves anything, its not the adapter. Ergo it's Zend_Db.

Posted by Ashley Kitson (akzincdig) on 2008-06-28T10:13:32.000+0000

I'm using PHP 5.2.3

"$Adapter->insert(array('true_or_false' => 'true'))" Yep, that's about right

(got t go now - will catch up 2morrow.)

Posted by Wil Sinclair (wil) on 2008-12-04T13:17:30.000+0000

Reassigning to Ralph since he's the new maintainer of Zend_Db

Posted by David Rogers (al_the_x) on 2009-03-02T12:21:52.000+0000

Can I just submit a patch for this issue?

Posted by Ralph Schindler (ralph) on 2009-12-21T12:55:19.000+0000

Yes, I would like to see a patch- do you have one that addresses this issue?


Posted by David Rogers (al_the_x) on 2010-04-01T13:51:27.000+0000

I have a patch for this issue, but as there are no tests for the Abstract class, I haven't run the tests on every driver. With some direction there, I could do so, however. Please advise. Thanks.

Posted by Ralph Schindler (ralph) on 2011-02-17T15:32:24.000+0000

Can you attache the issue to this ticket?

Posted by David Rogers (al_the_x) on 2011-02-17T16:58:48.000+0000

I don't quite follow, Ralph. What do you mean by "attach the issue to this ticket"...?

Posted by Ralph Schindler (ralph) on 2011-02-17T17:34:11.000+0000

Ha, sorry. I mean patch. You said you had a patch for this issue.

Posted by Leopold Chmielewski (poldekch) on 2011-03-07T06:00:16.000+0000

David, can you post a patch for this issue, I urgently need it...

Posted by David Rogers (al_the_x) on 2011-03-07T09:11:11.000+0000


I'll try to dig that up and send it your way. I think that I isolated the issue to Zend_Db_Table when I last worked on this, since only Table instances have the metadata about the column to know how to coerce the values. I originally tried to mod Zend_Db_Adapter_Mysql, but without the metadata... Anyway. I'll put a patch together this week.


Posted by Kim Blomqvist (kblomqvist) on 2011-03-28T19:18:21.000+0000

Any progress on the patch?

Posted by Stephane Mennesson (smennesson) on 2011-04-26T15:32:58.000+0000

Hello I'm experiencing the same problem with Zend 1.11.4 A temporary solution for users that can't wait for the patch is to use Zend_Db_Expr: $Adapter->insert(array('true_or_false' => new Zend_Db_Expr("'true'")))

Have you found an issue?

See the Overview section for more details.


© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.