include/dba.php

   1 <?php
   2 require_once("dbm.php");
   3 require_once('include/datetime.php');
   4
   5 /**
   6  * @class MySQL database class
   7  *
   8  * For debugging, insert 'dbg(1);' anywhere in the program flow.
   9  * dbg(0); will turn it off. Logging is performed at LOGGER_DATA level.
  10  * When logging, all binary info is converted to text and html entities are escaped so that
  11  * the debugging stream is safe to view within both terminals and web pages.
  12  *
  13  * This class is for the low level database stuff that does driver specific things.
  14  */
  15
  16 class dba {
  17
  18         private $debug = 0;
  19         private $db;
  20         private $result;
  21         private $driver;
  22         public  $connected = false;
  23         public  $error = false;
  24         private $_server_info = '';
  25         private static $dbo;
  26         private static $relation = array();
  27
  28         function __construct($server, $user, $pass, $db, $install = false) {
  29                 $a = get_app();
  30
  31                 $stamp1 = microtime(true);
  32
  33                 $server = trim($server);
  34                 $user = trim($user);
  35                 $pass = trim($pass);
  36                 $db = trim($db);
  37
  38                 if (!(strlen($server) && strlen($user))) {
  39                         $this->connected = false;
  40                         $this->db = null;
  41                         return;
  42                 }
  43
  44                 if ($install) {
  45                         if (strlen($server) && ($server !== 'localhost') && ($server !== '127.0.0.1')) {
  46                                 if (! dns_get_record($server, DNS_A + DNS_CNAME + DNS_PTR)) {
  47                                         $this->error = sprintf(t('Cannot locate DNS info for database server \'%s\''), $server);
  48                                         $this->connected = false;
  49                                         $this->db = null;
  50                                         return;
  51                                 }
  52                         }
  53                 }
  54
  55                 if (class_exists('\PDO') && in_array('mysql', PDO::getAvailableDrivers())) {
  56                         $this->driver = 'pdo';
  57                         $connect = "mysql:host=".$server.";dbname=".$db;
  58                         if (isset($a->config["system"]["db_charset"])) {
  59                                 $connect .= ";charset=".$a->config["system"]["db_charset"];
  60                         }
  61                         $this->db = @new PDO($connect, $user, $pass);
  62                         if (!$this->db->errorCode()) {
  63                                 $this->connected = true;
  64                         }
  65                 } elseif (class_exists('mysqli')) {
  66                         $this->driver = 'mysqli';
  67                         $this->db = @new mysqli($server,$user,$pass,$db);
  68                         if (!mysqli_connect_errno()) {
  69                                 $this->connected = true;
  70
  71                                 if (isset($a->config["system"]["db_charset"])) {
  72                                         $this->db->set_charset($a->config["system"]["db_charset"]);
  73                                 }
  74                         }
  75                 } elseif (function_exists('mysql_connect')) {
  76                         $this->driver = 'mysql';
  77                         $this->db = mysql_connect($server,$user,$pass);
  78                         if ($this->db && mysql_select_db($db,$this->db)) {
  79                                 $this->connected = true;
  80
  81                                 if (isset($a->config["system"]["db_charset"])) {
  82                                         mysql_set_charset($a->config["system"]["db_charset"], $this->db);
  83                                 }
  84                         }
  85                 } else {
  86                         // No suitable SQL driver was found.
  87                         if (!$install) {
  88                                 system_unavailable();
  89                         }
  90                 }
  91
  92                 if (!$this->connected) {
  93                         $this->db = null;
  94                         if (!$install) {
  95                                 system_unavailable();
  96                         }
  97                 }
  98                 $a->save_timestamp($stamp1, "network");
  99
 100                 self::$dbo = $this;
 101         }
 102
 103         /**
 104          * @brief Returns the MySQL server version string
 105          *
 106          * This function discriminate between the deprecated mysql API and the current
 107          * object-oriented mysqli API. Example of returned string: 5.5.46-0+deb8u1
 108          *
 109          * @return string
 110          */
 111         public function server_info() {
 112                 if ($this->_server_info == '') {
 113                         switch ($this->driver) {
 114                                 case 'pdo':
 115                                         $this->_server_info = $this->db->getAttribute(PDO::ATTR_SERVER_VERSION);
 116                                         break;
 117                                 case 'mysqli':
 118                                         $this->_server_info = $this->db->server_info;
 119                                         break;
 120                                 case 'mysql':
 121                                         $this->_server_info = mysql_get_server_info($this->db);
 122                                         break;
 123                         }
 124                 }
 125                 return $this->_server_info;
 126         }
 127
 128         /**
 129          * @brief Returns the selected database name
 130          *
 131          * @return string
 132          */
 133         public function database_name() {
 134                 $r = $this->q("SELECT DATABASE() AS `db`");
 135
 136                 return $r[0]['db'];
 137         }
 138
 139         /**
 140          * @brief Analyze a database query and log this if some conditions are met.
 141          *
 142          * @param string $query The database query that will be analyzed
 143          */
 144         public function log_index($query) {
 145                 $a = get_app();
 146
 147                 if ($a->config["system"]["db_log_index"] == "") {
 148                         return;
 149                 }
 150
 151                 // Don't explain an explain statement
 152                 if (strtolower(substr($query, 0, 7)) == "explain") {
 153                         return;
 154                 }
 155
 156                 // Only do the explain on "select", "update" and "delete"
 157                 if (!in_array(strtolower(substr($query, 0, 6)), array("select", "update", "delete"))) {
 158                         return;
 159                 }
 160
 161                 $r = $this->q("EXPLAIN ".$query);
 162                 if (!dbm::is_result($r)) {
 163                         return;
 164                 }
 165
 166                 $watchlist = explode(',', $a->config["system"]["db_log_index_watch"]);
 167                 $blacklist = explode(',', $a->config["system"]["db_log_index_blacklist"]);
 168
 169                 foreach ($r AS $row) {
 170                         if ((intval($a->config["system"]["db_loglimit_index"]) > 0)) {
 171                                 $log = (in_array($row['key'], $watchlist) AND
 172                                         ($row['rows'] >= intval($a->config["system"]["db_loglimit_index"])));
 173                         } else {
 174                                 $log = false;
 175                         }
 176
 177                         if ((intval($a->config["system"]["db_loglimit_index_high"]) > 0) AND ($row['rows'] >= intval($a->config["system"]["db_loglimit_index_high"]))) {
 178                                 $log = true;
 179                         }
 180
 181                         if (in_array($row['key'], $blacklist) OR ($row['key'] == "")) {
 182                                 $log = false;
 183                         }
 184
 185                         if ($log) {
 186                                 $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
 187                                 @file_put_contents($a->config["system"]["db_log_index"], datetime_convert()."\t".
 188                                                 $row['key']."\t".$row['rows']."\t".$row['Extra']."\t".
 189                                                 basename($backtrace[1]["file"])."\t".
 190                                                 $backtrace[1]["line"]."\t".$backtrace[2]["function"]."\t".
 191                                                 substr($query, 0, 2000)."\n", FILE_APPEND);
 192                         }
 193                 }
 194         }
 195
 196         public function q($sql, $onlyquery = false) {
 197                 $a = get_app();
 198
 199                 if (!$this->db || !$this->connected) {
 200                         return false;
 201                 }
 202
 203                 $this->error = '';
 204
 205                 $connstr = ($this->connected() ? "Connected" : "Disonnected");
 206
 207                 $stamp1 = microtime(true);
 208
 209                 $orig_sql = $sql;
 210
 211                 if (x($a->config,'system') && x($a->config['system'], 'db_callstack')) {
 212                         $sql = "/*".$a->callstack()." */ ".$sql;
 213                 }
 214
 215                 $columns = 0;
 216
 217                 switch ($this->driver) {
 218                         case 'pdo':
 219                                 $result = @$this->db->query($sql);
 220                                 // Is used to separate between queries that returning data - or not
 221                                 if (!is_bool($result)) {
 222                                         $columns = $result->columnCount();
 223                                 }
 224                                 break;
 225                         case 'mysqli':
 226                                 $result = @$this->db->query($sql);
 227                                 break;
 228                         case 'mysql':
 229                                 $result = @mysql_query($sql,$this->db);
 230                                 break;
 231                 }
 232                 $stamp2 = microtime(true);
 233                 $duration = (float)($stamp2 - $stamp1);
 234
 235                 $a->save_timestamp($stamp1, "database");
 236
 237                 if (strtolower(substr($orig_sql, 0, 6)) != "select") {
 238                         $a->save_timestamp($stamp1, "database_write");
 239                 }
 240                 if (x($a->config,'system') && x($a->config['system'],'db_log')) {
 241                         if (($duration > $a->config["system"]["db_loglimit"])) {
 242                                 $duration = round($duration, 3);
 243                                 $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
 244                                 @file_put_contents($a->config["system"]["db_log"], datetime_convert()."\t".$duration."\t".
 245                                                 basename($backtrace[1]["file"])."\t".
 246                                                 $backtrace[1]["line"]."\t".$backtrace[2]["function"]."\t".
 247                                                 substr($sql, 0, 2000)."\n", FILE_APPEND);
 248                         }
 249                 }
 250
 251                 switch ($this->driver) {
 252                         case 'pdo':
 253                                 $errorInfo = $this->db->errorInfo();
 254                                 if ($errorInfo) {
 255                                         $this->error = $errorInfo[2];
 256                                         $this->errorno = $errorInfo[1];
 257                                 }
 258                                 break;
 259                         case 'mysqli':
 260                                 if ($this->db->errno) {
 261                                         $this->error = $this->db->error;
 262                                         $this->errorno = $this->db->errno;
 263                                 }
 264                                 break;
 265                         case 'mysql':
 266                                 if (mysql_errno($this->db)) {
 267                                         $this->error = mysql_error($this->db);
 268                                         $this->errorno = mysql_errno($this->db);
 269                                 }
 270                                 break;
 271                 }
 272                 if (strlen($this->error)) {
 273                         logger('DB Error ('.$connstr.') '.$this->errorno.': '.$this->error);
 274                 }
 275
 276                 if ($this->debug) {
 277
 278                         $mesg = '';
 279
 280                         if ($result === false) {
 281                                 $mesg = 'false';
 282                         } elseif ($result === true) {
 283                                 $mesg = 'true';
 284                         } else {
 285                                 switch ($this->driver) {
 286                                         case 'pdo':
 287                                                 $mesg = $result->rowCount().' results'.EOL;
 288                                                 break;
 289                                         case 'mysqli':
 290                                                 $mesg = $result->num_rows.' results'.EOL;
 291                                                 break;
 292                                         case 'mysql':
 293                                                 $mesg = mysql_num_rows($result).' results'.EOL;
 294                                                 break;
 295                                 }
 296                         }
 297
 298                         $str =  'SQL = ' . printable($sql) . EOL . 'SQL returned ' . $mesg
 299                                 . (($this->error) ? ' error: ' . $this->error : '')
 300                                 . EOL;
 301
 302                         logger('dba: ' . $str );
 303                 }
 304
 305                 /**
 306                  * If dbfail.out exists, we will write any failed calls directly to it,
 307                  * regardless of any logging that may or may nor be in effect.
 308                  * These usually indicate SQL syntax errors that need to be resolved.
 309                  */
 310
 311                 if ($result === false) {
 312                         logger('dba: ' . printable($sql) . ' returned false.' . "\n" . $this->error);
 313                         if (file_exists('dbfail.out')) {
 314                                 file_put_contents('dbfail.out', datetime_convert() . "\n" . printable($sql) . ' returned false' . "\n" . $this->error . "\n", FILE_APPEND);
 315                         }
 316                 }
 317
 318                 if (is_bool($result)) {
 319                         return $result;
 320                 }
 321                 if ($onlyquery) {
 322                         $this->result = $result;
 323                         return true;
 324                 }
 325
 326                 $r = array();
 327                 switch ($this->driver) {
 328                         case 'pdo':
 329                                 while ($x = $result->fetch(PDO::FETCH_ASSOC)) {
 330                                         $r[] = $x;
 331                                 }
 332                                 $result->closeCursor();
 333                                 break;
 334                         case 'mysqli':
 335                                 while ($x = $result->fetch_array(MYSQLI_ASSOC)) {
 336                                         $r[] = $x;
 337                                 }
 338                                 $result->free_result();
 339                                 break;
 340                         case 'mysql':
 341                                 while ($x = mysql_fetch_array($result, MYSQL_ASSOC)) {
 342                                         $r[] = $x;
 343                                 }
 344                                 mysql_free_result($result);
 345                                 break;
 346                 }
 347
 348                 // PDO doesn't return "true" on successful operations - like mysqli does
 349                 // Emulate this behaviour by checking if the query returned data and had columns
 350                 // This should be reliable enough
 351                 if (($this->driver == 'pdo') AND (count($r) == 0) AND ($columns == 0)) {
 352                         return true;
 353                 }
 354
 355                 //$a->save_timestamp($stamp1, "database");
 356
 357                 if ($this->debug) {
 358                         logger('dba: ' . printable(print_r($r, true)));
 359                 }
 360                 return($r);
 361         }
 362
 363         public function dbg($dbg) {
 364                 $this->debug = $dbg;
 365         }
 366
 367         public function escape($str) {
 368                 if ($this->db && $this->connected) {
 369                         switch ($this->driver) {
 370                                 case 'pdo':
 371                                         return substr(@$this->db->quote($str, PDO::PARAM_STR), 1, -1);
 372                                 case 'mysqli':
 373                                         return @$this->db->real_escape_string($str);
 374                                 case 'mysql':
 375                                         return @mysql_real_escape_string($str,$this->db);
 376                         }
 377                 }
 378         }
 379
 380         function connected() {
 381                 switch ($this->driver) {
 382                         case 'pdo':
 383                                 // Not sure if this really is working like expected
 384                                 $connected = ($this->db->getAttribute(PDO::ATTR_CONNECTION_STATUS) != "");
 385                                 break;
 386                         case 'mysqli':
 387                                 $connected = $this->db->ping();
 388                                 break;
 389                         case 'mysql':
 390                                 $connected = mysql_ping($this->db);
 391                                 break;
 392                 }
 393                 return $connected;
 394         }
 395
 396         function insert_id() {
 397                 switch ($this->driver) {
 398                         case 'pdo':
 399                                 $id = $this->db->lastInsertId();
 400                                 break;
 401                         case 'mysqli':
 402                                 $id = $this->db->insert_id;
 403                                 break;
 404                         case 'mysql':
 405                                 $id = mysql_insert_id($this->db);
 406                                 break;
 407                 }
 408                 return $id;
 409         }
 410
 411         function __destruct() {
 412                 if ($this->db) {
 413                         switch ($this->driver) {
 414                                 case 'pdo':
 415                                         $this->db = null;
 416                                         break;
 417                                 case 'mysqli':
 418                                         $this->db->close();
 419                                         break;
 420                                 case 'mysql':
 421                                         mysql_close($this->db);
 422                                         break;
 423                         }
 424                 }
 425         }
 426
 427         /**
 428          * @brief Replaces ANY_VALUE() function by MIN() function,
 429          *  if the database server does not support ANY_VALUE().
 430          *
 431          * Considerations for Standard SQL, or MySQL with ONLY_FULL_GROUP_BY (default since 5.7.5).
 432          * ANY_VALUE() is available from MySQL 5.7.5 https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html
 433          * A standard fall-back is to use MIN().
 434          *
 435          * @param string $sql An SQL string without the values
 436          * @return string The input SQL string modified if necessary.
 437          */
 438         public function any_value_fallback($sql) {
 439                 $server_info = $this->server_info();
 440                 if (version_compare($server_info, '5.7.5', '<') ||
 441                         (stripos($server_info, 'MariaDB') !== false)) {
 442                         $sql = str_ireplace('ANY_VALUE(', 'MIN(', $sql);
 443                 }
 444                 return $sql;
 445         }
 446
 447         /**
 448          * @brief Replaces the ? placeholders with the parameters in the $args array
 449          *
 450          * @param string $sql SQL query
 451          * @param array $args The parameters that are to replace the ? placeholders
 452          * @return string The replaced SQL query
 453          */
 454         static private function replace_parameters($sql, $args) {
 455                 $offset = 0;
 456                 foreach ($args AS $param => $value) {
 457                         if (is_int($args[$param]) OR is_float($args[$param])) {
 458                                 $replace = intval($args[$param]);
 459                         } else {
 460                                 $replace = "'".self::$dbo->escape($args[$param])."'";
 461                         }
 462
 463                         $pos = strpos($sql, '?', $offset);
 464                         if ($pos !== false) {
 465                                 $sql = substr_replace($sql, $replace, $pos, 1);
 466                         }
 467                         $offset = $pos + strlen($replace);
 468                 }
 469                 return $sql;
 470         }
 471
 472         /**
 473          * @brief Executes a prepared statement that returns data
 474          * @usage Example: $r = p("SELECT * FROM `item` WHERE `guid` = ?", $guid);
 475          * @param string $sql SQL statement
 476          * @return object statement object
 477          */
 478         static public function p($sql) {
 479                 $a = get_app();
 480
 481                 $stamp1 = microtime(true);
 482
 483                 $args = func_get_args();
 484                 unset($args[0]);
 485
 486                 // When the second function parameter is an array then use this as the parameter array
 487                 if ((count($args) == 1) AND (is_array($args[1]))) {
 488                         $params = $args[1];
 489                         $i = 0;
 490                         foreach ($params AS $param) {
 491                                 $args[++$i] = $param;
 492                         }
 493                 }
 494
 495                 if (!self::$dbo OR !self::$dbo->connected) {
 496                         return false;
 497                 }
 498
 499                 $sql = self::$dbo->any_value_fallback($sql);
 500
 501                 if (x($a->config,'system') && x($a->config['system'], 'db_callstack')) {
 502                         $sql = "/*".$a->callstack()." */ ".$sql;
 503                 }
 504
 505                 self::$dbo->error = '';
 506                 self::$dbo->errorno = 0;
 507
 508                 switch (self::$dbo->driver) {
 509                         case 'pdo':
 510                                 if (!$stmt = self::$dbo->db->prepare($sql)) {
 511                                         $errorInfo = self::$dbo->db->errorInfo();
 512                                         self::$dbo->error = $errorInfo[2];
 513                                         self::$dbo->errorno = $errorInfo[1];
 514                                         $retval = false;
 515                                         break;
 516                                 }
 517
 518                                 foreach ($args AS $param => $value) {
 519                                         $stmt->bindParam($param, $args[$param]);
 520                                 }
 521
 522                                 if (!$stmt->execute()) {
 523                                         $errorInfo = self::$dbo->db->errorInfo();
 524                                         self::$dbo->error = $errorInfo[2];
 525                                         self::$dbo->errorno = $errorInfo[1];
 526                                         $retval = false;
 527                                 } else {
 528                                         $retval = $stmt;
 529                                 }
 530                                 break;
 531                         case 'mysqli':
 532                                 $stmt = self::$dbo->db->stmt_init();
 533
 534                                 if (!$stmt->prepare($sql)) {
 535                                         self::$dbo->error = self::$dbo->db->error;
 536                                         self::$dbo->errorno = self::$dbo->db->errno;
 537                                         $retval = false;
 538                                         break;
 539                                 }
 540
 541                                 $params = '';
 542                                 $values = array();
 543                                 foreach ($args AS $param => $value) {
 544                                         if (is_int($args[$param])) {
 545                                                 $params .= 'i';
 546                                         } elseif (is_float($args[$param])) {
 547                                                 $params .= 'd';
 548                                         } elseif (is_string($args[$param])) {
 549                                                 $params .= 's';
 550                                         } else {
 551                                                 $params .= 'b';
 552                                         }
 553                                         $values[] = &$args[$param];
 554                                 }
 555
 556                                 array_unshift($values, $params);
 557
 558                                 call_user_func_array(array($stmt, 'bind_param'), $values);
 559
 560                                 if (!$stmt->execute()) {
 561                                         self::$dbo->error = self::$dbo->db->error;
 562                                         self::$dbo->errorno = self::$dbo->db->errno;
 563                                         $retval = false;
 564                                 } else {
 565                                         $stmt->store_result();
 566                                         $retval = $stmt;
 567                                 }
 568                                 break;
 569                         case 'mysql':
 570                                 // For the old "mysql" functions we cannot use prepared statements
 571                                 $retval = mysql_query(self::replace_parameters($sql, $args), self::$dbo->db);
 572                                 if (mysql_errno(self::$dbo->db)) {
 573                                         self::$dbo->error = mysql_error(self::$dbo->db);
 574                                         self::$dbo->errorno = mysql_errno(self::$dbo->db);
 575                                 }
 576                                 break;
 577                 }
 578
 579                 if (self::$dbo->errorno != 0) {
 580                         logger('DB Error '.self::$dbo->errorno.': '.self::$dbo->error);
 581                 }
 582
 583                 $a->save_timestamp($stamp1, 'database');
 584
 585                 if (x($a->config,'system') && x($a->config['system'], 'db_log')) {
 586
 587                         $stamp2 = microtime(true);
 588                         $duration = (float)($stamp2 - $stamp1);
 589
 590                         if (($duration > $a->config["system"]["db_loglimit"])) {
 591                                 $duration = round($duration, 3);
 592                                 $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
 593
 594                                 @file_put_contents($a->config["system"]["db_log"], datetime_convert()."\t".$duration."\t".
 595                                                 basename($backtrace[1]["file"])."\t".
 596                                                 $backtrace[1]["line"]."\t".$backtrace[2]["function"]."\t".
 597                                                 substr(self::replace_parameters($sql, $args), 0, 2000)."\n", FILE_APPEND);
 598                         }
 599                 }
 600                 return $retval;
 601         }
 602
 603         /**
 604          * @brief Executes a prepared statement like UPDATE or INSERT that doesn't return data
 605          *
 606          * @param string $sql SQL statement
 607          * @return boolean Was the query successfull? False is returned only if an error occurred
 608          */
 609         static public function e($sql) {
 610                 $a = get_app();
 611
 612                 $stamp = microtime(true);
 613
 614                 $args = func_get_args();
 615
 616                 $stmt = call_user_func_array('self::p', $args);
 617
 618                 if (is_bool($stmt)) {
 619                         $retval = $stmt;
 620                 } elseif (is_object($stmt)) {
 621                         $retval = true;
 622                 } else {
 623                         $retval = false;
 624                 }
 625
 626                 self::close($stmt);
 627
 628                 $a->save_timestamp($stamp, "database_write");
 629
 630                 return $retval;
 631         }
 632
 633         /**
 634          * @brief Check if data exists
 635          *
 636          * @param string $sql SQL statement
 637          * @return boolean Are there rows for that query?
 638          */
 639         static public function exists($sql) {
 640                 $args = func_get_args();
 641
 642                 $stmt = call_user_func_array('self::p', $args);
 643
 644                 if (is_bool($stmt)) {
 645                         $retval = $stmt;
 646                 } else {
 647                         $retval = (self::num_rows($stmt) > 0);
 648                 }
 649
 650                 self::close($stmt);
 651
 652                 return $retval;
 653         }
 654
 655         /**
 656          * @brief Fetches the first row
 657          *
 658          * @param string $sql SQL statement
 659          * @return array first row of query
 660          */
 661         static public function fetch_first($sql) {
 662                 $args = func_get_args();
 663
 664                 $stmt = call_user_func_array('self::p', $args);
 665
 666                 if (is_bool($stmt)) {
 667                         $retval = $stmt;
 668                 } else {
 669                         $retval = self::fetch($stmt);
 670                 }
 671
 672                 self::close($stmt);
 673
 674                 return $retval;
 675         }
 676
 677         /**
 678          * @brief Returns the number of rows of a statement
 679          *
 680          * @param object Statement object
 681          * @return int Number of rows
 682          */
 683         static public function num_rows($stmt) {
 684                 switch (self::$dbo->driver) {
 685                         case 'pdo':
 686                                 return $stmt->rowCount();
 687                         case 'mysqli':
 688                                 return $stmt->num_rows;
 689                         case 'mysql':
 690                                 return mysql_num_rows($stmt);
 691                 }
 692                 return 0;
 693         }
 694
 695         /**
 696          * @brief Fetch a single row
 697          *
 698          * @param object $stmt statement object
 699          * @return array current row
 700          */
 701         static public function fetch($stmt) {
 702                 if (!is_object($stmt)) {
 703                         return false;
 704                 }
 705
 706                 switch (self::$dbo->driver) {
 707                         case 'pdo':
 708                                 return $stmt->fetch(PDO::FETCH_ASSOC);
 709                         case 'mysqli':
 710                                 // This code works, but is slow
 711
 712                                 // Bind the result to a result array
 713                                 $cols = array();
 714
 715                                 $cols_num = array();
 716                                 for ($x = 0; $x < $stmt->field_count; $x++) {
 717                                         $cols[] = &$cols_num[$x];
 718                                 }
 719
 720                                 call_user_func_array(array($stmt, 'bind_result'), $cols);
 721
 722                                 if (!$stmt->fetch()) {
 723                                         return false;
 724                                 }
 725
 726                                 // The slow part:
 727                                 // We need to get the field names for the array keys
 728                                 // It seems that there is no better way to do this.
 729                                 $result = $stmt->result_metadata();
 730                                 $fields = $result->fetch_fields();
 731
 732                                 $columns = array();
 733                                 foreach ($cols_num AS $param => $col) {
 734                                         $columns[$fields[$param]->name] = $col;
 735                                 }
 736                                 return $columns;
 737                         case 'mysql':
 738                                 return mysql_fetch_array(self::$dbo->result, MYSQL_ASSOC);
 739                 }
 740         }
 741
 742         /**
 743          * @brief Insert a row into a table
 744          *
 745          * @param string $table Table name
 746          * @param array $param parameter array
 747          *
 748          * @return boolean was the insert successfull?
 749          */
 750         static public function insert($table, $param) {
 751                 $sql = "INSERT INTO `".self::$dbo->escape($table)."` (`".implode("`, `", array_keys($param))."`) VALUES (".
 752                         substr(str_repeat("?, ", count($param)), 0, -2).");";
 753
 754                 return self::e($sql, $param);
 755         }
 756
 757         /**
 758          * @brief Build the array with the table relations
 759          *
 760          * The array is build from the database definitions in dbstructure.php
 761          *
 762          * This process must only be started once, since the value is cached.
 763          */
 764         static private function build_relation_data() {
 765                 $definition = db_definition();
 766
 767                 foreach ($definition AS $table => $structure) {
 768                         foreach ($structure['fields'] AS $field => $field_struct) {
 769                                 if (isset($field_struct['relation'])) {
 770                                         foreach ($field_struct['relation'] AS $rel_table => $rel_field) {
 771                                                 self::$relation[$rel_table][$rel_field][$table][] = $field;
 772                                         }
 773                                 }
 774                         }
 775                 }
 776         }
 777
 778         /**
 779          * @brief Insert a row into a table
 780          *
 781          * @param string $table Table name
 782          * @param array $param parameter array
 783          * @param boolean $in_commit Internal use: Only do a commit after the last delete
 784          * @param array $callstack Internal use: prevent endless loops
 785          *
 786          * @return boolean|array was the delete successfull? When $in_commit is set: deletion data
 787          */
 788         static public function delete($table, $param, $in_commit = false, $callstack = array()) {
 789
 790                 $commands = array();
 791
 792                 // Create a key for the loop prevention
 793                 $key = $table.':'.implode(':', array_keys($param)).':'.implode(':', $param);
 794
 795                 // We quit when this key already exists in the callstack.
 796                 if (isset($callstack[$key])) {
 797                         return $commands;
 798                 }
 799
 800                 $callstack[$key] = $key;
 801
 802                 $table = self::$dbo->escape($table);
 803
 804                 $commands[$key] = array('table' => $table, 'param' => $param);
 805
 806                 // To speed up the whole process we cache the table relations
 807                 if (count(self::$relation) == 0) {
 808                         self::build_relation_data();
 809                 }
 810
 811                 // Is there a relation entry for the table?
 812                 if (isset(self::$relation[$table])) {
 813                         // We only allow a simple "one field" relation.
 814                         $field = array_keys(self::$relation[$table])[0];
 815                         $rel_def = array_values(self::$relation[$table])[0];
 816
 817                         // When the search field is the relation field, we don't need to fetch the rows
 818                         // This is useful when the leading record is already deleted in the frontend but the rest is done in the backend
 819                         if ((count($param) == 1) AND ($field == array_keys($param)[0])) {
 820                                 foreach ($rel_def AS $rel_table => $rel_fields) {
 821                                         foreach ($rel_fields AS $rel_field) {
 822                                                 $retval = self::delete($rel_table, array($rel_field => array_values($param)[0]), true, $callstack);
 823                                                 $commands = array_merge($commands, $retval);
 824                                         }
 825                                 }
 826                         } else {
 827                                 // Fetch all rows that are to be deleted
 828                                 $sql = "SELECT ".self::$dbo->escape($field)." FROM `".$table."` WHERE `".
 829                                 implode("` = ? AND `", array_keys($param))."` = ?";
 830
 831                                 $data = self::p($sql, $param);
 832                                 while ($row = self::fetch($data)) {
 833                                         // Now we accumulate the delete commands
 834                                         $retval = self::delete($table, array($field => $row[$field]), true, $callstack);
 835                                         $commands = array_merge($commands, $retval);
 836                                 }
 837
 838                                 // Since we had split the delete command we don't need the original command anymore
 839                                 unset($commands[$key]);
 840                         }
 841                 }
 842
 843                 if (!$in_commit) {
 844                         // Now we finalize the process
 845                         self::p("COMMIT");
 846                         self::p("START TRANSACTION");
 847
 848                         $compacted = array();
 849                         $counter = array();
 850                         foreach ($commands AS $command) {
 851                                 if (count($command['param']) > 1) {
 852                                         $sql = "DELETE FROM `".$command['table']."` WHERE `".
 853                                                 implode("` = ? AND `", array_keys($command['param']))."` = ?";
 854
 855                                         logger(dba::replace_parameters($sql, $command['param']), LOGGER_DATA);
 856
 857                                         if (!self::e($sql, $param)) {
 858                                                 self::p("ROLLBACK");
 859                                                 return false;
 860                                         }
 861                                 } else {
 862                                         $key_table = $command['table'];
 863                                         $key_param = array_keys($command['param'])[0];
 864                                         $value = array_values($command['param'])[0];
 865
 866                                         // Split the SQL queries in chunks of 100 values
 867                                         // We do the $i stuff here to make the code better readable
 868                                         $i = $counter[$key_table][$key_param];
 869                                         if (count($compacted[$key_table][$key_param][$i]) > 100) {
 870                                                 ++$i;
 871                                         }
 872
 873                                         $compacted[$key_table][$key_param][$i][$value] = $value;
 874                                         $counter[$key_table][$key_param] = $i;
 875                                 }
 876                         }
 877                         foreach ($compacted AS $table => $values) {
 878                                 foreach ($values AS $field => $field_value_list) {
 879                                         foreach ($field_value_list AS $field_values) {
 880                                                 $sql = "DELETE FROM `".$table."` WHERE `".$field."` IN (".
 881                                                         substr(str_repeat("?, ", count($field_values)), 0, -2).");";
 882
 883                                                 logger(dba::replace_parameters($sql, $field_values), LOGGER_DATA);
 884
 885                                                 if (!self::e($sql, $param)) {
 886                                                         self::p("ROLLBACK");
 887                                                         return false;
 888                                                 }
 889                                         }
 890                                 }
 891                         }
 892                         self::p("COMMIT");
 893                         return true;
 894                 }
 895
 896                 return $commands;
 897         }
 898
 899         /**
 900          * @brief Updates rows
 901          *
 902          * Updates rows in the database. When $old_fields is set to an array,
 903          * the system will only do an update if the fields in that array changed.
 904          *
 905          * Attention:
 906          * Only the values in $old_fields are compared.
 907          * This is an intentional behaviour.
 908          *
 909          * Example:
 910          * We include the timestamp field in $fields but not in $old_fields.
 911          * Then the row will only get the new timestamp when the other fields had changed.
 912          *
 913          * When $old_fields is set to a boolean value the system will do this compare itself.
 914          * When $old_fields is set to "true" the system will do an insert if the row doesn't exists.
 915          *
 916          * Attention:
 917          * Only set $old_fields to a boolean value when you are sure that you will update a single row.
 918          * When you set $old_fields to "true" then $fields must contain all relevant fields!
 919          *
 920          * @param string $table Table name
 921          * @param array $fields contains the fields that are updated
 922          * @param array $condition condition array with the key values
 923          * @param array|boolean $old_fields array with the old field values that are about to be replaced
 924          *
 925          * @return boolean was the update successfull?
 926          */
 927         static public function update($table, $fields, $condition, $old_fields = array()) {
 928
 929                 /** @todo We may use MySQL specific functions here:
 930                  * INSERT INTO `config` (`cat`, `k`, `v`) VALUES ('%s', '%s', '%s') ON DUPLICATE KEY UPDATE `v` = '%s'"
 931                  * But I think that it doesn't make sense here.
 932                 */
 933
 934                 $table = self::$dbo->escape($table);
 935
 936                 if (is_bool($old_fields)) {
 937                         $sql = "SELECT * FROM `".$table."` WHERE `".
 938                         implode("` = ? AND `", array_keys($condition))."` = ? LIMIT 1";
 939
 940                         $params = array();
 941                         foreach ($condition AS $value) {
 942                                 $params[] = $value;
 943                         }
 944
 945                         $do_insert = $old_fields;
 946
 947                         $old_fields = self::fetch_first($sql, $params);
 948                         if (is_bool($old_fields)) {
 949                                 if ($do_insert) {
 950                                         return self::insert($table, $fields);
 951                                 }
 952                                 $old_fields = array();
 953                         }
 954                 }
 955
 956                 $do_update = (count($old_fields) == 0);
 957
 958                 foreach ($old_fields AS $fieldname => $content) {
 959                         if (isset($fields[$fieldname])) {
 960                                 if ($fields[$fieldname] == $content) {
 961                                         unset($fields[$fieldname]);
 962                                 } else {
 963                                         $do_update = true;
 964                                 }
 965                         }
 966                 }
 967
 968                 if (!$do_update OR (count($fields) == 0)) {
 969                         return true;
 970                 }
 971
 972                 $sql = "UPDATE `".$table."` SET `".
 973                         implode("` = ?, `", array_keys($fields))."` = ? WHERE `".
 974                         implode("` = ? AND `", array_keys($condition))."` = ?";
 975
 976                 $params = array();
 977                 foreach ($fields AS $value) {
 978                         $params[] = $value;
 979                 }
 980                 foreach ($condition AS $value) {
 981                         $params[] = $value;
 982                 }
 983
 984                 return self::e($sql, $params);
 985         }
 986
 987         /**
 988          * @brief Closes the current statement
 989          *
 990          * @param object $stmt statement object
 991          * @return boolean was the close successfull?
 992          */
 993         static public function close($stmt) {
 994                 if (!is_object($stmt)) {
 995                         return false;
 996                 }
 997
 998                 switch (self::$dbo->driver) {
 999                         case 'pdo':
1000                                 return $stmt->closeCursor();
1001                         case 'mysqli':
1002                                 return $stmt->free_result();
1003                                 return $stmt->close();
1004                         case 'mysql':
1005                                 return mysql_free_result($stmt);
1006                 }
1007         }
1008 }
1009
1010 function printable($s) {
1011         $s = preg_replace("~([\x01-\x08\x0E-\x0F\x10-\x1F\x7F-\xFF])~",".", $s);
1012         $s = str_replace("\x00",'.',$s);
1013         if (x($_SERVER,'SERVER_NAME')) {
1014                 $s = escape_tags($s);
1015         }
1016         return $s;
1017 }
1018
1019 // Procedural functions
1020 function dbg($state) {
1021         global $db;
1022
1023         if ($db) {
1024                 $db->dbg($state);
1025         }
1026 }
1027
1028 function dbesc($str) {
1029         global $db;
1030
1031         if ($db && $db->connected) {
1032                 return($db->escape($str));
1033         } else {
1034                 return(str_replace("'","\\'",$str));
1035         }
1036 }
1037
1038 // Function: q($sql,$args);
1039 // Description: execute SQL query with printf style args.
1040 // Example: $r = q("SELECT * FROM `%s` WHERE `uid` = %d",
1041 //                   'user', 1);
1042 function q($sql) {
1043         global $db;
1044         $args = func_get_args();
1045         unset($args[0]);
1046
1047         if ($db && $db->connected) {
1048                 $sql = $db->any_value_fallback($sql);
1049                 $stmt = @vsprintf($sql,$args); // Disabled warnings
1050                 //logger("dba: q: $stmt", LOGGER_ALL);
1051                 if ($stmt === false)
1052                         logger('dba: vsprintf error: ' . print_r(debug_backtrace(),true), LOGGER_DEBUG);
1053
1054                 $db->log_index($stmt);
1055
1056                 return $db->q($stmt);
1057         }
1058
1059         /**
1060          *
1061          * This will happen occasionally trying to store the
1062          * session data after abnormal program termination
1063          *
1064          */
1065         logger('dba: no database: ' . print_r($args,true));
1066         return false;
1067 }
1068
1069 /**
1070  * @brief Performs a query with "dirty reads"
1071  *
1072  * By doing dirty reads (reading uncommitted data) no locks are performed
1073  * This function can be used to fetch data that doesn't need to be reliable.
1074  *
1075  * @param $args Query parameters (1 to N parameters of different types)
1076  * @return array Query array
1077  */
1078 function qu($sql) {
1079         global $db;
1080
1081         $args = func_get_args();
1082         unset($args[0]);
1083
1084         if ($db && $db->connected) {
1085                 $sql = $db->any_value_fallback($sql);
1086                 $stmt = @vsprintf($sql,$args); // Disabled warnings
1087                 if ($stmt === false)
1088                         logger('dba: vsprintf error: ' . print_r(debug_backtrace(),true), LOGGER_DEBUG);
1089
1090                 $db->log_index($stmt);
1091
1092                 $db->q("SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
1093                 $retval = $db->q($stmt);
1094                 $db->q("SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;");
1095                 return $retval;
1096         }
1097
1098         /**
1099          *
1100          * This will happen occasionally trying to store the
1101          * session data after abnormal program termination
1102          *
1103          */
1104         logger('dba: no database: ' . print_r($args,true));
1105         return false;
1106 }
1107
1108 /**
1109  *
1110  * Raw db query, no arguments
1111  *
1112  */
1113 function dbq($sql) {
1114         global $db;
1115
1116         if ($db && $db->connected) {
1117                 $ret = $db->q($sql);
1118         } else {
1119                 $ret = false;
1120         }
1121         return $ret;
1122 }
1123
1124 // Caller is responsible for ensuring that any integer arguments to
1125 // dbesc_array are actually integers and not malformed strings containing
1126 // SQL injection vectors. All integer array elements should be specifically
1127 // cast to int to avoid trouble.
1128 function dbesc_array_cb(&$item, $key) {
1129         if (is_string($item))
1130                 $item = dbesc($item);
1131 }
1132
1133 function dbesc_array(&$arr) {
1134         if (is_array($arr) && count($arr)) {
1135                 array_walk($arr,'dbesc_array_cb');
1136         }
1137 }
1138
1139 function dba_timer() {
1140         return microtime(true);
1141 }