[ Index ]

PHP Cross Reference of MyBB 1.8.27

title

Body

[close]

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


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