phpBB/includes/db/mysqli.php

   1 <?php
   2 /**
   3 *
   4 * @package dbal
   5 * @version $Id$
   6 * @copyright (c) 2005 phpBB Group
   7 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
   8 *
   9 */
  10
  11 /**
  12 * @ignore
  13 */
  14 if (!defined('IN_PHPBB'))
  15 {
  16         exit;
  17 }
  18
  19 /**
  20 * MySQLi Database Abstraction Layer
  21 * Compatible with:
  22 * MySQL 4.1+
  23 * MySQL 5.0+
  24 * @package dbal
  25 */
  26 class phpbb_dbal_mysqli extends phpbb_dbal
  27 {
  28         /**
  29         * @var string Database type. No distinction between versions or used extensions.
  30         */
  31         public $dbms_type = 'mysql';
  32
  33         /**
  34         * Connect to server. See {@link phpbb_dbal::sql_connect() sql_connect()} for details.
  35         */
  36         public function sql_connect($server, $user, $password, $database, $port = false, $persistency = false , $new_link = false)
  37         {
  38                 $this->persistency = $persistency;
  39                 $this->user = $user;
  40                 $this->server = $server;
  41                 $this->dbname = $database;
  42                 $this->port = (!$port) ? NULL : $port;
  43
  44                 // Persistant connections not supported by the mysqli extension?
  45                 $this->db_connect_id = @mysqli_connect($this->server, $this->user, $password, $this->dbname, $this->port);
  46
  47                 if ($this->db_connect_id && $this->dbname != '')
  48                 {
  49                         @mysqli_query($this->db_connect_id, "SET NAMES 'utf8'");
  50
  51                         // enforce strict mode on databases that support it
  52                         if (version_compare($this->sql_server_info(true), '5.0.2', '>='))
  53                         {
  54                                 $result = @mysqli_query($this->db_connect_id, 'SELECT @@session.sql_mode AS sql_mode');
  55                                 $row = @mysqli_fetch_assoc($result);
  56                                 @mysqli_free_result($result);
  57
  58                                 $modes = array_map('trim', explode(',', $row['sql_mode']));
  59
  60                                 // TRADITIONAL includes STRICT_ALL_TABLES and STRICT_TRANS_TABLES
  61                                 if (!in_array('TRADITIONAL', $modes))
  62                                 {
  63                                         if (!in_array('STRICT_ALL_TABLES', $modes))
  64                                         {
  65                                                 $modes[] = 'STRICT_ALL_TABLES';
  66                                         }
  67
  68                                         if (!in_array('STRICT_TRANS_TABLES', $modes))
  69                                         {
  70                                                 $modes[] = 'STRICT_TRANS_TABLES';
  71                                         }
  72                                 }
  73
  74                                 $mode = implode(',', $modes);
  75                                 @mysqli_query($this->db_connect_id, "SET SESSION sql_mode='{$mode}'");
  76                         }
  77                         return $this->db_connect_id;
  78                 }
  79
  80                 return $this->sql_error('');
  81         }
  82
  83         /**
  84         * Version information about used database. See {@link phpbb_dbal::sql_server_info() sql_server_info()} for details.
  85         */
  86         public function sql_server_info($raw = false)
  87         {
  88                 if (!phpbb::registered('acm') || ($this->sql_server_version = phpbb::$acm->get('#mysqli_version')) === false)
  89                 {
  90                         $result = @mysqli_query($this->db_connect_id, 'SELECT VERSION() AS version');
  91                         $row = @mysqli_fetch_assoc($result);
  92                         @mysqli_free_result($result);
  93
  94                         $this->sql_server_version = trim($row['version']);
  95
  96                         if (phpbb::registered('acm'))
  97                         {
  98                                 phpbb::$acm->put('#mysqli_version', $this->sql_server_version);
  99                         }
 100                 }
 101
 102                 return ($raw) ? $this->sql_server_version : 'MySQL(i) ' . $this->sql_server_version;
 103         }
 104
 105         /**
 106         * DB-specific base query method. See {@link phpbb_dbal::_sql_query() _sql_query()} for details.
 107         */
 108         protected function _sql_query($query)
 109         {
 110                 return @mysqli_query($this->db_connect_id, $query);
 111         }
 112
 113         /**
 114         * Build LIMIT query and run it. See {@link phpbb_dbal::_sql_query_limit() _sql_query_limit()} for details.
 115         */
 116         protected function _sql_query_limit($query, $total, $offset, $cache_ttl)
 117         {
 118                 // if $total is set to 0 we do not want to limit the number of rows
 119                 if ($total == 0)
 120                 {
 121                         // MySQL 4.1+ no longer supports -1 in limit queries
 122                         $total = '18446744073709551615';
 123                 }
 124
 125                 $query .= "\n LIMIT " . ((!empty($offset)) ? $offset . ', ' . $total : $total);
 126                 return $this->sql_query($query, $cache_ttl);
 127         }
 128
 129         /**
 130         * Close sql connection. See {@link phpbb_dbal::_sql_close() _sql_close()} for details.
 131         */
 132         protected function _sql_close()
 133         {
 134                 return @mysqli_close($this->db_connect_id);
 135         }
 136
 137         /**
 138         * SQL Transaction. See {@link phpbb_dbal::_sql_transaction() _sql_transaction()} for details.
 139         */
 140         protected function _sql_transaction($status)
 141         {
 142                 switch ($status)
 143                 {
 144                         case 'begin':
 145                                 return @mysqli_autocommit($this->db_connect_id, false);
 146                         break;
 147
 148                         case 'commit':
 149                                 $result = @mysqli_commit($this->db_connect_id);
 150                                 @mysqli_autocommit($this->db_connect_id, true);
 151                                 return $result;
 152                         break;
 153
 154                         case 'rollback':
 155                                 $result = @mysqli_rollback($this->db_connect_id);
 156                                 @mysqli_autocommit($this->db_connect_id, true);
 157                                 return $result;
 158                         break;
 159                 }
 160
 161                 return true;
 162         }
 163
 164         /**
 165         * Return number of affected rows. See {@link phpbb_dbal::sql_affectedrows() sql_affectedrows()} for details.
 166         */
 167         public function sql_affectedrows()
 168         {
 169                 return ($this->db_connect_id) ? @mysqli_affected_rows($this->db_connect_id) : false;
 170         }
 171
 172         /**
 173         * Get last inserted id after insert statement. See {@link phpbb_dbal::sql_nextid() sql_nextid()} for details.
 174         */
 175         public function sql_nextid()
 176         {
 177                 return ($this->db_connect_id) ? @mysqli_insert_id($this->db_connect_id) : false;
 178         }
 179
 180         /**
 181         * Fetch current row. See {@link phpbb_dbal::_sql_fetchrow() _sql_fetchrow()} for details.
 182         */
 183         protected function _sql_fetchrow($query_id)
 184         {
 185                 return @mysqli_fetch_assoc($query_id);
 186         }
 187
 188         /**
 189         * Free query result. See {@link phpbb_dbal::_sql_freeresult() _sql_freeresult()} for details.
 190         */
 191         protected function _sql_freeresult($query_id)
 192         {
 193                 return @mysqli_free_result($query_id);
 194         }
 195
 196         /**
 197         * Correctly adjust LIKE expression for special characters. See {@link phpbb_dbal::_sql_like_expression() _sql_like_expression()} for details.
 198         */
 199         protected function _sql_like_expression($expression)
 200         {
 201                 return $expression;
 202         }
 203
 204         /**
 205         * Escape string used in sql query. See {@link phpbb_dbal::sql_escape() sql_escape()} for details.
 206         */
 207         public function sql_escape($msg)
 208         {
 209                 return @mysqli_real_escape_string($this->db_connect_id, $msg);
 210         }
 211
 212         /**
 213         * Expose a DBMS specific function. See {@link phpbb_dbal::sql_function() sql_function()} for details.
 214         */
 215         public function sql_function($type, $col)
 216         {
 217                 switch ($type)
 218                 {
 219                         case 'length_varchar':
 220                         case 'length_text':
 221                                 return 'LENGTH(' . $col . ')';
 222                         break;
 223                 }
 224         }
 225
 226         /**
 227         * Handle data by using prepared statements. See {@link phpbb_dbal::sql_handle_data() sql_handle_data()} for details.
 228         * @todo implement correctly by using types. ;)
 229         */
 230         public function sql_handle_data($type, $table, $data, $where = '')
 231         {
 232                 if ($type === 'INSERT')
 233                 {
 234                         $stmt = mysqli_prepare($this->db_connect_id, "INSERT INTO $table (". implode(', ', array_keys($data)) . ") VALUES (" . substr(str_repeat('?, ', sizeof($data)) ,0, -1) . ')');
 235                 }
 236                 else
 237                 {
 238                         $query = "UPDATE $table SET ";
 239
 240                         $set = array();
 241                         foreach (array_keys($data) as $key)
 242                         {
 243                                 $set[] = "$key = ?";
 244                         }
 245                         $query .= implode(', ', $set);
 246
 247                         if ($where !== '')
 248                         {
 249                                 $query .= ' WHERE ' . $where;
 250                         }
 251
 252                         $stmt = mysqli_prepare($this->db_connect_id, $query);
 253                 }
 254
 255                 // get the stmt onto the top of the function arguments
 256                 array_unshift($data, $stmt);
 257
 258                 call_user_func_array('mysqli_stmt_bind_param', $data);
 259                 mysqli_stmt_execute($stmt);
 260
 261                 mysqli_stmt_close($stmt);
 262         }
 263
 264         /**
 265         * Build DB-specific query bits. See {@link phpbb_dbal::_sql_custom_build() _sql_custom_build()} for details.
 266         */
 267         protected function _sql_custom_build($stage, $data)
 268         {
 269                 switch ($stage)
 270                 {
 271                         case 'FROM':
 272                                 $data = '(' . $data . ')';
 273                         break;
 274                 }
 275
 276                 return $data;
 277         }
 278
 279         /**
 280         * return sql error array. See {@link phpbb_dbal::_sql_error() _sql_error()} for details.
 281         */
 282         protected function _sql_error()
 283         {
 284                 if (!$this->db_connect_id)
 285                 {
 286                         return array(
 287                                 'message'       => @mysqli_connect_error(),
 288                                 'code'          => @mysqli_connect_errno()
 289                         );
 290                 }
 291
 292                 return array(
 293                         'message'       => @mysqli_error($this->db_connect_id),
 294                         'code'          => @mysqli_errno($this->db_connect_id)
 295                 );
 296         }
 297
 298
 299         /**
 300         * Run DB-specific code to build SQL Report to explain queries, show statistics and runtime information. See {@link phpbb_dbal::_sql_report() _sql_report()} for details.
 301         */
 302         protected function _sql_report($mode, $query = '')
 303         {
 304                 static $test_prof;
 305
 306                 // current detection method, might just switch to see the existance of INFORMATION_SCHEMA.PROFILING
 307                 if ($test_prof === null)
 308                 {
 309                         $test_prof = false;
 310                         if (strpos(mysqli_get_server_info($this->db_connect_id), 'community') !== false)
 311                         {
 312                                 $ver = mysqli_get_server_version($this->db_connect_id);
 313                                 if ($ver >= 50037 && $ver < 50100)
 314                                 {
 315                                         $test_prof = true;
 316                                 }
 317                         }
 318                 }
 319
 320                 switch ($mode)
 321                 {
 322                         case 'start':
 323
 324                                 $explain_query = $query;
 325                                 if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 326                                 {
 327                                         $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 328                                 }
 329                                 else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 330                                 {
 331                                         $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 332                                 }
 333
 334                                 if (preg_match('/^SELECT/', $explain_query))
 335                                 {
 336                                         $html_table = false;
 337
 338                                         // begin profiling
 339                                         if ($test_prof)
 340                                         {
 341                                                 @mysqli_query($this->db_connect_id, 'SET profiling = 1;');
 342                                         }
 343
 344                                         if ($result = @mysqli_query($this->db_connect_id, "EXPLAIN $explain_query"))
 345                                         {
 346                                                 while ($row = @mysqli_fetch_assoc($result))
 347                                                 {
 348                                                         $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 349                                                 }
 350                                         }
 351                                         @mysqli_free_result($result);
 352
 353                                         if ($html_table)
 354                                         {
 355                                                 $this->html_hold .= '</table>';
 356                                         }
 357
 358                                         if ($test_prof)
 359                                         {
 360                                                 $html_table = false;
 361
 362                                                 // get the last profile
 363                                                 if ($result = @mysqli_query($this->db_connect_id, 'SHOW PROFILE ALL;'))
 364                                                 {
 365                                                         $this->html_hold .= '<br />';
 366                                                         while ($row = @mysqli_fetch_assoc($result))
 367                                                         {
 368                                                                 // make <unknown> HTML safe
 369                                                                 if (!empty($row['Source_function']))
 370                                                                 {
 371                                                                         $row['Source_function'] = str_replace(array('<', '>'), array('&lt;', '&gt;'), $row['Source_function']);
 372                                                                 }
 373
 374                                                                 // remove unsupported features
 375                                                                 foreach ($row as $key => $val)
 376                                                                 {
 377                                                                         if ($val === null)
 378                                                                         {
 379                                                                                 unset($row[$key]);
 380                                                                         }
 381                                                                 }
 382                                                                 $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 383                                                         }
 384                                                 }
 385                                                 @mysqli_free_result($result);
 386
 387                                                 if ($html_table)
 388                                                 {
 389                                                         $this->html_hold .= '</table>';
 390                                                 }
 391
 392                                                 @mysqli_query($this->db_connect_id, 'SET profiling = 0;');
 393                                         }
 394                                 }
 395
 396                         break;
 397
 398                         case 'fromcache':
 399                                 $endtime = explode(' ', microtime());
 400                                 $endtime = $endtime[0] + $endtime[1];
 401
 402                                 $result = @mysqli_query($this->db_connect_id, $query);
 403                                 while ($void = @mysqli_fetch_assoc($result))
 404                                 {
 405                                         // Take the time spent on parsing rows into account
 406                                 }
 407                                 @mysqli_free_result($result);
 408
 409                                 $splittime = explode(' ', microtime());
 410                                 $splittime = $splittime[0] + $splittime[1];
 411
 412                                 $this->sql_report('record_fromcache', $query, $endtime, $splittime);
 413
 414                         break;
 415                 }
 416         }
 417 }
 418
 419 ?>