Mercurial > defr > drupal > core

annotate includes/database.mysqli.inc @ 20:e3d20ebd63d1 tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added tag 6.9 for changeset 3edae6ecd6c6
author Franck Deroche <franck@defr.org>
date Thu, 15 Jan 2009 10:16:10 +0100
parents c1f4ac30525a
children
rev   line source
webmaster@1 1 <?php
webmaster@1 2 // $Id: database.mysqli.inc,v 1.54 2008/01/23 09:59:29 goba Exp $
webmaster@1 3
webmaster@1 4 /**
webmaster@1 5 * @file
webmaster@1 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.
webmaster@1 7 */
webmaster@1 8
webmaster@1 9 // Maintainers of this file should consult:
webmaster@1 10 // http://www.php.net/manual/en/ref.mysqli.php
webmaster@1 11
webmaster@1 12 /**
webmaster@1 13 * @ingroup database
webmaster@1 14 * @{
webmaster@1 15 */
webmaster@1 16
webmaster@1 17 // Include functions shared between mysql and mysqli.
webmaster@1 18 require_once './includes/database.mysql-common.inc';
webmaster@1 19
webmaster@1 20 /**
webmaster@1 21 * Report database status.
webmaster@1 22 */
webmaster@1 23 function db_status_report($phase) {
webmaster@1 24 $t = get_t();
webmaster@1 25
webmaster@1 26 $version = db_version();
webmaster@1 27
webmaster@1 28 $form['mysql'] = array(
webmaster@1 29 'title' => $t('MySQL database'),
webmaster@1 30 'value' => ($phase == 'runtime') ? l($version, 'admin/reports/status/sql') : $version,
webmaster@1 31 );
webmaster@1 32
webmaster@1 33 if (version_compare($version, DRUPAL_MINIMUM_MYSQL) < 0) {
webmaster@1 34 $form['mysql']['severity'] = REQUIREMENT_ERROR;
webmaster@1 35 $form['mysql']['description'] = $t('Your MySQL Server is too old. Drupal requires at least MySQL %version.', array('%version' => DRUPAL_MINIMUM_MYSQL));
webmaster@1 36 }
webmaster@1 37
webmaster@1 38 return $form;
webmaster@1 39 }
webmaster@1 40
webmaster@1 41 /**
webmaster@1 42 * Returns the version of the database server currently in use.
webmaster@1 43 *
webmaster@1 44 * @return Database server version
webmaster@1 45 */
webmaster@1 46 function db_version() {
webmaster@1 47 global $active_db;
webmaster@1 48 list($version) = explode('-', mysqli_get_server_info($active_db));
webmaster@1 49 return $version;
webmaster@1 50 }
webmaster@1 51
webmaster@1 52 /**
webmaster@1 53 * Initialise a database connection.
webmaster@1 54 *
webmaster@1 55 * Note that mysqli does not support persistent connections.
webmaster@1 56 */
webmaster@1 57 function db_connect($url) {
webmaster@1 58 // Check if MySQLi support is present in PHP
webmaster@1 59 if (!function_exists('mysqli_init') && !extension_loaded('mysqli')) {
webmaster@1 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.');
webmaster@1 61 }
webmaster@1 62
webmaster@1 63 $url = parse_url($url);
webmaster@1 64
webmaster@1 65 // Decode url-encoded information in the db connection string
webmaster@1 66 $url['user'] = urldecode($url['user']);
webmaster@1 67 // Test if database url has a password.
webmaster@1 68 $url['pass'] = isset($url['pass']) ? urldecode($url['pass']) : '';
webmaster@1 69 $url['host'] = urldecode($url['host']);
webmaster@1 70 $url['path'] = urldecode($url['path']);
webmaster@1 71 if (!isset($url['port'])) {
webmaster@1 72 $url['port'] = NULL;
webmaster@1 73 }
webmaster@1 74
webmaster@1 75 $connection = mysqli_init();
webmaster@1 76 @mysqli_real_connect($connection, $url['host'], $url['user'], $url['pass'], substr($url['path'], 1), $url['port'], NULL, MYSQLI_CLIENT_FOUND_ROWS);
webmaster@1 77
webmaster@1 78 if (mysqli_connect_errno() > 0) {
webmaster@1 79 _db_error_page(mysqli_connect_error());
webmaster@1 80 }
webmaster@1 81
webmaster@1 82 // Force UTF-8.
webmaster@1 83 mysqli_query($connection, 'SET NAMES "utf8"');
webmaster@1 84
webmaster@1 85 return $connection;
webmaster@1 86 }
webmaster@1 87
webmaster@1 88 /**
webmaster@1 89 * Helper function for db_query().
webmaster@1 90 */
webmaster@1 91 function _db_query($query, $debug = 0) {
webmaster@1 92 global $active_db, $queries, $user;
webmaster@1 93
webmaster@1 94 if (variable_get('dev_query', 0)) {
webmaster@1 95 list($usec, $sec) = explode(' ', microtime());
webmaster@1 96 $timer = (float)$usec + (float)$sec;
webmaster@1 97 // If devel.module query logging is enabled, prepend a comment with the username and calling function
webmaster@1 98 // to the SQL string. This is useful when running mysql's SHOW PROCESSLIST to learn what exact
webmaster@1 99 // code is issueing the slow query.
webmaster@1 100 $bt = debug_backtrace();
webmaster@1 101 // t() may not be available yet so we don't wrap 'Anonymous'
webmaster@1 102 $name = $user->uid ? $user->name : variable_get('anonymous', 'Anonymous');
webmaster@1 103 // str_replace() to prevent SQL injection via username or anonymous name.
webmaster@1 104 $name = str_replace(array('*', '/'), '', $name);
webmaster@1 105 $query = '/* '. $name .' : '. $bt[2]['function'] .' */ '. $query;
webmaster@1 106 }
webmaster@1 107
webmaster@1 108 $result = mysqli_query($active_db, $query);
webmaster@1 109
webmaster@1 110 if (variable_get('dev_query', 0)) {
webmaster@1 111 $query = $bt[2]['function'] ."\n". $query;
webmaster@1 112 list($usec, $sec) = explode(' ', microtime());
webmaster@1 113 $stop = (float)$usec + (float)$sec;
webmaster@1 114 $diff = $stop - $timer;
webmaster@1 115 $queries[] = array($query, $diff);
webmaster@1 116 }
webmaster@1 117
webmaster@1 118 if ($debug) {
webmaster@1 119 print '<p>query: '. $query .'<br />error:'. mysqli_error($active_db) .'</p>';
webmaster@1 120 }
webmaster@1 121
webmaster@1 122 if (!mysqli_errno($active_db)) {
webmaster@1 123 return $result;
webmaster@1 124 }
webmaster@1 125 else {
webmaster@1 126 // Indicate to drupal_error_handler that this is a database error.
webmaster@1 127 ${DB_ERROR} = TRUE;
webmaster@1 128 trigger_error(check_plain(mysqli_error($active_db) ."\nquery: ". $query), E_USER_WARNING);
webmaster@1 129 return FALSE;
webmaster@1 130 }
webmaster@1 131 }
webmaster@1 132
webmaster@1 133 /**
webmaster@1 134 * Fetch one result row from the previous query as an object.
webmaster@1 135 *
webmaster@1 136 * @param $result
webmaster@1 137 * A database query result resource, as returned from db_query().
webmaster@1 138 * @return
webmaster@1 139 * An object representing the next row of the result, or FALSE. The attributes
webmaster@1 140 * of this object are the table fields selected by the query.
webmaster@1 141 */
webmaster@1 142 function db_fetch_object($result) {
webmaster@1 143 if ($result) {
webmaster@1 144 $object = mysqli_fetch_object($result);
webmaster@1 145 return isset($object) ? $object : FALSE;
webmaster@1 146 }
webmaster@1 147 }
webmaster@1 148
webmaster@1 149 /**
webmaster@1 150 * Fetch one result row from the previous query as an array.
webmaster@1 151 *
webmaster@1 152 * @param $result
webmaster@1 153 * A database query result resource, as returned from db_query().
webmaster@1 154 * @return
webmaster@1 155 * An associative array representing the next row of the result, or FALSE.
webmaster@1 156 * The keys of this object are the names of the table fields selected by the
webmaster@1 157 * query, and the values are the field values for this result row.
webmaster@1 158 */
webmaster@1 159 function db_fetch_array($result) {
webmaster@1 160 if ($result) {
webmaster@1 161 $array = mysqli_fetch_array($result, MYSQLI_ASSOC);
webmaster@1 162 return isset($array) ? $array : FALSE;
webmaster@1 163 }
webmaster@1 164 }
webmaster@1 165
webmaster@1 166 /**
webmaster@1 167 * Return an individual result field from the previous query.
webmaster@1 168 *
webmaster@1 169 * Only use this function if exactly one field is being selected; otherwise,
webmaster@1 170 * use db_fetch_object() or db_fetch_array().
webmaster@1 171 *
webmaster@1 172 * @param $result
webmaster@1 173 * A database query result resource, as returned from db_query().
webmaster@1 174 * @return
webmaster@1 175 * The resulting field or FALSE.
webmaster@1 176 */
webmaster@1 177 function db_result($result) {
webmaster@1 178 if ($result && mysqli_num_rows($result) > 0) {
webmaster@1 179 // The mysqli_fetch_row function has an optional second parameter $row
webmaster@1 180 // but that can't be used for compatibility with Oracle, DB2, etc.
webmaster@1 181 $array = mysqli_fetch_row($result);
webmaster@1 182 return $array[0];
webmaster@1 183 }
webmaster@1 184 return FALSE;
webmaster@1 185 }
webmaster@1 186
webmaster@1 187 /**
webmaster@1 188 * Determine whether the previous query caused an error.
webmaster@1 189 */
webmaster@1 190 function db_error() {
webmaster@1 191 global $active_db;
webmaster@1 192 return mysqli_errno($active_db);
webmaster@1 193 }
webmaster@1 194
webmaster@1 195 /**
webmaster@1 196 * Determine the number of rows changed by the preceding query.
webmaster@1 197 */
webmaster@1 198 function db_affected_rows() {
webmaster@1 199 global $active_db; /* mysqli connection resource */
webmaster@1 200 return mysqli_affected_rows($active_db);
webmaster@1 201 }
webmaster@1 202
webmaster@1 203 /**
webmaster@1 204 * Runs a limited-range query in the active database.
webmaster@1 205 *
webmaster@1 206 * Use this as a substitute for db_query() when a subset of the query is to be
webmaster@1 207 * returned.
webmaster@1 208 * User-supplied arguments to the query should be passed in as separate parameters
webmaster@1 209 * so that they can be properly escaped to avoid SQL injection attacks.
webmaster@1 210 *
webmaster@1 211 * @param $query
webmaster@1 212 * A string containing an SQL query.
webmaster@1 213 * @param ...
webmaster@1 214 * A variable number of arguments which are substituted into the query
webmaster@1 215 * using printf() syntax. The query arguments can be enclosed in one
webmaster@1 216 * array instead.
webmaster@1 217 * Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
webmaster@1 218 * in '') and %%.
webmaster@1 219 *
webmaster@1 220 * NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
webmaster@1 221 * and TRUE values to decimal 1.
webmaster@1 222 *
webmaster@1 223 * @param $from
webmaster@1 224 * The first result row to return.
webmaster@1 225 * @param $count
webmaster@1 226 * The maximum number of result rows to return.
webmaster@1 227 * @return
webmaster@1 228 * A database query result resource, or FALSE if the query was not executed
webmaster@1 229 * correctly.
webmaster@1 230 */
webmaster@1 231 function db_query_range($query) {
webmaster@1 232 $args = func_get_args();
webmaster@1 233 $count = array_pop($args);
webmaster@1 234 $from = array_pop($args);
webmaster@1 235 array_shift($args);
webmaster@1 236
webmaster@1 237 $query = db_prefix_tables($query);
webmaster@1 238 if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
webmaster@1 239 $args = $args[0];
webmaster@1 240 }
webmaster@1 241 _db_query_callback($args, TRUE);
webmaster@1 242 $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
webmaster@1 243 $query .= ' LIMIT '. (int)$from .', '. (int)$count;
webmaster@1 244 return _db_query($query);
webmaster@1 245 }
webmaster@1 246
webmaster@1 247 /**
webmaster@1 248 * Runs a SELECT query and stores its results in a temporary table.
webmaster@1 249 *
webmaster@1 250 * Use this as a substitute for db_query() when the results need to stored
webmaster@1 251 * in a temporary table. Temporary tables exist for the duration of the page
webmaster@1 252 * request.
webmaster@1 253 * User-supplied arguments to the query should be passed in as separate parameters
webmaster@1 254 * so that they can be properly escaped to avoid SQL injection attacks.
webmaster@1 255 *
webmaster@1 256 * Note that if you need to know how many results were returned, you should do
webmaster@1 257 * a SELECT COUNT(*) on the temporary table afterwards. db_affected_rows() does
webmaster@1 258 * not give consistent result across different database types in this case.
webmaster@1 259 *
webmaster@1 260 * @param $query
webmaster@1 261 * A string containing a normal SELECT SQL query.
webmaster@1 262 * @param ...
webmaster@1 263 * A variable number of arguments which are substituted into the query
webmaster@1 264 * using printf() syntax. The query arguments can be enclosed in one
webmaster@1 265 * array instead.
webmaster@1 266 * Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
webmaster@1 267 * in '') and %%.
webmaster@1 268 *
webmaster@1 269 * NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
webmaster@1 270 * and TRUE values to decimal 1.
webmaster@1 271 *
webmaster@1 272 * @param $table
webmaster@1 273 * The name of the temporary table to select into. This name will not be
webmaster@1 274 * prefixed as there is no risk of collision.
webmaster@1 275 * @return
webmaster@1 276 * A database query result resource, or FALSE if the query was not executed
webmaster@1 277 * correctly.
webmaster@1 278 */
webmaster@1 279 function db_query_temporary($query) {
webmaster@1 280 $args = func_get_args();
webmaster@1 281 $tablename = array_pop($args);
webmaster@1 282 array_shift($args);
webmaster@1 283
webmaster@1 284 $query = preg_replace('/^SELECT/i', 'CREATE TEMPORARY TABLE '. $tablename .' Engine=HEAP SELECT', db_prefix_tables($query));
webmaster@1 285 if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
webmaster@1 286 $args = $args[0];
webmaster@1 287 }
webmaster@1 288 _db_query_callback($args, TRUE);
webmaster@1 289 $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
webmaster@1 290 return _db_query($query);
webmaster@1 291 }
webmaster@1 292
webmaster@1 293 /**
webmaster@1 294 * Returns a properly formatted Binary Large Object value.
webmaster@1 295 *
webmaster@1 296 * @param $data
webmaster@1 297 * Data to encode.
webmaster@1 298 * @return
webmaster@1 299 * Encoded data.
webmaster@1 300 */
webmaster@1 301 function db_encode_blob($data) {
webmaster@1 302 global $active_db;
webmaster@1 303 return "'". mysqli_real_escape_string($active_db, $data) ."'";
webmaster@1 304 }
webmaster@1 305
webmaster@1 306 /**
webmaster@1 307 * Returns text from a Binary Large OBject value.
webmaster@1 308 *
webmaster@1 309 * @param $data
webmaster@1 310 * Data to decode.
webmaster@1 311 * @return
webmaster@1 312 * Decoded data.
webmaster@1 313 */
webmaster@1 314 function db_decode_blob($data) {
webmaster@1 315 return $data;
webmaster@1 316 }
webmaster@1 317
webmaster@1 318 /**
webmaster@1 319 * Prepare user input for use in a database query, preventing SQL injection attacks.
webmaster@1 320 */
webmaster@1 321 function db_escape_string($text) {
webmaster@1 322 global $active_db;
webmaster@1 323 return mysqli_real_escape_string($active_db, $text);
webmaster@1 324 }
webmaster@1 325
webmaster@1 326 /**
webmaster@1 327 * Lock a table.
webmaster@1 328 */
webmaster@1 329 function db_lock_table($table) {
webmaster@1 330 db_query('LOCK TABLES {'. db_escape_table($table) .'} WRITE');
webmaster@1 331 }
webmaster@1 332
webmaster@1 333 /**
webmaster@1 334 * Unlock all locked tables.
webmaster@1 335 */
webmaster@1 336 function db_unlock_tables() {
webmaster@1 337 db_query('UNLOCK TABLES');
webmaster@1 338 }
webmaster@1 339
webmaster@1 340 /**
webmaster@1 341 * Check if a table exists.
webmaster@1 342 */
webmaster@1 343 function db_table_exists($table) {
webmaster@1 344 return (bool) db_fetch_object(db_query("SHOW TABLES LIKE '{". db_escape_table($table) ."}'"));
webmaster@1 345 }
webmaster@1 346
webmaster@1 347 /**
webmaster@1 348 * Check if a column exists in the given table.
webmaster@1 349 */
webmaster@1 350 function db_column_exists($table, $column) {
webmaster@1 351 return (bool) db_fetch_object(db_query("SHOW COLUMNS FROM {". db_escape_table($table) ."} LIKE '". db_escape_table($column) ."'"));
webmaster@1 352 }
webmaster@1 353
webmaster@1 354 /**
webmaster@1 355 * Wraps the given table.field entry with a DISTINCT(). The wrapper is added to
webmaster@1 356 * the SELECT list entry of the given query and the resulting query is returned.
webmaster@1 357 * This function only applies the wrapper if a DISTINCT doesn't already exist in
webmaster@1 358 * the query.
webmaster@1 359 *
webmaster@1 360 * @param $table Table containing the field to set as DISTINCT
webmaster@1 361 * @param $field Field to set as DISTINCT
webmaster@1 362 * @param $query Query to apply the wrapper to
webmaster@1 363 * @return SQL query with the DISTINCT wrapper surrounding the given table.field.
webmaster@1 364 */
webmaster@1 365 function db_distinct_field($table, $field, $query) {
webmaster@1 366 $field_to_select = 'DISTINCT('. $table .'.'. $field .')';
webmaster@1 367 // (?<!text) is a negative look-behind (no need to rewrite queries that already use DISTINCT).
webmaster@1 368 return preg_replace('/(SELECT.*)(?:'. $table .'\.|\s)(?<!DISTINCT\()(?<!DISTINCT\('. $table .'\.)'. $field .'(.*FROM )/AUsi', '\1 '. $field_to_select .'\2', $query);
webmaster@1 369 }
webmaster@1 370
webmaster@1 371 /**
webmaster@1 372 * @} End of "ingroup database".
webmaster@1 373 */
webmaster@1 374