[ Index ]

PHP Cross Reference of MyBB 1.8.27

title

Body

[close]

/inc/ -> db_mysqli.php (source)

   1  <?php
   2  /**
   3   * MyBB 1.8
   4   * Copyright 2014 MyBB Group, All Rights Reserved
   5   *
   6   * Website: http://www.mybb.com
   7   * License: http://www.mybb.com/about/license
   8   *
   9   */
  10  
  11  class DB_MySQLi implements DB_Base
  12  {
  13      /**
  14       * The title of this layer.
  15       *
  16       * @var string
  17       */
  18      public $title = "MySQLi";
  19  
  20      /**
  21       * The short title of this layer.
  22       *
  23       * @var string
  24       */
  25      public $short_title = "MySQLi";
  26  
  27      /**
  28       * The type of db software being used.
  29       *
  30       * @var string
  31       */
  32      public $type;
  33  
  34      /**
  35       * A count of the number of queries.
  36       *
  37       * @var int
  38       */
  39      public $query_count = 0;
  40  
  41      /**
  42       * A list of the performed queries.
  43       *
  44       * @var array
  45       */
  46      public $querylist = array();
  47  
  48      /**
  49       * 1 if error reporting enabled, 0 if disabled.
  50       *
  51       * @var boolean
  52       */
  53      public $error_reporting = 1;
  54  
  55      /**
  56       * The read database connection resource.
  57       *
  58       * @var mysqli
  59       */
  60      public $read_link;
  61  
  62      /**
  63       * The write database connection resource
  64       *
  65       * @var mysqli
  66       */
  67      public $write_link;
  68  
  69      /**
  70       * Reference to the last database connection resource used.
  71       *
  72       * @var mysqli
  73       */
  74      public $current_link;
  75  
  76      /**
  77       * The database name.
  78       *
  79       * @var string
  80       */
  81      public $database;
  82  
  83      /**
  84       * Explanation of a query.
  85       *
  86       * @var string
  87       */
  88      public $explain;
  89  
  90      /**
  91       * The current version of MySQL.
  92       *
  93       * @var string
  94       */
  95      public $version;
  96  
  97      /**
  98       * The current table type in use (myisam/innodb)
  99       *
 100       * @var string
 101       */
 102      public $table_type = "myisam";
 103  
 104      /**
 105       * The table prefix used for simple select, update, insert and delete queries
 106       *
 107       * @var string
 108       */
 109      public $table_prefix;
 110  
 111      /**
 112       * The extension used to run the SQL database
 113       *
 114       * @var string
 115       */
 116      public $engine = "mysqli";
 117  
 118      /**
 119       * Weather or not this engine can use the search functionality
 120       *
 121       * @var boolean
 122       */
 123      public $can_search = true;
 124  
 125      /**
 126       * The database encoding currently in use (if supported)
 127       *
 128       * @var string
 129       */
 130      public $db_encoding = "utf8";
 131  
 132      /**
 133       * The time spent performing queries
 134       *
 135       * @var float
 136       */
 137      public $query_time = 0;
 138  
 139      /**
 140       * Stores previous run query type: 1 => write; 0 => read
 141       *
 142       * @var int
 143       */
 144      protected $last_query_type = 0;
 145  
 146      /**
 147       * Connect to the database server.
 148       *
 149       * @param array $config Array of DBMS connection details.
 150       * @return mysqli The DB connection resource. Returns false on fail or -1 on a db connect failure.
 151       */
 152  	function connect($config)
 153      {
 154          // Simple connection to one server
 155          if(array_key_exists('hostname', $config))
 156          {
 157              $connections['read'][] = $config;
 158          }
 159          else
 160          // Connecting to more than one server
 161          {
 162              // Specified multiple servers, but no specific read/write servers
 163              if(!array_key_exists('read', $config))
 164              {
 165                  foreach($config as $key => $settings)
 166                  {
 167                      if(is_int($key))
 168                      {
 169                          $connections['read'][] = $settings;
 170                      }
 171                  }
 172              }
 173              // Specified both read & write servers
 174              else
 175              {
 176                  $connections = $config;
 177              }
 178          }
 179  
 180          $this->db_encoding = $config['encoding'];
 181  
 182          // Actually connect to the specified servers
 183          foreach(array('read', 'write') as $type)
 184          {
 185              if(!isset($connections[$type]) || !is_array($connections[$type]))
 186              {
 187                  break;
 188              }
 189  
 190              if(array_key_exists('hostname', $connections[$type]))
 191              {
 192                  $details = $connections[$type];
 193                  unset($connections[$type]);
 194                  $connections[$type][] = $details;
 195              }
 196  
 197              // Shuffle the connections
 198              shuffle($connections[$type]);
 199  
 200              // Loop-de-loop
 201              foreach($connections[$type] as $single_connection)
 202              {
 203                  $connect_function = "mysqli_connect";
 204                  $persist = "";
 205                  if(!empty($single_connection['pconnect']) && version_compare(PHP_VERSION, '5.3.0', '>='))
 206                  {
 207                      $persist = 'p:';
 208                  }
 209  
 210                  $link = "{$type}_link";
 211  
 212                  get_execution_time();
 213  
 214                  // Specified a custom port for this connection?
 215                  $port = 0;
 216                  if(strstr($single_connection['hostname'],':'))
 217                  {
 218                      list($hostname, $port) = explode(":", $single_connection['hostname'], 2);
 219                  }
 220  
 221                  if($port)
 222                  {
 223                      $this->$link = @$connect_function($persist.$hostname, $single_connection['username'], $single_connection['password'], "", $port);
 224                  }
 225                  else
 226                  {
 227                      $this->$link = @$connect_function($persist.$single_connection['hostname'], $single_connection['username'], $single_connection['password']);
 228                  }
 229  
 230                  $time_spent = get_execution_time();
 231                  $this->query_time += $time_spent;
 232  
 233                  // Successful connection? break down brother!
 234                  if($this->$link)
 235                  {
 236                      $this->connections[] = "[".strtoupper($type)."] {$single_connection['username']}@{$single_connection['hostname']} (Connected in ".format_time_duration($time_spent).")";
 237                      break;
 238                  }
 239                  else
 240                  {
 241                      $this->connections[] = "<span style=\"color: red\">[FAILED] [".strtoupper($type)."] {$single_connection['username']}@{$single_connection['hostname']}</span>";
 242                  }
 243              }
 244          }
 245  
 246          // No write server was specified (simple connection or just multiple servers) - mirror write link
 247          if(!array_key_exists('write', $connections))
 248          {
 249              $this->write_link = &$this->read_link;
 250          }
 251  
 252          // Have no read connection?
 253          if(!$this->read_link)
 254          {
 255              $this->error("[READ] Unable to connect to MySQL server");
 256              return false;
 257          }
 258          // No write?
 259          else if(!$this->write_link)
 260          {
 261              $this->error("[WRITE] Unable to connect to MySQL server");
 262              return false;
 263          }
 264  
 265          // Select databases
 266          if(!$this->select_db($config['database']))
 267          {
 268              return -1;
 269          }
 270  
 271          $this->current_link = &$this->read_link;
 272          return $this->read_link;
 273      }
 274  
 275      /**
 276       * Selects the database to use.
 277       *
 278       * @param string $database The database name.
 279       * @return boolean True when successfully connected, false if not.
 280       */
 281  	function select_db($database)
 282      {
 283          $this->database = $database;
 284  
 285          $master_success = @mysqli_select_db($this->read_link, $database) or $this->error("[READ] Unable to select database", $this->read_link);
 286          if($this->write_link)
 287          {
 288              $slave_success = @mysqli_select_db($this->write_link, $database) or $this->error("[WRITE] Unable to select slave database", $this->write_link);
 289  
 290              $success = ($master_success && $slave_success ? true : false);
 291          }
 292          else
 293          {
 294              $success = $master_success;
 295          }
 296  
 297          if($success && $this->db_encoding)
 298          {
 299              @mysqli_set_charset($this->read_link, $this->db_encoding);
 300  
 301              if($slave_success && count($this->connections) > 1)
 302              {
 303                  @mysqli_set_charset($this->write_link, $this->db_encoding);
 304              }
 305          }
 306          return $success;
 307      }
 308  
 309      /**
 310       * Query the database.
 311       *
 312       * @param string $string The query SQL.
 313       * @param boolean|int $hide_errors 1 if hide errors, 0 if not.
 314       * @param integer $write_query 1 if executes on master database, 0 if not.
 315       * @return mysqli_result The query data.
 316       */
 317  	function query($string, $hide_errors=0, $write_query=0)
 318      {
 319          global $mybb;
 320  
 321          get_execution_time();
 322  
 323          // Only execute write queries on master server
 324          if(($write_query || $this->last_query_type) && $this->write_link)
 325          {
 326              $this->current_link = &$this->write_link;
 327              $query = @mysqli_query($this->write_link, $string);
 328          }
 329          else
 330          {
 331              $this->current_link = &$this->read_link;
 332              $query = @mysqli_query($this->read_link, $string);
 333          }
 334  
 335          if($this->error_number() && !$hide_errors)
 336          {
 337              $this->error($string);
 338              exit;
 339          }
 340  
 341          if($write_query)
 342          {
 343              $this->last_query_type = 1;
 344          }
 345          else
 346          {
 347              $this->last_query_type = 0;
 348          }
 349  
 350          $query_time = get_execution_time();
 351          $this->query_time += $query_time;
 352          $this->query_count++;
 353  
 354          if($mybb->debug_mode)
 355          {
 356              $this->explain_query($string, $query_time);
 357          }
 358          return $query;
 359      }
 360  
 361      /**
 362       * Execute a write query on the master database
 363       *
 364       * @param string $query The query SQL.
 365       * @param boolean|int $hide_errors 1 if hide errors, 0 if not.
 366       * @return mysqli_result The query data.
 367       */
 368  	function write_query($query, $hide_errors=0)
 369      {
 370          return $this->query($query, $hide_errors, 1);
 371      }
 372  
 373      /**
 374       * Explain a query on the database.
 375       *
 376       * @param string $string The query SQL.
 377       * @param string $qtime The time it took to perform the query.
 378       */
 379  	function explain_query($string, $qtime)
 380      {
 381          global $plugins;
 382  
 383          $debug_extra = '';
 384          if($plugins->current_hook)
 385          {
 386              $debug_extra = "<div style=\"float_right\">(Plugin Hook: {$plugins->current_hook})</div>";
 387          }
 388          if(preg_match("#^\s*select#i", $string))
 389          {
 390              $query = mysqli_query($this->current_link, "EXPLAIN $string");
 391              $this->explain .= "<table style=\"background-color: #666;\" width=\"95%\" cellpadding=\"4\" cellspacing=\"1\" align=\"center\">\n".
 392                  "<tr>\n".
 393                  "<td colspan=\"8\" style=\"background-color: #ccc;\">{$debug_extra}<div><strong>#".$this->query_count." - Select Query</strong></div></td>\n".
 394                  "</tr>\n".
 395                  "<tr>\n".
 396                  "<td colspan=\"8\" style=\"background-color: #fefefe;\"><span style=\"font-family: Courier; font-size: 14px;\">".htmlspecialchars_uni($string)."</span></td>\n".
 397                  "</tr>\n".
 398                  "<tr style=\"background-color: #efefef;\">\n".
 399                  "<td><strong>Table</strong></td>\n".
 400                  "<td><strong>Type</strong></td>\n".
 401                  "<td><strong>Possible Keys</strong></td>\n".
 402                  "<td><strong>Key</strong></td>\n".
 403                  "<td><strong>Key Length</strong></td>\n".
 404                  "<td><strong>Ref</strong></td>\n".
 405                  "<td><strong>Rows</strong></td>\n".
 406                  "<td><strong>Extra</strong></td>\n".
 407                  "</tr>\n";
 408  
 409              while($table = mysqli_fetch_assoc($query))
 410              {
 411                  $this->explain .=
 412                      "<tr bgcolor=\"#ffffff\">\n".
 413                      "<td>".$table['table']."</td>\n".
 414                      "<td>".$table['type']."</td>\n".
 415                      "<td>".$table['possible_keys']."</td>\n".
 416                      "<td>".$table['key']."</td>\n".
 417                      "<td>".$table['key_len']."</td>\n".
 418                      "<td>".$table['ref']."</td>\n".
 419                      "<td>".$table['rows']."</td>\n".
 420                      "<td>".$table['Extra']."</td>\n".
 421                      "</tr>\n";
 422              }
 423              $this->explain .=
 424                  "<tr>\n".
 425                  "<td colspan=\"8\" style=\"background-color: #fff;\">Query Time: ".format_time_duration($qtime)."</td>\n".
 426                  "</tr>\n".
 427                  "</table>\n".
 428                  "<br />\n";
 429          }
 430          else
 431          {
 432              $this->explain .= "<table style=\"background-color: #666;\" width=\"95%\" cellpadding=\"4\" cellspacing=\"1\" align=\"center\">\n".
 433                  "<tr>\n".
 434                  "<td style=\"background-color: #ccc;\">{$debug_extra}<div><strong>#".$this->query_count." - Write Query</strong></div></td>\n".
 435                  "</tr>\n".
 436                  "<tr style=\"background-color: #fefefe;\">\n".
 437                  "<td><span style=\"font-family: Courier; font-size: 14px;\">".htmlspecialchars_uni($string)."</span></td>\n".
 438                  "</tr>\n".
 439                  "<tr>\n".
 440                  "<td bgcolor=\"#ffffff\">Query Time: ".format_time_duration($qtime)."</td>\n".
 441                  "</tr>\n".
 442                  "</table>\n".
 443                  "<br />\n";
 444          }
 445  
 446          $this->querylist[$this->query_count]['query'] = $string;
 447          $this->querylist[$this->query_count]['time'] = $qtime;
 448      }
 449  
 450      /**
 451       * Return a result array for a query.
 452       *
 453       * @param mysqli_result $query The query data.
 454       * @param int $resulttype The type of array to return. Either MYSQLI_NUM, MYSQLI_BOTH or MYSQLI_ASSOC
 455       * @return array The array of results.
 456       */
 457  	function fetch_array($query, $resulttype=MYSQLI_ASSOC)
 458      {
 459          switch($resulttype)
 460          {
 461              case MYSQLI_NUM:
 462              case MYSQLI_BOTH:
 463                  break;
 464              default:
 465                  $resulttype = MYSQLI_ASSOC;
 466                  break;
 467          }
 468  
 469          $array = mysqli_fetch_array($query, $resulttype);
 470  
 471          return $array;
 472      }
 473  
 474      /**
 475       * Return a specific field from a query.
 476       *
 477       * @param mysqli_result $query The query ID.
 478       * @param string $field The name of the field to return.
 479       * @param int|bool $row The number of the row to fetch it from.
 480       */
 481  	function fetch_field($query, $field, $row=false)
 482      {
 483          if($row !== false)
 484          {
 485              $this->data_seek($query, $row);
 486          }
 487          $array = $this->fetch_array($query);
 488          if($array !== null)
 489          {
 490              return $array[$field];
 491          }
 492          return null;
 493      }
 494  
 495      /**
 496       * Moves internal row pointer to the next row
 497       *
 498       * @param mysqli_result $query The query ID.
 499       * @param int $row The pointer to move the row to.
 500       * @return bool
 501       */
 502  	function data_seek($query, $row)
 503      {
 504          return mysqli_data_seek($query, $row);
 505      }
 506  
 507      /**
 508       * Return the number of rows resulting from a query.
 509       *
 510       * @param mysqli_result $query The query data.
 511       * @return int The number of rows in the result.
 512       */
 513  	function num_rows($query)
 514      {
 515          return mysqli_num_rows($query);
 516      }
 517  
 518      /**
 519       * Return the last id number of inserted data.
 520       *
 521       * @return int The id number.
 522       */
 523  	function insert_id()
 524      {
 525          $id = mysqli_insert_id($this->current_link);
 526          return $id;
 527      }
 528  
 529      /**
 530       * Close the connection with the DBMS.
 531       *
 532       */
 533  	function close()
 534      {
 535          @mysqli_close($this->read_link);
 536          if($this->write_link)
 537          {
 538              @mysqli_close($this->write_link);
 539          }
 540      }
 541  
 542      /**
 543       * Return an error number.
 544       *
 545       * @return int The error number of the current error.
 546       */
 547  	function error_number()
 548      {
 549          if($this->current_link)
 550          {
 551              return mysqli_errno($this->current_link);
 552          }
 553          else
 554          {
 555              return mysqli_connect_errno();
 556          }
 557      }
 558  
 559      /**
 560       * Return an error string.
 561       *
 562       * @return string The explanation for the current error.
 563       */
 564  	function error_string()
 565      {
 566          if($this->current_link)
 567          {
 568              return mysqli_error($this->current_link);
 569          }
 570          else
 571          {
 572              return mysqli_connect_error();
 573          }
 574      }
 575  
 576      /**
 577       * Output a database error.
 578       *
 579       * @param string $string The string to present as an error.
 580       * @return bool Whether error reporting is enabled or not
 581       */
 582  	function error($string="")
 583      {
 584          if($this->error_reporting)
 585          {
 586              if(class_exists("errorHandler"))
 587              {
 588                  global $error_handler;
 589  
 590                  if(!is_object($error_handler))
 591                  {
 592                      require_once  MYBB_ROOT."inc/class_error.php";
 593                      $error_handler = new errorHandler();
 594                  }
 595  
 596                  $error = array(
 597                      "error_no" => $this->error_number(),
 598                      "error" => $this->error_string(),
 599                      "query" => $string
 600                  );
 601                  $error_handler->error(MYBB_SQL, $error);
 602              }
 603              else
 604              {
 605                  trigger_error("<strong>[SQL] [".$this->error_number()."] ".$this->error_string()."</strong><br />{$string}", E_USER_ERROR);
 606              }
 607  
 608              return true;
 609          }
 610          else
 611          {
 612              return false;
 613          }
 614      }
 615  
 616      /**
 617       * Returns the number of affected rows in a query.
 618       *
 619       * @return int The number of affected rows.
 620       */
 621  	function affected_rows()
 622      {
 623          return mysqli_affected_rows($this->current_link);
 624      }
 625  
 626      /**
 627       * Return the number of fields.
 628       *
 629       * @param mysqli_result $query The query data.
 630       * @return int The number of fields.
 631       */
 632  	function num_fields($query)
 633      {
 634          return mysqli_num_fields($query);
 635      }
 636  
 637      /**
 638       * Lists all tables in the database.
 639       *
 640       * @param string $database The database name.
 641       * @param string $prefix Prefix of the table (optional)
 642       * @return array The table list.
 643       */
 644  	function list_tables($database, $prefix='')
 645      {
 646          if($prefix)
 647          {
 648              if(version_compare($this->get_version(), '5.0.2', '>='))
 649              {
 650                  $query = $this->query("SHOW FULL TABLES FROM `$database` WHERE table_type = 'BASE TABLE' AND `Tables_in_$database` LIKE '".$this->escape_string($prefix)."%'");
 651              }
 652              else
 653              {
 654                  $query = $this->query("SHOW TABLES FROM `$database` LIKE '".$this->escape_string($prefix)."%'");
 655              }
 656          }
 657          else
 658          {
 659              if(version_compare($this->get_version(), '5.0.2', '>='))
 660              {
 661                  $query = $this->query("SHOW FULL TABLES FROM `$database` WHERE table_type = 'BASE TABLE'");
 662              }
 663              else
 664              {
 665                  $query = $this->query("SHOW TABLES FROM `$database`");
 666              }
 667          }
 668  
 669          $tables = array();
 670          while(list($table) = mysqli_fetch_array($query))
 671          {
 672              $tables[] = $table;
 673          }
 674  
 675          return $tables;
 676      }
 677  
 678      /**
 679       * Check if a table exists in a database.
 680       *
 681       * @param string $table The table name.
 682       * @return boolean True when exists, false if not.
 683       */
 684  	function table_exists($table)
 685      {
 686          // Execute on master server to ensure if we've just created a table that we get the correct result
 687          if(version_compare($this->get_version(), '5.0.2', '>='))
 688          {
 689              $query = $this->query("SHOW FULL TABLES FROM `".$this->database."` WHERE table_type = 'BASE TABLE' AND `Tables_in_".$this->database."` = '{$this->table_prefix}$table'");
 690          }
 691          else
 692          {
 693              $query = $this->query("SHOW TABLES LIKE '{$this->table_prefix}$table'");
 694          }
 695  
 696          $exists = $this->num_rows($query);
 697          if($exists > 0)
 698          {
 699              return true;
 700          }
 701          else
 702          {
 703              return false;
 704          }
 705      }
 706  
 707      /**
 708       * Check if a field exists in a database.
 709       *
 710       * @param string $field The field name.
 711       * @param string $table The table name.
 712       * @return boolean True when exists, false if not.
 713       */
 714  	function field_exists($field, $table)
 715      {
 716          $query = $this->write_query("
 717              SHOW COLUMNS
 718              FROM {$this->table_prefix}$table
 719              LIKE '$field'
 720          ");
 721          $exists = $this->num_rows($query);
 722  
 723          if($exists > 0)
 724          {
 725              return true;
 726          }
 727          else
 728          {
 729              return false;
 730          }
 731      }
 732  
 733      /**
 734       * Add a shutdown query.
 735       *
 736       * @param mysqli_result $query The query data.
 737       * @param string $name An optional name for the query.
 738       */
 739  	function shutdown_query($query, $name="")
 740      {
 741          global $shutdown_queries;
 742          if($name)
 743          {
 744              $shutdown_queries[$name] = $query;
 745          }
 746          else
 747          {
 748              $shutdown_queries[] = $query;
 749          }
 750      }
 751  
 752      /**
 753       * Performs a simple select query.
 754       *
 755       * @param string $table The table name to be queried.
 756       * @param string $fields Comma delimetered list of fields to be selected.
 757       * @param string $conditions SQL formatted list of conditions to be matched.
 758       * @param array $options List of options: group by, order by, order direction, limit, limit start.
 759       * @return mysqli_result The query data.
 760       */
 761  	function simple_select($table, $fields="*", $conditions="", $options=array())
 762      {
 763          $query = "SELECT ".$fields." FROM ".$this->table_prefix.$table;
 764  
 765          if($conditions != "")
 766          {
 767              $query .= " WHERE ".$conditions;
 768          }
 769  
 770          if(isset($options['group_by']))
 771          {
 772              $query .= " GROUP BY ".$options['group_by'];
 773          }
 774  
 775          if(isset($options['order_by']))
 776          {
 777              $query .= " ORDER BY ".$options['order_by'];
 778              if(isset($options['order_dir']))
 779              {
 780                  $query .= " ".my_strtoupper($options['order_dir']);
 781              }
 782          }
 783  
 784          if(isset($options['limit_start']) && isset($options['limit']))
 785          {
 786              $query .= " LIMIT ".$options['limit_start'].", ".$options['limit'];
 787          }
 788          else if(isset($options['limit']))
 789          {
 790              $query .= " LIMIT ".$options['limit'];
 791          }
 792  
 793          return $this->query($query);
 794      }
 795  
 796      /**
 797       * Build an insert query from an array.
 798       *
 799       * @param string $table The table name to perform the query on.
 800       * @param array $array An array of fields and their values.
 801       * @return int The insert ID if available
 802       */
 803  	function insert_query($table, $array)
 804      {
 805          global $mybb;
 806  
 807          if(!is_array($array))
 808          {
 809              return false;
 810          }
 811  
 812          foreach($array as $field => $value)
 813          {
 814              if(isset($mybb->binary_fields[$table][$field]) && $mybb->binary_fields[$table][$field])
 815              {
 816                  if($value[0] != 'X') // Not escaped?
 817                  {
 818                      $value = $this->escape_binary($value);
 819                  }
 820                  
 821                  $array[$field] = $value;
 822              }
 823              else
 824              {
 825                  $array[$field] = $this->quote_val($value);
 826              }
 827          }
 828  
 829          $fields = "`".implode("`,`", array_keys($array))."`";
 830          $values = implode(",", $array);
 831          $this->write_query("
 832              INSERT
 833              INTO {$this->table_prefix}{$table} (".$fields.")
 834              VALUES (".$values.")
 835          ");
 836          return $this->insert_id();
 837      }
 838  
 839      /**
 840       * Build one query for multiple inserts from a multidimensional array.
 841       *
 842       * @param string $table The table name to perform the query on.
 843       * @param array $array An array of inserts.
 844       * @return void
 845       */
 846  	function insert_query_multiple($table, $array)
 847      {
 848          global $mybb;
 849  
 850          if(!is_array($array))
 851          {
 852              return;
 853          }
 854          // Field names
 855          $fields = array_keys($array[0]);
 856          $fields = "`".implode("`,`", $fields)."`";
 857  
 858          $insert_rows = array();
 859          foreach($array as $values)
 860          {
 861              foreach($values as $field => $value)
 862              {
 863                  if(isset($mybb->binary_fields[$table][$field]) && $mybb->binary_fields[$table][$field])
 864                  {
 865                      if($value[0] != 'X') // Not escaped?
 866                      {
 867                          $value = $this->escape_binary($value);
 868                      }
 869                  
 870                      $values[$field] = $value;
 871                  }
 872                  else
 873                  {
 874                      $values[$field] = $this->quote_val($value);
 875                  }
 876              }
 877              $insert_rows[] = "(".implode(",", $values).")";
 878          }
 879          $insert_rows = implode(", ", $insert_rows);
 880  
 881          $this->write_query("
 882              INSERT
 883              INTO {$this->table_prefix}{$table} ({$fields})
 884              VALUES {$insert_rows}
 885          ");
 886      }
 887  
 888      /**
 889       * Build an update query from an array.
 890       *
 891       * @param string $table The table name to perform the query on.
 892       * @param array $array An array of fields and their values.
 893       * @param string $where An optional where clause for the query.
 894       * @param string $limit An optional limit clause for the query.
 895       * @param boolean $no_quote An option to quote incoming values of the array.
 896       * @return mysqli_result The query data.
 897       */
 898  	function update_query($table, $array, $where="", $limit="", $no_quote=false)
 899      {
 900          global $mybb;
 901  
 902          if(!is_array($array))
 903          {
 904              return false;
 905          }
 906  
 907          $comma = "";
 908          $query = "";
 909          $quote = "'";
 910  
 911          if($no_quote == true)
 912          {
 913              $quote = "";
 914          }
 915  
 916          foreach($array as $field => $value)
 917          {
 918              if(isset($mybb->binary_fields[$table][$field]) && $mybb->binary_fields[$table][$field])
 919              {
 920                  if($value[0] != 'X') // Not escaped?
 921                  {
 922                      $value = $this->escape_binary($value);
 923                  }
 924                  
 925                  $query .= $comma."`".$field."`={$value}";
 926              }
 927              else
 928              {
 929                  $quoted_value = $this->quote_val($value, $quote);
 930  
 931                  $query .= $comma."`".$field."`={$quoted_value}";
 932              }
 933              $comma = ', ';
 934          }
 935  
 936          if(!empty($where))
 937          {
 938              $query .= " WHERE $where";
 939          }
 940  
 941          if(!empty($limit))
 942          {
 943              $query .= " LIMIT $limit";
 944          }
 945  
 946          return $this->write_query("
 947              UPDATE {$this->table_prefix}$table
 948              SET $query
 949          ");
 950      }
 951  
 952      /**
 953       * @param int|string $value
 954       * @param string $quote
 955       *
 956       * @return int|string
 957       */
 958  	private function quote_val($value, $quote="'")
 959      {
 960          if(is_int($value))
 961          {
 962              $quoted = $value;
 963          }
 964          else
 965          {
 966              $quoted = $quote . $value . $quote;
 967          }
 968  
 969          return $quoted;
 970      }
 971  
 972      /**
 973       * Build a delete query.
 974       *
 975       * @param string $table The table name to perform the query on.
 976       * @param string $where An optional where clause for the query.
 977       * @param string $limit An optional limit clause for the query.
 978       * @return mysqli_result The query data.
 979       */
 980  	function delete_query($table, $where="", $limit="")
 981      {
 982          $query = "";
 983          if(!empty($where))
 984          {
 985              $query .= " WHERE $where";
 986          }
 987          if(!empty($limit))
 988          {
 989              $query .= " LIMIT $limit";
 990          }
 991          return $this->write_query("DELETE FROM {$this->table_prefix}$table $query");
 992      }
 993  
 994      /**
 995       * Escape a string according to the MySQL escape format.
 996       *
 997       * @param string $string The string to be escaped.
 998       * @return string The escaped string.
 999       */
1000  	function escape_string($string)
1001      {
1002          if($this->db_encoding == 'utf8')
1003          {
1004              $string = validate_utf8_string($string, false);
1005          }
1006          elseif($this->db_encoding == 'utf8mb4')
1007          {
1008              $string = validate_utf8_string($string);
1009          }
1010  
1011          if(function_exists("mysqli_real_escape_string") && $this->read_link)
1012          {
1013              $string = mysqli_real_escape_string($this->read_link, $string);
1014          }
1015          else
1016          {
1017              $string = addslashes($string);
1018          }
1019          return $string;
1020      }
1021  
1022      /**
1023       * Frees the resources of a MySQLi query.
1024       *
1025       * @param mysqli_result $query The query to destroy.
1026       * @return boolean Returns true
1027       */
1028  	function free_result($query)
1029      {
1030          mysqli_free_result($query);
1031          return true; // Kept for compatibility reasons
1032      }
1033  
1034      /**
1035       * Escape a string used within a like command.
1036       *
1037       * @param string $string The string to be escaped.
1038       * @return string The escaped string.
1039       */
1040  	function escape_string_like($string)
1041      {
1042          return $this->escape_string(str_replace(array('\\', '%', '_') , array('\\\\', '\\%' , '\\_') , $string));
1043      }
1044  
1045      /**
1046       * Gets the current version of MySQL.
1047       *
1048       * @return string Version of MySQL.
1049       */
1050  	function get_version()
1051      {
1052          if($this->version)
1053          {
1054              return $this->version;
1055          }
1056  
1057          $query = $this->query("SELECT VERSION() as version");
1058          $ver = $this->fetch_array($query);
1059          $version = $ver['version'];
1060  
1061          if($version)
1062          {
1063              $version = explode(".", $version, 3);
1064              $this->version = (int)$version[0].".".(int)$version[1].".".(int)$version[2];
1065          }
1066          return $this->version;
1067      }
1068  
1069      /**
1070       * Optimizes a specific table.
1071       *
1072       * @param string $table The name of the table to be optimized.
1073       */
1074  	function optimize_table($table)
1075      {
1076          $this->write_query("OPTIMIZE TABLE ".$this->table_prefix.$table."");
1077      }
1078  
1079      /**
1080       * Analyzes a specific table.
1081       *
1082       * @param string $table The name of the table to be analyzed.
1083       */
1084  	function analyze_table($table)
1085      {
1086          $this->write_query("ANALYZE TABLE ".$this->table_prefix.$table."");
1087      }
1088  
1089      /**
1090       * Show the "create table" command for a specific table.
1091       *
1092       * @param string $table The name of the table.
1093       * @return string The MySQL command to create the specified table.
1094       */
1095  	function show_create_table($table)
1096      {
1097          $query = $this->write_query("SHOW CREATE TABLE ".$this->table_prefix.$table."");
1098          $structure = $this->fetch_array($query);
1099  
1100          return $structure['Create Table'];
1101      }
1102  
1103      /**
1104       * Show the "show fields from" command for a specific table.
1105       *
1106       * @param string $table The name of the table.
1107       * @return array Field info for that table
1108       */
1109  	function show_fields_from($table)
1110      {
1111          $query = $this->write_query("SHOW FIELDS FROM ".$this->table_prefix.$table."");
1112          $field_info = array();
1113          while($field = $this->fetch_array($query))
1114          {
1115              $field_info[] = $field;
1116          }
1117          return $field_info;
1118      }
1119  
1120      /**
1121       * Returns whether or not the table contains a fulltext index.
1122       *
1123       * @param string $table The name of the table.
1124       * @param string $index Optionally specify the name of the index.
1125       * @return boolean True or false if the table has a fulltext index or not.
1126       */
1127  	function is_fulltext($table, $index="")
1128      {
1129          $structure = $this->show_create_table($table);
1130          if($index != "")
1131          {
1132              if(preg_match("#FULLTEXT KEY (`?)$index(`?)#i", $structure))
1133              {
1134                  return true;
1135              }
1136              else
1137              {
1138                  return false;
1139              }
1140          }
1141          if(preg_match('#FULLTEXT KEY#i', $structure))
1142          {
1143              return true;
1144          }
1145          return false;
1146      }
1147  
1148      /**
1149       * Returns whether or not this database engine supports fulltext indexing.
1150       *
1151       * @param string $table The table to be checked.
1152       * @return boolean True or false if supported or not.
1153       */
1154  
1155  	function supports_fulltext($table)
1156      {
1157          $version = $this->get_version();
1158          $query = $this->write_query("SHOW TABLE STATUS LIKE '{$this->table_prefix}$table'");
1159          $status = $this->fetch_array($query);
1160          $table_type = my_strtoupper($status['Engine']);
1161          if(version_compare($version, '3.23.23', '>=') && ($table_type == 'MYISAM' || $table_type == 'ARIA'))
1162          {
1163              return true;
1164          }
1165          elseif(version_compare($version, '5.6', '>=') && $table_type == 'INNODB')
1166          {
1167              return true;
1168          }
1169          return false;
1170      }
1171  
1172      /**
1173       * Returns whether or not this database engine supports boolean fulltext matching.
1174       *
1175       * @param string $table The table to be checked.
1176       * @return boolean True or false if supported or not.
1177       */
1178  	function supports_fulltext_boolean($table)
1179      {
1180          $version = $this->get_version();
1181          $supports_fulltext = $this->supports_fulltext($table);
1182          if(version_compare($version, '4.0.1', '>=') && $supports_fulltext == true)
1183          {
1184              return true;
1185          }
1186          return false;
1187      }
1188  
1189      /**
1190       * Checks to see if an index exists on a specified table
1191       *
1192       * @param string $table The name of the table.
1193       * @param string $index The name of the index.
1194       * @return bool Returns whether index exists
1195       */
1196  	function index_exists($table, $index)
1197      {
1198          $index_exists = false;
1199          $query = $this->write_query("SHOW INDEX FROM {$this->table_prefix}{$table}");
1200          while($ukey = $this->fetch_array($query))
1201          {
1202              if($ukey['Key_name'] == $index)
1203              {
1204                  $index_exists = true;
1205                  break;
1206              }
1207          }
1208  
1209          if($index_exists)
1210          {
1211              return true;
1212          }
1213  
1214          return false;
1215      }
1216  
1217      /**
1218       * Creates a fulltext index on the specified column in the specified table with optional index name.
1219       *
1220       * @param string $table The name of the table.
1221       * @param string $column Name of the column to be indexed.
1222       * @param string $name The index name, optional.
1223       */
1224  	function create_fulltext_index($table, $column, $name="")
1225      {
1226          $this->write_query("ALTER TABLE {$this->table_prefix}$table ADD FULLTEXT $name ($column)");
1227      }
1228  
1229      /**
1230       * Drop an index with the specified name from the specified table
1231       *
1232       * @param string $table The name of the table.
1233       * @param string $name The name of the index.
1234       */
1235  	function drop_index($table, $name)
1236      {
1237          $this->write_query("ALTER TABLE {$this->table_prefix}$table DROP INDEX $name");
1238      }
1239  
1240      /**
1241       * Drop an table with the specified table
1242       *
1243       * @param string $table The table to drop
1244       * @param boolean $hard hard drop - no checking
1245       * @param boolean $table_prefix use table prefix
1246       */
1247  	function drop_table($table, $hard=false, $table_prefix=true)
1248      {
1249          if($table_prefix == false)
1250          {
1251              $table_prefix = "";
1252          }
1253          else
1254          {
1255              $table_prefix = $this->table_prefix;
1256          }
1257  
1258          if($hard == false)
1259          {
1260              $this->write_query('DROP TABLE IF EXISTS '.$table_prefix.$table);
1261          }
1262          else
1263          {
1264              $this->write_query('DROP TABLE '.$table_prefix.$table);
1265          }
1266      }
1267  
1268      /**
1269       * Renames a table
1270       *
1271       * @param string $old_table The old table name
1272       * @param string $new_table the new table name
1273       * @param boolean $table_prefix use table prefix
1274       * @return mysqli_result
1275       */
1276  	function rename_table($old_table, $new_table, $table_prefix=true)
1277      {
1278          if($table_prefix == false)
1279          {
1280              $table_prefix = "";
1281          }
1282          else
1283          {
1284              $table_prefix = $this->table_prefix;
1285          }
1286  
1287          return $this->write_query("RENAME TABLE {$table_prefix}{$old_table} TO {$table_prefix}{$new_table}");
1288      }
1289  
1290      /**
1291       * Replace contents of table with values
1292       *
1293       * @param string $table The table
1294       * @param array $replacements The replacements
1295       * @param string|array $default_field The default field(s)
1296       * @param boolean $insert_id Whether or not to return an insert id. True by default
1297       * @return mysqli_result|bool
1298       */
1299  	function replace_query($table, $replacements=array(), $default_field="", $insert_id=true)
1300      {
1301          global $mybb;
1302  
1303          $values = '';
1304          $comma = '';
1305          foreach($replacements as $column => $value)
1306          {
1307              if(isset($mybb->binary_fields[$table][$column]) && $mybb->binary_fields[$table][$column])
1308              {
1309                  if($value[0] != 'X') // Not escaped?
1310                  {
1311                      $value = $this->escape_binary($value);
1312                  }
1313                  
1314                  $values .= $comma."`".$column."`=".$value;
1315              }
1316              else
1317              {
1318                  $values .= $comma."`".$column."`=".$this->quote_val($value);
1319              }
1320  
1321              $comma = ',';
1322          }
1323  
1324          if(empty($replacements))
1325          {
1326               return false;
1327          }
1328  
1329          return $this->write_query("REPLACE INTO {$this->table_prefix}{$table} SET {$values}");
1330      }
1331  
1332      /**
1333       * Drops a column
1334       *
1335       * @param string $table The table
1336       * @param string $column The column name
1337       * @return mysqli_result
1338       */
1339  	function drop_column($table, $column)
1340      {
1341          $column = trim($column, '`');
1342  
1343          return $this->write_query("ALTER TABLE {$this->table_prefix}{$table} DROP `{$column}`");
1344      }
1345  
1346      /**
1347       * Adds a column
1348       *
1349       * @param string $table The table
1350       * @param string $column The column name
1351       * @param string $definition the new column definition
1352       * @return mysqli_result
1353       */
1354  	function add_column($table, $column, $definition)
1355      {
1356          $column = trim($column, '`');
1357  
1358          return $this->write_query("ALTER TABLE {$this->table_prefix}{$table} ADD `{$column}` {$definition}");
1359      }
1360  
1361      /**
1362       * Modifies a column
1363       *
1364       * @param string $table The table
1365       * @param string $column The column name
1366       * @param string $new_definition the new column definition
1367       * @param boolean|string $new_not_null Whether to "drop" or "set" the NOT NULL attribute (no change if false)
1368       * @param boolean|string $new_default_value The new default value, or false to drop the attribute
1369       * @return bool Returns true if all queries are executed successfully or false if one of them failed
1370       */
1371  	function modify_column($table, $column, $new_definition, $new_not_null=false, $new_default_value=false)
1372      {
1373          $column = trim($column, '`');
1374  
1375          if($new_not_null !== false)
1376          {
1377              if(strtolower($new_not_null) == "set")
1378              {
1379                  $not_null = "NOT NULL";
1380              }
1381              else
1382              {
1383                  $not_null = "NULL";
1384              }
1385          }
1386          else
1387          {
1388              $not_null = '';
1389          }
1390  
1391          if($new_default_value !== false)
1392          {
1393              $default = "DEFAULT ".$new_default_value;
1394          }
1395          else
1396          {
1397              $default = '';
1398          }
1399  
1400          return (bool)$this->write_query("ALTER TABLE {$this->table_prefix}{$table} MODIFY `{$column}` {$new_definition} {$not_null} {$default}");
1401      }
1402  
1403      /**
1404       * Renames a column
1405       *
1406       * @param string $table The table
1407       * @param string $old_column The old column name
1408       * @param string $new_column the new column name
1409       * @param string $new_definition the new column definition
1410       * @param boolean|string $new_not_null Whether to "drop" or "set" the NOT NULL attribute (no change if false)
1411       * @param boolean|string $new_default_value The new default value, or false to drop the attribute
1412       * @return bool Returns true if all queries are executed successfully
1413       */
1414  	function rename_column($table, $old_column, $new_column, $new_definition, $new_not_null=false, $new_default_value=false)
1415      {
1416          $old_column = trim($old_column, '`');
1417          $new_column = trim($new_column, '`');
1418  
1419          if($new_not_null !== false)
1420          {
1421              if(strtolower($new_not_null) == "set")
1422              {
1423                  $not_null = "NOT NULL";
1424              }
1425              else
1426              {
1427                  $not_null = "NULL";
1428              }
1429          }
1430          else
1431          {
1432              $not_null = '';
1433          }
1434  
1435          if($new_default_value !== false)
1436          {
1437              $default = "DEFAULT ".$new_default_value;
1438          }
1439          else
1440          {
1441              $default = '';
1442          }
1443  
1444          return (bool)$this->write_query("ALTER TABLE {$this->table_prefix}{$table} CHANGE `{$old_column}` `{$new_column}` {$new_definition} {$not_null} {$default}");
1445      }
1446  
1447      /**
1448       * Sets the table prefix used by the simple select, insert, update and delete functions
1449       *
1450       * @param string $prefix The new table prefix
1451       */
1452  	function set_table_prefix($prefix)
1453      {
1454          $this->table_prefix = $prefix;
1455      }
1456  
1457      /**
1458       * Fetched the total size of all mysql tables or a specific table
1459       *
1460       * @param string $table The table (optional)
1461       * @return integer the total size of all mysql tables or a specific table
1462       */
1463  	function fetch_size($table='')
1464      {
1465          if($table != '')
1466          {
1467              $query = $this->query("SHOW TABLE STATUS LIKE '".$this->table_prefix.$table."'");
1468          }
1469          else
1470          {
1471              $query = $this->query("SHOW TABLE STATUS");
1472          }
1473          $total = 0;
1474          while($table = $this->fetch_array($query))
1475          {
1476              $total += $table['Data_length']+$table['Index_length'];
1477          }
1478          return $total;
1479      }
1480  
1481      /**
1482       * Fetch a list of database character sets this DBMS supports
1483       *
1484       * @return array|bool Array of supported character sets with array key being the name, array value being display name. False if unsupported
1485       */
1486  	function fetch_db_charsets()
1487      {
1488          if($this->write_link && version_compare($this->get_version(), "4.1", "<"))
1489          {
1490              return false;
1491          }
1492          return array(
1493              'big5' => 'Big5 Traditional Chinese',
1494              'dec8' => 'DEC West European',
1495              'cp850' => 'DOS West European',
1496              'hp8' => 'HP West European',
1497              'koi8r' => 'KOI8-R Relcom Russian',
1498              'latin1' => 'ISO 8859-1 Latin 1',
1499              'latin2' => 'ISO 8859-2 Central European',
1500              'swe7' => '7bit Swedish',
1501              'ascii' => 'US ASCII',
1502              'ujis' => 'EUC-JP Japanese',
1503              'sjis' => 'Shift-JIS Japanese',
1504              'hebrew' => 'ISO 8859-8 Hebrew',
1505              'tis620' => 'TIS620 Thai',
1506              'euckr' => 'EUC-KR Korean',
1507              'koi8u' => 'KOI8-U Ukrainian',
1508              'gb2312' => 'GB2312 Simplified Chinese',
1509              'greek' => 'ISO 8859-7 Greek',
1510              'cp1250' => 'Windows Central European',
1511              'gbk' => 'GBK Simplified Chinese',
1512              'latin5' => 'ISO 8859-9 Turkish',
1513              'armscii8' => 'ARMSCII-8 Armenian',
1514              'utf8' => 'UTF-8 Unicode',
1515              'utf8mb4' => '4-Byte UTF-8 Unicode (requires MySQL 5.5.3 or above)',
1516              'ucs2' => 'UCS-2 Unicode',
1517              'cp866' => 'DOS Russian',
1518              'keybcs2' => 'DOS Kamenicky Czech-Slovak',
1519              'macce' => 'Mac Central European',
1520              'macroman' => 'Mac West European',
1521              'cp852' => 'DOS Central European',
1522              'latin7' => 'ISO 8859-13 Baltic',
1523              'cp1251' => 'Windows Cyrillic',
1524              'cp1256' => 'Windows Arabic',
1525              'cp1257' => 'Windows Baltic',
1526              'geostd8' => 'GEOSTD8 Georgian',
1527              'cp932' => 'SJIS for Windows Japanese',
1528              'eucjpms' => 'UJIS for Windows Japanese',
1529          );
1530      }
1531  
1532      /**
1533       * Fetch a database collation for a particular database character set
1534       *
1535       * @param string $charset The database character set
1536       * @return string|bool The matching database collation, false if unsupported
1537       */
1538  	function fetch_charset_collation($charset)
1539      {
1540          $collations = array(
1541              'big5' => 'big5_chinese_ci',
1542              'dec8' => 'dec8_swedish_ci',
1543              'cp850' => 'cp850_general_ci',
1544              'hp8' => 'hp8_english_ci',
1545              'koi8r' => 'koi8r_general_ci',
1546              'latin1' => 'latin1_swedish_ci',
1547              'latin2' => 'latin2_general_ci',
1548              'swe7' => 'swe7_swedish_ci',
1549              'ascii' => 'ascii_general_ci',
1550              'ujis' => 'ujis_japanese_ci',
1551              'sjis' => 'sjis_japanese_ci',
1552              'hebrew' => 'hebrew_general_ci',
1553              'tis620' => 'tis620_thai_ci',
1554              'euckr' => 'euckr_korean_ci',
1555              'koi8u' => 'koi8u_general_ci',
1556              'gb2312' => 'gb2312_chinese_ci',
1557              'greek' => 'greek_general_ci',
1558              'cp1250' => 'cp1250_general_ci',
1559              'gbk' => 'gbk_chinese_ci',
1560              'latin5' => 'latin5_turkish_ci',
1561              'armscii8' => 'armscii8_general_ci',
1562              'utf8' => 'utf8_general_ci',
1563              'utf8mb4' => 'utf8mb4_general_ci',
1564              'ucs2' => 'ucs2_general_ci',
1565              'cp866' => 'cp866_general_ci',
1566              'keybcs2' => 'keybcs2_general_ci',
1567              'macce' => 'macce_general_ci',
1568              'macroman' => 'macroman_general_ci',
1569              'cp852' => 'cp852_general_ci',
1570              'latin7' => 'latin7_general_ci',
1571              'cp1251' => 'cp1251_general_ci',
1572              'cp1256' => 'cp1256_general_ci',
1573              'cp1257' => 'cp1257_general_ci',
1574              'geostd8' => 'geostd8_general_ci',
1575              'cp932' => 'cp932_japanese_ci',
1576              'eucjpms' => 'eucjpms_japanese_ci',
1577          );
1578          if($collations[$charset])
1579          {
1580              return $collations[$charset];
1581          }
1582          return false;
1583      }
1584  
1585      /**
1586       * Fetch a character set/collation string for use with CREATE TABLE statements. Uses current DB encoding
1587       *
1588       * @return string The built string, empty if unsupported
1589       */
1590  	function build_create_table_collation()
1591      {
1592          if(!$this->db_encoding)
1593          {
1594              return '';
1595          }
1596  
1597          $collation = $this->fetch_charset_collation($this->db_encoding);
1598          if(!$collation)
1599          {
1600              return '';
1601          }
1602          return " CHARACTER SET {$this->db_encoding} COLLATE {$collation}";
1603      }
1604  
1605      /**
1606       * Time how long it takes for a particular piece of code to run. Place calls above & below the block of code.
1607       *
1608       * @deprecated
1609       */
1610  	function get_execution_time()
1611      {
1612          return get_execution_time();
1613      }
1614  
1615      /**
1616       * Binary database fields require special attention.
1617       *
1618       * @param string $string Binary value
1619       * @return string Encoded binary value
1620       */
1621  	function escape_binary($string)
1622      {
1623          return "X'".$this->escape_string(bin2hex($string))."'";
1624      }
1625  
1626      /**
1627       * Unescape binary data.
1628       *
1629       * @param string $string Binary value
1630       * @return string Encoded binary value
1631       */
1632  	function unescape_binary($string)
1633      {
1634          // Nothing to do
1635          return $string;
1636      }
1637  }
1638  


2005 - 2021 © MyBB.de | Alle Rechte vorbehalten! | Sponsor: netcup Cross-referenced by PHPXref