#654662 by c960657 and Crell: Allow escaping wildcard characters in LIKE queries.

3 files changed, 61 insertions, 3 deletions

diff --git a/includes/database/database.inc b/includes/database/database.inc
index 4b1cff0f6..fb324eded 100644
--- a/includes/database/database.inc
+++ b/includes/database/database.inc
@@ -820,6 +820,34 @@ abstract class DatabaseConnection extends PDO {
   }
 
   /**
+   * Escape characters that work as wildcard characters in a LIKE pattern.
+   *
+   * The wildcard characters "%" and "_" as well as backslash are prefixed with
+   * a backslash. Use this to do a seach for a verbatim string without any
+   * wildcard behavior.
+   *
+   * For example, the following does a case-insensitive query for all rows whose
+   * name starts with $prefix:
+   * @code
+   * $result = db_query(
+   *   'SELECT * FROM person WHERE name LIKE :pattern',
+   *   array(':pattern' => db_like($prefix) . '%')
+   * );
+   * @endcode
+   *
+   * Backslash is defined as escape character for LIKE patterns in
+   * DatabaseCondition::mapConditionOperator().
+   *
+   * @param $string
+   *   The string to escape.
+   * @return
+   *   The escaped string.
+   */
+  public function escapeLike($string) {
+    return addcslashes($string, '\%_');
+  }
+
+  /**
    * Determine if there is an active transaction open.
    *
    * @return
@@ -2235,6 +2263,34 @@ function db_escape_table($table) {
 }
 
 /**
+ * Escape characters that work as wildcard characters in a LIKE pattern.
+ *
+ * The wildcard characters "%" and "_" as well as backslash are prefixed with
+ * a backslash. Use this to do a seach for a verbatim string without any
+ * wildcard behavior.
+ *
+ * For example, the following does a case-insensitive query for all rows whose
+ * name starts with $prefix:
+ * @code
+ * $result = db_query(
+ *   'SELECT * FROM person WHERE name LIKE :pattern',
+ *   array(':pattern' => db_like($prefix) . '%')
+ * );
+ * @endcode
+ *
+ * Backslash is defined as escape character for LIKE patterns in
+ * DatabaseCondition::mapConditionOperator().
+ *
+ * @param $string
+ *   The string to escape.
+ * @return
+ *   The escaped string.
+ */
+function db_like($string) {
+  return Database::getConnection()->escapeLike($string);
+}
+
+/**
  * Retrieve the name of the currently active database driver, such as
  * "mysql" or "pgsql".
  *
diff --git a/includes/database/pgsql/database.inc b/includes/database/pgsql/database.inc
index 6772471b6..8b60b957b 100644
--- a/includes/database/pgsql/database.inc
+++ b/includes/database/pgsql/database.inc
@@ -120,8 +120,9 @@ class DatabaseConnection_pgsql extends DatabaseConnection {
   public function mapConditionOperator($operator) {
     static $specials = array(
       // In PostgreSQL, 'LIKE' is case-sensitive. For case-insensitive LIKE
-      // statements, we need to use ILIKE instead.
-      'LIKE' => array('operator' => 'ILIKE'),
+      // statements, we need to use ILIKE instead. Use backslash for escaping
+      // wildcard characters.
+      'LIKE' => array('operator' => 'ILIKE', 'postfix' => " ESCAPE '\\\\'"),
     );
 
     return isset($specials[$operator]) ? $specials[$operator] : NULL;
diff --git a/includes/database/query.inc b/includes/database/query.inc
index 50c106c0d..0061abe0f 100644
--- a/includes/database/query.inc
+++ b/includes/database/query.inc
@@ -1330,13 +1330,14 @@ class DatabaseCondition implements QueryConditionInterface, Countable {
       'NOT IN' => array('delimiter' => ', ', 'prefix' => ' (', 'postfix' => ')'),
       'IS NULL' => array('use_value' => FALSE),
       'IS NOT NULL' => array('use_value' => FALSE),
+      // Use backslash for escaping wildcard characters.
+      'LIKE' => array('postfix' => " ESCAPE '\\\\'"),
       // These ones are here for performance reasons.
       '=' => array(),
       '<' => array(),
       '>' => array(),
       '>=' => array(),
       '<=' => array(),
-      'LIKE' => array(),
     );
     if (isset($specials[$operator])) {
       $return = $specials[$operator];