- Patch #10407 by Cvbge: fixed cache problems with PostgreSQL.

7 files changed, 182 insertions, 105 deletions

diff --git a/database/database.pgsql b/database/database.pgsql
index c58b8c7e1..59ce317f3 100644
--- a/database/database.pgsql
+++ b/database/database.pgsql
@@ -164,7 +164,7 @@ CREATE TABLE boxes (
 
 CREATE TABLE cache (
   cid varchar(255) NOT NULL default '',
-  data text default '',
+  data bytea default '',
   expire integer NOT NULL default '0',
   created integer NOT NULL default '0',
   headers text default '',
diff --git a/database/updates.inc b/database/updates.inc
index 006a57ba2..9b10baa46 100644
--- a/database/updates.inc
+++ b/database/updates.inc
@@ -104,8 +104,9 @@ $sql_updates = array(
   "2005-10-23" => "update_151",
   "2005-10-28" => "update_152",
   "2005-11-03" => "update_153",
-  "2005-11-14" => "update_154"
-);
+  "2005-11-14" => "update_154",
+  "2005-11-27" => "update_155",
+); // Please leave trailing , in the last line
 
 function update_110() {
   $ret = array();
@@ -1159,6 +1160,30 @@ function update_154() {
   return $ret;
 }
 
+function update_155() {
+  $ret = array();
+
+  // Postgresql only update
+  switch ($GLOBALS['db_type']) {
+    case 'pgsql':
+      $ret[] = update_sql("DROP TABLE {cache}");
+      $ret[] = update_sql("CREATE TABLE {cache} (
+        cid varchar(255) NOT NULL default '',
+        data bytea default '',
+        expire integer NOT NULL default '0',
+        created integer NOT NULL default '0',
+        headers text default '',
+        PRIMARY KEY (cid)
+        )");
+      $ret[] = update_sql("CREATE INDEX {cache}_expire_idx ON {cache}(expire)");
+      break;
+    case 'mysql':
+    case 'mysqli':
+      break;
+  }
+
+  return $ret;
+}
 
 
 /**
diff --git a/includes/bootstrap.inc b/includes/bootstrap.inc
index a4b7ff6c1..6c87605ac 100644
--- a/includes/bootstrap.inc
+++ b/includes/bootstrap.inc
@@ -321,12 +321,10 @@ function cache_get($key) {
  *   A string containing HTTP header information for cached pages.
  */
 function cache_set($cid, $data, $expire = CACHE_PERMANENT, $headers = NULL) {
-  $data = db_encode_blob($data);
-
   db_lock_table('cache');
-  db_query("UPDATE {cache} SET data = '%s', created = %d, expire = %d, headers = '%s' WHERE cid = '%s'", $data, time(), $expire, $headers, $cid);
+  db_query("UPDATE {cache} SET data = %b, created = %d, expire = %d, headers = '%s' WHERE cid = '%s'", $data, time(), $expire, $headers, $cid);
   if (!db_affected_rows()) {
-    @db_query("INSERT INTO {cache} (cid, data, created, expire, headers) VALUES ('%s', '%s', %d, %d, '%s')", $cid, $data, time(), $expire, $headers);
+    @db_query("INSERT INTO {cache} (cid, data, created, expire, headers) VALUES ('%s', %b, %d, %d, '%s')", $cid, $data, time(), $expire, $headers);
   }
   db_unlock_tables();
 }
diff --git a/includes/database.inc b/includes/database.inc
index 5315e66ec..d43fd5ff2 100644
--- a/includes/database.inc
+++ b/includes/database.inc
@@ -131,32 +131,64 @@ function db_set_active($name = 'default') {
 }
 
 /**
+ * Helper function for db_query().
+ */
+function _db_query_callback($match, $init = FALSE) {
+  static $args = NULL;
+  if ($init) {
+    $args = $match;
+    return;
+  }
+
+  switch ($match[1]) {
+    case '%d': // We must use type casting to int to convert false/null/(true?)
+      return (int) array_shift($args); // We don't need db_escape_string as numbers are db-safe
+    case '%s':
+      return db_escape_string(array_shift($args));
+    case '%%':
+      return '%';
+    case '%f':
+      return (float) array_shift($args);
+    case '%b': // binary data
+      return db_encode_blob(array_shift($args));
+  }
+}
+
+define('DB_QUERY_REGEXP', '/(%d|%s|%%|%f|%b)/');
+
+/**
  * Runs a basic query in the active database.
  *
- * User-supplied arguments to the query should be passed in as separate parameters
- * so that they can be properly escaped to avoid SQL injection attacks.
+ * User-supplied arguments to the query should be passed in as separate
+ * parameters so that they can be properly escaped to avoid SQL injection
+ * attacks.
  *
  * @param $query
  *   A string containing an SQL query.
  * @param ...
- *   A variable number of arguments which are substituted into the query using
- *   printf() syntax. Instead of a variable number of query arguments, you may
- *   also pass a single array containing the query arguments.
+ *   A variable number of arguments which are substituted into the query
+ *   using printf() syntax. Instead of a variable number of query arguments,
+ *   you may also pass a single array containing the query arguments.
+
+ *   Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
+ *   in '') and %%.
+ *
+ *   NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
+ *   and TRUE values to decimal 1.
+ *
  * @return
- *   A database query result resource, or FALSE if the query was not executed
- *   correctly.
+ *   A database query result resource, or FALSE if the query was not
+ *   executed correctly.
  */
 function db_query($query) {
   $args = func_get_args();
+  array_shift($args);
   $query = db_prefix_tables($query);
-  if (count($args) > 1) {
-    if (is_array($args[1])) {
-      $args = array_merge(array($query), $args[1]);
-    }
-    $args = array_map('db_escape_string', $args);
-    $args[0] = $query;
-    $query = call_user_func_array('sprintf', $args);
+  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
+    $args = $args[0];
   }
+  _db_query_callback($args, TRUE);
+  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
   return _db_query($query);
 }
 
@@ -167,15 +199,13 @@ function db_query($query) {
  */
 function db_queryd($query) {
   $args = func_get_args();
+  array_shift($args);
   $query = db_prefix_tables($query);
-  if (count($args) > 1) {
-    if (is_array($args[1])) {
-      $args = array_merge(array($query), $args[1]);
-    }
-    $args = array_map('db_escape_string', $args);
-    $args[0] = $query;
-    $query = call_user_func_array('sprintf', $args);
+  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
+    $args = $args[0];
   }
+  _db_query_callback($args, TRUE);
+  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
   return _db_query($query, 1);
 }
 
diff --git a/includes/database.mysql.inc b/includes/database.mysql.inc
index 8f96dc3bb..ed02fa886 100644
--- a/includes/database.mysql.inc
+++ b/includes/database.mysql.inc
@@ -214,12 +214,23 @@ function db_affected_rows() {
  * User-supplied arguments to the query should be passed in as separate parameters
  * so that they can be properly escaped to avoid SQL injection attacks.
  *
+ * Note that if you need to know how many results were returned, you should do
+ * a SELECT COUNT(*) on the temporary table afterwards. db_num_rows() and
+ * db_affected_rows() do not give consistent result across different database
+ * types in this case.
+ *
  * @param $query
  *   A string containing an SQL query.
  * @param ...
- *   A variable number of arguments which are substituted into the query using
- *   printf() syntax. Instead of a variable number of query arguments, you may
- *   also pass a single array containing the query arguments.
+ *   A variable number of arguments which are substituted into the query
+ *   using printf() syntax. The query arguments can be enclosed in one
+ *   array instead.
+ *   Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
+ *   in '') and %%.
+ *
+ *   NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
+ *   and TRUE values to decimal 1.
+ *
  * @param $from
  *   The first result row to return.
  * @param $count
@@ -232,17 +243,14 @@ function db_query_range($query) {
   $args = func_get_args();
   $count = array_pop($args);
   $from = array_pop($args);
+  array_shift($args);
 
   $query = db_prefix_tables($query);
-  if (count($args) > 1) {
-    // Check for array (alternative syntax).
-    if (is_array($args[1])) {
-      $args = array_merge(array($query), $args[1]);
-    }
-    $args = array_map('db_escape_string', $args);
-    $args[0] = $query;
-    $query = call_user_func_array('sprintf', $args);
+  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
+    $args = $args[0];
   }
+  _db_query_callback($args, TRUE);
+  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
   $query .= ' LIMIT '. $from .', '. $count;
   return _db_query($query);
 }
@@ -264,9 +272,15 @@ function db_query_range($query) {
  * @param $query
  *   A string containing a normal SELECT SQL query.
  * @param ...
- *   A variable number of arguments which are substituted into the query using
- *   printf() syntax. Instead of a variable number of query arguments, you may
- *   also pass a single array containing the query arguments.
+ *   A variable number of arguments which are substituted into the query
+ *   using printf() syntax. The query arguments can be enclosed in one
+ *   array instead.
+ *   Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
+ *   in '') and %%.
+ *
+ *   NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
+ *   and TRUE values to decimal 1.
+ *
  * @param $table
  *   The name of the temporary table to select into. This name will not be
  *   prefixed as there is no risk of collision.
@@ -277,17 +291,14 @@ function db_query_range($query) {
 function db_query_temporary($query) {
   $args = func_get_args();
   $tablename = array_pop($args);
+  array_shift($args);
 
   $query = preg_replace('/^SELECT/i', 'CREATE TEMPORARY TABLE '. $tablename .' SELECT', db_prefix_tables($query));
-  if (count($args) > 1) {
-    // Check for array (alternative syntax).
-    if (is_array($args[1])) {
-      $args = array_merge(array($query), $args[1]);
-    }
-    $args = array_map('db_escape_string', $args);
-    $args[0] = $query;
-    $query = call_user_func_array('sprintf', $args);
+  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
+    $args = $args[0];
   }
+  _db_query_callback($args, TRUE);
+  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
   return _db_query($query);
 }
 
@@ -300,7 +311,7 @@ function db_query_temporary($query) {
  *  Encoded data.
  */
 function db_encode_blob($data) {
-  return $data;
+  return "'". mysql_real_escape_string($data) ."'";
 }
 
 /**
@@ -336,7 +347,6 @@ function db_unlock_tables() {
   db_query('UNLOCK TABLES');
 }
 
-
 /**
  * @} End of "ingroup database".
  */
diff --git a/includes/database.mysqli.inc b/includes/database.mysqli.inc
index 7cad39921..f2583b214 100644
--- a/includes/database.mysqli.inc
+++ b/includes/database.mysqli.inc
@@ -233,9 +233,15 @@ function db_affected_rows() {
  * @param $query
  *   A string containing an SQL query.
  * @param ...
- *   A variable number of arguments which are substituted into the query using
- *   printf() syntax. Instead of a variable number of query arguments, you may
- *   also pass a single array containing the query arguments.
+ *   A variable number of arguments which are substituted into the query
+ *   using printf() syntax. The query arguments can be enclosed in one
+ *   array instead.
+ *   Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
+ *   in '') and %%.
+ *
+ *   NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
+ *   and TRUE values to decimal 1.
+ *
  * @param $from
  *   The first result row to return.
  * @param $count
@@ -248,17 +254,14 @@ function db_query_range($query) {
   $args = func_get_args();
   $count = array_pop($args);
   $from = array_pop($args);
+  array_shift($args);
 
   $query = db_prefix_tables($query);
-  if (count($args) > 1) {
-    // Check for array (alternative syntax).
-    if (is_array($args[1])) {
-      $args = array_merge(array($query), $args[1]);
-    }
-    $args = array_map('db_escape_string', $args);
-    $args[0] = $query;
-    $query = call_user_func_array('sprintf', $args);
+  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
+    $args = $args[0];
   }
+  _db_query_callback($args, TRUE);
+  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
   $query .= ' LIMIT '. $from .', '. $count;
   return _db_query($query);
 }
@@ -275,14 +278,20 @@ function db_query_range($query) {
  * Note that if you need to know how many results were returned, you should do
  * a SELECT COUNT(*) on the temporary table afterwards. db_num_rows() and
  * db_affected_rows() do not give consistent result across different database
- * types.
+ * types in this case.
  *
  * @param $query
  *   A string containing a normal SELECT SQL query.
  * @param ...
- *   A variable number of arguments which are substituted into the query using
- *   printf() syntax. Instead of a variable number of query arguments, you may
- *   also pass a single array containing the query arguments.
+ *   A variable number of arguments which are substituted into the query
+ *   using printf() syntax. The query arguments can be enclosed in one
+ *   array instead.
+ *   Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
+ *   in '') and %%.
+ *
+ *   NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
+ *   and TRUE values to decimal 1.
+ *
  * @param $table
  *   The name of the temporary table to select into. This name will not be
  *   prefixed as there is no risk of collision.
@@ -293,17 +302,14 @@ function db_query_range($query) {
 function db_query_temporary($query) {
   $args = func_get_args();
   $tablename = array_pop($args);
+  array_shift($args);
 
   $query = preg_replace('/^SELECT/i', 'CREATE TEMPORARY TABLE '. $tablename .' SELECT', db_prefix_tables($query));
-  if (count($args) > 1) {
-    // Check for array (alternative syntax).
-    if (is_array($args[1])) {
-      $args = array_merge(array($query), $args[1]);
-    }
-    $args = array_map('db_escape_string', $args);
-    $args[0] = $query;
-    $query = call_user_func_array('sprintf', $args);
+  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
+    $args = $args[0];
   }
+  _db_query_callback($args, TRUE);
+  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
   return _db_query($query);
 }
 
@@ -316,7 +322,7 @@ function db_query_temporary($query) {
  *  Encoded data.
  */
 function db_encode_blob($data) {
-  return $data;
+  return "'". mysql_real_escape_string($data) ."'";
 }
 
 /**
@@ -339,7 +345,6 @@ function db_escape_string($text) {
   return mysqli_real_escape_string($active_db, $text);
 }
 
-
 /**
  * Lock a table.
  */
diff --git a/includes/database.pgsql.inc b/includes/database.pgsql.inc
index a25f86baf..415565a47 100644
--- a/includes/database.pgsql.inc
+++ b/includes/database.pgsql.inc
@@ -194,17 +194,24 @@ function db_affected_rows() {
 /**
  * Runs a limited-range query in the active database.
  *
- * Use this as a substitute for db_query() when a subset of the query is to be
- * returned.
- * User-supplied arguments to the query should be passed in as separate parameters
- * so that they can be properly escaped to avoid SQL injection attacks.
+ * Use this as a substitute for db_query() when a subset of the query
+ * is to be returned.
+ * User-supplied arguments to the query should be passed in as separate
+ * parameters so that they can be properly escaped to avoid SQL injection
+ * attacks.
  *
  * @param $query
  *   A string containing an SQL query.
  * @param ...
- *   A variable number of arguments which are substituted into the query using
- *   printf() syntax. Instead of a variable number of query arguments, you may
- *   also pass a single array containing the query arguments.
+ *   A variable number of arguments which are substituted into the query
+ *   using printf() syntax. Instead of a variable number of query arguments,
+ *   you may also pass a single array containing the query arguments.
+ *   Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
+ *   in '') and %%.
+ *
+ *   NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
+ *   and TRUE values to decimal 1.
+ *
  * @param $from
  *   The first result row to return.
  * @param $count
@@ -217,17 +224,14 @@ function db_query_range($query) {
   $args = func_get_args();
   $count = array_pop($args);
   $from = array_pop($args);
+  array_shift($args);
 
   $query = db_prefix_tables($query);
-  if (count($args) > 1) {
-    // Check for array (alternative syntax).
-    if (is_array($args[1])) {
-      $args = array_merge(array($query), $args[1]);
-    }
-    $args = array_map('db_escape_string', $args);
-    $args[0] = $query;
-    $query = call_user_func_array('sprintf', $args);
+  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
+    $args = $args[0];
   }
+  _db_query_callback($args, TRUE);
+  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
   $query .= ' LIMIT '. $count .' OFFSET '. $from;
   return _db_query($query);
 }
@@ -249,9 +253,15 @@ function db_query_range($query) {
  * @param $query
  *   A string containing a normal SELECT SQL query.
  * @param ...
- *   A variable number of arguments which are substituted into the query using
- *   printf() syntax. Instead of a variable number of query arguments, you may
- *   also pass a single array containing the query arguments.
+ *   A variable number of arguments which are substituted into the query
+ *   using printf() syntax. The query arguments can be enclosed in one
+ *   array instead.
+ *   Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
+ *   in '') and %%.
+ *
+ *   NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
+ *   and TRUE values to decimal 1.
+ *
  * @param $table
  *   The name of the temporary table to select into. This name will not be
  *   prefixed as there is no risk of collision.
@@ -262,22 +272,20 @@ function db_query_range($query) {
 function db_query_temporary($query) {
   $args = func_get_args();
   $tablename = array_pop($args);
+  array_shift($args);
 
   $query = preg_replace('/^SELECT/i', 'CREATE TEMPORARY TABLE '. $tablename .' AS SELECT', db_prefix_tables($query));
-  if (count($args) > 1) {
-    // Check for array (alternative syntax).
-    if (is_array($args[1])) {
-      $args = array_merge(array($query), $args[1]);
-    }
-    $args = array_map('db_escape_string', $args);
-    $args[0] = $query;
-    $query = call_user_func_array('sprintf', $args);
+  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
+    $args = $args[0];
   }
+  _db_query_callback($args, TRUE);
+  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
   return _db_query($query);
 }
 
 /**
  * Returns a properly formatted Binary Large OBject value.
+ * In case of PostgreSQL encodes data for insert into bytea field.
  *
  * @param $data
  *   Data to encode.
@@ -285,11 +293,12 @@ function db_query_temporary($query) {
  *  Encoded data.
  */
 function db_encode_blob($data) {
-  return addcslashes($data, "\0..\37\\");
+  return "'". pg_escape_bytea($data) ."'";
 }
 
 /**
  * Returns text from a Binary Large OBject value.
+ * In case of PostgreSQL decodes data after select from bytea field.
  *
  * @param $data
  *   Data to decode.
@@ -297,7 +306,7 @@ function db_encode_blob($data) {
  *  Decoded data.
  */
 function db_decode_blob($data) {
-  return stripcslashes($data);
+  return pg_unescape_bytea($data);
 }
 
 /**