diff options
| -rw-r--r-- | system/database/DB_query_builder.php | 60 | ||||
| -rw-r--r-- | tests/codeigniter/database/query_builder/join_test.php | 27 |
2 files changed, 59 insertions, 28 deletions
diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index d6f35e0df..6bf039ead 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -531,38 +531,46 @@ abstract class CI_DB_query_builder extends CI_DB_driver { is_bool($escape) OR $escape = $this->_protect_identifiers; - // Split multiple conditions - if ($escape === TRUE && preg_match_all('/\sAND\s|\sOR\s/i', $cond, $m, PREG_OFFSET_CAPTURE)) + if ( ! $this->_has_operator($cond)) { - $newcond = ''; - $m[0][] = array('', strlen($cond)); - - for ($i = 0, $c = count($m[0]), $s = 0; - $i < $c; - $s = $m[0][$i][1] + strlen($m[0][$i][0]), $i++) - { - $temp = substr($cond, $s, ($m[0][$i][1] - $s)); - $newcond .= preg_match("/(\(*)?([\[\]\w\.'-]+)(\s*[^\"\[`'\w]+\s*)(.+)/i", $temp, $match) - ? $match[1].$this->protect_identifiers($match[2]).$match[3].$this->protect_identifiers($match[4]) - : $temp; - - $newcond .= $m[0][$i][0]; - } - - $cond = ' ON '.$newcond; - } - // Split apart the condition and protect the identifiers - elseif ($escape === TRUE && preg_match("/(\(*)?([\[\]\w\.'-]+)(\s*[^\"\[`'\w]+\s*)(.+)/i", $cond, $match)) - { - $cond = ' ON '.$match[1].$this->protect_identifiers($match[2]).$match[3].$this->protect_identifiers($match[4]); + $cond = ' USING ('.($escape ? $this->escape_identifiers($cond) : $cond).')'; } - elseif ( ! $this->_has_operator($cond)) + elseif ($escape === FALSE) { - $cond = ' USING ('.($escape ? $this->escape_identifiers($cond) : $cond).')'; + $cond = ' ON '.$cond; } else { - $cond = ' ON '.$cond; + // Split multiple conditions + if (preg_match_all('/\sAND\s|\sOR\s/i', $cond, $joints, PREG_OFFSET_CAPTURE)) + { + $conditions = array(); + $joints = $joints[0]; + array_unshift($joints, array('', 0)); + + for ($i = count($joints) - 1, $pos = strlen($cond); $i >= 0; $i--) + { + $joints[$i][1] += strlen($joints[$i][0]); // offset + $conditions[$i] = substr($cond, $joints[$i][1], $pos - $joints[$i][1]); + $pos = $joints[$i][1] - strlen($joints[$i][0]); + $joints[$i] = $joints[$i][0]; + } + } + else + { + $conditions = array($cond); + $joints = array(''); + } + + $cond = ' ON '; + for ($i = 0, $c = count($conditions); $i < $c; $i++) + { + $operator = $this->_get_operator($conditions[$i]); + $cond .= $joints[$i]; + $cond .= preg_match("/(\(*)?([\[\]\w\.'-]+)".preg_quote($operator)."(.*)/i", $conditions[$i], $match) + ? $match[1].$this->protect_identifiers($match[2]).$operator.$this->protect_identifiers($match[3]) + : $conditions[$i]; + } } // Do we want to escape the table name? diff --git a/tests/codeigniter/database/query_builder/join_test.php b/tests/codeigniter/database/query_builder/join_test.php index 58cb21492..54b2a4e18 100644 --- a/tests/codeigniter/database/query_builder/join_test.php +++ b/tests/codeigniter/database/query_builder/join_test.php @@ -37,6 +37,29 @@ class Join_test extends CI_TestCase { // ------------------------------------------------------------------------ + public function test_join_escape_is_null() + { + $expected = 'SELECT '.$this->db->escape_identifiers('field') + ."\nFROM ".$this->db->escape_identifiers('table1') + ."\nJOIN ".$this->db->escape_identifiers('table2').' ON '.$this->db->escape_identifiers('field').' IS NULL'; + + $this->assertEquals( + $expected, + $this->db->select('field')->from('table1')->join('table2', 'field IS NULL')->get_compiled_select() + ); + + $expected = 'SELECT '.$this->db->escape_identifiers('field') + ."\nFROM ".$this->db->escape_identifiers('table1') + ."\nJOIN ".$this->db->escape_identifiers('table2').' ON '.$this->db->escape_identifiers('field').' IS NOT NULL'; + + $this->assertEquals( + $expected, + $this->db->select('field')->from('table1')->join('table2', 'field IS NOT NULL')->get_compiled_select() + ); + } + + // ------------------------------------------------------------------------ + public function test_join_escape_multiple_conditions() { // We just need a valid query produced, not one that makes sense @@ -65,11 +88,11 @@ class Join_test extends CI_TestCase { $expected = 'SELECT '.implode(', ', $fields) ."\nFROM ".$this->db->escape_identifiers('table1') ."\nRIGHT JOIN ".$this->db->escape_identifiers('table2').' ON '.implode(' = ', $fields) - .' AND ('.$fields[0]." = 'foo' OR ".$fields[1].' = 0)'; + .' AND ('.$fields[0]." = 'foo' OR ".$fields[1].' IS NULL)'; $result = $this->db->select('table1.field1, table2.field2') ->from('table1') - ->join('table2', "table1.field1 = table2.field2 AND (table1.field1 = 'foo' OR table2.field2 = 0)", 'RIGHT') + ->join('table2', "table1.field1 = table2.field2 AND (table1.field1 = 'foo' OR table2.field2 IS NULL)", 'RIGHT') ->get_compiled_select(); $this->assertEquals($expected, $result); |