ZF-1563: QuoteInto doesn't work with Sqlite

Issue Type:
Bug
Created:
2007-06-14T05:37:18.000+0000
Last Updated:
2008-06-19T14:22:43.000+0000
Status:
Resolved
Fix version(s):
  • 1.5.2 (15/May/08)
Reporter:
Julien Duponchelle (noplay)
Assignee:
Simon Mundy (peptolab)
Tags:
  • Zend_Db
Related issues:
Attachments:

Description

We discover a problem with quoteInto and SQLite.

The Zend Framework generate this kind of SQL (we use quoteInto for projects_id and users_id):


DELETE FROM "usvn_users_to_projects" WHERE (projects_id = '1' AND users_id = 1)

But in SQlite quote seems to be mandatory and we need write:


DELETE FROM "usvn_users_to_projects" WHERE (projects_id = '1' AND users_id = '1')

We made an ugly hack to fix the problem into Zend/Db/Adapter/Pdo/Sqlite.php


public function quote($value)
{
    if (is_int($value) || is_float($value)) {
       return parent::quote("$value");
    }
    return parent::quote($value);
}

Comments

Posted by Bill Karwin (bkarwin) on 2007-06-14T15:06:54.000+0000

I'm not sure I understand. Are the SQL datatypes of projects_id and users_id integer or varchar?

Does SQLite have a problem comparing an integer literal to a varchar? Some RDBMS brands have the opposite problem, you can't compare a string literal to an integer column.

You also have a workaround:


$expr = $db->quoteInto('users_id = ?', (string) 1);

Posted by Bill Karwin (bkarwin) on 2007-06-14T15:07:41.000+0000

This is not a critical bug, since there are already two workarounds.

I'd be glad to fix it regardless, but I'm reducing the severity.

Posted by Bill Karwin (bkarwin) on 2007-06-14T15:11:50.000+0000

Resolved in revision 5314.

Posted by Bill Karwin (bkarwin) on 2007-06-28T21:00:10.000+0000

Reopening. SQLite does require that integers are compared to integers, and strings are compared to strings.

Posted by Bill Karwin (bkarwin) on 2007-06-28T21:01:28.000+0000

Fixed in revision 5493.

Posted by Julien Duponchelle (noplay) on 2007-09-27T04:58:06.000+0000

Problem is still present. That's why we use this patch:

=== www/Zend/Db/Adapter/Pdo/Sqlite.php

--- www/Zend/Db/Adapter/Pdo/Sqlite.php (revision 1088) +++ www/Zend/Db/Adapter/Pdo/Sqlite.php (local) @@ -278,4 +278,22 @@ return $sql; }

  • /**
    • Safely quotes a value for an SQL statement.
  • *
    • If an array is passed as the value, the array values are quoted
    • and then returned as a comma-separated string.
  • *
    • @param mixed $value The value to quote.
    • @param mixed $type OPTIONAL the SQL datatype name, or constant, or null.
    • @return mixed An SQL-safe quoted value (or string of separated values).
  • */
  • public function quote($value, $type = null)
  • {
  • if (is_int($value) || is_float($value)) {
  • return parent::quote("$value", $type);
  • }
  • return parent::quote($value, $type);
  • }
  • }

Posted by Bill Karwin (bkarwin) on 2007-10-17T15:31:54.000+0000

Unmark fixed version.

Posted by Wil Sinclair (wil) on 2008-03-21T17:05:28.000+0000

This issue should have been fixed for the 1.5 release.

Posted by Wil Sinclair (wil) on 2008-03-25T20:43:51.000+0000

Please categorize/fix as needed.

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

This doesn't appear to have been fixed in 1.5.0. Please update if this is not correct.

Posted by Simon Mundy (peptolab) on 2008-06-19T00:37:11.000+0000

Hi Julian

I'm looking into this for the 1.6 release if it's able to be resolved. Can I clarify - what is the reason for explicitly casting as a string by default? The only concern I have defaulting to a string type is that it will break an existing expected behaviour. Or is SQLite expecting a string and this behaviour has always been incorrect.

The patch itself is straight-forward, as are the tests, but I want to be sure this is absolutely required before committing.

Posted by Julien Duponchelle (noplay) on 2008-06-19T14:22:42.000+0000

I just test and now it's work without our patch. Thanks for your help.