1<?php 2/** 3 * Interface to the MySQL Improved extension (MySQLi) 4 */ 5 6declare(strict_types=1); 7 8namespace PhpMyAdmin\Dbal; 9 10use mysqli; 11use mysqli_result; 12use mysqli_stmt; 13use PhpMyAdmin\DatabaseInterface; 14use PhpMyAdmin\Query\Utilities; 15use stdClass; 16use function mysqli_report; 17use const E_USER_WARNING; 18use const MYSQLI_ASSOC; 19use const MYSQLI_AUTO_INCREMENT_FLAG; 20use const MYSQLI_BLOB_FLAG; 21use const MYSQLI_BOTH; 22use const MYSQLI_CLIENT_COMPRESS; 23use const MYSQLI_CLIENT_SSL; 24use const MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT; 25use const MYSQLI_ENUM_FLAG; 26use const MYSQLI_MULTIPLE_KEY_FLAG; 27use const MYSQLI_NOT_NULL_FLAG; 28use const MYSQLI_NUM; 29use const MYSQLI_NUM_FLAG; 30use const MYSQLI_OPT_LOCAL_INFILE; 31use const MYSQLI_OPT_SSL_VERIFY_SERVER_CERT; 32use const MYSQLI_PART_KEY_FLAG; 33use const MYSQLI_PRI_KEY_FLAG; 34use const MYSQLI_REPORT_OFF; 35use const MYSQLI_SET_FLAG; 36use const MYSQLI_STORE_RESULT; 37use const MYSQLI_TIMESTAMP_FLAG; 38use const MYSQLI_TYPE_BIT; 39use const MYSQLI_TYPE_BLOB; 40use const MYSQLI_TYPE_DATE; 41use const MYSQLI_TYPE_DATETIME; 42use const MYSQLI_TYPE_DECIMAL; 43use const MYSQLI_TYPE_DOUBLE; 44use const MYSQLI_TYPE_ENUM; 45use const MYSQLI_TYPE_FLOAT; 46use const MYSQLI_TYPE_GEOMETRY; 47use const MYSQLI_TYPE_INT24; 48use const MYSQLI_TYPE_JSON; 49use const MYSQLI_TYPE_LONG; 50use const MYSQLI_TYPE_LONG_BLOB; 51use const MYSQLI_TYPE_LONGLONG; 52use const MYSQLI_TYPE_MEDIUM_BLOB; 53use const MYSQLI_TYPE_NEWDATE; 54use const MYSQLI_TYPE_NEWDECIMAL; 55use const MYSQLI_TYPE_NULL; 56use const MYSQLI_TYPE_SET; 57use const MYSQLI_TYPE_SHORT; 58use const MYSQLI_TYPE_STRING; 59use const MYSQLI_TYPE_TIME; 60use const MYSQLI_TYPE_TIMESTAMP; 61use const MYSQLI_TYPE_TINY; 62use const MYSQLI_TYPE_TINY_BLOB; 63use const MYSQLI_TYPE_VAR_STRING; 64use const MYSQLI_TYPE_YEAR; 65use const MYSQLI_UNIQUE_KEY_FLAG; 66use const MYSQLI_UNSIGNED_FLAG; 67use const MYSQLI_USE_RESULT; 68use const MYSQLI_ZEROFILL_FLAG; 69use function define; 70use function defined; 71use function implode; 72use function is_array; 73use function is_bool; 74use function mysqli_init; 75use function stripos; 76use function trigger_error; 77use function mysqli_get_client_info; 78use function sprintf; 79use const E_USER_ERROR; 80 81/** 82 * Interface to the MySQL Improved extension (MySQLi) 83 */ 84class DbiMysqli implements DbiExtension 85{ 86 /** @var array */ 87 private static $flagNames = [ 88 MYSQLI_NUM_FLAG => 'num', 89 MYSQLI_PART_KEY_FLAG => 'part_key', 90 MYSQLI_SET_FLAG => 'set', 91 MYSQLI_TIMESTAMP_FLAG => 'timestamp', 92 MYSQLI_AUTO_INCREMENT_FLAG => 'auto_increment', 93 MYSQLI_ENUM_FLAG => 'enum', 94 MYSQLI_ZEROFILL_FLAG => 'zerofill', 95 MYSQLI_UNSIGNED_FLAG => 'unsigned', 96 MYSQLI_BLOB_FLAG => 'blob', 97 MYSQLI_MULTIPLE_KEY_FLAG => 'multiple_key', 98 MYSQLI_UNIQUE_KEY_FLAG => 'unique_key', 99 MYSQLI_PRI_KEY_FLAG => 'primary_key', 100 MYSQLI_NOT_NULL_FLAG => 'not_null', 101 ]; 102 103 /** 104 * connects to the database server 105 * 106 * @param string $user mysql user name 107 * @param string $password mysql user password 108 * @param array $server host/port/socket/persistent 109 * 110 * @return mysqli|bool false on error or a mysqli object on success 111 */ 112 public function connect($user, $password, array $server) 113 { 114 if ($server) { 115 $server['host'] = empty($server['host']) 116 ? 'localhost' 117 : $server['host']; 118 } 119 120 mysqli_report(MYSQLI_REPORT_OFF); 121 122 $mysqli = mysqli_init(); 123 124 $client_flags = 0; 125 126 /* Optionally compress connection */ 127 if ($server['compress'] && defined('MYSQLI_CLIENT_COMPRESS')) { 128 $client_flags |= MYSQLI_CLIENT_COMPRESS; 129 } 130 131 /* Optionally enable SSL */ 132 if ($server['ssl']) { 133 $client_flags |= MYSQLI_CLIENT_SSL; 134 if (! empty($server['ssl_key']) || 135 ! empty($server['ssl_cert']) || 136 ! empty($server['ssl_ca']) || 137 ! empty($server['ssl_ca_path']) || 138 ! empty($server['ssl_ciphers']) 139 ) { 140 $mysqli->ssl_set( 141 $server['ssl_key'] ?? '', 142 $server['ssl_cert'] ?? '', 143 $server['ssl_ca'] ?? '', 144 $server['ssl_ca_path'] ?? '', 145 $server['ssl_ciphers'] ?? '' 146 ); 147 } 148 /* 149 * disables SSL certificate validation on mysqlnd for MySQL 5.6 or later 150 * @link https://bugs.php.net/bug.php?id=68344 151 * @link https://github.com/phpmyadmin/phpmyadmin/pull/11838 152 */ 153 if (! $server['ssl_verify']) { 154 $mysqli->options( 155 MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, 156 $server['ssl_verify'] 157 ); 158 $client_flags |= MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT; 159 } 160 } 161 162 if ($GLOBALS['cfg']['PersistentConnections']) { 163 $host = 'p:' . $server['host']; 164 } else { 165 $host = $server['host']; 166 } 167 168 if ($server['hide_connection_errors']) { 169 $return_value = @$mysqli->real_connect( 170 $host, 171 $user, 172 $password, 173 '', 174 $server['port'], 175 (string) $server['socket'], 176 $client_flags 177 ); 178 } else { 179 $return_value = $mysqli->real_connect( 180 $host, 181 $user, 182 $password, 183 '', 184 $server['port'], 185 (string) $server['socket'], 186 $client_flags 187 ); 188 } 189 190 if ($return_value === false || $return_value === null) { 191 /* 192 * Switch to SSL if server asked us to do so, unfortunately 193 * there are more ways MySQL server can tell this: 194 * 195 * - MySQL 8.0 and newer should return error 3159 196 * - #2001 - SSL Connection is required. Please specify SSL options and retry. 197 * - #9002 - SSL connection is required. Please specify SSL options and retry. 198 */ 199 $error_number = $mysqli->connect_errno; 200 $error_message = $mysqli->connect_error; 201 if (! $server['ssl'] 202 && ($error_number == 3159 203 || (($error_number == 2001 || $error_number == 9002) 204 && stripos($error_message, 'SSL Connection is required') !== false)) 205 ) { 206 trigger_error( 207 __('SSL connection enforced by server, automatically enabling it.'), 208 E_USER_WARNING 209 ); 210 $server['ssl'] = true; 211 212 return self::connect($user, $password, $server); 213 } 214 215 if ($error_number === 1045 && $server['hide_connection_errors']) { 216 trigger_error( 217 sprintf( 218 __( 219 'Error 1045: Access denied for user. Additional error information' 220 . ' may be available, but is being hidden by the %s configuration directive.' 221 ), 222 '[code][doc@cfg_Servers_hide_connection_errors]' 223 . '$cfg[\'Servers\'][$i][\'hide_connection_errors\'][/doc][/code]' 224 ), 225 E_USER_ERROR 226 ); 227 } 228 229 return false; 230 } 231 232 if (defined('PMA_ENABLE_LDI')) { 233 $mysqli->options(MYSQLI_OPT_LOCAL_INFILE, true); 234 } else { 235 $mysqli->options(MYSQLI_OPT_LOCAL_INFILE, false); 236 } 237 238 return $mysqli; 239 } 240 241 /** 242 * selects given database 243 * 244 * @param string $databaseName database name to select 245 * @param mysqli $mysqli the mysqli object 246 * 247 * @return bool 248 */ 249 public function selectDb($databaseName, $mysqli) 250 { 251 return $mysqli->select_db($databaseName); 252 } 253 254 /** 255 * runs a query and returns the result 256 * 257 * @param string $query query to execute 258 * @param mysqli $mysqli mysqli object 259 * @param int $options query options 260 * 261 * @return mysqli_result|bool 262 */ 263 public function realQuery($query, $mysqli, $options) 264 { 265 if ($options == ($options | DatabaseInterface::QUERY_STORE)) { 266 $method = MYSQLI_STORE_RESULT; 267 } elseif ($options == ($options | DatabaseInterface::QUERY_UNBUFFERED)) { 268 $method = MYSQLI_USE_RESULT; 269 } else { 270 $method = 0; 271 } 272 273 return $mysqli->query($query, $method); 274 } 275 276 /** 277 * Run the multi query and output the results 278 * 279 * @param mysqli $mysqli mysqli object 280 * @param string $query multi query statement to execute 281 * 282 * @return bool 283 */ 284 public function realMultiQuery($mysqli, $query) 285 { 286 return $mysqli->multi_query($query); 287 } 288 289 /** 290 * returns array of rows with associative and numeric keys from $result 291 * 292 * @param mysqli_result $result result set identifier 293 */ 294 public function fetchArray($result): ?array 295 { 296 if (! $result instanceof mysqli_result) { 297 return null; 298 } 299 300 return $result->fetch_array(MYSQLI_BOTH); 301 } 302 303 /** 304 * returns array of rows with associative keys from $result 305 * 306 * @param mysqli_result $result result set identifier 307 */ 308 public function fetchAssoc($result): ?array 309 { 310 if (! $result instanceof mysqli_result) { 311 return null; 312 } 313 314 return $result->fetch_array(MYSQLI_ASSOC); 315 } 316 317 /** 318 * returns array of rows with numeric keys from $result 319 * 320 * @param mysqli_result $result result set identifier 321 */ 322 public function fetchRow($result): ?array 323 { 324 if (! $result instanceof mysqli_result) { 325 return null; 326 } 327 328 return $result->fetch_array(MYSQLI_NUM); 329 } 330 331 /** 332 * Adjusts the result pointer to an arbitrary row in the result 333 * 334 * @param mysqli_result $result database result 335 * @param int $offset offset to seek 336 * 337 * @return bool true on success, false on failure 338 */ 339 public function dataSeek($result, $offset) 340 { 341 return $result->data_seek($offset); 342 } 343 344 /** 345 * Frees memory associated with the result 346 * 347 * @param mysqli_result $result database result 348 * 349 * @return void 350 */ 351 public function freeResult($result) 352 { 353 if (! ($result instanceof mysqli_result)) { 354 return; 355 } 356 357 $result->close(); 358 } 359 360 /** 361 * Check if there are any more query results from a multi query 362 * 363 * @param mysqli $mysqli the mysqli object 364 * 365 * @return bool true or false 366 */ 367 public function moreResults($mysqli) 368 { 369 return $mysqli->more_results(); 370 } 371 372 /** 373 * Prepare next result from multi_query 374 * 375 * @param mysqli $mysqli the mysqli object 376 * 377 * @return bool true or false 378 */ 379 public function nextResult($mysqli) 380 { 381 return $mysqli->next_result(); 382 } 383 384 /** 385 * Store the result returned from multi query 386 * 387 * @param mysqli $mysqli the mysqli object 388 * 389 * @return mysqli_result|bool false when empty results / result set when not empty 390 */ 391 public function storeResult($mysqli) 392 { 393 return $mysqli->store_result(); 394 } 395 396 /** 397 * Returns a string representing the type of connection used 398 * 399 * @param mysqli $mysqli mysql link 400 * 401 * @return string type of connection used 402 */ 403 public function getHostInfo($mysqli) 404 { 405 return $mysqli->host_info; 406 } 407 408 /** 409 * Returns the version of the MySQL protocol used 410 * 411 * @param mysqli $mysqli mysql link 412 * 413 * @return string version of the MySQL protocol used 414 */ 415 public function getProtoInfo($mysqli) 416 { 417 return $mysqli->protocol_version; 418 } 419 420 /** 421 * returns a string that represents the client library version 422 * 423 * @return string MySQL client library version 424 */ 425 public function getClientInfo() 426 { 427 return mysqli_get_client_info(); 428 } 429 430 /** 431 * returns last error message or false if no errors occurred 432 * 433 * @param mysqli $mysqli mysql link 434 * 435 * @return string|bool error or false 436 */ 437 public function getError($mysqli) 438 { 439 $GLOBALS['errno'] = 0; 440 441 if ($mysqli !== null && $mysqli !== false) { 442 $error_number = $mysqli->errno; 443 $error_message = $mysqli->error; 444 } else { 445 $error_number = $mysqli->connect_errno; 446 $error_message = $mysqli->connect_error; 447 } 448 if ($error_number == 0) { 449 return false; 450 } 451 452 // keep the error number for further check after 453 // the call to getError() 454 $GLOBALS['errno'] = $error_number; 455 456 return Utilities::formatError($error_number, $error_message); 457 } 458 459 /** 460 * returns the number of rows returned by last query 461 * 462 * @param mysqli_result $result result set identifier 463 * 464 * @return string|int 465 */ 466 public function numRows($result) 467 { 468 // see the note for tryQuery(); 469 if (is_bool($result)) { 470 return 0; 471 } 472 473 return $result->num_rows; 474 } 475 476 /** 477 * returns the number of rows affected by last query 478 * 479 * @param mysqli $mysqli the mysqli object 480 * 481 * @return int 482 */ 483 public function affectedRows($mysqli) 484 { 485 return $mysqli->affected_rows; 486 } 487 488 /** 489 * returns meta info for fields in $result 490 * 491 * @param mysqli_result $result result set identifier 492 * 493 * @return array|bool meta info for fields in $result 494 */ 495 public function getFieldsMeta($result) 496 { 497 if (! $result instanceof mysqli_result) { 498 return false; 499 } 500 // Issue #16043 - client API mysqlnd seem not to have MYSQLI_TYPE_JSON defined 501 if (! defined('MYSQLI_TYPE_JSON')) { 502 define('MYSQLI_TYPE_JSON', 245); 503 } 504 // Build an associative array for a type look up 505 $typeAr = []; 506 $typeAr[MYSQLI_TYPE_DECIMAL] = 'real'; 507 $typeAr[MYSQLI_TYPE_NEWDECIMAL] = 'real'; 508 $typeAr[MYSQLI_TYPE_BIT] = 'int'; 509 $typeAr[MYSQLI_TYPE_TINY] = 'int'; 510 $typeAr[MYSQLI_TYPE_SHORT] = 'int'; 511 $typeAr[MYSQLI_TYPE_LONG] = 'int'; 512 $typeAr[MYSQLI_TYPE_FLOAT] = 'real'; 513 $typeAr[MYSQLI_TYPE_DOUBLE] = 'real'; 514 $typeAr[MYSQLI_TYPE_NULL] = 'null'; 515 $typeAr[MYSQLI_TYPE_TIMESTAMP] = 'timestamp'; 516 $typeAr[MYSQLI_TYPE_LONGLONG] = 'int'; 517 $typeAr[MYSQLI_TYPE_INT24] = 'int'; 518 $typeAr[MYSQLI_TYPE_DATE] = 'date'; 519 $typeAr[MYSQLI_TYPE_TIME] = 'time'; 520 $typeAr[MYSQLI_TYPE_DATETIME] = 'datetime'; 521 $typeAr[MYSQLI_TYPE_YEAR] = 'year'; 522 $typeAr[MYSQLI_TYPE_NEWDATE] = 'date'; 523 $typeAr[MYSQLI_TYPE_ENUM] = 'unknown'; 524 $typeAr[MYSQLI_TYPE_SET] = 'unknown'; 525 $typeAr[MYSQLI_TYPE_TINY_BLOB] = 'blob'; 526 $typeAr[MYSQLI_TYPE_MEDIUM_BLOB] = 'blob'; 527 $typeAr[MYSQLI_TYPE_LONG_BLOB] = 'blob'; 528 $typeAr[MYSQLI_TYPE_BLOB] = 'blob'; 529 $typeAr[MYSQLI_TYPE_VAR_STRING] = 'string'; 530 $typeAr[MYSQLI_TYPE_STRING] = 'string'; 531 // MySQL returns MYSQLI_TYPE_STRING for CHAR 532 // and MYSQLI_TYPE_CHAR === MYSQLI_TYPE_TINY 533 // so this would override TINYINT and mark all TINYINT as string 534 // see https://github.com/phpmyadmin/phpmyadmin/issues/8569 535 //$typeAr[MYSQLI_TYPE_CHAR] = 'string'; 536 $typeAr[MYSQLI_TYPE_GEOMETRY] = 'geometry'; 537 $typeAr[MYSQLI_TYPE_BIT] = 'bit'; 538 $typeAr[MYSQLI_TYPE_JSON] = 'json'; 539 540 $fields = $result->fetch_fields(); 541 542 if (! is_array($fields)) { 543 return false; 544 } 545 546 foreach ($fields as $k => $field) { 547 $fields[$k]->_type = $field->type; 548 $fields[$k]->type = $typeAr[$field->type]; 549 $fields[$k]->_flags = $field->flags; 550 $fields[$k]->flags = $this->fieldFlags($result, $k); 551 552 // Enhance the field objects for mysql-extension compatibility 553 //$flags = explode(' ', $fields[$k]->flags); 554 //array_unshift($flags, 'dummy'); 555 $fields[$k]->multiple_key 556 = (int) (bool) ($fields[$k]->_flags & MYSQLI_MULTIPLE_KEY_FLAG); 557 $fields[$k]->primary_key 558 = (int) (bool) ($fields[$k]->_flags & MYSQLI_PRI_KEY_FLAG); 559 $fields[$k]->unique_key 560 = (int) (bool) ($fields[$k]->_flags & MYSQLI_UNIQUE_KEY_FLAG); 561 $fields[$k]->not_null 562 = (int) (bool) ($fields[$k]->_flags & MYSQLI_NOT_NULL_FLAG); 563 $fields[$k]->unsigned 564 = (int) (bool) ($fields[$k]->_flags & MYSQLI_UNSIGNED_FLAG); 565 $fields[$k]->zerofill 566 = (int) (bool) ($fields[$k]->_flags & MYSQLI_ZEROFILL_FLAG); 567 $fields[$k]->numeric 568 = (int) (bool) ($fields[$k]->_flags & MYSQLI_NUM_FLAG); 569 $fields[$k]->blob 570 = (int) (bool) ($fields[$k]->_flags & MYSQLI_BLOB_FLAG); 571 } 572 573 return $fields; 574 } 575 576 /** 577 * return number of fields in given $result 578 * 579 * @param mysqli_result $result result set identifier 580 * 581 * @return int field count 582 */ 583 public function numFields($result) 584 { 585 return $result->field_count; 586 } 587 588 /** 589 * returns the length of the given field $i in $result 590 * 591 * @param mysqli_result $result result set identifier 592 * @param int $i field 593 * 594 * @return int|bool length of field 595 */ 596 public function fieldLen($result, $i) 597 { 598 if ($i >= $this->numFields($result)) { 599 return false; 600 } 601 /** @var stdClass $fieldDefinition */ 602 $fieldDefinition = $result->fetch_field_direct($i); 603 if ($fieldDefinition !== false) { 604 return $fieldDefinition->length; 605 } 606 607 return false; 608 } 609 610 /** 611 * returns name of $i. field in $result 612 * 613 * @param mysqli_result $result result set identifier 614 * @param int $i field 615 * 616 * @return string name of $i. field in $result 617 */ 618 public function fieldName($result, $i) 619 { 620 if ($i >= $this->numFields($result)) { 621 return ''; 622 } 623 /** @var stdClass $fieldDefinition */ 624 $fieldDefinition = $result->fetch_field_direct($i); 625 if ($fieldDefinition !== false) { 626 return $fieldDefinition->name; 627 } 628 629 return ''; 630 } 631 632 /** 633 * returns concatenated string of human readable field flags 634 * 635 * @param mysqli_result $result result set identifier 636 * @param int $i field 637 * 638 * @return string|false field flags 639 */ 640 public function fieldFlags($result, $i) 641 { 642 if ($i >= $this->numFields($result)) { 643 return false; 644 } 645 /** @var stdClass|false $fieldDefinition */ 646 $fieldDefinition = $result->fetch_field_direct($i); 647 if ($fieldDefinition === false) { 648 return ''; 649 } 650 651 $type = $fieldDefinition->type; 652 $charsetNumber = $fieldDefinition->charsetnr; 653 $fieldDefinitionFlags = $fieldDefinition->flags; 654 $flags = []; 655 foreach (self::$flagNames as $flag => $name) { 656 if (! ($fieldDefinitionFlags & $flag)) { 657 continue; 658 } 659 660 $flags[] = $name; 661 } 662 // See https://dev.mysql.com/doc/refman/6.0/en/c-api-datatypes.html: 663 // to determine if a string is binary, we should not use MYSQLI_BINARY_FLAG 664 // but instead the charsetnr member of the MYSQL_FIELD 665 // structure. Watch out: some types like DATE returns 63 in charsetnr 666 // so we have to check also the type. 667 // Unfortunately there is no equivalent in the mysql extension. 668 if (($type == MYSQLI_TYPE_TINY_BLOB || $type == MYSQLI_TYPE_BLOB 669 || $type == MYSQLI_TYPE_MEDIUM_BLOB || $type == MYSQLI_TYPE_LONG_BLOB 670 || $type == MYSQLI_TYPE_VAR_STRING || $type == MYSQLI_TYPE_STRING) 671 && $charsetNumber == 63 672 ) { 673 $flags[] = 'binary'; 674 } 675 676 return implode(' ', $flags); 677 } 678 679 /** 680 * returns properly escaped string for use in MySQL queries 681 * 682 * @param mysqli $mysqli database link 683 * @param string $string string to be escaped 684 * 685 * @return string a MySQL escaped string 686 */ 687 public function escapeString($mysqli, $string) 688 { 689 return $mysqli->real_escape_string($string); 690 } 691 692 /** 693 * Prepare an SQL statement for execution. 694 * 695 * @param mysqli $mysqli database link 696 * @param string $query The query, as a string. 697 * 698 * @return mysqli_stmt|false A statement object or false. 699 */ 700 public function prepare($mysqli, string $query) 701 { 702 return $mysqli->prepare($query); 703 } 704} 705