MDL-24080 new $notlike parameter added to our new sql_like(), hopefully nobody starte...

diff --git a/lib/dml/moodle_database.php b/lib/dml/moodle_database.php
index 335d60d..6294474 100644 (file)
--- a/lib/dml/moodle_database.php
+++ b/lib/dml/moodle_database.php
@@ -1744,15 +1744,17 @@ abstract class moodle_database {
      * @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 bool $notlike true means "NOT LIKE"
      * @param string $escapechar escape char for '%' and '_'
      * @return string SQL code fragment
      */
-    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $escapechar = '\\') {
+    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') {
         if (strpos($param, '%') !== false) {
             debugging('Potential SQL injection detected, sql_like() expects bound parameters (? or :named)');
         }
+        $LIKE = $notlike ? 'NOT LIKE' : 'LIKE';
         // by default ignore any sensitiveness - each database does it in a different way
-        return "$fieldname LIKE $param ESCAPE '$escapechar'";
+        return "$fieldname $LIKE $param ESCAPE '$escapechar'";
     }
 
     /**

diff --git a/lib/dml/mssql_native_moodle_database.php b/lib/dml/mssql_native_moodle_database.php
index 1831cfd..8fe74d8 100644 (file)
--- a/lib/dml/mssql_native_moodle_database.php
+++ b/lib/dml/mssql_native_moodle_database.php
@@ -1098,10 +1098,11 @@ class mssql_native_moodle_database extends moodle_database {
      * @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 bool $notlike true means "NOT LIKE"
      * @param string $escapechar escape char for '%' and '_'
      * @return string SQL code fragment
      */
-    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $escapechar = '\\') {
+    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') {
         if (strpos($param, '%') !== false) {
             debugging('Potential SQL injection detected, sql_ilike() expects bound parameters (? or :named)');
         }
@@ -1119,7 +1120,9 @@ class mssql_native_moodle_database extends moodle_database {
             $collation = str_replace('_AS', '_AI', $collation);
         }
 
-        return "$fieldname COLLATE $collation LIKE $param ESCAPE '$escapechar'";
+        $LIKE = $notlike ? 'NOT LIKE' : 'LIKE';
+
+        return "$fieldname COLLATE $collation $LIKE $param ESCAPE '$escapechar'";
     }
 
     public function sql_concat() {

diff --git a/lib/dml/mysqli_native_moodle_database.php b/lib/dml/mysqli_native_moodle_database.php
index 0549bd3..33ef2b7 100644 (file)
--- a/lib/dml/mysqli_native_moodle_database.php
+++ b/lib/dml/mysqli_native_moodle_database.php
@@ -1032,22 +1032,24 @@ class mysqli_native_moodle_database extends moodle_database {
      * @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 bool $notlike true means "NOT LIKE"
      * @param string $escapechar escape char for '%' and '_'
      * @return string SQL code fragment
      */
-    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $escapechar = '\\') {
+    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $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 '\'
 
+        $LIKE = $notlike ? 'NOT LIKE' : 'LIKE';
         if ($casesensitive) {
-            return "$fieldname LIKE $param COLLATE utf8_bin ESCAPE '$escapechar'";
+            return "$fieldname $LIKE $param COLLATE utf8_bin ESCAPE '$escapechar'";
         } else {
             if ($accentsensitive) {
-                return "LOWER($fieldname) LIKE LOWER($param) COLLATE utf8_bin ESCAPE '$escapechar'";
+                return "LOWER($fieldname) $LIKE LOWER($param) COLLATE utf8_bin ESCAPE '$escapechar'";
             } else {
-                return "$fieldname LIKE $param ESCAPE '$escapechar'";
+                return "$fieldname $LIKE $param ESCAPE '$escapechar'";
             }
         }
     }

diff --git a/lib/dml/oci_native_moodle_database.php b/lib/dml/oci_native_moodle_database.php
index ec5c54a..f4995d9 100644 (file)
--- a/lib/dml/oci_native_moodle_database.php
+++ b/lib/dml/oci_native_moodle_database.php
@@ -1409,20 +1409,23 @@ class oci_native_moodle_database extends moodle_database {
      * @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 bool $notlike true means "NOT LIKE"
      * @param string $escapechar escape char for '%' and '_'
      * @return string SQL code fragment
      */
-    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $escapechar = '\\') {
+    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') {
         if (strpos($param, '%') !== false) {
             debugging('Potential SQL injection detected, sql_ilike() expects bound parameters (? or :named)');
         }
 
+        $LIKE = $notlike ? 'NOT LIKE' : 'LIKE';
+
         // no accent sensitiveness here for now, sorry
 
         if ($casesensitive) {
-            return "$fieldname LIKE $param ESCAPE '$escapechar'";
+            return "$fieldname $LIKE $param ESCAPE '$escapechar'";
         } else {
-            return "LOWER($fieldname) LIKE LOWER($param) ESCAPE '$escapechar'";
+            return "LOWER($fieldname) $LIKE LOWER($param) ESCAPE '$escapechar'";
         }
     }
 

diff --git a/lib/dml/pgsql_native_moodle_database.php b/lib/dml/pgsql_native_moodle_database.php
index 60cabb2..de9f55b 100644 (file)
--- a/lib/dml/pgsql_native_moodle_database.php
+++ b/lib/dml/pgsql_native_moodle_database.php
@@ -1049,10 +1049,11 @@ class pgsql_native_moodle_database extends moodle_database {
      * @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 bool $notlike true means "NOT LIKE"
      * @param string $escapechar escape char for '%' and '_'
      * @return string SQL code fragment
      */
-    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $escapechar = '\\') {
+    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') {
         if (strpos($param, '%') !== false) {
             debugging('Potential SQL injection detected, sql_ilike() expects bound parameters (? or :named)');
         }
@@ -1060,10 +1061,11 @@ class pgsql_native_moodle_database extends moodle_database {
 
         // postgresql does not support accent insensitive text comparisons, sorry
         if ($casesensitive) {
-            return "$fieldname LIKE $param ESCAPE '$escapechar'";
+            $LIKE = $notlike ? 'NOT LIKE' : 'LIKE';
         } else {
-            return "$fieldname ILIKE $param ESCAPE '$escapechar'";
+            $LIKE = $notlike ? 'NOT ILIKE' : 'ILIKE';
         }
+        return "$fieldname $LIKE $param ESCAPE '$escapechar'";
     }
 
     public function sql_ilike() {

diff --git a/lib/dml/simpletest/testdml.php b/lib/dml/simpletest/testdml.php
index 6e57738..efa90d0 100755 (executable)
--- a/lib/dml/simpletest/testdml.php
+++ b/lib/dml/simpletest/testdml.php
@@ -2189,7 +2189,7 @@ class dml_test extends UnitTestCase {
         $records = $DB->get_records_sql($sql, array("ouc\\_"));
         $this->assertEqual(count($records), 1);
 
-        $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, '|');
+        $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, false, '|');
         $records = $DB->get_records_sql($sql, array($DB->sql_like_escape("ouc%", '|')));
         $this->assertEqual(count($records), 1);
 
@@ -2197,6 +2197,14 @@ class dml_test extends UnitTestCase {
         $records = $DB->get_records_sql($sql, array('aui'));
         $this->assertEqual(count($records), 1);
 
+        $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, true); // NOT LIKE
+        $records = $DB->get_records_sql($sql, array("%o%"));
+        $this->assertEqual(count($records), 3);
+
+        $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false, true, true); // NOT ILIKE
+        $records = $DB->get_records_sql($sql, array("%D%"));
+        $this->assertEqual(count($records), 6);
+
         // TODO: 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'));

diff --git a/lib/dml/sqlsrv_native_moodle_database.php b/lib/dml/sqlsrv_native_moodle_database.php
index 7258bf0..3385fac 100644 (file)
--- a/lib/dml/sqlsrv_native_moodle_database.php
+++ b/lib/dml/sqlsrv_native_moodle_database.php
@@ -1192,15 +1192,17 @@ class sqlsrv_native_moodle_database extends moodle_database {
      * @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 bool $notlike true means "NOT LIKE"
      * @param string $escapechar escape char for '%' and '_'
      * @return string SQL code fragment
      */
-    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $escapechar = '\\') {
+    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') {
         if (strpos($param, '%') !== false) {
             debugging('Potential SQL injection detected, sql_ilike() expects bound parameters (? or :named)');
         }
 
         $collation = $this->get_collation();
+        $LIKE = $notlike ? 'NOT LIKE' : 'LIKE';
 
         if ($casesensitive) {
             $collation = str_replace('_CI', '_CS', $collation);
@@ -1213,7 +1215,7 @@ class sqlsrv_native_moodle_database extends moodle_database {
             $collation = str_replace('_AS', '_AI', $collation);
         }
 
-        return "$fieldname COLLATE $collation LIKE $param ESCAPE '$escapechar'";
+        return "$fieldname COLLATE $collation $LIKE $param ESCAPE '$escapechar'";
     }
 
     public function sql_concat() {