1 <?php

     2 // $Id: database.mysqli.inc,v 1.54 2008/01/23 09:59:29 goba Exp $

     3 

     4 /**

     5  * @file

     6  * Database interface code for MySQL database servers using the mysqli client libraries. mysqli is included in PHP 5 by default and allows developers to use the advanced features of MySQL 4.1.x, 5.0.x and beyond.

     7  */

     8 

     9  // Maintainers of this file should consult:

    10  // http://www.php.net/manual/en/ref.mysqli.php

    11 

    12 /**

    13  * @ingroup database

    14  * @{

    15  */

    16 

    17 // Include functions shared between mysql and mysqli.

    18 require_once './includes/database.mysql-common.inc';

    19 

    20 /**

    21  * Report database status.

    22  */

    23 function db_status_report($phase) {

    24   $t = get_t();

    25 

    26   $version = db_version();

    27 

    28   $form['mysql'] = array(

    29     'title' => $t('MySQL database'),

    30     'value' => ($phase == 'runtime') ? l($version, 'admin/reports/status/sql') : $version,

    31   );

    32 

    33   if (version_compare($version, DRUPAL_MINIMUM_MYSQL) < 0) {

    34     $form['mysql']['severity'] = REQUIREMENT_ERROR;

    35     $form['mysql']['description'] = $t('Your MySQL Server is too old. Drupal requires at least MySQL %version.', array('%version' => DRUPAL_MINIMUM_MYSQL));

    36   }

    37 

    38   return $form;

    39 }

    40 

    41 /**

    42  * Returns the version of the database server currently in use.

    43  *

    44  * @return Database server version

    45  */

    46 function db_version() {

    47   global $active_db;

    48   list($version) = explode('-', mysqli_get_server_info($active_db));

    49   return $version;

    50 }

    51 

    52 /**

    53  * Initialise a database connection.

    54  *

    55  * Note that mysqli does not support persistent connections.

    56  */

    57 function db_connect($url) {

    58   // Check if MySQLi support is present in PHP

    59   if (!function_exists('mysqli_init') && !extension_loaded('mysqli')) {

    60     _db_error_page('Unable to use the MySQLi database because the MySQLi extension for PHP is not installed. Check your <code>php.ini</code> to see how you can enable it.');

    61   }

    62 

    63   $url = parse_url($url);

    64 

    65   // Decode url-encoded information in the db connection string

    66   $url['user'] = urldecode($url['user']);

    67   // Test if database url has a password.

    68   $url['pass'] = isset($url['pass']) ? urldecode($url['pass']) : '';

    69   $url['host'] = urldecode($url['host']);

    70   $url['path'] = urldecode($url['path']);

    71   if (!isset($url['port'])) {

    72     $url['port'] = NULL;

    73   }

    74 

    75   $connection = mysqli_init();

    76   @mysqli_real_connect($connection, $url['host'], $url['user'], $url['pass'], substr($url['path'], 1), $url['port'], NULL, MYSQLI_CLIENT_FOUND_ROWS);

    77 

    78   if (mysqli_connect_errno() > 0) {

    79     _db_error_page(mysqli_connect_error());

    80   }

    81 

    82   // Force UTF-8.

    83   mysqli_query($connection, 'SET NAMES "utf8"');

    84 

    85   return $connection;

    86 }

    87 

    88 /**

    89  * Helper function for db_query().

    90  */

    91 function _db_query($query, $debug = 0) {

    92   global $active_db, $queries, $user;

    93 

    94   if (variable_get('dev_query', 0)) {

    95     list($usec, $sec) = explode(' ', microtime());

    96     $timer = (float)$usec + (float)$sec;

    97     // If devel.module query logging is enabled, prepend a comment with the username and calling function

    98     // to the SQL string. This is useful when running mysql's SHOW PROCESSLIST to learn what exact

    99     // code is issueing the slow query.

   100     $bt = debug_backtrace();

   101     // t() may not be available yet so we don't wrap 'Anonymous'

   102     $name = $user->uid ? $user->name : variable_get('anonymous', 'Anonymous');

   103     // str_replace() to prevent SQL injection via username or anonymous name.

   104     $name = str_replace(array('*', '/'), '', $name);

   105     $query = '/* '. $name .' : '. $bt[2]['function'] .' */ '. $query;

   106   }

   107 

   108   $result = mysqli_query($active_db, $query);

   109 

   110   if (variable_get('dev_query', 0)) {

   111     $query = $bt[2]['function'] ."\n". $query;

   112     list($usec, $sec) = explode(' ', microtime());

   113     $stop = (float)$usec + (float)$sec;

   114     $diff = $stop - $timer;

   115     $queries[] = array($query, $diff);

   116   }

   117 

   118   if ($debug) {

   119     print '<p>query: '. $query .'<br />error:'. mysqli_error($active_db) .'</p>';

   120   }

   121 

   122   if (!mysqli_errno($active_db)) {

   123     return $result;

   124   }

   125   else {

   126     // Indicate to drupal_error_handler that this is a database error.

   127     ${DB_ERROR} = TRUE;

   128     trigger_error(check_plain(mysqli_error($active_db) ."\nquery: ". $query), E_USER_WARNING);

   129     return FALSE;

   130   }

   131 }

   132 

   133 /**

   134  * Fetch one result row from the previous query as an object.

   135  *

   136  * @param $result

   137  *   A database query result resource, as returned from db_query().

   138  * @return

   139  *   An object representing the next row of the result, or FALSE. The attributes

   140  *   of this object are the table fields selected by the query.

   141  */

   142 function db_fetch_object($result) {

   143   if ($result) {

   144     $object = mysqli_fetch_object($result);

   145     return isset($object) ? $object : FALSE;

   146   }

   147 }

   148 

   149 /**

   150  * Fetch one result row from the previous query as an array.

   151  *

   152  * @param $result

   153  *   A database query result resource, as returned from db_query().

   154  * @return

   155  *   An associative array representing the next row of the result, or FALSE.

   156  *   The keys of this object are the names of the table fields selected by the

   157  *   query, and the values are the field values for this result row.

   158  */

   159 function db_fetch_array($result) {

   160   if ($result) {

   161     $array = mysqli_fetch_array($result, MYSQLI_ASSOC);

   162     return isset($array) ? $array : FALSE;

   163   }

   164 }

   165 

   166 /**

   167  * Return an individual result field from the previous query.

   168  *

   169  * Only use this function if exactly one field is being selected; otherwise,

   170  * use db_fetch_object() or db_fetch_array().

   171  *

   172  * @param $result

   173  *   A database query result resource, as returned from db_query().

   174  * @return

   175  *   The resulting field or FALSE.

   176  */

   177 function db_result($result) {

   178   if ($result && mysqli_num_rows($result) > 0) {

   179     // The mysqli_fetch_row function has an optional second parameter $row

   180     // but that can't be used for compatibility with Oracle, DB2, etc.

   181     $array = mysqli_fetch_row($result);

   182     return $array[0];

   183   }

   184   return FALSE;

   185 }

   186 

   187 /**

   188  * Determine whether the previous query caused an error.

   189  */

   190 function db_error() {

   191   global $active_db;

   192   return mysqli_errno($active_db);

   193 }

   194 

   195 /**

   196  * Determine the number of rows changed by the preceding query.

   197  */

   198 function db_affected_rows() {

   199   global $active_db; /* mysqli connection resource */

   200   return mysqli_affected_rows($active_db);

   201 }

   202 

   203 /**

   204  * Runs a limited-range query in the active database.

   205  *

   206  * Use this as a substitute for db_query() when a subset of the query is to be

   207  * returned.

   208  * User-supplied arguments to the query should be passed in as separate parameters

   209  * so that they can be properly escaped to avoid SQL injection attacks.

   210  *

   211  * @param $query

   212  *   A string containing an SQL query.

   213  * @param ...

   214  *   A variable number of arguments which are substituted into the query

   215  *   using printf() syntax. The query arguments can be enclosed in one

   216  *   array instead.

   217  *   Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose

   218  *   in '') and %%.

   219  *

   220  *   NOTE: using this syntax will cast NULL and FALSE values to decimal 0,

   221  *   and TRUE values to decimal 1.

   222  *

   223  * @param $from

   224  *   The first result row to return.

   225  * @param $count

   226  *   The maximum number of result rows to return.

   227  * @return

   228  *   A database query result resource, or FALSE if the query was not executed

   229  *   correctly.

   230  */

   231 function db_query_range($query) {

   232   $args = func_get_args();

   233   $count = array_pop($args);

   234   $from = array_pop($args);

   235   array_shift($args);

   236 

   237   $query = db_prefix_tables($query);

   238   if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax

   239     $args = $args[0];

   240   }

   241   _db_query_callback($args, TRUE);

   242   $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);

   243   $query .= ' LIMIT '. (int)$from .', '. (int)$count;

   244   return _db_query($query);

   245 }

   246 

   247 /**

   248  * Runs a SELECT query and stores its results in a temporary table.

   249  *

   250  * Use this as a substitute for db_query() when the results need to stored

   251  * in a temporary table. Temporary tables exist for the duration of the page

   252  * request.

   253  * User-supplied arguments to the query should be passed in as separate parameters

   254  * so that they can be properly escaped to avoid SQL injection attacks.

   255  *

   256  * Note that if you need to know how many results were returned, you should do

   257  * a SELECT COUNT(*) on the temporary table afterwards. db_affected_rows() does

   258  * not give consistent result across different database types in this case.

   259  *

   260  * @param $query

   261  *   A string containing a normal SELECT SQL query.

   262  * @param ...

   263  *   A variable number of arguments which are substituted into the query

   264  *   using printf() syntax. The query arguments can be enclosed in one

   265  *   array instead.

   266  *   Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose

   267  *   in '') and %%.

   268  *

   269  *   NOTE: using this syntax will cast NULL and FALSE values to decimal 0,

   270  *   and TRUE values to decimal 1.

   271  *

   272  * @param $table

   273  *   The name of the temporary table to select into. This name will not be

   274  *   prefixed as there is no risk of collision.

   275  * @return

   276  *   A database query result resource, or FALSE if the query was not executed

   277  *   correctly.

   278  */

   279 function db_query_temporary($query) {

   280   $args = func_get_args();

   281   $tablename = array_pop($args);

   282   array_shift($args);

   283 

   284   $query = preg_replace('/^SELECT/i', 'CREATE TEMPORARY TABLE '. $tablename .' Engine=HEAP SELECT', db_prefix_tables($query));

   285   if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax

   286     $args = $args[0];

   287   }

   288   _db_query_callback($args, TRUE);

   289   $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);

   290   return _db_query($query);

   291 }

   292 

   293 /**

   294  * Returns a properly formatted Binary Large Object value.

   295  *

   296  * @param $data

   297  *   Data to encode.

   298  * @return

   299  *  Encoded data.

   300  */

   301 function db_encode_blob($data) {

   302   global $active_db;

   303   return "'". mysqli_real_escape_string($active_db, $data) ."'";

   304 }

   305 

   306 /**

   307  * Returns text from a Binary Large OBject value.

   308  *

   309  * @param $data

   310  *   Data to decode.

   311  * @return

   312  *  Decoded data.

   313  */

   314 function db_decode_blob($data) {

   315   return $data;

   316 }

   317 

   318 /**

   319  * Prepare user input for use in a database query, preventing SQL injection attacks.

   320  */

   321 function db_escape_string($text) {

   322   global $active_db;

   323   return mysqli_real_escape_string($active_db, $text);

   324 }

   325 

   326 /**

   327  * Lock a table.

   328  */

   329 function db_lock_table($table) {

   330   db_query('LOCK TABLES {'. db_escape_table($table) .'} WRITE');

   331 }

   332 

   333 /**

   334  * Unlock all locked tables.

   335  */

   336 function db_unlock_tables() {

   337   db_query('UNLOCK TABLES');

   338 }

   339 

   340 /**

   341  * Check if a table exists.

   342  */

   343 function db_table_exists($table) {

   344   return (bool) db_fetch_object(db_query("SHOW TABLES LIKE '{". db_escape_table($table) ."}'"));

   345 }

   346 

   347 /**

   348  * Check if a column exists in the given table.

   349  */

   350 function db_column_exists($table, $column) {

   351   return (bool) db_fetch_object(db_query("SHOW COLUMNS FROM {". db_escape_table($table) ."} LIKE '". db_escape_table($column) ."'"));

   352 }

   353 

   354 /**

   355  * Wraps the given table.field entry with a DISTINCT(). The wrapper is added to

   356  * the SELECT list entry of the given query and the resulting query is returned.

   357  * This function only applies the wrapper if a DISTINCT doesn't already exist in

   358  * the query.

   359  *

   360  * @param $table Table containing the field to set as DISTINCT

   361  * @param $field Field to set as DISTINCT

   362  * @param $query Query to apply the wrapper to

   363  * @return SQL query with the DISTINCT wrapper surrounding the given table.field.

   364  */

   365 function db_distinct_field($table, $field, $query) {

   366   $field_to_select = 'DISTINCT('. $table .'.'. $field .')';

   367   // (?<!text) is a negative look-behind (no need to rewrite queries that already use DISTINCT).

   368   return preg_replace('/(SELECT.*)(?:'. $table .'\.|\s)(?<!DISTINCT\()(?<!DISTINCT\('. $table .'\.)'. $field .'(.*FROM )/AUsi', '\1 '. $field_to_select .'\2', $query);

   369 }

   370 

   371 /**

   372  * @} End of "ingroup database".

   373  */

   374