database.mysqli.inc 13.5 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?php
// $Id$

/**
 * @file
 * 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.
 */

/* Maintainers of this file should consult
 * http://www.php.net/manual/en/ref.mysqli.php
 */

/**
 * @ingroup database
 * @{
 */

18
19
20
// Include functions shared between mysql and mysqli.
require_once './includes/database.mysql-common.inc';

21
22
23
/**
 * Report database status.
 */
24
function db_status_report($phase) {
25
26
  $t = get_t();

27
28
  $version = db_version();

29
30
  $form['mysql'] = array(
    'title' => $t('MySQL database'),
31
    'value' => ($phase == 'runtime') ? l($version, 'admin/logs/status/sql') : $version,
32
33
34
35
36
37
  );

  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));
  }
38

39
40
41
  return $form;
}

42
43
44
45
46
47
48
49
50
51
52
/**
 * Returns the version of the database server currently in use.
 *
 * @return Database server version
 */
function db_version() {
  global $active_db;
  list($version) = explode('-', mysqli_get_server_info($active_db));
  return $version;
}

53
54
55
56
57
58
/**
 * Initialise a database connection.
 *
 * Note that mysqli does not support persistent connections.
 */
function db_connect($url) {
59
  // Check if MySQLi support is present in PHP
60
  if (!function_exists('mysqli_init') && !extension_loaded('mysqli')) {
61
    drupal_maintenance_theme();
62
63
    drupal_set_title('PHP MySQLi support not enabled');
    print theme('maintenance_page', '<p>We were 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.</p>
64
65
66
67
<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;
  }

68
69
  $url = parse_url($url);

70
71
  // Decode url-encoded information in the db connection string
  $url['user'] = urldecode($url['user']);
72
  // Test if database url has a password.
73
  if (isset($url['pass'])) {
74
75
76
77
78
    $url['pass'] = urldecode($url['pass']);
  }
  else {
    $url['pass'] = '';
  }
79
80
81
  $url['host'] = urldecode($url['host']);
  $url['path'] = urldecode($url['path']);

82
  $connection = mysqli_init();
83
  @mysqli_real_connect($connection, $url['host'], $url['user'], $url['pass'], substr($url['path'], 1), $url['port'], NULL, MYSQLI_CLIENT_FOUND_ROWS);
84

85
86
  // Find all database connection errors and error 1045 for access denied for user account
  if (mysqli_connect_errno() >= 2000 || mysqli_connect_errno() == 1045) {
87
    drupal_maintenance_theme();
88
    drupal_set_header('HTTP/1.1 503 Service Unavailable');
89
    drupal_set_title('Unable to connect to database server');
90
91
    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>
<p>If you have already finished installed 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>
92
93
94
95
96
97
98
99
100
101
102
<p>The MySQL error was: '. theme('placeholder', mysqli_error($connection)) .'.</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>
  <li>Are you sure that the mysqli libraries are compiled in your PHP installation? Try using the mysql library instead by editing your <code>settings.php</code> configuration file in Drupal.</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;
  }
103
  else if (mysqli_connect_errno() > 0) {
104
105
106
107
108
109
110
111
112
113
114
115
116
117
    drupal_maintenance_theme();
    drupal_set_title('Unable to select database');
    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>
<p>The MySQL error was: '. theme('placeholder', mysqli_error($connection)) .'.</p>
<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;
  }

118
119
  /* Force UTF-8 */
  mysqli_query($connection, 'SET NAMES "utf8"');
120

121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
  return $connection;
}

/**
 * Helper function for db_query().
 */
function _db_query($query, $debug = 0) {
  global $active_db, $queries;

  if (variable_get('dev_query', 0)) {
    list($usec, $sec) = explode(' ', microtime());
    $timer = (float)$usec + (float)$sec;
  }

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

  if (variable_get('dev_query', 0)) {
138
    $bt = debug_backtrace();
139
    $query = $bt[2]['function'] ."\n". $query;
140
141
142
143
144
145
146
147
148
149
150
151
152
153
    list($usec, $sec) = explode(' ', microtime());
    $stop = (float)$usec + (float)$sec;
    $diff = $stop - $timer;
    $queries[] = array($query, $diff);
  }

  if ($debug) {
    print '<p>query: '. $query .'<br />error:'. mysqli_error($active_db) .'</p>';
  }

  if (!mysqli_errno($active_db)) {
    return $result;
  }
  else {
154
155
    // Indicate to drupal_error_handler that this is a database error.
    ${DB_ERROR} = TRUE;
156
    trigger_error(check_plain(mysqli_error($active_db) ."\nquery: ". $query), E_USER_WARNING);
157
158
159
160
161
162
163
164
165
166
    return FALSE;
  }
}

/**
 * Fetch one result row from the previous query as an object.
 *
 * @param $result
 *   A database query result resource, as returned from db_query().
 * @return
167
168
 *   An object representing the next row of the result, or FALSE. The attributes
 *   of this object are the table fields selected by the query.
169
170
171
 */
function db_fetch_object($result) {
  if ($result) {
172
173
    $object = mysqli_fetch_object($result);
    return isset($object) ? $object : FALSE;
174
175
176
177
178
179
180
181
182
  }
}

/**
 * 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
183
184
 *   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
185
 *   query, and the values are the field values for this result row.
186
187
188
 */
function db_fetch_array($result) {
  if ($result) {
189
190
    $array = mysqli_fetch_array($result, MYSQLI_ASSOC);
    return isset($array) ? $array : FALSE;
191
192
193
194
195
196
197
198
199
200
201
202
  }
}

/**
* 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
203
*   The resulting field or FALSE.
204
*/
205
206
207
208
209
function db_result($result) {
  if ($result && mysqli_num_rows($result) > 0) {
    // The mysqli_fetch_row function has an optional second parameter $row
    // but that can't be used for compatibility with Oracle, DB2, etc.
    $array = mysqli_fetch_row($result);
210
211
    return $array[0];
  }
212
  return FALSE;
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
}

/**
 * Determine whether the previous query caused an error.
 */
function db_error() {
  global $active_db;
  return mysqli_errno($active_db);
}

/**
 * Determine the number of rows changed by the preceding query.
 */
function db_affected_rows() {
  global $active_db; /* mysqli connection resource */
  return mysqli_affected_rows($active_db);
}

/**
 * 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.
 *
 * @param $query
 *   A string containing an SQL query.
 * @param ...
242
243
244
245
246
247
248
249
250
 *   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.
 *
251
252
253
254
255
256
257
258
259
260
261
262
 * @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.
 */
function db_query_range($query) {
  $args = func_get_args();
  $count = array_pop($args);
  $from = array_pop($args);
263
  array_shift($args);
264
265

  $query = db_prefix_tables($query);
266
267
  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
    $args = $args[0];
268
  }
269
270
  _db_query_callback($args, TRUE);
  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
271
  $query .= ' LIMIT '. (int)$from .', '. (int)$count;
272
273
274
  return _db_query($query);
}

275
276
277
278
279
280
281
282
283
284
/**
 * 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
285
286
 * a SELECT COUNT(*) on the temporary table afterwards. db_affected_rows() does
 * not give consistent result across different database types in this case.
287
288
289
290
 *
 * @param $query
 *   A string containing a normal SELECT SQL query.
 * @param ...
291
292
293
294
295
296
297
298
299
 *   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.
 *
300
301
302
303
304
305
306
307
308
309
 * @param $table
 *   The name of the temporary table to select into. This name will not be
 *   prefixed as there is no risk of collision.
 * @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);
310
  array_shift($args);
311

312
  $query = preg_replace('/^SELECT/i', 'CREATE TEMPORARY TABLE '. $tablename .' Engine=HEAP SELECT', db_prefix_tables($query));
313
314
  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
    $args = $args[0];
315
  }
316
317
  _db_query_callback($args, TRUE);
  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
318
319
320
  return _db_query($query);
}

321
/**
322
 * Returns a properly formatted Binary Large Object value.
323
324
325
326
327
328
329
 *
 * @param $data
 *   Data to encode.
 * @return
 *  Encoded data.
 */
function db_encode_blob($data) {
330
  global $active_db;
331
  return "'". mysqli_real_escape_string($active_db, $data) ."'";
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
}

/**
 * Returns text from a Binary Large OBject value.
 *
 * @param $data
 *   Data to decode.
 * @return
 *  Decoded data.
 */
function db_decode_blob($data) {
  return $data;
}

/**
 * Prepare user input for use in a database query, preventing SQL injection attacks.
 */
function db_escape_string($text) {
350
351
  global $active_db;
  return mysqli_real_escape_string($active_db, $text);
352
353
354
355
356
357
}

/**
 * Lock a table.
 */
function db_lock_table($table) {
358
  db_query('LOCK TABLES {'. db_escape_table($table) .'} WRITE');
359
360
361
362
363
364
365
366
367
}

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

drumm's avatar
drumm committed
368
369
370
371
/**
 * Check if a table exists.
 */
function db_table_exists($table) {
372
  return db_fetch_object(db_query("SHOW TABLES LIKE '{". db_escape_table($table) ."}'")) ? TRUE : FALSE;
drumm's avatar
drumm committed
373
374
}

375
376
377
378
/**
 * Check if a column exists in the given table.
 */
function db_column_exists($table, $column) {
379
  return db_fetch_object(db_query("SHOW COLUMNS FROM {%s} LIKE '%s'", $table, $column)) ? TRUE : FALSE;
380
381
}

382
383
384
385
386
387
388
389
390
391
392
393
394
395
/**
 * 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).
396
  return preg_replace('/(SELECT.*)(?:'. $table .'\.|\s)(?<!DISTINCT\()(?<!DISTINCT\('. $table .'\.)'. $field .'(.*FROM )/AUsi', '\1 '. $field_to_select .'\2', $query);
397
398
}

399
400
401
402
/**
 * @} End of "ingroup database".
 */