D6 CVS HEAD contain the following codes:

find drupal | xargs egrep -nH "WHERE.*= ''"
drupal/modules/node/node.admin.inc:364:  $count = db_result(db_query("SELECT COUNT(*) FROM {node} n WHERE language != ''"));
drupal/modules/system/system.module:1194:  $instances_present = db_fetch_object(db_query("SELECT aid FROM {actions} WHERE parameters != ''"));
drupal/modules/locale/locale.install:104:  $ret[] = update_sql("DELETE FROM {locales_target} WHERE translation = ''");
drupal/modules/path/path.admin.inc:18:  $count = db_result(db_query("SELECT COUNT(*) FROM {url_alias} WHERE language != ''"));
drupal/includes/actions.inc:242:  $aid = db_result(db_query("SELECT aid FROM {actions} WHERE MD5(aid) = '%s' AND parameters != ''", $hash));
drupal/includes/actions.inc:259:  $result = db_query("SELECT * FROM {actions} WHERE parameters = ''");

Using empty string '' as default string placeholder may cause cross database incompatible issue. It is most likely correct in case of MySQL and PgSQL, but not every database are handling in correct ANSI style:

  1. In case of Oracle, empty string '' is equal to NULL, and so the = '' will always return false. This is all due to Oracle internal handling, which may judge as buggy. This is a DB level problem, PDO can't help about this.
  2. In case of MSSQL, fetching empty string '' by PHP will return as ' ' (a single space string). Surly that most if ($value->string == '') checking after db_fetch_array() and db_fetch_object() will result as buggy. This is due to PHP MSSQL driver implementation. This is a PHP driver level problem, PDO also can't help about this.

We may solve this problem by using DB_DEFAULT as default string placeholder, rather than an empty string directly. E.g., in case of Oracle, DB_DEFAULT can define as '_OCI8_DEFAULT_' so it is no longer empty. This will waste some disk space, but code is now portable:

<?php
/**
 * Indicates the default string placeholder.
 */
define('DB_DEFAULT', '_OCI8_DEFAULT_');
?>

In case of MySQL/PgSQL, as there is no problem in empty string handling, the mapping can just act as dummy. So no extra disk space will be wasted for cross database compatible:

<?php
/**
 * Indicates the default string placeholder.
 */
define('DB_DEFAULT', '');
?>

We need to use DB_DEFAULT for both DB schema, DB level compare and also PHP level compare (just similar as compare with using ''). E.g.:

<?php
db_query("SELECT * FROM {actions} WHERE parameters = '%s'", DB_DEFAULT);
?>

Comments

hswong3i’s picture

hswong3i commented

Oracle reference:
http://www.techonthenet.com/oracle/questions/empty_null.php
http://www.adp-gmbh.ch/ora/misc/null.html

MSSQL reference:
http://bugs.php.net/bug.php?id=26996

hswong3i’s picture

hswong3i commented
Priority: Normal » Critical
hswong3i’s picture

hswong3i commented

According to latest Oracle driver research result, the bug for Oracle and MSSQL should able to handle within driver implementation. In case of Oracle:

  1. Define a constant DB_EMPTY_STRING_PLACEHOLDER with random-password-like content:
    <?php
define('DB_EMPTY_STRING_PLACEHOLDER', 'ee6ui3ah');
?>
  2. During table creation/altering, if field is text/clob with default value '', replace it as DB_EMPTY_STRING_PLACEHOLDER:
    <?php
  if (($field['type'] == 'text') || ($field['type'] == 'varchar')) {
    if (isset($field['not null']) && (!isset($field['default']) || $field['default'] == '')) {
      $field['default'] = DB_EMPTY_STRING_PLACEHOLDER;
    }
  }
?>
  3. If db_escape_string() catch any '' input, replace it as DB_EMPTY_STRING_PLACEHOLDER. So this replacement will preform for whatever SELECT/INSERT/UPDATE/DELETE queries automatically, if placeholder %s use correctly:
    <?php
function db_escape_string($data) {
  // Trim first 4000 characters, based on Oracle VARCHAR2(4000) limitation.
  $data = preg_replace('/(.{0,4000})(.*)/', '\1', $data);
  // Replace any single ' with ''
  $data = str_replace("'", "''", $data);
  // Replace empty string placeholder.
  $data = $data != '' ? $data : DB_EMPTY_STRING_PLACEHOLDER;
  return "'". $data ."'";
}
?>
  4. Finally, replace all output content as '' if it is equal to DB_EMPTY_STRING_PLACEHOLDER, during db_fetch_object(), db_fetch_array() or db_result():
    <?php
function db_result($result) {
  if ($result && oci_fetch($result)) {
    if ($value = oci_result($result, 1)) {
      // Replace empty string placeholder.
      $ret = $value != DB_EMPTY_STRING_PLACEHOLDER ? $value : '';
      return $ret;
    }
  }
  return FALSE;
}
?>

According to this handle, it will require for 2 restriction in global coding standard:

  1. Keep %s syntax, so driver will able to catch it and handle its conversation within db_escape_string(). This will not conflict with PDO implementation, since we can simply replace all %d|%f|%s|%b|%c as ? in case of MySQL/PgSQL PDO implementation, rather than calling db_escape* in userspace manually. This simple preg_replace() should be cheaper than current _db_query_callback() handling.
  2. %s should use correctly, especially for ''. If '' is written as inline query, the above catch-and-convert will not able to take action.
hswong3i’s picture

hswong3i commented

(This is part of my personal research project Siren's issue.)

hswong3i’s picture

hswong3i commented
Title: DB compatibility: avoid using empty string as default placeholder » avoid using empty string as default placeholder
catch’s picture

catch
he/him
Primary language English
commented
Status: Active » Closed (duplicate)

#225450: Database Layer: The Next Generation

dwhlabs’s picture

dwhlabs commented

Oracle reference

http://dwhlabs.com/Databaselab/Oracle/SQL/orac_TUTORIALS_SQL.aspx

About DWHlabs

DWHLabs is an online data management web portal. It offers studies on software topics like Data Modeling, Data Warehousing, Database, ERP, Middle Wear, Testing, and Open Source. Viewers can be a beginner, employee, trainer, consultant or a Professional personality. This application is a best platform to the viewers, where they can share files, participate in discussion boards, share their blogs, and find software training schools in INDIA and abroad, getting answers to the questions.