1<?php 2/** 3 * @package Joomla.Platform 4 * @subpackage Database 5 * 6 * @copyright Copyright (C) 2005 - 2020 Open Source Matters, Inc. All rights reserved. 7 * @license GNU General Public License version 2 or later; see LICENSE 8 */ 9 10defined('JPATH_PLATFORM') or die; 11 12/** 13 * MySQL database driver supporting PDO based connections 14 * 15 * @link https://www.php.net/manual/en/ref.pdo-mysql.php 16 * @since 3.4 17 */ 18class JDatabaseDriverPdomysql extends JDatabaseDriverPdo 19{ 20 /** 21 * The name of the database driver. 22 * 23 * @var string 24 * @since 3.4 25 */ 26 public $name = 'pdomysql'; 27 28 /** 29 * The type of the database server family supported by this driver. 30 * 31 * @var string 32 * @since CMS 3.5.0 33 */ 34 public $serverType = 'mysql'; 35 36 /** 37 * The character(s) used to quote SQL statement names such as table names or field names, 38 * etc. The child classes should define this as necessary. If a single character string the 39 * same character is used for both sides of the quoted name, else the first character will be 40 * used for the opening quote and the second for the closing quote. 41 * 42 * @var string 43 * @since 3.4 44 */ 45 protected $nameQuote = '`'; 46 47 /** 48 * The null or zero representation of a timestamp for the database driver. This should be 49 * defined in child classes to hold the appropriate value for the engine. 50 * 51 * @var string 52 * @since 3.4 53 */ 54 protected $nullDate = '0000-00-00 00:00:00'; 55 56 /** 57 * The minimum supported database version. 58 * 59 * @var string 60 * @since 3.4 61 */ 62 protected static $dbMinimum = '5.0.4'; 63 64 /** 65 * Constructor. 66 * 67 * @param array $options Array of database options with keys: host, user, password, database, select. 68 * 69 * @since 3.4 70 */ 71 public function __construct($options) 72 { 73 // Get some basic values from the options. 74 $options['driver'] = 'mysql'; 75 76 if (!isset($options['charset']) || $options['charset'] == 'utf8') 77 { 78 $options['charset'] = 'utf8mb4'; 79 } 80 81 /** 82 * Pre-populate the UTF-8 Multibyte compatibility flag. Unfortunately PDO won't report the server version 83 * unless we're connected to it, and we cannot connect to it unless we know if it supports utf8mb4, which requires 84 * us knowing the server version. Because of this chicken and egg issue, we _assume_ it's supported and we'll just 85 * catch any problems at connection time. 86 */ 87 $this->utf8mb4 = ($options['charset'] == 'utf8mb4'); 88 89 // Finalize initialisation. 90 parent::__construct($options); 91 } 92 93 /** 94 * Connects to the database if needed. 95 * 96 * @return void Returns void if the database connected successfully. 97 * 98 * @since 3.4 99 * @throws RuntimeException 100 */ 101 public function connect() 102 { 103 if ($this->connection) 104 { 105 return; 106 } 107 108 try 109 { 110 // Try to connect to MySQL 111 parent::connect(); 112 } 113 catch (\RuntimeException $e) 114 { 115 // If the connection failed, but not because of the wrong character set, then bubble up the exception. 116 if (!$this->utf8mb4) 117 { 118 throw $e; 119 } 120 121 /* 122 * Otherwise, try connecting again without using 123 * utf8mb4 and see if maybe that was the problem. If the 124 * connection succeeds, then we will have learned that the 125 * client end of the connection does not support utf8mb4. 126 */ 127 $this->utf8mb4 = false; 128 $this->options['charset'] = 'utf8'; 129 130 parent::connect(); 131 } 132 133 if ($this->utf8mb4) 134 { 135 /* 136 * At this point we know the client supports utf8mb4. Now 137 * we must check if the server supports utf8mb4 as well. 138 */ 139 $serverVersion = $this->connection->getAttribute(PDO::ATTR_SERVER_VERSION); 140 $this->utf8mb4 = version_compare($serverVersion, '5.5.3', '>='); 141 142 if (!$this->utf8mb4) 143 { 144 // Reconnect with the utf8 character set. 145 parent::disconnect(); 146 $this->options['charset'] = 'utf8'; 147 parent::connect(); 148 } 149 } 150 151 $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 152 $this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); 153 154 // Set sql_mode to non_strict mode 155 $this->connection->query("SET @@SESSION.sql_mode = '';"); 156 157 // Disable query cache and turn profiling ON in debug mode. 158 if ($this->debug) 159 { 160 $this->connection->query('SET query_cache_type = 0;'); 161 162 if ($this->hasProfiling()) 163 { 164 $this->connection->query('SET profiling_history_size = 100, profiling = 1;'); 165 } 166 } 167 } 168 169 /** 170 * Test to see if the MySQL connector is available. 171 * 172 * @return boolean True on success, false otherwise. 173 * 174 * @since 3.4 175 */ 176 public static function isSupported() 177 { 178 return class_exists('PDO') && in_array('mysql', PDO::getAvailableDrivers()); 179 } 180 181 /** 182 * Drops a table from the database. 183 * 184 * @param string $tableName The name of the database table to drop. 185 * @param boolean $ifExists Optionally specify that the table must exist before it is dropped. 186 * 187 * @return JDatabaseDriverPdomysql Returns this object to support chaining. 188 * 189 * @since 3.4 190 * @throws RuntimeException 191 */ 192 public function dropTable($tableName, $ifExists = true) 193 { 194 $this->connect(); 195 196 $query = $this->getQuery(true); 197 198 $query->setQuery('DROP TABLE ' . ($ifExists ? 'IF EXISTS ' : '') . $this->quoteName($tableName)); 199 200 $this->setQuery($query); 201 202 $this->execute(); 203 204 return $this; 205 } 206 207 /** 208 * Select a database for use. 209 * 210 * @param string $database The name of the database to select for use. 211 * 212 * @return boolean True if the database was successfully selected. 213 * 214 * @since 3.4 215 * @throws RuntimeException 216 */ 217 public function select($database) 218 { 219 $this->connect(); 220 221 $this->setQuery('USE ' . $this->quoteName($database)); 222 223 $this->execute(); 224 225 return $this; 226 } 227 228 /** 229 * Method to get the database collation in use by sampling a text field of a table in the database. 230 * 231 * @return mixed The collation in use by the database (string) or boolean false if not supported. 232 * 233 * @since 3.4 234 * @throws RuntimeException 235 */ 236 public function getCollation() 237 { 238 $this->connect(); 239 240 // Attempt to get the database collation by accessing the server system variable. 241 $this->setQuery('SHOW VARIABLES LIKE "collation_database"'); 242 $result = $this->loadObject(); 243 244 if (property_exists($result, 'Value')) 245 { 246 return $result->Value; 247 } 248 else 249 { 250 return false; 251 } 252 } 253 254 /** 255 * Method to get the database connection collation, as reported by the driver. If the connector doesn't support 256 * reporting this value please return an empty string. 257 * 258 * @return string 259 */ 260 public function getConnectionCollation() 261 { 262 $this->connect(); 263 264 // Attempt to get the database collation by accessing the server system variable. 265 $this->setQuery('SHOW VARIABLES LIKE "collation_connection"'); 266 $result = $this->loadObject(); 267 268 if (property_exists($result, 'Value')) 269 { 270 return $result->Value; 271 } 272 else 273 { 274 return false; 275 } 276 } 277 278 /** 279 * Shows the table CREATE statement that creates the given tables. 280 * 281 * @param mixed $tables A table name or a list of table names. 282 * 283 * @return array A list of the create SQL for the tables. 284 * 285 * @since 3.4 286 * @throws RuntimeException 287 */ 288 public function getTableCreate($tables) 289 { 290 $this->connect(); 291 292 // Initialise variables. 293 $result = array(); 294 295 // Sanitize input to an array and iterate over the list. 296 settype($tables, 'array'); 297 298 foreach ($tables as $table) 299 { 300 $this->setQuery('SHOW CREATE TABLE ' . $this->quoteName($table)); 301 302 $row = $this->loadRow(); 303 304 // Populate the result array based on the create statements. 305 $result[$table] = $row[1]; 306 } 307 308 return $result; 309 } 310 311 /** 312 * Retrieves field information about a given table. 313 * 314 * @param string $table The name of the database table. 315 * @param boolean $typeOnly True to only return field types. 316 * 317 * @return array An array of fields for the database table. 318 * 319 * @since 3.4 320 * @throws RuntimeException 321 */ 322 public function getTableColumns($table, $typeOnly = true) 323 { 324 $this->connect(); 325 326 $result = array(); 327 328 // Set the query to get the table fields statement. 329 $this->setQuery('SHOW FULL COLUMNS FROM ' . $this->quoteName($table)); 330 331 $fields = $this->loadObjectList(); 332 333 // If we only want the type as the value add just that to the list. 334 if ($typeOnly) 335 { 336 foreach ($fields as $field) 337 { 338 $result[$field->Field] = preg_replace('/[(0-9)]/', '', $field->Type); 339 } 340 } 341 // If we want the whole field data object add that to the list. 342 else 343 { 344 foreach ($fields as $field) 345 { 346 $result[$field->Field] = $field; 347 } 348 } 349 350 return $result; 351 } 352 353 /** 354 * Get the details list of keys for a table. 355 * 356 * @param string $table The name of the table. 357 * 358 * @return array An array of the column specification for the table. 359 * 360 * @since 3.4 361 * @throws RuntimeException 362 */ 363 public function getTableKeys($table) 364 { 365 $this->connect(); 366 367 // Get the details columns information. 368 $this->setQuery('SHOW KEYS FROM ' . $this->quoteName($table)); 369 370 $keys = $this->loadObjectList(); 371 372 return $keys; 373 } 374 375 /** 376 * Method to get an array of all tables in the database. 377 * 378 * @return array An array of all the tables in the database. 379 * 380 * @since 3.4 381 * @throws RuntimeException 382 */ 383 public function getTableList() 384 { 385 $this->connect(); 386 387 // Set the query to get the tables statement. 388 $this->setQuery('SHOW TABLES'); 389 $tables = $this->loadColumn(); 390 391 return $tables; 392 } 393 394 /** 395 * Locks a table in the database. 396 * 397 * @param string $table The name of the table to unlock. 398 * 399 * @return JDatabaseDriverPdomysql Returns this object to support chaining. 400 * 401 * @since 3.4 402 * @throws RuntimeException 403 */ 404 public function lockTable($table) 405 { 406 $this->setQuery('LOCK TABLES ' . $this->quoteName($table) . ' WRITE')->execute(); 407 408 return $this; 409 } 410 411 /** 412 * Renames a table in the database. 413 * 414 * @param string $oldTable The name of the table to be renamed 415 * @param string $newTable The new name for the table. 416 * @param string $backup Not used by MySQL. 417 * @param string $prefix Not used by MySQL. 418 * 419 * @return JDatabaseDriverPdomysql Returns this object to support chaining. 420 * 421 * @since 3.4 422 * @throws RuntimeException 423 */ 424 public function renameTable($oldTable, $newTable, $backup = null, $prefix = null) 425 { 426 $this->setQuery('RENAME TABLE ' . $this->quoteName($oldTable) . ' TO ' . $this->quoteName($newTable)); 427 428 $this->execute(); 429 430 return $this; 431 } 432 433 /** 434 * Method to escape a string for usage in an SQL statement. 435 * 436 * Oracle escaping reference: 437 * http://www.orafaq.com/wiki/SQL_FAQ#How_does_one_escape_special_characters_when_writing_SQL_queries.3F 438 * 439 * SQLite escaping notes: 440 * http://www.sqlite.org/faq.html#q14 441 * 442 * Method body is as implemented by the Zend Framework 443 * 444 * Note: Using query objects with bound variables is 445 * preferable to the below. 446 * 447 * @param string $text The string to be escaped. 448 * @param boolean $extra Unused optional parameter to provide extra escaping. 449 * 450 * @return string The escaped string. 451 * 452 * @since 3.4 453 */ 454 public function escape($text, $extra = false) 455 { 456 if (is_int($text)) 457 { 458 return $text; 459 } 460 461 if (is_float($text)) 462 { 463 // Force the dot as a decimal point. 464 return str_replace(',', '.', $text); 465 } 466 467 $this->connect(); 468 469 $result = substr($this->connection->quote($text), 1, -1); 470 471 if ($extra) 472 { 473 $result = addcslashes($result, '%_'); 474 } 475 476 return $result; 477 } 478 479 /** 480 * Unlocks tables in the database. 481 * 482 * @return JDatabaseDriverPdomysql Returns this object to support chaining. 483 * 484 * @since 3.4 485 * @throws RuntimeException 486 */ 487 public function unlockTables() 488 { 489 $this->setQuery('UNLOCK TABLES')->execute(); 490 491 return $this; 492 } 493 494 /** 495 * Method to commit a transaction. 496 * 497 * @param boolean $toSavepoint If true, commit to the last savepoint. 498 * 499 * @return void 500 * 501 * @since 3.4 502 * @throws RuntimeException 503 */ 504 public function transactionCommit($toSavepoint = false) 505 { 506 $this->connect(); 507 508 if (!$toSavepoint || $this->transactionDepth <= 1) 509 { 510 parent::transactionCommit($toSavepoint); 511 } 512 else 513 { 514 $this->transactionDepth--; 515 } 516 } 517 518 /** 519 * Method to roll back a transaction. 520 * 521 * @param boolean $toSavepoint If true, rollback to the last savepoint. 522 * 523 * @return void 524 * 525 * @since 3.4 526 * @throws RuntimeException 527 */ 528 public function transactionRollback($toSavepoint = false) 529 { 530 $this->connect(); 531 532 if (!$toSavepoint || $this->transactionDepth <= 1) 533 { 534 parent::transactionRollback($toSavepoint); 535 } 536 else 537 { 538 $savepoint = 'SP_' . ($this->transactionDepth - 1); 539 $this->setQuery('ROLLBACK TO SAVEPOINT ' . $this->quoteName($savepoint)); 540 541 if ($this->execute()) 542 { 543 $this->transactionDepth--; 544 } 545 } 546 } 547 548 /** 549 * Method to initialize a transaction. 550 * 551 * @param boolean $asSavepoint If true and a transaction is already active, a savepoint will be created. 552 * 553 * @return void 554 * 555 * @since 3.4 556 * @throws RuntimeException 557 */ 558 public function transactionStart($asSavepoint = false) 559 { 560 $this->connect(); 561 562 if (!$asSavepoint || !$this->transactionDepth) 563 { 564 parent::transactionStart($asSavepoint); 565 } 566 else 567 { 568 $savepoint = 'SP_' . $this->transactionDepth; 569 $this->setQuery('SAVEPOINT ' . $this->quoteName($savepoint)); 570 571 if ($this->execute()) 572 { 573 $this->transactionDepth++; 574 } 575 } 576 } 577 578 /** 579 * Internal function to check if profiling is available. 580 * 581 * @return boolean 582 * 583 * @since 3.9.1 584 */ 585 private function hasProfiling() 586 { 587 $result = $this->setQuery("SHOW VARIABLES LIKE 'have_profiling'")->loadAssoc(); 588 589 return isset($result); 590 } 591} 592