[ Index ]

PHP Cross Reference of MyBB 1.8.38

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


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