MDL-23925, MDL-23888 new sql_ilike() and sql_binary_equal() - this should finally...
authorPetr Skoda <skodak@moodle.org>
Tue, 24 Aug 2010 21:50:53 +0000 (21:50 +0000)
committerPetr Skoda <skodak@moodle.org>
Tue, 24 Aug 2010 21:50:53 +0000 (21:50 +0000)
lib/dml/moodle_database.php
lib/dml/mssql_native_moodle_database.php
lib/dml/mysqli_native_moodle_database.php
lib/dml/oci_native_moodle_database.php
lib/dml/pgsql_native_moodle_database.php
lib/dml/simpletest/testdml.php
lib/dml/sqlsrv_native_moodle_database.php

index b7b33ab..cc2fc82 100644 (file)
@@ -505,12 +505,26 @@ abstract class moodle_database {
             if (is_null($value)) {
                 $where[] = "$key IS NULL";
             } else {
+                $isstring = (is_string($value) && !is_number($value));
                 if ($allowed_types & SQL_PARAMS_NAMED) {
-                    $normkey = trim(preg_replace('/[^a-zA-Z0-9-_]/', '_', $key), '-_'); // Need to normalize key names
-                    $where[] = "$key = :$normkey";                                      // because they can contain, originally,
-                    $params[$normkey] = $value;                                         // spaces and other forbidden chars when
-                } else {                                                                // using sql_xxx() functions and friends.
-                    $where[] = "$key = ?";
+                    // Need to verify key names because they can contain, originally,
+                    // spaces and other forbidden chars when using sql_xxx() functions and friends.
+                    $normkey = trim(preg_replace('/[^a-zA-Z0-9-_]/', '_', $key), '-_');
+                    if ($normkey !== $key) {
+                        debugging('Invalid key found in the conditions array.');
+                    }
+                    if ($isstring) {
+                        $where[] = $this->sql_binary_equal($key, ':'.$normkey);
+                    } else {
+                        $where[] = "$key = :$normkey";
+                    }
+                    $params[$normkey] = $value;
+                } else {
+                    if ($isstring) {
+                        $where[] = $this->sql_binary_equal($key, '?');
+                    } else {
+                        $where[] = "$key = ?";
+                    }
                     $params[] = $value;
                 }
             }
@@ -1727,6 +1741,46 @@ abstract class moodle_database {
         return $this->sql_order_by_text($fieldname, $numchars);
     }
 
+    /**
+     * Case and collation sensitive string 'string = string'
+     * @param string $string1
+     * @param string $string2
+     * @return string SQL fragment
+     */
+    public function sql_binary_equal($string1, $string2) {
+        return "$string1 = $string2";
+    }
+
+    /**
+     * Returns 'LIKE' part of a query.
+     *
+     * @param string $fieldname usually name of the table column
+     * @param string $param usually bound query parameter (?, :named)
+     * @param bool $casesensitive use case sensitive search
+     * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
+     * @param string $escapechar escape char for '%' and '_'
+     * @return string SQL code fragment
+     */
+    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $escapechar = '\\') {
+        if (strpos($param, '%') !== false) {
+            debugging('Potential SQL injection detected, sql_ilike() expects bound parameters (? or :named)');
+        }
+        // by default ignore any sensitiveness - each database does it in a different way
+        return "$fieldname LIKE $param ESCAPE '$escapechar'";
+    }
+
+    /**
+     * Escape sql LIKE special characters.
+     * @param string $text
+     * @param string $escapechar
+     * @return string
+     */
+    public function sql_like_escape($text, $escapechar = '\\') {
+        $text = str_replace('_', $escapechar.'_', $text);
+        $text = str_replace('%', $escapechar.'%', $text);
+        return $text;
+    }
+
     /**
      * Returns the proper SQL to do LIKE in a case-insensitive way.
      *
@@ -1734,9 +1788,11 @@ abstract class moodle_database {
      * the case insensitive search using regexp_like() or NLS_COMP=LINGUISTIC :-(
      * See http://docs.moodle.org/en/XMLDB_Problems#Case-insensitive_searches
      *
+     * @deprecated
      * @return string
      */
     public function sql_ilike() {
+        //TODO: debugging('sql_ilike() is deprecated, please use sql_like() instead');
         return 'LIKE';
     }
 
index a8d6cbd..46a4eec 100644 (file)
@@ -37,8 +37,8 @@ require_once($CFG->libdir.'/dml/mssql_native_moodle_temptables.php');
 class mssql_native_moodle_database extends moodle_database {
 
     protected $mssql     = null;
-
     protected $last_error_reporting; // To handle mssql driver default verbosity
+    protected $collation;  // current DB collation cache
 
     /**
      * Detects if all needed PHP stuff installed.
@@ -1062,6 +1062,76 @@ class mssql_native_moodle_database extends moodle_database {
         return ' CEILING(' . $fieldname . ')';
     }
 
+
+    protected function get_collation() {
+        if (isset($this->collation)) {
+            return $this->collation;
+        }
+        if (!empty($this->dboptions['dbcollation'])) {
+            // perf speedup
+            $this->collation = $this->dboptions['dbcollation'];
+            return $this->collation;
+        }
+
+        // make some default
+        $this->collation = 'Latin1_General_CI_AI';
+
+        $sql = "SELECT CAST(DATABASEPROPERTYEX('$this->dbname', 'Collation') AS varchar(255)) AS SQLCollation";
+        $this->query_start($sql, null, SQL_QUERY_AUX);
+        $result = sqlsrv_query($this->sqlsrv, $sql);
+        $this->query_end($result);
+
+        if ($result) {
+            if ($rawcolumn = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
+                $this->collation = reset($rawcolumn);
+            }
+            $this->free_result($result);
+        }
+
+        return $this->collation;
+    }
+
+   /**
+     * Case and collation sensitive string 'string = string'
+     * @param string $string1
+     * @param string $string2
+     * @return string SQL fragment
+     */
+    public function sql_binary_equal($string1, $string2) {
+        return "$string1 COLLATE Latin1_General_CS_AS = $string2";
+    }
+
+    /**
+     * Returns 'LIKE' part of a query.
+     *
+     * @param string $fieldname usually name of the table column
+     * @param string $param usually bound query parameter (?, :named)
+     * @param bool $casesensitive use case sensitive search
+     * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
+     * @param string $escapechar escape char for '%' and '_'
+     * @return string SQL code fragment
+     */
+    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $escapechar = '\\') {
+        if (strpos($param, '%') !== false) {
+            debugging('Potential SQL injection detected, sql_ilike() expects bound parameters (? or :named)');
+        }
+
+        $collation = $this->get_collation();
+
+        if ($casesensitive) {
+            $collation = str_replace('_CI', '_CS', $collation);
+        } else {
+            $collation = str_replace('_CS', '_CI', $collation);
+        }
+        if ($accentsensitive) {
+            $collation = str_replace('_AI', '_AS', $collation);
+        } else {
+            $collation = str_replace('_AS', '_AI', $collation);
+        }
+
+        return "$fieldname COLLATE $collation LIKE $param ESCAPE '$escapechar'";
+    }
+
     public function sql_concat() {
         $arr = func_get_args();
         foreach ($arr as $key => $ele) {
index 94b2d25..c07793e 100644 (file)
@@ -1025,6 +1025,44 @@ class mysqli_native_moodle_database extends moodle_database {
         return ' CAST(' . $fieldname . ' AS SIGNED) ';
     }
 
+
+    /**
+     * Case and collation sensitive string 'string = string'
+     * @param string $string1
+     * @param string $string2
+     * @return string SQL fragment
+     */
+    public function sql_binary_equal($string1, $string2) {
+        return "BINARY $string1 = $string2";
+    }
+
+    /**
+     * Returns 'LIKE' part of a query.
+     *
+     * @param string $fieldname usually name of the table column
+     * @param string $param usually bound query parameter (?, :named)
+     * @param bool $casesensitive use case sensitive search
+     * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
+     * @param string $escapechar escape char for '%' and '_'
+     * @return string SQL code fragment
+     */
+    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $escapechar = '\\') {
+        if (strpos($param, '%') !== false) {
+            debugging('Potential SQL injection detected, sql_ilike() expects bound parameters (? or :named)');
+        }
+        $escapechar = $this->mysqli->real_escape_string($escapechar); // prevents problems with C-style escapes of enclosing '\'
+
+        if ($casesensitive) {
+            return "$fieldname LIKE $param COLLATE utf8_bin ESCAPE '$escapechar'";
+        } else {
+            if ($accentsensitive) {
+                return "LOWER($fieldname) LIKE LOWER($param) COLLATE utf8_bin ESCAPE '$escapechar'";
+            } else {
+                return "$fieldname LIKE $param ESCAPE '$escapechar'";
+            }
+        }
+    }
+
     public function sql_concat() {
         $arr = func_get_args();
         $s = implode(', ', $arr);
index d95bba5..ec5c54a 100644 (file)
@@ -1402,12 +1402,28 @@ class oci_native_moodle_database extends moodle_database {
         }
     }
 
-    // TODO: Change this function and uses to support 2 parameters: fieldname and value
-    // that way we can use REGEXP_LIKE(x, y, 'i') to provide case-insensitive like searches
-    // to lower() comparison or whatever
-    public function sql_ilike() {
-        // TODO: add some ilike workaround
-        return 'LIKE';
+    /**
+     * Returns 'LIKE' part of a query.
+     *
+     * @param string $fieldname usually name of the table column
+     * @param string $param usually bound query parameter (?, :named)
+     * @param bool $casesensitive use case sensitive search
+     * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
+     * @param string $escapechar escape char for '%' and '_'
+     * @return string SQL code fragment
+     */
+    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $escapechar = '\\') {
+        if (strpos($param, '%') !== false) {
+            debugging('Potential SQL injection detected, sql_ilike() expects bound parameters (? or :named)');
+        }
+
+        // no accent sensitiveness here for now, sorry
+
+        if ($casesensitive) {
+            return "$fieldname LIKE $param ESCAPE '$escapechar'";
+        } else {
+            return "LOWER($fieldname) LIKE LOWER($param) ESCAPE '$escapechar'";
+        }
     }
 
     // NOTE: Oracle concat implementation isn't ANSI compliant when using NULLs (the result of
index 0ee68de..e903ad0 100644 (file)
@@ -1042,7 +1042,32 @@ class pgsql_native_moodle_database extends moodle_database {
         return true;
     }
 
+    /**
+     * Returns 'LIKE' part of a query.
+     *
+     * @param string $fieldname usually name of the table column
+     * @param string $param usually bound query parameter (?, :named)
+     * @param bool $casesensitive use case sensitive search
+     * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
+     * @param string $escapechar escape char for '%' and '_'
+     * @return string SQL code fragment
+     */
+    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $escapechar = '\\') {
+        if (strpos($param, '%') !== false) {
+            debugging('Potential SQL injection detected, sql_ilike() expects bound parameters (? or :named)');
+        }
+        $escapechar = pg_escape_string($this->pgsql, $escapechar); // prevents problems with C-style escapes of enclosing '\'
+
+        // postgresql does not support accent insensitive text comparisons, sorry
+        if ($casesensitive) {
+            return "$fieldname LIKE $param ESCAPE '$escapechar'";
+        } else {
+            return "$fieldname ILIKE $param ESCAPE '$escapechar'";
+        }
+    }
+
     public function sql_ilike() {
+        //TODO: debugging('sql_ilike() is deprecated, please use sql_like() instead');
         return 'ILIKE';
     }
 
index d0110bd..436da63 100755 (executable)
@@ -2061,7 +2061,107 @@ class dml_test extends UnitTestCase {
         $this->assertEqual(count($records), 2);
     }
 
-    function test_ilike() {
+
+    function test_sql_binary_equal() {
+        $DB = $this->tdb;
+        $dbman = $DB->get_manager();
+
+        $table = $this->get_test_table();
+        $tablename = $table->getName();
+
+        $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
+        $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
+        $table->add_field('descr', XMLDB_TYPE_CHAR, '255', null, null, null, null);
+        $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
+        $dbman->create_table($table);
+        $this->tables[$tablename] = $table;
+
+        $DB->insert_record($tablename, array('name'=>'skodak', 'descr'=>'Skodak'));
+        $DB->insert_record($tablename, array('name'=>'skodák', 'descr'=>'skodák'));
+        $DB->insert_record($tablename, array('name'=>'Skodak', 'descr'=>'Skodák'));
+        $DB->insert_record($tablename, array('name'=>'?ko?ák', 'descr'=>'?ko?ák'));
+        $DB->insert_record($tablename, array('name'=>'skodäk', 'descr'=>'skodak'));
+
+        $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_binary_equal('name', '?');
+        $records = $DB->get_records_sql($sql, array("skodak"));
+        $this->assertEqual(count($records), 1);
+
+        $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_binary_equal('name', '?');
+        $records = $DB->get_records_sql($sql, array("?ko?ák"));
+        $this->assertEqual(count($records), 1);
+
+        $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_binary_equal('name', 'descr');
+        $records = $DB->get_records_sql($sql, array());
+        $this->assertEqual(count($records), 1);
+
+        // get_records() is supposed to use binary comparison too
+        $records = $DB->get_records($tablename, array('name'=>"skodak"));
+        $this->assertEqual(count($records), 1);
+        $records = $DB->get_records($tablename, array('name'=>"?ko?ák"));
+        $this->assertEqual(count($records), 1);
+
+        $bool = $DB->record_exists($tablename, array('name'=>"skodak"));
+        $this->assertTrue($bool);
+        $bool = $DB->record_exists($tablename, array('name'=>"skodAk"));
+        $this->assertFalse($bool);
+    }
+
+    function test_sql_like() {
+        $DB = $this->tdb;
+        $dbman = $DB->get_manager();
+
+        $table = $this->get_test_table();
+        $tablename = $table->getName();
+
+        $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
+        $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
+        $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
+        $dbman->create_table($table);
+        $this->tables[$tablename] = $table;
+
+        $DB->insert_record($tablename, array('name'=>'SuperDuperRecord'));
+        $DB->insert_record($tablename, array('name'=>'Nodupor'));
+        $DB->insert_record($tablename, array('name'=>'ouch'));
+        $DB->insert_record($tablename, array('name'=>'ouc_'));
+        $DB->insert_record($tablename, array('name'=>'ouc%'));
+        $DB->insert_record($tablename, array('name'=>'aui'));
+        $DB->insert_record($tablename, array('name'=>'aüi'));
+        $DB->insert_record($tablename, array('name'=>'aÜi'));
+
+        $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', false);
+        $records = $DB->get_records_sql($sql, array("%dup_r%"));
+        $this->assertEqual(count($records), 2);
+
+        $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', true);
+        $records = $DB->get_records_sql($sql, array("%dup%"));
+        $this->assertEqual(count($records), 1);
+
+        $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?'); // defaults
+        $records = $DB->get_records_sql($sql, array("%dup%"));
+        $this->assertEqual(count($records), 1);
+
+        $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', true);
+        $records = $DB->get_records_sql($sql, array("ouc\\_"));
+        $this->assertEqual(count($records), 1);
+
+        $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', true, true, '|');
+        $records = $DB->get_records_sql($sql, array($DB->sql_like_escape("ouc%", '|')));
+        $this->assertEqual(count($records), 1);
+
+        $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', true, true);
+        $records = $DB->get_records_sql($sql, array('aui'));
+        $this->assertEqual(count($records), 1);
+
+        // we do not require accent insensitivness yet, just make sure it does not throw errors
+        $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', true, false);
+        $records = $DB->get_records_sql($sql, array('aui'));
+        $this->assertEqual(count($records), 2, 'Accent insensitive LIKE searches may not be supported in all database backends, ignore this problem for now.');
+        $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', false, false);
+        $records = $DB->get_records_sql($sql, array('aui'));
+        $this->assertEqual(count($records), 3, 'Accent insensitive LIKE searches may not be supported in all database backends, ignore this problem for now.');
+    }
+
+    function test_sql_ilike() {
         $DB = $this->tdb;
         $dbman = $DB->get_manager();
 
@@ -2081,7 +2181,7 @@ class dml_test extends UnitTestCase {
         $sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_ilike()." ?";
         $params = array("%dup_r%");
         $records = $DB->get_records_sql($sql, $params);
-        $this->assertEqual(count($records), 2);
+        $this->assertEqual(count($records), 2, 'DB->sql_ilike() is deprecated, ignore this problem.');
     }
 
     function test_sql_concat() {
index 9770428..c73e585 100644 (file)
@@ -38,6 +38,7 @@ class sqlsrv_native_moodle_database extends moodle_database {
     protected $sqlsrv = null;
     protected $last_error_reporting; // To handle SQL*Server-Native driver default verbosity
     protected $temptables; // Control existing temptables (sqlsrv_moodle_temptables object)
+    protected $collation;  // current DB collation cache
 
     /**
      * Constructor - instantiates the database, specifying if it's external (connect to other systems) or no (Moodle DB)
@@ -698,7 +699,7 @@ class sqlsrv_native_moodle_database extends moodle_database {
             } else if (is_null($param)) {
                 $return .= 'NULL';
 
-            } else if ($this->is_number($param)) { // we can not use is_numeric() because it eats leading zeros from strings like 0045646
+            } else if (is_number($param)) { // we can not use is_numeric() because it eats leading zeros from strings like 0045646
                 $return .= $param;
             } else if (is_float($param)) {
                 $return .= $param;
@@ -712,23 +713,6 @@ class sqlsrv_native_moodle_database extends moodle_database {
         return $return;
     }
 
-    /**
-      * Return true if given value is integer or string with integer value
-      *
-      * @param mixed $value String or Int
-      * @return bool true if number, false if not
-      */
-    private static function is_number($value) {
-        /* this function exists in moodlelib, so i would need to include that which is not recommended ... */
-        if (is_int($value)) {
-            return true;
-        } else if (is_string($value)) {
-            return ((string)(int)$value) === $value;
-        } else {
-            return false;
-        }
-    }
-
     /**
      * Execute general sql query. Should be used only when no other method suitable.
      * Do NOT use this to make changes in db structure, use database_manager::execute_sql() instead!
@@ -1173,6 +1157,75 @@ class sqlsrv_native_moodle_database extends moodle_database {
         return ' CEILING('.$fieldname.')';
     }
 
+    protected function get_collation() {
+        if (isset($this->collation)) {
+            return $this->collation;
+        }
+        if (!empty($this->dboptions['dbcollation'])) {
+            // perf speedup
+            $this->collation = $this->dboptions['dbcollation'];
+            return $this->collation;
+        }
+
+        // make some default
+        $this->collation = 'Latin1_General_CI_AI';
+
+        $sql = "SELECT CAST(DATABASEPROPERTYEX('$this->dbname', 'Collation') AS varchar(255)) AS SQLCollation";
+        $this->query_start($sql, null, SQL_QUERY_AUX);
+        $result = sqlsrv_query($this->sqlsrv, $sql);
+        $this->query_end($result);
+
+        if ($result) {
+            if ($rawcolumn = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
+                $this->collation = reset($rawcolumn);
+            }
+            $this->free_result($result);
+        }
+
+        return $this->collation;
+    }
+
+   /**
+     * Case and collation sensitive string 'string = string'
+     * @param string $string1
+     * @param string $string2
+     * @return string SQL fragment
+     */
+    public function sql_binary_equal($string1, $string2) {
+        return "$string1 COLLATE Latin1_General_CS_AS = $string2";
+    }
+
+    /**
+     * Returns 'LIKE' part of a query.
+     *
+     * @param string $fieldname usually name of the table column
+     * @param string $param usually bound query parameter (?, :named)
+     * @param bool $casesensitive use case sensitive search
+     * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
+     * @param string $escapechar escape char for '%' and '_'
+     * @return string SQL code fragment
+     */
+    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $escapechar = '\\') {
+        if (strpos($param, '%') !== false) {
+            debugging('Potential SQL injection detected, sql_ilike() expects bound parameters (? or :named)');
+        }
+
+        $collation = $this->get_collation();
+
+        if ($casesensitive) {
+            $collation = str_replace('_CI', '_CS', $collation);
+        } else {
+            $collation = str_replace('_CS', '_CI', $collation);
+        }
+        if ($accentsensitive) {
+            $collation = str_replace('_AI', '_AS', $collation);
+        } else {
+            $collation = str_replace('_AS', '_AI', $collation);
+        }
+
+        return "$fieldname COLLATE $collation LIKE $param ESCAPE '$escapechar'";
+    }
+
     public function sql_concat() {
         $arr = func_get_args();