| Textpattern | PHP Cross Reference | Content Management Systems |
Description: Database abstraction layer.
1 <?php 2 3 /* 4 * Textpattern Content Management System 5 * https://textpattern.com/ 6 * 7 * Copyright (C) 2020 The Textpattern Development Team 8 * 9 * This file is part of Textpattern. 10 * 11 * Textpattern is free software; you can redistribute it and/or 12 * modify it under the terms of the GNU General Public License 13 * as published by the Free Software Foundation, version 2. 14 * 15 * Textpattern is distributed in the hope that it will be useful, 16 * but WITHOUT ANY WARRANTY; without even the implied warranty of 17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 18 * GNU General Public License for more details. 19 * 20 * You should have received a copy of the GNU General Public License 21 * along with Textpattern. If not, see <https://www.gnu.org/licenses/>. 22 */ 23 24 /** 25 * Database abstraction layer. 26 * 27 * @package DB 28 */ 29 30 if (!defined('PFX')) { 31 /** 32 * Database table prefix. 33 */ 34 35 define('PFX', !empty($txpcfg['table_prefix']) ? $txpcfg['table_prefix'] : ''); 36 } 37 38 /** 39 * Initialises a database connection. 40 * 41 * @package DB 42 */ 43 44 class DB 45 { 46 /** 47 * The database server hostname. 48 * 49 * @var string 50 */ 51 52 public $host; 53 54 /** 55 * The database server port. 56 * 57 * @var int 58 */ 59 60 public $port; 61 62 /** 63 * The database server socket. 64 * 65 * @var string 66 */ 67 68 public $socket; 69 70 /** 71 * The database name. 72 * 73 * @var string 74 */ 75 76 public $db; 77 78 /** 79 * The username. 80 * 81 * @var string 82 */ 83 84 public $user; 85 86 /** 87 * The password. 88 * 89 * @var string 90 */ 91 92 public $pass; 93 94 /** 95 * Database table prefix. 96 * 97 * @var string 98 * @since 4.6.0 99 */ 100 101 public $table_prefix = ''; 102 103 /** 104 * Database client flags. 105 * 106 * @var int 107 */ 108 109 public $client_flags = 0; 110 111 /** 112 * SSL parameters. 113 * 114 * @var string 115 */ 116 117 public $ssl; 118 119 /** 120 * Database connection charset. 121 * 122 * @var string 123 * @since 4.6.0 124 */ 125 126 public $charset = ''; 127 128 /** 129 * The database link identifier. 130 * 131 * @var resource 132 */ 133 134 public $link; 135 136 /** 137 * Database server version. 138 * 139 * @var string 140 */ 141 142 public $version; 143 144 /** 145 * Default table options definition. 146 * 147 * @var array 148 */ 149 150 public $table_options = array(); 151 152 /** 153 * The default character set for the connection. 154 * 155 * @var string 156 * @since 4.6.0 157 */ 158 159 public $default_charset; 160 161 /** 162 * Creates a new link. 163 */ 164 165 public function __construct() 166 { 167 global $txpcfg, $connected; 168 169 $client_flags = MYSQLI_CLIENT_FOUND_ROWS; 170 171 $this->link = mysqli_init(); 172 173 if (strpos($txpcfg['host'], ':') === false) { 174 $this->host = $txpcfg['host']; 175 $this->port = ini_get("mysqli.default_port"); 176 } else { 177 list($this->host, $this->port) = explode(':', $txpcfg['host'], 2); 178 $this->port = intval($this->port); 179 } 180 181 if (isset($txpcfg['socket'])) { 182 $this->socket = $txpcfg['socket']; 183 } else { 184 $this->socket = ini_get("mysqli.default_socket"); 185 } 186 187 if (isset($txpcfg['ssl']) && is_array($txpcfg['ssl'])) { 188 $client_flags = $client_flags | MYSQLI_CLIENT_SSL; 189 190 foreach (array('key', 'cert', 'ca', 'capath', 'ciphers') as $ssl_param) { 191 if (isset($txpcfg['ssl'][$ssl_param])) { 192 $this->ssl[$ssl_param] = $txpcfg['ssl'][$ssl_param]; 193 } else { 194 $this->ssl[$ssl_param] = null; 195 } 196 } 197 198 if (isset($txpcfg['ssl']['flags']) && is_array($txpcfg['ssl']['flags'])) { 199 foreach ($txpcfg['ssl']['flags'] as $ssl_flag => $ssl_flag_value) { 200 switch ($ssl_flag_value) { 201 case 'true': 202 mysqli_options($this->link, $ssl_flag, true); 203 break; 204 case 'false': 205 mysqli_options($this->link, $ssl_flag, false); 206 break; 207 default: 208 mysqli_options($this->link, $ssl_flag, $ssl_flag_value); 209 break; 210 } 211 } 212 } 213 214 mysqli_ssl_set( 215 $this->link, 216 $this->ssl['key'], 217 $this->ssl['cert'], 218 $this->ssl['ca'], 219 $this->ssl['capath'], 220 $this->ssl['ciphers'] 221 ); 222 } 223 224 $this->db = $txpcfg['db']; 225 $this->user = $txpcfg['user']; 226 $this->pass = $txpcfg['pass']; 227 $this->table_options['engine'] = !empty($txpcfg['dbengine']) ? $txpcfg['dbengine'] : 'MyISAM'; 228 229 if (!empty($txpcfg['table_prefix'])) { 230 $this->table_prefix = $txpcfg['table_prefix']; 231 } 232 233 if (isset($txpcfg['client_flags'])) { 234 $this->client_flags = $txpcfg['client_flags']; 235 } else { 236 $this->client_flags = $client_flags; 237 } 238 239 if (isset($txpcfg['dbcharset'])) { 240 $this->charset = $txpcfg['dbcharset']; 241 } 242 243 // Suppress screen output from mysqli_real_connect(). 244 $error_reporting = error_reporting(); 245 error_reporting($error_reporting & ~(E_WARNING | E_NOTICE)); 246 247 if (!mysqli_real_connect($this->link, $this->host, $this->user, $this->pass, $this->db, $this->port, $this->socket, $this->client_flags)) { 248 die(db_down()); 249 } 250 251 error_reporting($error_reporting); 252 253 $version = $this->version = mysqli_get_server_info($this->link); 254 $connected = true; 255 256 // Be backwards compatible. 257 if ($this->charset) { 258 mysqli_query($this->link, "SET NAMES ".$this->charset); 259 $this->table_options['charset'] = $this->charset; 260 261 if (isset($txpcfg['table_collation'])) { 262 $this->table_options['collate'] = $txpcfg['table_collation']; 263 } else { 264 if ($this->charset == 'utf8mb4') { 265 $this->table_options['collate'] = "utf8mb4_unicode_ci"; 266 } elseif ($this->charset == 'utf8') { 267 $this->table_options['collate'] = "utf8_general_ci"; 268 } 269 } 270 } 271 272 $this->default_charset = mysqli_character_set_name($this->link); 273 } 274 } 275 276 /** 277 * Current database link. 278 * 279 * @access private 280 * @global DB $DB 281 */ 282 283 $DB = new DB; 284 285 /** 286 * Prefixes a database table's name for use in a query. 287 * 288 * Textpattern can be installed to a shared database, this is achieved by 289 * prefixing database tables. This function can be used to add those prefixes to 290 * a known named table when building SQL statements. 291 * 292 * Always use this function, or the safe_pfx_j(), when you refer tables in raw 293 * SQL statements, including where clauses, joins and sub-queries. 294 * 295 * This function will also quote the table name if necessary. 296 * 297 * You don't need to use this function in any of the dedicated "table" 298 * parameters database functions offer. Any table used in a table parameter is 299 * prefixed for you. 300 * 301 * @param string $table The database table 302 * @return string The $table with a prefix 303 * @see safe_pfx_j() 304 * @example 305 * if (safe_query('DROP TABLE '.safe_pfx('myTable')) 306 * { 307 * echo 'myTable dropped'; 308 * } 309 */ 310 311 function safe_pfx($table) 312 { 313 global $DB; 314 $name = $DB->table_prefix.$table; 315 316 if (preg_match('@[^\w._$]@', $name)) { 317 return '`'.$name.'`'; 318 } 319 320 return $name; 321 } 322 323 /** 324 * Prefixes a database table's name for use in a joined query. 325 * 326 * This function prefixes the given table name similarly to safe_pfx(), but also 327 * creates a named, unprefixed, AS alias for it. 328 * 329 * The created alias is same as the table name given. This function is here to 330 * help to make joined queries where you need to refer to two or more tables in 331 * a one query. 332 * 333 * As with safe_pfx(), you don't need to use this function in any of the 334 * dedicated "table" parameters database functions offer. Any table used in a 335 * table parameter is prefixed for you. 336 * 337 * @param string $table The database table, or comma-separated list of tables 338 * @return string The $table with a prefix 339 * @see safe_pfx() 340 * @example 341 * if ($r = getRows('SELECT id FROM '.safe_pfx_j('tableA').' JOIN '.safe_pfx('tableB').' ON tableB.id = tableA.id and tableB.active = 1')) 342 * { 343 * print_r($r); 344 * } 345 */ 346 347 function safe_pfx_j($table) 348 { 349 global $DB; 350 $ts = array(); 351 352 foreach (explode(',', $table) as $t) { 353 $name = $DB->table_prefix.trim($t); 354 if (preg_match('@[^\w._$]@', $name)) { 355 $ts[] = "`$name`".($DB->table_prefix ? " as `$t`" : ''); 356 } else { 357 $ts[] = "$name".($DB->table_prefix ? " as $t" : ''); 358 } 359 } 360 361 return join(', ', $ts); 362 } 363 364 /** 365 * Escapes special characters in a string for use in an SQL statement. 366 * 367 * @param string $in The input string 368 * @return string 369 * @since 4.5.0 370 * @see doSlash() 371 * @example 372 * if (safe_update('myTable', "value = '".doSlash($user_value)."'", "name = '".doSlash($user_name)."'")) 373 * { 374 * echo 'Updated.'; 375 * } 376 */ 377 378 function safe_escape($in = '') 379 { 380 global $DB; 381 382 return mysqli_real_escape_string($DB->link, $in); 383 } 384 385 /** 386 * Escape LIKE pattern's wildcards in a string for use in an SQL statement. 387 * 388 * @param string $in The input string 389 * @return string 390 * @since 4.6.0 391 * @see doLike() 392 * @example 393 * if (safe_update('myTable', "value = '".doLike($user_value)."'", "name LIKE '".doLike($user_name)."'")) 394 * { 395 * echo 'Updated.'; 396 * } 397 */ 398 399 function safe_escape_like($in = '') 400 { 401 return safe_escape(str_replace( 402 array('\\', '%', '_', '\''), 403 array('\\\\', '\\%', '\\_', '\\\''), 404 (string) $in 405 )); 406 } 407 408 /** 409 * Executes an SQL statement. 410 * 411 * @param string $q The SQL statement to execute 412 * @param bool $debug Dump query 413 * @param bool $unbuf If TRUE, executes the statement without fetching and buffering the results 414 * @return mixed 415 * @example 416 * echo safe_query('SELECT * FROM table'); 417 */ 418 419 function safe_query($q = '', $debug = false, $unbuf = false) 420 { 421 global $DB, $trace, $production_status; 422 423 $method = ($unbuf) ? MYSQLI_USE_RESULT : MYSQLI_STORE_RESULT; 424 425 if (!$q) { 426 return false; 427 } 428 429 if ($debug or TXP_DEBUG === 1) { 430 dmp($q); 431 } 432 433 if ($production_status !== 'live') { 434 $trace->start("[SQL: $q ]", true); 435 } 436 437 $result = mysqli_query($DB->link, $q, $method); 438 439 if ($production_status !== 'live') { 440 if (is_bool($result)) { 441 $trace->stop(); 442 } else { 443 $trace->stop("[Rows: ".intval(@mysqli_num_rows($result))."]"); 444 } 445 } 446 447 if ($result === false) { 448 trigger_error(mysqli_error($DB->link), E_USER_ERROR); 449 } 450 451 if (!$result) { 452 return false; 453 } 454 455 return $result; 456 } 457 458 /** 459 * Deletes a row from a table. 460 * 461 * @param string $table The table 462 * @param string $where The where clause 463 * @param bool $debug Dump query 464 * @return bool FALSE on error 465 * @see safe_update() 466 * @see safe_insert() 467 * @example 468 * if (safe_delete('myTable', "name = 'test'")) 469 * { 470 * echo "'test' removed from 'myTable'."; 471 * } 472 */ 473 474 function safe_delete($table, $where, $debug = false) 475 { 476 return (bool) safe_query("DELETE FROM ".safe_pfx($table)." WHERE $where", $debug); 477 } 478 479 /** 480 * Updates a table row. 481 * 482 * @param string $table The table 483 * @param string $set The set clause 484 * @param string $where The where clause 485 * @param bool $debug Dump query 486 * @return bool FALSE on error 487 * @see safe_insert() 488 * @see safe_delete() 489 * @example 490 * if (safe_update('myTable', "myField = 'newValue'", "name = 'test'")) 491 * { 492 * echo "'test' updated, 'myField' set to 'newValue'"; 493 * } 494 */ 495 496 function safe_update($table, $set, $where, $debug = false) 497 { 498 return (bool) safe_query("UPDATE ".safe_pfx($table)." SET $set WHERE $where", $debug); 499 } 500 501 /** 502 * Inserts a new row into a table. 503 * 504 * @param string $table The table 505 * @param string $set The set clause 506 * @param bool $debug Dump query 507 * @return int|bool The last generated ID or FALSE on error. If the ID is 0, returns TRUE 508 * @see safe_update() 509 * @see safe_delete() 510 * @example 511 * if ($id = safe_insert('myTable', "name = 'test', myField = 'newValue'")) 512 * { 513 * echo "Created a row to 'myTable' with the name 'test'. It has ID of {$id}."; 514 * } 515 */ 516 517 function safe_insert($table, $set, $debug = false) 518 { 519 global $DB; 520 $q = "INSERT INTO ".safe_pfx($table)." SET $set"; 521 522 if ($r = safe_query($q, $debug)) { 523 $id = mysqli_insert_id($DB->link); 524 525 return ($id === 0 ? true : $id); 526 } 527 528 return false; 529 } 530 531 /** 532 * Inserts a new row, or updates an existing if a matching row is found. 533 * 534 * @param string $table The table 535 * @param string $set The set clause 536 * @param string|array $where The where clause 537 * @param bool $debug Dump query 538 * @return int|bool The last generated ID or FALSE on error. If the ID is 0, returns TRUE 539 * @example 540 * if ($r = safe_upsert('myTable', "data = 'foobar'", "name = 'example'")) 541 * { 542 * echo "Inserted new row to 'myTable', or updated 'example'."; 543 * } 544 */ 545 546 function safe_upsert($table, $set, $where, $debug = false) 547 { 548 global $DB; 549 550 if (is_array($set)) { 551 $set = join_qs(quote_list($set), ','); 552 } 553 554 $whereset = is_array($where) ? join_qs(quote_list($where), null) : array($where); 555 $where = implode(' AND ', $whereset); 556 557 // FIXME: lock the table so this is atomic? 558 $r = safe_update($table, $set, $where, $debug); 559 560 if ($r && (mysqli_affected_rows($DB->link) || safe_count($table, $where, $debug))) { 561 return $r; 562 } else { 563 return safe_insert($table, join(', ', array(implode(', ', $whereset), $set)), $debug); 564 } 565 } 566 567 /** 568 * Changes the structure of a table. 569 * 570 * @param string $table The table 571 * @param string $alter The statement to execute 572 * @param bool $debug Dump query 573 * @return bool FALSE on error 574 * @example 575 * if (safe_alter('myTable', 'ADD myColumn TINYINT(1)')) 576 * { 577 * echo "'myColumn' added to 'myTable'"; 578 * } 579 */ 580 581 function safe_alter($table, $alter, $debug = false) 582 { 583 return (bool) safe_query("ALTER TABLE ".safe_pfx($table)." $alter", $debug); 584 } 585 586 /** 587 * Locks a table. 588 * 589 * The $table argument accepts comma-separated list of table names, if you need 590 * to lock multiple tables at once. 591 * 592 * @param string $table The table 593 * @param string $type The lock type 594 * @param bool $debug Dump the query 595 * @return bool TRUE if the tables are locked 596 * @since 4.6.0 597 * @example 598 * if (safe_lock('myTable')) 599 * { 600 * echo "'myTable' is 'write' locked."; 601 * } 602 */ 603 604 function safe_lock($table, $type = 'write', $debug = false) 605 { 606 return (bool) safe_query("LOCK TABLES ".join(' '.$type.', ', doArray(do_list_unique($table), 'safe_pfx')).' '.$type, $debug); 607 } 608 609 /** 610 * Unlocks tables. 611 * 612 * @param bool $debug Dump the query 613 * @return bool TRUE if tables aren't locked 614 * @since 4.6.0 615 * @example 616 * if (safe_unlock()) 617 * { 618 * echo 'Tables are unlocked.'; 619 * } 620 */ 621 622 function safe_unlock($debug = false) 623 { 624 return (bool) safe_query("UNLOCK TABLES", $debug); 625 } 626 627 /** 628 * Gets an array of information about an index. 629 * 630 * @param string $table The table 631 * @param string $index The index 632 * @param bool $debug Dump the query 633 * @return array|bool Array of information about the index, or FALSE on error 634 * @since 4.6.0 635 * @example 636 * if ($index = safe_index('myTable', 'myIndex')) 637 * { 638 * echo "'myIndex' found in 'myTable' with the type of {$index['Index_type']}."; 639 * } 640 */ 641 642 function safe_index($table, $index, $debug = false) 643 { 644 $index = strtolower($index); 645 646 if ($r = safe_show("INDEX", $table, $debug)) { 647 foreach ($r as $a) { 648 if (strtolower($a['Key_name']) === $index) { 649 return $a; 650 } 651 } 652 } 653 654 return false; 655 } 656 657 /** 658 * Creates an index. 659 * 660 * @param string $table The table 661 * @param string $columns Indexed columns 662 * @param string $name The name 663 * @param string $index The index. Either 'unique', 'fulltext', 'spatial' (default = standard index) 664 * @param string $type The index type 665 * @param bool $debug Dump the query 666 * @return bool TRUE if index exists 667 * @since 4.6.0 668 * @example 669 * if (safe_create_index('myTable', 'col1(11), col2(11)', 'myIndex')) 670 * { 671 * echo "'myIndex' exists in 'myTable'."; 672 * } 673 */ 674 675 function safe_create_index($table, $columns, $name, $index = '', $type = '', $debug = false) 676 { 677 if (safe_index($table, $name, $debug)) { 678 return true; 679 } 680 681 if (strtolower($name) == 'primary') { 682 $q = "ALTER TABLE ".safe_pfx($table)." ADD PRIMARY KEY ($columns)"; 683 } else { 684 $q = "CREATE $index INDEX `$name` ".($type ? " USING ".$type : '')." ON ".safe_pfx($table)." ($columns)"; 685 } 686 687 return (bool) safe_query($q, $debug); 688 } 689 690 /** 691 * Removes an index. 692 * 693 * @param string $table The table 694 * @param string $index The index 695 * @param bool $debug Dump the query 696 * @return bool TRUE if the index no longer exists 697 * @since 4.6.0 698 * @example 699 * if (safe_drop_index('myTable', 'primary')) 700 * { 701 * echo "Primary key no longer exists in 'myTable'."; 702 * } 703 */ 704 705 function safe_drop_index($table, $index, $debug = false) 706 { 707 if (!safe_index($table, $index, $debug)) { 708 return true; 709 } 710 711 if (strtolower($index) === 'primary') { 712 $q = "ALTER TABLE ".safe_pfx($table)." DROP PRIMARY KEY"; 713 } else { 714 $q = "DROP INDEX `$index` ON ".safe_pfx($table); 715 } 716 717 return (bool) safe_query($q, $debug); 718 } 719 720 /** 721 * Optimises a table. 722 * 723 * @param string $table The table 724 * @param bool $debug Dump query 725 * @return bool FALSE on error 726 * @example 727 * if (safe_optimize('myTable')) 728 * { 729 * echo "myTable optimised successfully."; 730 * } 731 */ 732 733 function safe_optimize($table, $debug = false) 734 { 735 return (bool) safe_query("OPTIMIZE TABLE ".safe_pfx($table), $debug); 736 } 737 738 /** 739 * Repairs a table. 740 * 741 * @param string $table The table 742 * @param bool $debug Dump query 743 * @return bool FALSE on error 744 * @example 745 * if (safe_repair('myTable')) 746 * { 747 * echo "myTable repaired successfully."; 748 * } 749 */ 750 751 function safe_repair($table, $debug = false) 752 { 753 return (bool) safe_query("REPAIR TABLE ".safe_pfx($table), $debug); 754 } 755 756 /** 757 * Truncates a table. 758 * 759 * Running this function empties a table completely, resets indexes and the auto 760 * increment value. 761 * 762 * @param string $table The table 763 * @param bool $debug Dump query 764 * @return bool TRUE if the table is empty 765 * @see safe_delete() 766 * @since 4.6.0 767 * @example 768 * if (safe_truncate('myTable')) 769 * { 770 * echo "myTable emptied successfully."; 771 * } 772 */ 773 774 function safe_truncate($table, $debug = false) 775 { 776 return (bool) safe_query("TRUNCATE TABLE ".safe_pfx($table), $debug); 777 } 778 779 /** 780 * Removes a table. 781 * 782 * This function removes all data and definitions associated with a table. 783 * 784 * @param string $table The table 785 * @param bool $debug Dump query 786 * @return bool TRUE if the table no longer exists 787 * @since 4.6.0 788 * @example 789 * if (safe_drop('myTable')) 790 * { 791 * echo "'myTable' no longer exists."; 792 * } 793 */ 794 795 function safe_drop($table, $debug = false) 796 { 797 return (bool) safe_query("DROP TABLE IF EXISTS ".safe_pfx($table), $debug); 798 } 799 800 /** 801 * Creates a table. 802 * 803 * Creates a table with the given name. This table will be created with 804 * identical properties to core tables, ensuring the best possible compatibility. 805 * 806 * @param string $table The table 807 * @param string $definition The create definition 808 * @param string $options Table options 809 * @param bool $debug Dump the query 810 * @return bool TRUE if table exists 811 * @since 4.6.0 812 * @example 813 * if (safe_create('myTable', "id int(11)")) 814 * { 815 * echo "'myTable' exists."; 816 * } 817 */ 818 819 function safe_create($table, $definition, $options = '', $debug = false) 820 { 821 global $DB; 822 823 foreach ($DB->table_options as $name => $value) { 824 $options .= ' '.strtoupper($name).' = '.$value; 825 } 826 827 $q = "CREATE TABLE IF NOT EXISTS ".safe_pfx($table)." ($definition) $options"; 828 829 return (bool) safe_query($q, $debug); 830 } 831 832 /** 833 * Renames a table. 834 * 835 * @param string $table The table 836 * @param string $newname The new name 837 * @param bool $debug Dump the query 838 * @return bool FALSE on error 839 * @since 4.6.0 840 */ 841 842 function safe_rename($table, $newname, $debug = false) 843 { 844 return (bool) safe_query("RENAME TABLE ".safe_pfx($table)." TO ".safe_pfx($newname), $debug); 845 } 846 847 /** 848 * Gets a field from a row. 849 * 850 * If the query results in multiple matches, the first row returned is used. 851 * 852 * @param string $thing The field 853 * @param string $table The table 854 * @param string $where The where clause 855 * @param bool $debug Dump query 856 * @return mixed The field or FALSE on error 857 * @example 858 * if ($field = safe_field("column", 'table', "1 = 1")) 859 * { 860 * echo $field; 861 * } 862 */ 863 864 function safe_field($thing, $table, $where, $debug = false) 865 { 866 $q = "SELECT $thing FROM ".safe_pfx_j($table)." WHERE $where"; 867 $r = safe_query($q, $debug); 868 869 if (@mysqli_num_rows($r) > 0) { 870 $row = mysqli_fetch_row($r); 871 mysqli_free_result($r); 872 873 return $row[0]; 874 } 875 876 return false; 877 } 878 879 /** 880 * Gets a list of values from a table's column. 881 * 882 * @param string $thing The column 883 * @param string $table The table 884 * @param string $where The where clause 885 * @param bool $debug Dump query 886 * @return array 887 */ 888 889 function safe_column($thing, $table, $where = '1', $debug = false) 890 { 891 if (is_array($thing)) { 892 list($index, $thing) = $thing + array(null, '*'); 893 $multiple = $thing === '*' || strpos($thing, ',') !== false; 894 $things = $thing === '*' ? $thing : implode(',', do_list_unique($index.','.$thing)); 895 } else { 896 $things = $thing; 897 } 898 899 $q = "SELECT $things FROM ".safe_pfx_j($table)." WHERE $where"; 900 $rs = getRows($q, $debug); 901 902 if ($rs) { 903 if (isset($index) && isset($rs[0][$index])) { 904 $out = array_column($rs, $multiple ? null : $thing, $index); 905 } elseif (isset($rs[0][$thing])) { 906 $out = array_column($rs, $thing, $thing); 907 } else { 908 foreach ($rs as $a) { 909 $v = array_shift($a); 910 $out[$v] = $v; 911 } 912 } 913 } 914 915 return empty($out) ? array() : $out; 916 } 917 918 /** 919 * Fetch a column as an numeric array. 920 * 921 * @param string $thing The field 922 * @param string $table The table 923 * @param string $where The where clause 924 * @param bool $debug Dump query 925 * @return array Numeric array of column values 926 * @since 4.5.0 927 */ 928 929 function safe_column_num($thing, $table, $where, $debug = false) 930 { 931 $q = "SELECT $thing FROM ".safe_pfx_j($table)." WHERE $where"; 932 $rs = getRows($q, $debug); 933 if ($rs) { 934 foreach ($rs as $a) { 935 $v = array_shift($a); 936 $out[] = $v; 937 } 938 939 return $out; 940 } 941 942 return array(); 943 } 944 945 /** 946 * Gets a row from a table as an associative array. 947 * 948 * @param string $things The select clause 949 * @param string $table The table 950 * @param string $where The where clause 951 * @param bool $debug Dump query 952 * @return array 953 * @see safe_rows() 954 * @see safe_rows_start() 955 * @uses getRow() 956 * @example 957 * if ($row = safe_row("column", 'table', "1 = 1")) 958 * { 959 * echo $row['column']; 960 * } 961 */ 962 963 function safe_row($things, $table, $where, $debug = false) 964 { 965 $q = "SELECT $things FROM ".safe_pfx_j($table)." WHERE $where"; 966 $rs = getRow($q, $debug); 967 968 if ($rs) { 969 return $rs; 970 } 971 972 return array(); 973 } 974 975 /** 976 * Gets a list rows from a table as an associative array. 977 * 978 * When working with large result sets remember that this function, unlike 979 * safe_rows_start(), loads results to memory all at once. To optimise 980 * performance in such situations, use safe_rows_start() instead. 981 * 982 * @param string $things The select clause 983 * @param string $table The table 984 * @param string $where The where clause 985 * @param bool $debug Dump query 986 * @return array Returns an empty array if no matches are found 987 * @see safe_row() 988 * @see safe_rows_start() 989 * @uses getRows() 990 * @example 991 * $rs = safe_rows("column", 'table', "1 = 1"); 992 * foreach ($rs as $row) 993 * { 994 * echo $row['column']; 995 * } 996 */ 997 998 function safe_rows($things, $table, $where, $debug = false) 999 { 1000 $q = "SELECT $things FROM ".safe_pfx_j($table)." WHERE $where"; 1001 $rs = getRows($q, $debug); 1002 1003 if ($rs) { 1004 return $rs; 1005 } 1006 1007 return array(); 1008 } 1009 1010 /** 1011 * Selects rows from a table and returns result as a resource. 1012 * 1013 * @param string $things The select clause 1014 * @param string $table The table 1015 * @param string $where The where clause 1016 * @param bool $debug Dump query 1017 * @return resource|bool A result resource or FALSE on error 1018 * @see nextRow() 1019 * @see numRows() 1020 * @example 1021 * if ($rs = safe_rows_start("column", 'table', "1 = 1")) 1022 * { 1023 * while ($row = nextRow($rs)) 1024 * { 1025 * echo $row['column']; 1026 * } 1027 * } 1028 */ 1029 1030 function safe_rows_start($things, $table, $where, $debug = false) 1031 { 1032 $q = "SELECT $things FROM ".safe_pfx_j($table)." WHERE $where"; 1033 1034 return startRows($q, $debug); 1035 } 1036 1037 /** 1038 * Counts number of rows in a table. 1039 * 1040 * @param string $table The table 1041 * @param string $where The where clause 1042 * @param bool $debug Dump query 1043 * @return int|bool Number of rows or FALSE on error 1044 * @example 1045 * if (($count = safe_count("table", "1 = 1")) !== false) 1046 * { 1047 * echo "myTable contains {$count} rows."; 1048 * } 1049 */ 1050 1051 function safe_count($table, $where, $debug = false) 1052 { 1053 return getCount($table, $where, $debug); 1054 } 1055 1056 /** 1057 * Shows information about a table. 1058 * 1059 * @param string $thing The information to show, e.g. "index", "columns" 1060 * @param string $table The table 1061 * @param bool $debug Dump query 1062 * @return array 1063 * @example 1064 * print_r(safe_show('columns', 'myTable')); 1065 */ 1066 1067 function safe_show($thing, $table, $debug = false) 1068 { 1069 $q = "SHOW $thing FROM ".safe_pfx($table).""; 1070 $rs = getRows($q, $debug); 1071 1072 if ($rs) { 1073 return $rs; 1074 } 1075 1076 return array(); 1077 } 1078 1079 /** 1080 * Gets a field from a row. 1081 * 1082 * This function offers an alternative short-hand syntax to safe_field(). 1083 * Most notably, this internally manages value escaping. 1084 * 1085 * @param string $col The field to get 1086 * @param string $table The table 1087 * @param string $key The searched field 1088 * @param string $val The searched value 1089 * @param bool $debug Dump query 1090 * @return mixed The field or FALSE on error 1091 * @see safe_field() 1092 * @example 1093 * echo fetch('name', 'myTable', 'id', 12); 1094 */ 1095 1096 function fetch($col, $table, $key, $val, $debug = false) 1097 { 1098 $key = doSlash($key); 1099 $val = (is_int($val)) ? $val : "'".doSlash($val)."'"; 1100 $q = "SELECT $col FROM ".safe_pfx($table)." WHERE `$key` = $val LIMIT 1"; 1101 1102 if ($r = safe_query($q, $debug)) { 1103 if (mysqli_num_rows($r) > 0) { 1104 $row = mysqli_fetch_row($r); 1105 mysqli_free_result($r); 1106 1107 return $row[0]; 1108 } 1109 1110 return ''; 1111 } 1112 1113 return false; 1114 } 1115 1116 /** 1117 * Gets a row as an associative array. 1118 * 1119 * @param string $query The SQL statement to execute 1120 * @param bool $debug Dump query 1121 * @return array|bool The row's values or FALSE on error 1122 * @see safe_row() 1123 */ 1124 1125 function getRow($query, $debug = false) 1126 { 1127 if ($r = safe_query($query, $debug)) { 1128 $row = (mysqli_num_rows($r) > 0) ? mysqli_fetch_assoc($r) : false; 1129 mysqli_free_result($r); 1130 1131 return $row; 1132 } 1133 1134 return false; 1135 } 1136 1137 /** 1138 * Gets multiple rows as an associative array. 1139 * 1140 * If you need to run simple SELECT queries that select rows from a table, 1141 * please see safe_rows() and safe_rows_start() first. 1142 * 1143 * @param string $query The SQL statement to execute 1144 * @param bool $debug Dump query 1145 * @return array|bool The rows or FALSE on error 1146 * @see safe_rows() 1147 * @example 1148 * if ($rs = getRows('SELECT * FROM table')) 1149 * { 1150 * print_r($rs); 1151 * } 1152 */ 1153 1154 function getRows($query, $debug = false) 1155 { 1156 if ($r = safe_query($query, $debug)) { 1157 if (mysqli_num_rows($r) > 0) { 1158 while ($a = mysqli_fetch_assoc($r)) { 1159 $out[] = $a; 1160 } 1161 1162 mysqli_free_result($r); 1163 1164 return $out; 1165 } 1166 } 1167 1168 return false; 1169 } 1170 1171 /** 1172 * Executes an SQL statement and returns results. 1173 * 1174 * This function is identical to safe_query() apart from the missing 1175 * $unbuf argument. 1176 * 1177 * @param string $query The SQL statement to execute 1178 * @param bool $debug Dump query 1179 * @return mixed 1180 * @see safe_query() 1181 * @access private 1182 */ 1183 1184 function startRows($query, $debug = false) 1185 { 1186 return safe_query($query, $debug); 1187 } 1188 1189 /** 1190 * Gets a next row as an associative array from a result resource. 1191 * 1192 * The function will free up memory reserved by the result resource if called 1193 * after the last row. 1194 * 1195 * @param resource $r The result resource 1196 * @return array|bool The row, or FALSE if there are no more rows 1197 * @see safe_rows_start() 1198 * @example 1199 * if ($rs = safe_rows_start("column", 'table', "1 = 1")) 1200 * { 1201 * while ($row = nextRow($rs)) 1202 * { 1203 * echo $row['column']; 1204 * } 1205 * } 1206 */ 1207 1208 function nextRow($r) 1209 { 1210 $row = mysqli_fetch_assoc($r); 1211 1212 if ($row === false) { 1213 mysqli_free_result($r); 1214 } 1215 1216 return $row; 1217 } 1218 1219 /** 1220 * Gets the number of rows in a result resource. 1221 * 1222 * @param resource $r The result resource 1223 * @return int|bool The number of rows or FALSE on error 1224 * @see safe_rows_start() 1225 * @example 1226 * if ($rs = safe_rows_start("column", 'table', "1 = 1")) 1227 * { 1228 * echo numRows($rs); 1229 * } 1230 */ 1231 1232 function numRows($r) 1233 { 1234 return mysqli_num_rows($r); 1235 } 1236 1237 /** 1238 * Gets the contents of a single cell from a resource set. 1239 * 1240 * @param string $query The SQL statement to execute 1241 * @param bool $debug Dump query 1242 * @return string|bool The contents, empty if no results were found or FALSE on error 1243 */ 1244 1245 function getThing($query, $debug = false) 1246 { 1247 if ($r = safe_query($query, $debug)) { 1248 if (mysqli_num_rows($r) != 0) { 1249 $row = mysqli_fetch_row($r); 1250 mysqli_free_result($r); 1251 1252 return $row[0]; 1253 } 1254 1255 return ''; 1256 } 1257 1258 return false; 1259 } 1260 1261 /** 1262 * Return values of one column from multiple rows in a num indexed array. 1263 * 1264 * @param string $query The SQL statement to execute 1265 * @param bool $debug Dump query 1266 * @return array 1267 */ 1268 1269 function getThings($query, $debug = false) 1270 { 1271 $rs = getRows($query, $debug); 1272 1273 if ($rs) { 1274 foreach ($rs as $a) { 1275 $out[] = array_shift($a); 1276 } 1277 1278 return $out; 1279 } 1280 1281 return array(); 1282 } 1283 1284 /** 1285 * Counts number of rows in a table. 1286 * 1287 * This function is identical to safe_count(). 1288 * 1289 * @param array|string $table The table/thing to count 1290 * @param string $where The where clause 1291 * @param bool $debug Dump query 1292 * @return int|bool Number of rows or FALSE on error 1293 * @access private 1294 * @see safe_count() 1295 */ 1296 1297 function getCount($table, $where, $debug = false) 1298 { 1299 if (is_array($table)) { 1300 list($table, $thing) = $table + array(null, '*'); 1301 } 1302 1303 $thing = isset($thing) ? doSlash($thing) : '*'; 1304 1305 return getThing("SELECT COUNT($thing) FROM ".safe_pfx_j($table)." WHERE $where", $debug); 1306 } 1307 1308 /** 1309 * Gets a tree structure. 1310 * 1311 * This function is used by categories. 1312 * 1313 * @param string $root The roots 1314 * @param string $type The type 1315 * @param string $where The where clause 1316 * @param string $tbl The table 1317 * @return array 1318 */ 1319 1320 function getTree($root, $type = 'article', $where = "1 = 1", $tbl = 'txp_category', $depth = true) 1321 { 1322 if (is_array($root)) { 1323 $names = true; 1324 } else { 1325 $root = do_list_unique($root); 1326 } 1327 1328 if (empty($root)) { 1329 return array(); 1330 } 1331 1332 if ($depth && $depth !== true) { 1333 $levels = array_map('intval', do_list($depth, array(',', '-'))); 1334 } 1335 1336 $type = doSlash($type); 1337 $out = $border = array(); 1338 1339 $rows = safe_rows( 1340 "lft AS l, rgt AS r", 1341 $tbl, 1342 "name IN ('".implode("','", doSlash($root))."') AND type = '$type'" 1343 ); 1344 1345 foreach ($rows as $rs) { 1346 extract($rs); 1347 $border[] = $depth ? "lft BETWEEN $l AND $r" : "lft=$l AND rgt=$r"; 1348 } 1349 1350 $border = implode(' OR ', $border); 1351 $right = array(); 1352 1353 $rs = $border ? safe_rows_start( 1354 isset($names) ? "id, name, lft, rgt" : "id, name, lft, rgt, parent, title", 1355 $tbl, 1356 "($border) AND type = '$type' AND name != 'root' AND $where ORDER BY lft ASC" 1357 ) : null; 1358 1359 while ($rs and $row = nextRow($rs)) { 1360 extract($row); 1361 1362 while (($level = count($right)) > 0 && $right[count($right) - 1] < $rgt) { 1363 array_pop($right); 1364 } 1365 1366 if (!isset($levels) || in_array($level, $levels)) { 1367 if (isset($names)) { 1368 $out[$id] = $name; 1369 } else { 1370 $out[] = array( 1371 'id' => $id, 1372 'name' => $name, 1373 'title' => $title, 1374 'level' => $level, 1375 'children' => ($rgt - $lft - 1) / 2, 1376 'parent' => $parent, 1377 ); 1378 } 1379 } 1380 1381 $right[] = $rgt; 1382 } 1383 1384 return $out; 1385 } 1386 1387 /** 1388 * Gets a target/path/up/to/root array. 1389 * 1390 * This function is used by categories. 1391 * 1392 * @param string $target The target 1393 * @param string $type The category type 1394 * @param string $tbl The table 1395 * @param string $root The root (excluded) 1396 * @return array 1397 */ 1398 1399 function getRootPath($target, $type = 'article', $tbl = 'txp_category', $root = 'root') 1400 { 1401 static $cache = array( 1402 'article' => array(), 1403 'file' => array(), 1404 'image' => array(), 1405 'link' => array() 1406 ); 1407 $out = array(); 1408 1409 if (!isset($cache[$type])) { 1410 return $out; 1411 } elseif (empty($cache[$type])) { 1412 foreach (safe_rows('id, name, parent, title, description', $tbl, "type = '".doSlash($type)."'") as $row) { 1413 $cache[$type][$row['name']] = $row; 1414 } 1415 } 1416 1417 while ($target !== $root && isset($cache[$type][$target])) { 1418 $out[] = $cache[$type][$target]; 1419 $target = $cache[$type][$target]['parent']; 1420 } 1421 1422 return $out; 1423 } 1424 1425 /** 1426 * Gets a tree path up to a target. 1427 * 1428 * This function is used by categories. 1429 * 1430 * @param string $target The target 1431 * @param string $type The category type 1432 * @param string $tbl The table 1433 * @return array 1434 */ 1435 1436 function getTreePath($target, $type, $tbl = 'txp_category') 1437 { 1438 $rs = safe_row( 1439 "lft AS l, rgt AS r", 1440 $tbl, 1441 "name = '".doSlash($target)."' AND type = '".doSlash($type)."'" 1442 ); 1443 1444 if (!$rs) { 1445 return array(); 1446 } 1447 1448 extract($rs); 1449 1450 $rs = safe_rows_start( 1451 "*", 1452 $tbl, 1453 "lft <= $l AND rgt >= $r AND type = '".doSlash($type)."' ORDER BY lft ASC" 1454 ); 1455 1456 $out = array(); 1457 $right = array(); 1458 1459 while ($rs and $row = nextRow($rs)) { 1460 extract($row); 1461 1462 while (count($right) > 0 && $right[count($right) - 1] < $rgt) { 1463 array_pop($right); 1464 } 1465 1466 $out[] = array( 1467 'id' => $id, 1468 'name' => $name, 1469 'title' => $title, 1470 'description' => $description, 1471 'type' => $type, 1472 'level' => count($right), 1473 'children' => ($rgt - $lft - 1) / 2, 1474 ); 1475 1476 $right[] = $rgt; 1477 } 1478 1479 return $out; 1480 } 1481 1482 /** 1483 * Rebuilds a nested tree set. 1484 * 1485 * This function is used by categories. 1486 * 1487 * @param string $parent The parent 1488 * @param string $left The left ID 1489 * @param string $type The category type 1490 * @param string $tbl The table 1491 * @return int The next left ID 1492 */ 1493 1494 function rebuild_tree($parent, $left, $type, $tbl = 'txp_category') 1495 { 1496 $left = intval($left); 1497 $right = $left + 1; 1498 1499 $parents[] = $parent; 1500 $parent = doSlash($parent); 1501 $stype = doSlash($type); 1502 1503 $result = safe_column( 1504 "name", 1505 $tbl, 1506 "parent = '$parent' AND type = '$stype' ORDER BY name" 1507 ); 1508 1509 foreach ($result as $row) { 1510 $right = rebuild_tree($row, $right, $type, $tbl); 1511 } 1512 1513 safe_update( 1514 $tbl, 1515 "lft = $left, rgt = $right", 1516 "name = '$parent' AND type = '$stype'" 1517 ); 1518 1519 return $right + 1; 1520 } 1521 1522 /** 1523 * Rebuilds a tree. 1524 * 1525 * This function is used by categories. 1526 * 1527 * @param string $type The category type 1528 * @param string $tbl The table 1529 * @return int The next left ID 1530 */ 1531 1532 function rebuild_tree_full($type, $tbl = 'txp_category') 1533 { 1534 $stype = doSlash($type); 1535 // Fix circular references, otherwise rebuild_tree() could get stuck in a loop. 1536 safe_update($tbl, "parent = ''", "type = '".$stype."' AND name = 'root'"); 1537 safe_update($tbl, "lft = 0, rgt = 0", "type = '".$stype."'"); 1538 rebuild_tree('root', 1, $type, $tbl); 1539 1540 // Attach lost nodes to root 1541 if (safe_count($tbl, "type = '".$stype."' AND rgt = 0")) { 1542 safe_update($tbl, "parent = 'root'", "type = '".$stype."' AND rgt = 0"); 1543 rebuild_tree('root', 1, $type, $tbl); 1544 } 1545 } 1546 1547 /** 1548 * Returns an error page. 1549 * 1550 * This function is used to return a bailout page when resolving database 1551 * connections fails. Sends a HTTP 503 error status and displays the last logged 1552 * MySQL error message. 1553 * 1554 * @return string HTML5 document 1555 * @access private 1556 */ 1557 1558 function db_down() 1559 { 1560 global $DB; 1561 // 503 status might discourage search engines from indexing or caching the 1562 // error message. 1563 txp_status_header('503 Service Unavailable'); 1564 if (is_object($DB)) { 1565 $error = txpspecialchars(mysqli_error($DB->link)); 1566 } else { 1567 $error = '$DB object is not available.'; 1568 } 1569 1570 return <<<eod 1571 <!DOCTYPE html> 1572 <html lang="en"> 1573 <head> 1574 <meta charset="utf-8"> 1575 <meta name="robots" content="noindex"> 1576 <title>Database unavailable</title> 1577 </head> 1578 <body> 1579 <p>Database unavailable.</p> 1580 <!-- $error --> 1581 </body> 1582 </html> 1583 eod; 1584 } 1585 1586 /** 1587 * Replacement for SQL NOW() 1588 * 1589 * This function can be used when constructing SQL SELECT queries as a 1590 * replacement for the NOW() function to allow the SQL server to cache the 1591 * queries. Should only be used when comparing with the Posted or Expired 1592 * columns from the textpattern (articles) table, the Created column from 1593 * the txp_file table or the Date column from the txp_link table. 1594 * 1595 * @param string $type Column name, lower case (one of 'posted', 'expires', 'created', 'date') 1596 * @param bool $update Force update 1597 * @return string SQL query string partial 1598 */ 1599 1600 function now($type, $update = false) 1601 { 1602 static $nows = array(); 1603 static $time = null; 1604 1605 if (!in_array($type = strtolower($type), array('posted', 'expires', 'created', 'date'))) { 1606 return false; 1607 } 1608 1609 if (isset($nows[$type])) { 1610 $now = $nows[$type]; 1611 } else { 1612 if ($time === null) { 1613 $time = time(); 1614 } 1615 1616 $pref = 'sql_now_'.$type; 1617 $now = get_pref($pref, $time - 1); 1618 1619 if ($time > $now or $update) { 1620 $table = ($type === 'date') ? 'txp_link' : (($type === 'created') ? 'txp_file' : 'textpattern'); 1621 $where = '1=1 having utime > '.$time.' order by utime asc limit 1'; 1622 $now = safe_field('unix_timestamp('.$type.') as utime', $table, $where); 1623 $now = ($now === false) ? 2147483647 : intval($now) - 1; 1624 update_pref($pref, $now); 1625 $nows[$type] = $now; 1626 } 1627 } 1628 1629 return 'from_unixtime('.$now.')'; 1630 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
title