1 <?php

     2 // $Id: database.mysql.inc,v 1.89 2008/01/24 10:46:54 goba Exp $

     3 

     4 /**

     5  * @file

     6  * Database interface code for MySQL database servers.

     7  */

     8 

     9 /**

    10  * @ingroup database

    11  * @{

    12  */

    13 

    14 // Include functions shared between mysql and mysqli.

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

    16 

    17 /**

    18  * Report database status.

    19  */

    20 function db_status_report($phase) {

    21   $t = get_t();

    22 

    23   $version = db_version();

    24 

    25   $form['mysql'] = array(

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

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

    28   );

    29 

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

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

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

    33   }

    34 

    35   return $form;

    36 }

    37 

    38 /**

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

    40  *

    41  * @return Database server version

    42  */

    43 function db_version() {

    44   list($version) = explode('-', mysql_get_server_info());

    45   return $version;

    46 }

    47 

    48 /**

    49  * Initialize a database connection.

    50  */

    51 function db_connect($url) {

    52   $url = parse_url($url);

    53 

    54   // Check if MySQL support is present in PHP

    55   if (!function_exists('mysql_connect')) {

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

    57   }

    58 

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

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

    61   // Test if database url has a password.

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

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

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

    65 

    66   // Allow for non-standard MySQL port.

    67   if (isset($url['port'])) {

    68     $url['host'] = $url['host'] .':'. $url['port'];

    69   }

    70 

    71   // - TRUE makes mysql_connect() always open a new link, even if

    72   //   mysql_connect() was called before with the same parameters.

    73   //   This is important if you are using two databases on the same

    74   //   server.

    75   // - 2 means CLIENT_FOUND_ROWS: return the number of found

    76   //   (matched) rows, not the number of affected rows.

    77   $connection = @mysql_connect($url['host'], $url['user'], $url['pass'], TRUE, 2);

    78   if (!$connection || !mysql_select_db(substr($url['path'], 1))) {

    79     // Show error screen otherwise

    80     _db_error_page(mysql_error());

    81   }

    82 

    83   // Force UTF-8.

    84   mysql_query('SET NAMES "utf8"', $connection);

    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 = mysql_query($query, $active_db);

   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:'. mysql_error($active_db) .'</p>';

   120   }

   121 

   122   if (!mysql_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(mysql_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     return mysql_fetch_object($result);

   145   }

   146 }

   147 

   148 /**

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

   150  *

   151  * @param $result

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

   153  * @return

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

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

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

   157  */

   158 function db_fetch_array($result) {

   159   if ($result) {

   160     return mysql_fetch_array($result, MYSQL_ASSOC);

   161   }

   162 }

   163 

   164 /**

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

   166  *

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

   168  * use db_fetch_object() or db_fetch_array().

   169  *

   170  * @param $result

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

   172  * @return

   173  *   The resulting field or FALSE.

   174  */

   175 function db_result($result) {

   176   if ($result && mysql_num_rows($result) > 0) {

   177     // The mysql_fetch_row function has an optional second parameter $row

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

   179     $array = mysql_fetch_row($result);

   180     return $array[0];

   181   }

   182   return FALSE;

   183 }

   184 

   185 /**

   186  * Determine whether the previous query caused an error.

   187  */

   188 function db_error() {

   189   global $active_db;

   190   return mysql_errno($active_db);

   191 }

   192 

   193 /**

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

   195  */

   196 function db_affected_rows() {

   197   global $active_db;

   198   return mysql_affected_rows($active_db);

   199 }

   200 

   201 /**

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

   203  *

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

   205  * returned.

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

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

   208  *

   209  * @param $query

   210  *   A string containing an SQL query.

   211  * @param ...

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

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

   214  *   array instead.

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

   216  *   in '') and %%.

   217  *

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

   219  *   and TRUE values to decimal 1.

   220  *

   221  * @param $from

   222  *   The first result row to return.

   223  * @param $count

   224  *   The maximum number of result rows to return.

   225  * @return

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

   227  *   correctly.

   228  */

   229 function db_query_range($query) {

   230   $args = func_get_args();

   231   $count = array_pop($args);

   232   $from = array_pop($args);

   233   array_shift($args);

   234 

   235   $query = db_prefix_tables($query);

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

   237     $args = $args[0];

   238   }

   239   _db_query_callback($args, TRUE);

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

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

   242   return _db_query($query);

   243 }

   244 

   245 /**

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

   247  *

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

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

   250  * request.

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

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

   253  *

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

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

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

   257  *

   258  * @param $query

   259  *   A string containing a normal SELECT SQL query.

   260  * @param ...

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

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

   263  *   array instead.

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

   265  *   in '') and %%.

   266  *

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

   268  *   and TRUE values to decimal 1.

   269  *

   270  * @param $table

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

   272  *   prefixed as there is no risk of collision.

   273  * @return

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

   275  *   correctly.

   276  */

   277 function db_query_temporary($query) {

   278   $args = func_get_args();

   279   $tablename = array_pop($args);

   280   array_shift($args);

   281 

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

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

   284     $args = $args[0];

   285   }

   286   _db_query_callback($args, TRUE);

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

   288   return _db_query($query);

   289 }

   290 

   291 /**

   292  * Returns a properly formatted Binary Large OBject value.

   293  *

   294  * @param $data

   295  *   Data to encode.

   296  * @return

   297  *  Encoded data.

   298  */

   299 function db_encode_blob($data) {

   300   global $active_db;

   301   return "'". mysql_real_escape_string($data, $active_db) ."'";

   302 }

   303 

   304 /**

   305  * Returns text from a Binary Large Object value.

   306  *

   307  * @param $data

   308  *   Data to decode.

   309  * @return

   310  *  Decoded data.

   311  */

   312 function db_decode_blob($data) {

   313   return $data;

   314 }

   315 

   316 /**

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

   318  */

   319 function db_escape_string($text) {

   320   global $active_db;

   321   return mysql_real_escape_string($text, $active_db);

   322 }

   323 

   324 /**

   325  * Lock a table.

   326  */

   327 function db_lock_table($table) {

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

   329 }

   330 

   331 /**

   332  * Unlock all locked tables.

   333  */

   334 function db_unlock_tables() {

   335   db_query('UNLOCK TABLES');

   336 }

   337 

   338 /**

   339  * Check if a table exists.

   340  */

   341 function db_table_exists($table) {

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

   343 }

   344 

   345 /**

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

   347  */

   348 function db_column_exists($table, $column) {

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

   350 }

   351 

   352 /**

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

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

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

   356  * the query.

   357  *

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

   359  * @param $field Field to set as DISTINCT

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

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

   362  */

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

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

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

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

   367 }

   368 

   369 /**

   370  * @} End of "ingroup database".

   371  */