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