Issues

ZF-1563: QuoteInto doesn't work with Sqlite

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

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);

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.

Resolved in revision 5314.

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

Fixed in revision 5493.

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);
  • }
  • }

Unmark fixed version.

This issue should have been fixed for the 1.5 release.

Please categorize/fix as needed.

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

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.

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