[ Index ]

PHP Cross Reference of MyBB 1.8.32

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


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