DB select() escaping -> error on spaces

[timo1]

Version: 2.1.1

The sql statement get destroyed when spaces in it.

Example Table:

CREATE TABLE IF NOT EXISTS `test` (
  `ts` datetime NOT NULL,
  `ts2` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `test` (`ts`, `ts2`) VALUES
('2012-06-12 21:01:05', '2012-06-11 21:01:05');

Code:

working correct:

$this->db->select('ts, SUM(CEIL((UNIX_TIMESTAMP(ts)-UNIX_TIMESTAMP(ts2))/60)) AS xy', FALSE);
$this->db->from('test');

$q = $this->db->get();
print_r($q->row_array());

error occurs (spaces arround "CEIL())":

$this->db->select('ts, SUM( CEIL((UNIX_TIMESTAMP(ts)-UNIX_TIMESTAMP(ts2))/60) ) AS xy', FALSE);
...

error message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (test)' at line 2

SELECT ts, SUM( CEIL((UNIX_TIMESTAMP(ts)-UNIX_TIMESTAMP(ts2))/60) ) AS xy FROM (test)

[lerio]

Same here. Datamapper 1.8.2 Library is broken too.

Here's the diff between DB_driver.php 2.1.0 and 2.1.1, and that's where the issue probably lies:

        // Convert tabs or multiple spaces into single spaces
-       $item = preg_replace('/[\t ]+/', ' ', $item);
+       $item = preg_replace('/\s+/', ' ', $item);
 
        // If the item has an alias declaration we remove it and set it aside.
        // Basically we remove everything to the right of the first space
-       $alias = '';
-       if (strpos($item, ' ') !== FALSE)
+       if (preg_match('/^([^\s]+) (AS )*(.+)$/i', $item, $matches))
        {
-           $alias = strstr($item, " ");
-           $item = substr($item, 0, - strlen($alias));
+           $item = $matches[1];
+
+           // Escape the alias
+           $alias = ' '.$matches[2].$this->_escape_identifiers($matches[3]);
        }
+       else
+       {
+           $alias = '';
+       }

[Nom4d3]

It seems this bug is related to this post:

http://codeigniter.com/forums/viewthread/219130/

[WanWizard]

Looking at the code, it seems that

if (preg_match('/^([^\s]+) (AS )*(.+)$/i', $item, $matches))

also matches when there is no "AS " in the string, due to the * following it (meaning zero or more matches). So it escapes stuff it shouldn't.

[WanWizard]

I get Datamapper back operational by changing that regex to

if (preg_match('/^([^\s]+) (AS )*([^\s]+)$/i', $item, $matches))

So basically only deal with the formats "string string" and "string AS string".

[narfbg]

There's more to it than that. That regular expression is intended to escape aliases as well as field names, but as it turned out - it's breaking more stuff than what it has fixed. For example ORDER BY fields are passed to that method along with ASC/DESC, WHERE fields along with operators after them, etc., etc.

The quick fix is to revert to the previous version - expect that in the new release shortly (as it is already in the 2.1-stable branch).

[WanWizard]

Any fix is good. ;)

[narfbg]

Version 2.1.1 has been re-released (under the same version number), with a fix for this bug included. Go download it again at http://codeigniter.com/downloads/ :)

[Nom4d3]

Great! I did some quick tests here and it seems that everything is working nice now!

Thanks!

[mcryan]

Excellent, thanks. Will download and test!

[lerio]

Working fine now. Thank you very much