database.mysql.inc 13.7 KB
Newer Older
Dries's avatar
 
Dries committed
1
<?php
Kjartan's avatar
Kjartan committed
2
// $Id$
Dries's avatar
 
Dries committed
3

Dries's avatar
 
Dries committed
4 5 6 7 8 9
/**
 * @file
 * Database interface code for MySQL database servers.
 */

/**
Dries's avatar
 
Dries committed
10
 * @ingroup database
Dries's avatar
 
Dries committed
11 12 13
 * @{
 */

14 15
// Include functions shared between mysql and mysqli.
require_once './includes/database.mysql-common.inc';
16 17 18 19 20 21 22

/**
 * Report database status.
 */
function db_status_report($phase) {
  $t = get_t();

23 24
  $version = db_version();

25 26
  $form['mysql'] = array(
    'title' => $t('MySQL database'),
27
    'value' => ($phase == 'runtime') ? l($version, 'admin/reports/status/sql') : $version,
28 29 30 31 32 33
  );

  if (version_compare($version, DRUPAL_MINIMUM_MYSQL) < 0) {
    $form['mysql']['severity'] = REQUIREMENT_ERROR;
    $form['mysql']['description'] = $t('Your MySQL Server is too old. Drupal requires at least MySQL %version.', array('%version' => DRUPAL_MINIMUM_MYSQL));
  }
34

35 36 37
  return $form;
}

38 39 40 41 42 43 44 45 46 47
/**
 * Returns the version of the database server currently in use.
 *
 * @return Database server version
 */
function db_version() {
  list($version) = explode('-', mysql_get_server_info());
  return $version;
}

Dries's avatar
 
Dries committed
48 49 50
/**
 * Initialize a database connection.
 */
Dries's avatar
 
Dries committed
51
function db_connect($url) {
52 53
  $url = parse_url($url);

54 55 56 57 58 59 60 61 62
  // Check if MySQL support is present in PHP
  if (!function_exists('mysql_connect')) {
    drupal_maintenance_theme();
    drupal_set_title('PHP MySQL support not enabled');
    print theme('maintenance_page', '<p>We were 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.</p>
<p>For more help, see the <a href="http://drupal.org/node/258">Installation and upgrading handbook</a>. If you are unsure what these terms mean you should probably contact your hosting provider.</p>');
    exit;
  }

63 64
  // Decode url-encoded information in the db connection string
  $url['user'] = urldecode($url['user']);
65
  // Test if database url has a password.
66
  if (isset($url['pass'])) {
67 68 69 70 71
    $url['pass'] = urldecode($url['pass']);
  }
  else {
    $url['pass'] = '';
  }
72 73 74
  $url['host'] = urldecode($url['host']);
  $url['path'] = urldecode($url['path']);

75
  // Allow for non-standard MySQL port.
Dries's avatar
 
Dries committed
76
  if (isset($url['port'])) {
77
    $url['host'] = $url['host'] .':'. $url['port'];
78
  }
Dries's avatar
 
Dries committed
79

80 81 82 83 84 85
  // - TRUE makes mysql_connect() always open a new link, even if
  //   mysql_connect() was called before with the same parameters.
  //   This is important if you are using two databases on the same
  //   server.
  // - 2 means CLIENT_FOUND_ROWS: return the number of found
  //   (matched) rows, not the number of affected rows.
86 87
  $connection = @mysql_connect($url['host'], $url['user'], $url['pass'], TRUE, 2);
  if (!$connection) {
88
    // Show error screen otherwise
89
    drupal_maintenance_theme();
90
    drupal_set_header('HTTP/1.1 503 Service Unavailable');
91
    drupal_set_title('Unable to connect to database server');
92
    print theme('maintenance_page', '<p>If you still have to install Drupal, proceed to the <a href="'. base_path() .'install.php">installation page</a>.</p>
93
<p>If you have already finished installing Drupal, this either means that the username and password information in your <code>settings.php</code> file is incorrect or that we can\'t connect to the MySQL database server. This could mean your hosting provider\'s database server is down.</p>
94 95 96 97 98 99 100 101 102 103
<p>The MySQL error was: '. theme('placeholder', mysql_error()) .'.</p>
<p>Currently, the username is '. theme('placeholder', $url['user']) .' and the database server is '. theme('placeholder', $url['host']) .'.</p>
<ul>
  <li>Are you sure you have the correct username and password?</li>
  <li>Are you sure that you have typed the correct hostname?</li>
  <li>Are you sure that the database server is running?</li>
</ul>
<p>For more help, see the <a href="http://drupal.org/node/258">Installation and upgrading handbook</a>. If you are unsure what these terms mean you should probably contact your hosting provider.</p>');
    exit;
  }
104

105 106 107
  if (!mysql_select_db(substr($url['path'], 1))) {
    drupal_maintenance_theme();
    drupal_set_title('Unable to select database');
108
    print theme('maintenance_page', '<p>We were able to connect to the MySQL database server (which means your username and password are okay) but not able to select the database.</p>
109
<p>The MySQL error was: '. theme('placeholder', mysql_error($connection)) .'.</p>
110 111 112 113 114 115 116 117 118
<p>Currently, the database is '. theme('placeholder', substr($url['path'], 1)) .'. The username is '. theme('placeholder', $url['user']) .' and the database server is '. theme('placeholder', $url['host']) .'.</p>
<ul>
  <li>Are you sure you have the correct database name?</li>
  <li>Are you sure the database exists?</li>
  <li>Are you sure the username has permission to access the database?</li>
</ul>
<p>For more help, see the <a href="http://drupal.org/node/258">Installation and upgrading handbook</a>. If you are unsure what these terms mean you should probably contact your hosting provider.</p>');
    exit;
  }
Dries's avatar
 
Dries committed
119

120 121 122 123
  /* On MySQL 4.1 and later, force UTF-8 */
  if (version_compare(mysql_get_server_info(), '4.1.0', '>=')) {
    mysql_query('SET NAMES "utf8"', $connection);
  }
Dries's avatar
 
Dries committed
124
  return $connection;
Dries's avatar
 
Dries committed
125 126
}

Dries's avatar
 
Dries committed
127 128 129
/**
 * Helper function for db_query().
 */
Dries's avatar
 
Dries committed
130
function _db_query($query, $debug = 0) {
131
  global $active_db, $queries, $user;
Dries's avatar
 
Dries committed
132

133
  if (variable_get('dev_query', 0)) {
Dries's avatar
 
Dries committed
134
    list($usec, $sec) = explode(' ', microtime());
Dries's avatar
 
Dries committed
135
    $timer = (float)$usec + (float)$sec;
136 137 138
    // If devel.module query logging is enabled, prepend a comment with the username and calling function
    // to the SQL string. This is useful when running mysql's SHOW PROCESSLIST to learn what exact
    // code is issueing the slow query.
139
    $bt = debug_backtrace();
140 141 142 143
    // t() may not be available yet so we don't wrap 'Anonymous'.
    $name = $user->uid ? $user->name : variable_get('anonymous', 'Anonymous');
    // str_replace() to prevent SQL injection via username or anonymous name.
    $name = str_replace(array('*', '/'), '', $name);
144
    $query = '/* '. $name .' : '. $bt[2]['function'] .' */ '. $query;
Dries's avatar
 
Dries committed
145
  }
Dries's avatar
 
Dries committed
146

Dries's avatar
 
Dries committed
147
  $result = mysql_query($query, $active_db);
Dries's avatar
 
Dries committed
148

149
  if (variable_get('dev_query', 0)) {
150
    $query = $bt[2]['function'] ."\n". $query;
Dries's avatar
 
Dries committed
151
    list($usec, $sec) = explode(' ', microtime());
Dries's avatar
 
Dries committed
152 153 154 155 156
    $stop = (float)$usec + (float)$sec;
    $diff = $stop - $timer;
    $queries[] = array($query, $diff);
  }

Dries's avatar
 
Dries committed
157
  if ($debug) {
158
    print '<p>query: '. $query .'<br />error:'. mysql_error($active_db) .'</p>';
Dries's avatar
 
Dries committed
159 160
  }

161
  if (!mysql_errno($active_db)) {
Dries's avatar
 
Dries committed
162
    return $result;
Kjartan's avatar
Kjartan committed
163 164
  }
  else {
165 166
    // Indicate to drupal_error_handler that this is a database error.
    ${DB_ERROR} = TRUE;
167
    trigger_error(check_plain(mysql_error($active_db) ."\nquery: ". $query), E_USER_WARNING);
168
    return FALSE;
Dries's avatar
 
Dries committed
169 170 171
  }
}

Dries's avatar
 
Dries committed
172 173 174 175 176 177
/**
 * Fetch one result row from the previous query as an object.
 *
 * @param $result
 *   A database query result resource, as returned from db_query().
 * @return
178 179
 *   An object representing the next row of the result, or FALSE. The attributes
 *   of this object are the table fields selected by the query.
Dries's avatar
 
Dries committed
180
 */
Dries's avatar
 
Dries committed
181 182 183 184 185 186
function db_fetch_object($result) {
  if ($result) {
    return mysql_fetch_object($result);
  }
}

Dries's avatar
 
Dries committed
187 188 189 190 191 192
/**
 * Fetch one result row from the previous query as an array.
 *
 * @param $result
 *   A database query result resource, as returned from db_query().
 * @return
Dries's avatar
Dries committed
193 194
 *   An associative array representing the next row of the result, or FALSE.
 *   The keys of this object are the names of the table fields selected by the
195
 *   query, and the values are the field values for this result row.
Dries's avatar
 
Dries committed
196
 */
Dries's avatar
 
Dries committed
197 198 199 200 201 202
function db_fetch_array($result) {
  if ($result) {
    return mysql_fetch_array($result, MYSQL_ASSOC);
  }
}

Dries's avatar
 
Dries committed
203 204 205 206 207 208 209 210 211
/**
 * Return an individual result field from the previous query.
 *
 * Only use this function if exactly one field is being selected; otherwise,
 * use db_fetch_object() or db_fetch_array().
 *
 * @param $result
 *   A database query result resource, as returned from db_query().
 * @return
212
 *   The resulting field or FALSE.
Dries's avatar
 
Dries committed
213
 */
214 215 216 217 218 219
function db_result($result) {
  if ($result && mysql_num_rows($result) > 0) {
    // The mysql_fetch_row function has an optional second parameter $row
    // but that can't be used for compatibility with Oracle, DB2, etc.
    $array = mysql_fetch_row($result);
    return $array[0];
Dries's avatar
 
Dries committed
220
  }
221
  return FALSE;
Dries's avatar
 
Dries committed
222 223
}

Dries's avatar
 
Dries committed
224 225 226
/**
 * Determine whether the previous query caused an error.
 */
Dries's avatar
 
Dries committed
227
function db_error() {
228 229
  global $active_db;
  return mysql_errno($active_db);
Dries's avatar
 
Dries committed
230 231
}

Dries's avatar
 
Dries committed
232 233 234
/**
 * Determine the number of rows changed by the preceding query.
 */
Dries's avatar
 
Dries committed
235
function db_affected_rows() {
236 237
  global $active_db;
  return mysql_affected_rows($active_db);
Dries's avatar
 
Dries committed
238 239
}

Dries's avatar
 
Dries committed
240
/**
Dries's avatar
 
Dries committed
241 242 243 244 245 246
 * 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.
Dries's avatar
 
Dries committed
247
 *
Dries's avatar
 
Dries committed
248 249 250
 * @param $query
 *   A string containing an SQL query.
 * @param ...
251 252 253 254 255 256 257 258 259
 *   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.
 *
Dries's avatar
 
Dries committed
260 261 262 263 264 265 266
 * @param $from
 *   The first result row to return.
 * @param $count
 *   The maximum number of result rows to return.
 * @return
 *   A database query result resource, or FALSE if the query was not executed
 *   correctly.
Dries's avatar
 
Dries committed
267
 */
Dries's avatar
Dries committed
268 269 270 271
function db_query_range($query) {
  $args = func_get_args();
  $count = array_pop($args);
  $from = array_pop($args);
272
  array_shift($args);
273 274

  $query = db_prefix_tables($query);
275 276
  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
    $args = $args[0];
Dries's avatar
Dries committed
277
  }
278 279
  _db_query_callback($args, TRUE);
  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
280
  $query .= ' LIMIT '. (int)$from .', '. (int)$count;
Dries's avatar
Dries committed
281
  return _db_query($query);
Dries's avatar
 
Dries committed
282 283
}

284 285 286 287 288 289 290 291 292 293
/**
 * Runs a SELECT query and stores its results in a temporary table.
 *
 * Use this as a substitute for db_query() when the results need to stored
 * in a temporary table. Temporary tables exist for the duration of the page
 * request.
 * 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
294 295
 * a SELECT COUNT(*) on the temporary table afterwards. db_affected_rows() does
 * not give consistent result across different database types in this case.
296 297 298 299
 *
 * @param $query
 *   A string containing a normal SELECT SQL query.
 * @param ...
300 301 302 303 304 305 306 307 308
 *   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.
 *
309
 * @param $table
310 311
 *   The name of the temporary table to select into. This name will not be
 *   prefixed as there is no risk of collision.
312 313 314 315 316 317 318
 * @return
 *   A database query result resource, or FALSE if the query was not executed
 *   correctly.
 */
function db_query_temporary($query) {
  $args = func_get_args();
  $tablename = array_pop($args);
319
  array_shift($args);
320

321
  $query = preg_replace('/^SELECT/i', 'CREATE TEMPORARY TABLE '. $tablename .' Engine=HEAP SELECT', db_prefix_tables($query));
322 323
  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
    $args = $args[0];
324
  }
325 326
  _db_query_callback($args, TRUE);
  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
327 328 329
  return _db_query($query);
}

Dries's avatar
 
Dries committed
330 331 332 333 334 335 336 337 338
/**
 * Returns a properly formatted Binary Large OBject value.
 *
 * @param $data
 *   Data to encode.
 * @return
 *  Encoded data.
 */
function db_encode_blob($data) {
339
  global $active_db;
340
  return "'". mysql_real_escape_string($data, $active_db) ."'";
Dries's avatar
 
Dries committed
341 342 343
}

/**
344
 * Returns text from a Binary Large Object value.
Dries's avatar
 
Dries committed
345 346 347 348 349 350 351 352 353 354
 *
 * @param $data
 *   Data to decode.
 * @return
 *  Decoded data.
 */
function db_decode_blob($data) {
  return $data;
}

Dries's avatar
 
Dries committed
355 356 357 358
/**
 * Prepare user input for use in a database query, preventing SQL injection attacks.
 */
function db_escape_string($text) {
359 360
  global $active_db;
  return mysql_real_escape_string($text, $active_db);
Dries's avatar
 
Dries committed
361 362
}

363 364 365 366
/**
 * Lock a table.
 */
function db_lock_table($table) {
367
  db_query('LOCK TABLES {'. db_escape_table($table) .'} WRITE');
368 369 370 371 372 373 374 375 376
}

/**
 * Unlock all locked tables.
 */
function db_unlock_tables() {
  db_query('UNLOCK TABLES');
}

drumm's avatar
drumm committed
377 378 379 380
/**
 * Check if a table exists.
 */
function db_table_exists($table) {
381
  return db_fetch_object(db_query("SHOW TABLES LIKE '{". db_escape_table($table) ."}'")) ? TRUE : FALSE;
drumm's avatar
drumm committed
382 383
}

384 385 386 387
/**
 * Check if a column exists in the given table.
 */
function db_column_exists($table, $column) {
388
  return db_fetch_object(db_query("SHOW COLUMNS FROM {%s} LIKE '%s'", $table, $column)) ? TRUE : FALSE;
389 390
}

391 392 393 394 395 396 397 398 399 400 401 402 403 404
/**
 * Wraps the given table.field entry with a DISTINCT(). The wrapper is added to
 * the SELECT list entry of the given query and the resulting query is returned.
 * This function only applies the wrapper if a DISTINCT doesn't already exist in
 * the query.
 *
 * @param $table Table containing the field to set as DISTINCT
 * @param $field Field to set as DISTINCT
 * @param $query Query to apply the wrapper to
 * @return SQL query with the DISTINCT wrapper surrounding the given table.field.
 */
function db_distinct_field($table, $field, $query) {
  $field_to_select = 'DISTINCT('. $table .'.'. $field .')';
  // (?<!text) is a negative look-behind (no need to rewrite queries that already use DISTINCT).
405
  return preg_replace('/(SELECT.*)(?:'. $table .'\.|\s)(?<!DISTINCT\()(?<!DISTINCT\('. $table .'\.)'. $field .'(.*FROM )/AUsi', '\1 '. $field_to_select .'\2', $query);
406 407
}

Dries's avatar
 
Dries committed
408
/**
Dries's avatar
 
Dries committed
409
 * @} End of "ingroup database".
Dries's avatar
 
Dries committed
410
 */