include/dba.php

   1 <?php
   2 use \Friendica\Core\System;
   3
   4 require_once("dbm.php");
   5 require_once('include/datetime.php');
   6
   7 /**
   8  * @class MySQL database class
   9  *
  10  * This class is for the low level database stuff that does driver specific things.
  11  */
  12
  13 class dba {
  14         public static $connected = true;
  15
  16         private static $_server_info = '';
  17         private static $db;
  18         private static $driver;
  19         private static $error = false;
  20         private static $errorno = 0;
  21         private static $affected_rows = 0;
  22         private static $in_transaction = false;
  23         private static $relation = array();
  24
  25         public static function connect($serveraddr, $user, $pass, $db, $install = false) {
  26                 if (!is_null(self::$db)) {
  27                         return true;
  28                 }
  29
  30                 $a = get_app();
  31
  32                 $stamp1 = microtime(true);
  33
  34                 $serveraddr = trim($serveraddr);
  35
  36                 $serverdata = explode(':', $serveraddr);
  37                 $server = $serverdata[0];
  38
  39                 if (count($serverdata) > 1) {
  40                         $port = trim($serverdata[1]);
  41                 }
  42
  43                 $server = trim($server);
  44                 $user = trim($user);
  45                 $pass = trim($pass);
  46                 $db = trim($db);
  47
  48                 if (!(strlen($server) && strlen($user))) {
  49                         self::$connected = false;
  50                         self::$db = null;
  51                         return false;
  52                 }
  53
  54                 if ($install) {
  55                         if (strlen($server) && ($server !== 'localhost') && ($server !== '127.0.0.1')) {
  56                                 if (! dns_get_record($server, DNS_A + DNS_CNAME + DNS_PTR)) {
  57                                         self::$error = sprintf(t('Cannot locate DNS info for database server \'%s\''), $server);
  58                                         self::$connected = false;
  59                                         self::$db = null;
  60                                         return false;
  61                                 }
  62                         }
  63                 }
  64
  65                 if (class_exists('\PDO') && in_array('mysql', PDO::getAvailableDrivers())) {
  66                         self::$driver = 'pdo';
  67                         $connect = "mysql:host=".$server.";dbname=".$db;
  68
  69                         if (isset($port)) {
  70                                 $connect .= ";port=".$port;
  71                         }
  72
  73                         if (isset($a->config["system"]["db_charset"])) {
  74                                 $connect .= ";charset=".$a->config["system"]["db_charset"];
  75                         }
  76                         try {
  77                                 self::$db = @new PDO($connect, $user, $pass);
  78                                 self::$connected = true;
  79                         } catch (PDOException $e) {
  80                                 self::$connected = false;
  81                         }
  82                 }
  83
  84                 if (!self::$connected && class_exists('mysqli')) {
  85                         self::$driver = 'mysqli';
  86                         self::$db = @new mysqli($server, $user, $pass, $db, $port);
  87                         if (!mysqli_connect_errno()) {
  88                                 self::$connected = true;
  89
  90                                 if (isset($a->config["system"]["db_charset"])) {
  91                                         self::$db->set_charset($a->config["system"]["db_charset"]);
  92                                 }
  93                         }
  94                 }
  95
  96                 if (!self::$connected && function_exists('mysql_connect')) {
  97                         self::$driver = 'mysql';
  98                         self::$db = mysql_connect($serveraddr, $user, $pass);
  99                         if (self::$db && mysql_select_db($db, self::$db)) {
 100                                 self::$connected = true;
 101
 102                                 if (isset($a->config["system"]["db_charset"])) {
 103                                         mysql_set_charset($a->config["system"]["db_charset"], self::$db);
 104                                 }
 105                         }
 106                 }
 107
 108                 // No suitable SQL driver was found.
 109                 if (!self::$connected) {
 110                         self::$db = null;
 111                         if (!$install) {
 112                                 system_unavailable();
 113                         }
 114                 }
 115                 $a->save_timestamp($stamp1, "network");
 116
 117                 return true;
 118         }
 119
 120         /**
 121          * @brief Returns the MySQL server version string
 122          *
 123          * This function discriminate between the deprecated mysql API and the current
 124          * object-oriented mysqli API. Example of returned string: 5.5.46-0+deb8u1
 125          *
 126          * @return string
 127          */
 128         public static function server_info() {
 129                 if (self::$_server_info == '') {
 130                         switch (self::$driver) {
 131                                 case 'pdo':
 132                                         self::$_server_info = self::$db->getAttribute(PDO::ATTR_SERVER_VERSION);
 133                                         break;
 134                                 case 'mysqli':
 135                                         self::$_server_info = self::$db->server_info;
 136                                         break;
 137                                 case 'mysql':
 138                                         self::$_server_info = mysql_get_server_info(self::$db);
 139                                         break;
 140                         }
 141                 }
 142                 return self::$_server_info;
 143         }
 144
 145         /**
 146          * @brief Returns the selected database name
 147          *
 148          * @return string
 149          */
 150         public static function database_name() {
 151                 $ret = self::p("SELECT DATABASE() AS `db`");
 152                 $data = self::inArray($ret);
 153                 return $data[0]['db'];
 154         }
 155
 156         /**
 157          * @brief Analyze a database query and log this if some conditions are met.
 158          *
 159          * @param string $query The database query that will be analyzed
 160          */
 161         private static function log_index($query) {
 162                 $a = get_app();
 163
 164                 if (empty($a->config["system"]["db_log_index"])) {
 165                         return;
 166                 }
 167
 168                 // Don't explain an explain statement
 169                 if (strtolower(substr($query, 0, 7)) == "explain") {
 170                         return;
 171                 }
 172
 173                 // Only do the explain on "select", "update" and "delete"
 174                 if (!in_array(strtolower(substr($query, 0, 6)), array("select", "update", "delete"))) {
 175                         return;
 176                 }
 177
 178                 $r = self::p("EXPLAIN ".$query);
 179                 if (!dbm::is_result($r)) {
 180                         return;
 181                 }
 182
 183                 $watchlist = explode(',', $a->config["system"]["db_log_index_watch"]);
 184                 $blacklist = explode(',', $a->config["system"]["db_log_index_blacklist"]);
 185
 186                 while ($row = dba::fetch($r)) {
 187                         if ((intval($a->config["system"]["db_loglimit_index"]) > 0)) {
 188                                 $log = (in_array($row['key'], $watchlist) &&
 189                                         ($row['rows'] >= intval($a->config["system"]["db_loglimit_index"])));
 190                         } else {
 191                                 $log = false;
 192                         }
 193
 194                         if ((intval($a->config["system"]["db_loglimit_index_high"]) > 0) && ($row['rows'] >= intval($a->config["system"]["db_loglimit_index_high"]))) {
 195                                 $log = true;
 196                         }
 197
 198                         if (in_array($row['key'], $blacklist) || ($row['key'] == "")) {
 199                                 $log = false;
 200                         }
 201
 202                         if ($log) {
 203                                 $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
 204                                 @file_put_contents($a->config["system"]["db_log_index"], datetime_convert()."\t".
 205                                                 $row['key']."\t".$row['rows']."\t".$row['Extra']."\t".
 206                                                 basename($backtrace[1]["file"])."\t".
 207                                                 $backtrace[1]["line"]."\t".$backtrace[2]["function"]."\t".
 208                                                 substr($query, 0, 2000)."\n", FILE_APPEND);
 209                         }
 210                 }
 211         }
 212
 213         public static function escape($str) {
 214                 switch (self::$driver) {
 215                         case 'pdo':
 216                                 return substr(@self::$db->quote($str, PDO::PARAM_STR), 1, -1);
 217                         case 'mysqli':
 218                                 return @self::$db->real_escape_string($str);
 219                         case 'mysql':
 220                                 return @mysql_real_escape_string($str,self::$db);
 221                 }
 222         }
 223
 224         public static function connected() {
 225                 switch (self::$driver) {
 226                         case 'pdo':
 227                                 // Not sure if this really is working like expected
 228                                 $connected = (self::$db->getAttribute(PDO::ATTR_CONNECTION_STATUS) != "");
 229                                 break;
 230                         case 'mysqli':
 231                                 $connected = self::$db->ping();
 232                                 break;
 233                         case 'mysql':
 234                                 $connected = mysql_ping(self::$db);
 235                                 break;
 236                 }
 237                 return $connected;
 238         }
 239
 240         /**
 241          * @brief Replaces ANY_VALUE() function by MIN() function,
 242          *  if the database server does not support ANY_VALUE().
 243          *
 244          * Considerations for Standard SQL, or MySQL with ONLY_FULL_GROUP_BY (default since 5.7.5).
 245          * ANY_VALUE() is available from MySQL 5.7.5 https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html
 246          * A standard fall-back is to use MIN().
 247          *
 248          * @param string $sql An SQL string without the values
 249          * @return string The input SQL string modified if necessary.
 250          */
 251         public static function any_value_fallback($sql) {
 252                 $server_info = self::server_info();
 253                 if (version_compare($server_info, '5.7.5', '<') ||
 254                         (stripos($server_info, 'MariaDB') !== false)) {
 255                         $sql = str_ireplace('ANY_VALUE(', 'MIN(', $sql);
 256                 }
 257                 return $sql;
 258         }
 259
 260         /**
 261          * @brief beautifies the query - useful for "SHOW PROCESSLIST"
 262          *
 263          * This is safe when we bind the parameters later.
 264          * The parameter values aren't part of the SQL.
 265          *
 266          * @param string $sql An SQL string without the values
 267          * @return string The input SQL string modified if necessary.
 268          */
 269         public static function clean_query($sql) {
 270                 $search = array("\t", "\n", "\r", "  ");
 271                 $replace = array(' ', ' ', ' ', ' ');
 272                 do {
 273                         $oldsql = $sql;
 274                         $sql = str_replace($search, $replace, $sql);
 275                 } while ($oldsql != $sql);
 276
 277                 return $sql;
 278         }
 279
 280
 281         /**
 282          * @brief Replaces the ? placeholders with the parameters in the $args array
 283          *
 284          * @param string $sql SQL query
 285          * @param array $args The parameters that are to replace the ? placeholders
 286          * @return string The replaced SQL query
 287          */
 288         private static function replace_parameters($sql, $args) {
 289                 $offset = 0;
 290                 foreach ($args AS $param => $value) {
 291                         if (is_int($args[$param]) || is_float($args[$param])) {
 292                                 $replace = intval($args[$param]);
 293                         } else {
 294                                 $replace = "'".self::escape($args[$param])."'";
 295                         }
 296
 297                         $pos = strpos($sql, '?', $offset);
 298                         if ($pos !== false) {
 299                                 $sql = substr_replace($sql, $replace, $pos, 1);
 300                         }
 301                         $offset = $pos + strlen($replace);
 302                 }
 303                 return $sql;
 304         }
 305
 306         /**
 307          * @brief Convert parameter array to an universal form
 308          * @param array $args Parameter array
 309          * @return array universalized parameter array
 310          */
 311         private static function getParam($args) {
 312                 unset($args[0]);
 313
 314                 // When the second function parameter is an array then use this as the parameter array
 315                 if ((count($args) > 0) && (is_array($args[1]))) {
 316                         return $args[1];
 317                 } else {
 318                         return $args;
 319                 }
 320         }
 321
 322         /**
 323          * @brief Executes a prepared statement that returns data
 324          * @usage Example: $r = p("SELECT * FROM `item` WHERE `guid` = ?", $guid);
 325          *
 326          * Please only use it with complicated queries.
 327          * For all regular queries please use dba::select or dba::exists
 328          *
 329          * @param string $sql SQL statement
 330          * @return object statement object
 331          */
 332         public static function p($sql) {
 333                 $a = get_app();
 334
 335                 $stamp1 = microtime(true);
 336
 337                 $params = self::getParam(func_get_args());
 338
 339                 // Renumber the array keys to be sure that they fit
 340                 $i = 0;
 341                 $args = array();
 342                 foreach ($params AS $param) {
 343                         // Avoid problems with some MySQL servers and boolean values. See issue #3645
 344                         if (is_bool($param)) {
 345                                 $param = (int)$param;
 346                         }
 347                         $args[++$i] = $param;
 348                 }
 349
 350                 if (!self::$connected) {
 351                         return false;
 352                 }
 353
 354                 if ((substr_count($sql, '?') != count($args)) && (count($args) > 0)) {
 355                         // Question: Should we continue or stop the query here?
 356                         logger('Parameter mismatch. Query "'.$sql.'" - Parameters '.print_r($args, true), LOGGER_DEBUG);
 357                 }
 358
 359                 $sql = self::clean_query($sql);
 360                 $sql = self::any_value_fallback($sql);
 361
 362                 $orig_sql = $sql;
 363
 364                 if (x($a->config,'system') && x($a->config['system'], 'db_callstack')) {
 365                         $sql = "/*".System::callstack()." */ ".$sql;
 366                 }
 367
 368                 self::$error = '';
 369                 self::$errorno = 0;
 370                 self::$affected_rows = 0;
 371
 372                 // We have to make some things different if this function is called from "e"
 373                 $trace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 2);
 374
 375                 if (isset($trace[1])) {
 376                         $called_from = $trace[1];
 377                 } else {
 378                         // We use just something that is defined to avoid warnings
 379                         $called_from = $trace[0];
 380                 }
 381                 // We are having an own error logging in the function "e"
 382                 $called_from_e = ($called_from['function'] == 'e');
 383
 384                 switch (self::$driver) {
 385                         case 'pdo':
 386                                 // If there are no arguments we use "query"
 387                                 if (count($args) == 0) {
 388                                         if (!$retval = self::$db->query($sql)) {
 389                                                 $errorInfo = self::$db->errorInfo();
 390                                                 self::$error = $errorInfo[2];
 391                                                 self::$errorno = $errorInfo[1];
 392                                                 $retval = false;
 393                                                 break;
 394                                         }
 395                                         self::$affected_rows = $retval->rowCount();
 396                                         break;
 397                                 }
 398
 399                                 if (!$stmt = self::$db->prepare($sql)) {
 400                                         $errorInfo = self::$db->errorInfo();
 401                                         self::$error = $errorInfo[2];
 402                                         self::$errorno = $errorInfo[1];
 403                                         $retval = false;
 404                                         break;
 405                                 }
 406
 407                                 foreach ($args AS $param => $value) {
 408                                         $stmt->bindParam($param, $args[$param]);
 409                                 }
 410
 411                                 if (!$stmt->execute()) {
 412                                         $errorInfo = $stmt->errorInfo();
 413                                         self::$error = $errorInfo[2];
 414                                         self::$errorno = $errorInfo[1];
 415                                         $retval = false;
 416                                 } else {
 417                                         $retval = $stmt;
 418                                         self::$affected_rows = $retval->rowCount();
 419                                 }
 420                                 break;
 421                         case 'mysqli':
 422                                 // There are SQL statements that cannot be executed with a prepared statement
 423                                 $parts = explode(' ', $orig_sql);
 424                                 $command = strtolower($parts[0]);
 425                                 $can_be_prepared = in_array($command, array('select', 'update', 'insert', 'delete'));
 426
 427                                 // The fallback routine is called as well when there are no arguments
 428                                 if (!$can_be_prepared || (count($args) == 0)) {
 429                                         $retval = self::$db->query(self::replace_parameters($sql, $args));
 430                                         if (self::$db->errno) {
 431                                                 self::$error = self::$db->error;
 432                                                 self::$errorno = self::$db->errno;
 433                                                 $retval = false;
 434                                         } else {
 435                                                 if (isset($retval->num_rows)) {
 436                                                         self::$affected_rows = $retval->num_rows;
 437                                                 } else {
 438                                                         self::$affected_rows = self::$db->affected_rows;
 439                                                 }
 440                                         }
 441                                         break;
 442                                 }
 443
 444                                 $stmt = self::$db->stmt_init();
 445
 446                                 if (!$stmt->prepare($sql)) {
 447                                         self::$error = $stmt->error;
 448                                         self::$errorno = $stmt->errno;
 449                                         $retval = false;
 450                                         break;
 451                                 }
 452
 453                                 $params = '';
 454                                 $values = array();
 455                                 foreach ($args AS $param => $value) {
 456                                         if (is_int($args[$param])) {
 457                                                 $params .= 'i';
 458                                         } elseif (is_float($args[$param])) {
 459                                                 $params .= 'd';
 460                                         } elseif (is_string($args[$param])) {
 461                                                 $params .= 's';
 462                                         } else {
 463                                                 $params .= 'b';
 464                                         }
 465                                         $values[] = &$args[$param];
 466                                 }
 467
 468                                 if (count($values) > 0) {
 469                                         array_unshift($values, $params);
 470                                         call_user_func_array(array($stmt, 'bind_param'), $values);
 471                                 }
 472
 473                                 if (!$stmt->execute()) {
 474                                         self::$error = self::$db->error;
 475                                         self::$errorno = self::$db->errno;
 476                                         $retval = false;
 477                                 } else {
 478                                         $stmt->store_result();
 479                                         $retval = $stmt;
 480                                         self::$affected_rows = $retval->affected_rows;
 481                                 }
 482                                 break;
 483                         case 'mysql':
 484                                 // For the old "mysql" functions we cannot use prepared statements
 485                                 $retval = mysql_query(self::replace_parameters($sql, $args), self::$db);
 486                                 if (mysql_errno(self::$db)) {
 487                                         self::$error = mysql_error(self::$db);
 488                                         self::$errorno = mysql_errno(self::$db);
 489                                 } else {
 490                                         self::$affected_rows = mysql_affected_rows($retval);
 491
 492                                         // Due to missing mysql_* support this here wasn't tested at all
 493                                         // See here: http://php.net/manual/en/function.mysql-num-rows.php
 494                                         if (self::$affected_rows <= 0) {
 495                                                 self::$affected_rows = mysql_num_rows($retval);
 496                                         }
 497                                 }
 498                                 break;
 499                 }
 500
 501                 // We are having an own error logging in the function "e"
 502                 if ((self::$errorno != 0) && !$called_from_e) {
 503                         // We have to preserve the error code, somewhere in the logging it get lost
 504                         $error = self::$error;
 505                         $errorno = self::$errorno;
 506
 507                         logger('DB Error '.self::$errorno.': '.self::$error."\n".
 508                                 System::callstack(8)."\n".self::replace_parameters($sql, $params));
 509
 510                         self::$error = $error;
 511                         self::$errorno = $errorno;
 512                 }
 513
 514                 $a->save_timestamp($stamp1, 'database');
 515
 516                 if (x($a->config,'system') && x($a->config['system'], 'db_log')) {
 517
 518                         $stamp2 = microtime(true);
 519                         $duration = (float)($stamp2 - $stamp1);
 520
 521                         if (($duration > $a->config["system"]["db_loglimit"])) {
 522                                 $duration = round($duration, 3);
 523                                 $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
 524
 525                                 @file_put_contents($a->config["system"]["db_log"], datetime_convert()."\t".$duration."\t".
 526                                                 basename($backtrace[1]["file"])."\t".
 527                                                 $backtrace[1]["line"]."\t".$backtrace[2]["function"]."\t".
 528                                                 substr(self::replace_parameters($sql, $args), 0, 2000)."\n", FILE_APPEND);
 529                         }
 530                 }
 531                 return $retval;
 532         }
 533
 534         /**
 535          * @brief Executes a prepared statement like UPDATE or INSERT that doesn't return data
 536          *
 537          * Please use dba::delete, dba::insert, dba::update, ... instead
 538          *
 539          * @param string $sql SQL statement
 540          * @return boolean Was the query successfull? False is returned only if an error occurred
 541          */
 542         public static function e($sql) {
 543                 $a = get_app();
 544
 545                 $stamp = microtime(true);
 546
 547                 $params = self::getParam(func_get_args());
 548
 549                 // In a case of a deadlock we are repeating the query 20 times
 550                 $timeout = 20;
 551
 552                 do {
 553                         $stmt = self::p($sql, $params);
 554
 555                         if (is_bool($stmt)) {
 556                                 $retval = $stmt;
 557                         } elseif (is_object($stmt)) {
 558                                 $retval = true;
 559                         } else {
 560                                 $retval = false;
 561                         }
 562
 563                         self::close($stmt);
 564
 565                 } while ((self::$errorno == 1213) && (--$timeout > 0));
 566
 567                 if (self::$errorno != 0) {
 568                         // We have to preserve the error code, somewhere in the logging it get lost
 569                         $error = self::$error;
 570                         $errorno = self::$errorno;
 571
 572                         logger('DB Error '.self::$errorno.': '.self::$error."\n".
 573                                 System::callstack(8)."\n".self::replace_parameters($sql, $params));
 574
 575                         self::$error = $error;
 576                         self::$errorno = $errorno;
 577                 }
 578
 579                 $a->save_timestamp($stamp, "database_write");
 580
 581                 return $retval;
 582         }
 583
 584         /**
 585          * @brief Check if data exists
 586          *
 587          * @param string $table Table name
 588          * @param array $condition array of fields for condition
 589          *
 590          * @return boolean Are there rows for that condition?
 591          */
 592         public static function exists($table, $condition) {
 593                 if (empty($table)) {
 594                         return false;
 595                 }
 596
 597                 $fields = array();
 598
 599                 $array_element = each($condition);
 600                 $array_key = $array_element['key'];
 601                 if (!is_int($array_key)) {
 602                         $fields = array($array_key);
 603                 }
 604
 605                 $stmt = self::select($table, $fields, $condition, array('limit' => 1, 'only_query' => true));
 606
 607                 if (is_bool($stmt)) {
 608                         $retval = $stmt;
 609                 } else {
 610                         $retval = (self::num_rows($stmt) > 0);
 611                 }
 612
 613                 self::close($stmt);
 614
 615                 return $retval;
 616         }
 617
 618         /**
 619          * @brief Fetches the first row
 620          *
 621          * Please use dba::select or dba::exists whenever this is possible.
 622          *
 623          * @param string $sql SQL statement
 624          * @return array first row of query
 625          */
 626         public static function fetch_first($sql) {
 627                 $params = self::getParam(func_get_args());
 628
 629                 $stmt = self::p($sql, $params);
 630
 631                 if (is_bool($stmt)) {
 632                         $retval = $stmt;
 633                 } else {
 634                         $retval = self::fetch($stmt);
 635                 }
 636
 637                 self::close($stmt);
 638
 639                 return $retval;
 640         }
 641
 642         /**
 643          * @brief Returns the number of affected rows of the last statement
 644          *
 645          * @return int Number of rows
 646          */
 647         public static function affected_rows() {
 648                 return self::$affected_rows;
 649         }
 650
 651         /**
 652          * @brief Returns the number of columns of a statement
 653          *
 654          * @param object Statement object
 655          * @return int Number of columns
 656          */
 657         public static function columnCount($stmt) {
 658                 if (!is_object($stmt)) {
 659                         return 0;
 660                 }
 661                 switch (self::$driver) {
 662                         case 'pdo':
 663                                 return $stmt->columnCount();
 664                         case 'mysqli':
 665                                 return $stmt->field_count;
 666                         case 'mysql':
 667                                 return mysql_affected_rows($stmt);
 668                 }
 669                 return 0;
 670         }
 671         /**
 672          * @brief Returns the number of rows of a statement
 673          *
 674          * @param object Statement object
 675          * @return int Number of rows
 676          */
 677         public static function num_rows($stmt) {
 678                 if (!is_object($stmt)) {
 679                         return 0;
 680                 }
 681                 switch (self::$driver) {
 682                         case 'pdo':
 683                                 return $stmt->rowCount();
 684                         case 'mysqli':
 685                                 return $stmt->num_rows;
 686                         case 'mysql':
 687                                 return mysql_num_rows($stmt);
 688                 }
 689                 return 0;
 690         }
 691
 692         /**
 693          * @brief Fetch a single row
 694          *
 695          * @param object $stmt statement object
 696          * @return array current row
 697          */
 698         public static function fetch($stmt) {
 699                 if (!is_object($stmt)) {
 700                         return false;
 701                 }
 702
 703                 switch (self::$driver) {
 704                         case 'pdo':
 705                                 return $stmt->fetch(PDO::FETCH_ASSOC);
 706                         case 'mysqli':
 707                                 if (get_class($stmt) == 'mysqli_result') {
 708                                         return $stmt->fetch_assoc();
 709                                 }
 710
 711                                 // This code works, but is slow
 712
 713                                 // Bind the result to a result array
 714                                 $cols = array();
 715
 716                                 $cols_num = array();
 717                                 for ($x = 0; $x < $stmt->field_count; $x++) {
 718                                         $cols[] = &$cols_num[$x];
 719                                 }
 720
 721                                 call_user_func_array(array($stmt, 'bind_result'), $cols);
 722
 723                                 if (!$stmt->fetch()) {
 724                                         return false;
 725                                 }
 726
 727                                 // The slow part:
 728                                 // We need to get the field names for the array keys
 729                                 // It seems that there is no better way to do this.
 730                                 $result = $stmt->result_metadata();
 731                                 $fields = $result->fetch_fields();
 732
 733                                 $columns = array();
 734                                 foreach ($cols_num AS $param => $col) {
 735                                         $columns[$fields[$param]->name] = $col;
 736                                 }
 737                                 return $columns;
 738                         case 'mysql':
 739                                 return mysql_fetch_array($stmt, MYSQL_ASSOC);
 740                 }
 741         }
 742
 743         /**
 744          * @brief Insert a row into a table
 745          *
 746          * @param string $table Table name
 747          * @param array $param parameter array
 748          * @param bool $on_duplicate_update Do an update on a duplicate entry
 749          *
 750          * @return boolean was the insert successfull?
 751          */
 752         public static function insert($table, $param, $on_duplicate_update = false) {
 753                 $sql = "INSERT INTO `".self::escape($table)."` (`".implode("`, `", array_keys($param))."`) VALUES (".
 754                         substr(str_repeat("?, ", count($param)), 0, -2).")";
 755
 756                 if ($on_duplicate_update) {
 757                         $sql .= " ON DUPLICATE KEY UPDATE `".implode("` = ?, `", array_keys($param))."` = ?";
 758
 759                         $values = array_values($param);
 760                         $param = array_merge_recursive($values, $values);
 761                 }
 762
 763                 return self::e($sql, $param);
 764         }
 765
 766         /**
 767          * @brief Fetch the id of the last insert command
 768          *
 769          * @return integer Last inserted id
 770          */
 771         public static function lastInsertId() {
 772                 switch (self::$driver) {
 773                         case 'pdo':
 774                                 $id = self::$db->lastInsertId();
 775                                 break;
 776                         case 'mysqli':
 777                                 $id = self::$db->insert_id;
 778                                 break;
 779                         case 'mysql':
 780                                 $id = mysql_insert_id(self::$db);
 781                                 break;
 782                 }
 783                 return $id;
 784         }
 785
 786         /**
 787          * @brief Locks a table for exclusive write access
 788          *
 789          * This function can be extended in the future to accept a table array as well.
 790          *
 791          * @param string $table Table name
 792          *
 793          * @return boolean was the lock successful?
 794          */
 795         public static function lock($table) {
 796                 // See here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html
 797                 self::e("SET autocommit=0");
 798                 $success = self::e("LOCK TABLES `".self::escape($table)."` WRITE");
 799                 if (!$success) {
 800                         self::e("SET autocommit=1");
 801                 } else {
 802                         self::$in_transaction = true;
 803                 }
 804                 return $success;
 805         }
 806
 807         /**
 808          * @brief Unlocks all locked tables
 809          *
 810          * @return boolean was the unlock successful?
 811          */
 812         public static function unlock() {
 813                 // See here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html
 814                 self::e("COMMIT");
 815                 $success = self::e("UNLOCK TABLES");
 816                 self::e("SET autocommit=1");
 817                 self::$in_transaction = false;
 818                 return $success;
 819         }
 820
 821         /**
 822          * @brief Starts a transaction
 823          *
 824          * @return boolean Was the command executed successfully?
 825          */
 826         public static function transaction() {
 827                 if (!self::e('COMMIT')) {
 828                         return false;
 829                 }
 830                 if (!self::e('START TRANSACTION')) {
 831                         return false;
 832                 }
 833                 self::$in_transaction = true;
 834                 return true;
 835         }
 836
 837         /**
 838          * @brief Does a commit
 839          *
 840          * @return boolean Was the command executed successfully?
 841          */
 842         public static function commit() {
 843                 if (!self::e('COMMIT')) {
 844                         return false;
 845                 }
 846                 self::$in_transaction = false;
 847                 return true;
 848         }
 849
 850         /**
 851          * @brief Does a rollback
 852          *
 853          * @return boolean Was the command executed successfully?
 854          */
 855         public static function rollback() {
 856                 if (!self::e('ROLLBACK')) {
 857                         return false;
 858                 }
 859                 self::$in_transaction = false;
 860                 return true;
 861         }
 862
 863         /**
 864          * @brief Build the array with the table relations
 865          *
 866          * The array is build from the database definitions in dbstructure.php
 867          *
 868          * This process must only be started once, since the value is cached.
 869          */
 870         private static function build_relation_data() {
 871                 $definition = db_definition();
 872
 873                 foreach ($definition AS $table => $structure) {
 874                         foreach ($structure['fields'] AS $field => $field_struct) {
 875                                 if (isset($field_struct['relation'])) {
 876                                         foreach ($field_struct['relation'] AS $rel_table => $rel_field) {
 877                                                 self::$relation[$rel_table][$rel_field][$table][] = $field;
 878                                         }
 879                                 }
 880                         }
 881                 }
 882         }
 883
 884         /**
 885          * @brief Delete a row from a table
 886          *
 887          * @param string $table Table name
 888          * @param array $param parameter array
 889          * @param boolean $in_process Internal use: Only do a commit after the last delete
 890          * @param array $callstack Internal use: prevent endless loops
 891          *
 892          * @return boolean|array was the delete successfull? When $in_process is set: deletion data
 893          */
 894         public static function delete($table, $param, $in_process = false, &$callstack = array()) {
 895                 $commands = array();
 896
 897                 // Create a key for the loop prevention
 898                 $key = $table.':'.implode(':', array_keys($param)).':'.implode(':', $param);
 899
 900                 // We quit when this key already exists in the callstack.
 901                 if (isset($callstack[$key])) {
 902                         return $commands;
 903                 }
 904
 905                 $callstack[$key] = true;
 906
 907                 $table = self::escape($table);
 908
 909                 $commands[$key] = array('table' => $table, 'param' => $param);
 910
 911                 // To speed up the whole process we cache the table relations
 912                 if (count(self::$relation) == 0) {
 913                         self::build_relation_data();
 914                 }
 915
 916                 // Is there a relation entry for the table?
 917                 if (isset(self::$relation[$table])) {
 918                         // We only allow a simple "one field" relation.
 919                         $field = array_keys(self::$relation[$table])[0];
 920                         $rel_def = array_values(self::$relation[$table])[0];
 921
 922                         // Create a key for preventing double queries
 923                         $qkey = $field.'-'.$table.':'.implode(':', array_keys($param)).':'.implode(':', $param);
 924
 925                         // When the search field is the relation field, we don't need to fetch the rows
 926                         // This is useful when the leading record is already deleted in the frontend but the rest is done in the backend
 927                         if ((count($param) == 1) && ($field == array_keys($param)[0])) {
 928                                 foreach ($rel_def AS $rel_table => $rel_fields) {
 929                                         foreach ($rel_fields AS $rel_field) {
 930                                                 $retval = self::delete($rel_table, array($rel_field => array_values($param)[0]), true, $callstack);
 931                                                 $commands = array_merge($commands, $retval);
 932                                         }
 933                                 }
 934                         // We quit when this key already exists in the callstack.
 935                         } elseif (!isset($callstack[$qkey])) {
 936
 937                                 $callstack[$qkey] = true;
 938
 939                                 // Fetch all rows that are to be deleted
 940                                 $data = self::select($table, array($field), $param);
 941
 942                                 while ($row = self::fetch($data)) {
 943                                         // Now we accumulate the delete commands
 944                                         $retval = self::delete($table, array($field => $row[$field]), true, $callstack);
 945                                         $commands = array_merge($commands, $retval);
 946                                 }
 947
 948                                 self::close($data);
 949
 950                                 // Since we had split the delete command we don't need the original command anymore
 951                                 unset($commands[$key]);
 952                         }
 953                 }
 954
 955                 if (!$in_process) {
 956                         // Now we finalize the process
 957                         $do_transaction = !self::$in_transaction;
 958
 959                         if ($do_transaction) {
 960                                 self::transaction();
 961                         }
 962
 963                         $compacted = array();
 964                         $counter = array();
 965
 966                         foreach ($commands AS $command) {
 967                                 $condition = $command['param'];
 968                                 $array_element = each($condition);
 969                                 $array_key = $array_element['key'];
 970                                 if (is_int($array_key)) {
 971                                         $condition_string = " WHERE ".array_shift($condition);
 972                                 } else {
 973                                         $condition_string = " WHERE `".implode("` = ? AND `", array_keys($condition))."` = ?";
 974                                 }
 975
 976                                 if ((count($command['param']) > 1) || is_int($array_key)) {
 977                                         $sql = "DELETE FROM `".$command['table']."`".$condition_string;
 978                                         logger(self::replace_parameters($sql, $condition), LOGGER_DATA);
 979
 980                                         if (!self::e($sql, $condition)) {
 981                                                 if ($do_transaction) {
 982                                                         self::rollback();
 983                                                 }
 984                                                 return false;
 985                                         }
 986                                 } else {
 987                                         $key_table = $command['table'];
 988                                         $key_param = array_keys($command['param'])[0];
 989                                         $value = array_values($command['param'])[0];
 990
 991                                         // Split the SQL queries in chunks of 100 values
 992                                         // We do the $i stuff here to make the code better readable
 993                                         $i = $counter[$key_table][$key_param];
 994                                         if (count($compacted[$key_table][$key_param][$i]) > 100) {
 995                                                 ++$i;
 996                                         }
 997
 998                                         $compacted[$key_table][$key_param][$i][$value] = $value;
 999                                         $counter[$key_table][$key_param] = $i;
1000                                 }
1001                         }
1002                         foreach ($compacted AS $table => $values) {
1003                                 foreach ($values AS $field => $field_value_list) {
1004                                         foreach ($field_value_list AS $field_values) {
1005                                                 $sql = "DELETE FROM `".$table."` WHERE `".$field."` IN (".
1006                                                         substr(str_repeat("?, ", count($field_values)), 0, -2).");";
1007
1008                                                 logger(self::replace_parameters($sql, $field_values), LOGGER_DATA);
1009
1010                                                 if (!self::e($sql, $field_values)) {
1011                                                         if ($do_transaction) {
1012                                                                 self::rollback();
1013                                                         }
1014                                                         return false;
1015                                                 }
1016                                         }
1017                                 }
1018                         }
1019                         if ($do_transaction) {
1020                                 self::commit();
1021                         }
1022                         return true;
1023                 }
1024
1025                 return $commands;
1026         }
1027
1028         /**
1029          * @brief Updates rows
1030          *
1031          * Updates rows in the database. When $old_fields is set to an array,
1032          * the system will only do an update if the fields in that array changed.
1033          *
1034          * Attention:
1035          * Only the values in $old_fields are compared.
1036          * This is an intentional behaviour.
1037          *
1038          * Example:
1039          * We include the timestamp field in $fields but not in $old_fields.
1040          * Then the row will only get the new timestamp when the other fields had changed.
1041          *
1042          * When $old_fields is set to a boolean value the system will do this compare itself.
1043          * When $old_fields is set to "true" the system will do an insert if the row doesn't exists.
1044          *
1045          * Attention:
1046          * Only set $old_fields to a boolean value when you are sure that you will update a single row.
1047          * When you set $old_fields to "true" then $fields must contain all relevant fields!
1048          *
1049          * @param string $table Table name
1050          * @param array $fields contains the fields that are updated
1051          * @param array $condition condition array with the key values
1052          * @param array|boolean $old_fields array with the old field values that are about to be replaced (true = update on duplicate)
1053          *
1054          * @return boolean was the update successfull?
1055          */
1056         public static function update($table, $fields, $condition, $old_fields = array()) {
1057                 $table = self::escape($table);
1058
1059                 if (count($condition) > 0) {
1060                         $array_element = each($condition);
1061                         $array_key = $array_element['key'];
1062                         if (is_int($array_key)) {
1063                                 $condition_string = " WHERE ".array_shift($condition);
1064                         } else {
1065                                 $condition_string = " WHERE `".implode("` = ? AND `", array_keys($condition))."` = ?";
1066                         }
1067                 } else {
1068                         $condition_string = "";
1069                 }
1070
1071                 if (is_bool($old_fields)) {
1072                         $do_insert = $old_fields;
1073
1074                         $old_fields = self::select($table, array(), $condition, array('limit' => 1));
1075
1076                         if (is_bool($old_fields)) {
1077                                 if ($do_insert) {
1078                                         $values = array_merge($condition, $fields);
1079                                         return self::insert($table, $values, $do_insert);
1080                                 }
1081                                 $old_fields = array();
1082                         }
1083                 }
1084
1085                 $do_update = (count($old_fields) == 0);
1086
1087                 foreach ($old_fields AS $fieldname => $content) {
1088                         if (isset($fields[$fieldname])) {
1089                                 if ($fields[$fieldname] == $content) {
1090                                         unset($fields[$fieldname]);
1091                                 } else {
1092                                         $do_update = true;
1093                                 }
1094                         }
1095                 }
1096
1097                 if (!$do_update || (count($fields) == 0)) {
1098                         return true;
1099                 }
1100
1101                 $sql = "UPDATE `".$table."` SET `".
1102                         implode("` = ?, `", array_keys($fields))."` = ?".$condition_string;
1103
1104                 $params1 = array_values($fields);
1105                 $params2 = array_values($condition);
1106                 $params = array_merge_recursive($params1, $params2);
1107
1108                 return self::e($sql, $params);
1109         }
1110
1111         /**
1112          * @brief Select rows from a table
1113          *
1114          * @param string $table Table name
1115          * @param array $fields array of selected fields
1116          * @param array $condition array of fields for condition
1117          * @param array $params array of several parameters
1118          *
1119          * @return boolean|object If "limit" is equal "1" only a single row is returned, else a query object is returned
1120          *
1121          * Example:
1122          * $table = "item";
1123          * $fields = array("id", "uri", "uid", "network");
1124          *
1125          * $condition = array("uid" => 1, "network" => 'dspr');
1126          * or:
1127          * $condition = array("`uid` = ? AND `network` IN (?, ?)", 1, 'dfrn', 'dspr');
1128          *
1129          * $params = array("order" => array("id", "received" => true), "limit" => 1);
1130          *
1131          * $data = dba::select($table, $fields, $condition, $params);
1132          */
1133         public static function select($table, $fields = array(), $condition = array(), $params = array()) {
1134                 if ($table == '') {
1135                         return false;
1136                 }
1137
1138                 if (count($fields) > 0) {
1139                         $select_fields = "`".implode("`, `", array_values($fields))."`";
1140                 } else {
1141                         $select_fields = "*";
1142                 }
1143
1144                 if (count($condition) > 0) {
1145                         $array_element = each($condition);
1146                         $array_key = $array_element['key'];
1147                         if (is_int($array_key)) {
1148                                 $condition_string = " WHERE ".array_shift($condition);
1149                         } else {
1150                                 $condition_string = " WHERE `".implode("` = ? AND `", array_keys($condition))."` = ?";
1151                         }
1152                 } else {
1153                         $condition_string = "";
1154                 }
1155
1156                 $param_string = '';
1157                 $single_row = false;
1158
1159                 if (isset($params['order'])) {
1160                         $param_string .= " ORDER BY ";
1161                         foreach ($params['order'] AS $fields => $order) {
1162                                 if (!is_int($fields)) {
1163                                         $param_string .= "`".$fields."` ".($order ? "DESC" : "ASC").", ";
1164                                 } else {
1165                                         $param_string .= "`".$order."`, ";
1166                                 }
1167                         }
1168                         $param_string = substr($param_string, 0, -2);
1169                 }
1170
1171                 if (isset($params['limit']) && is_int($params['limit'])) {
1172                         $param_string .= " LIMIT ".$params['limit'];
1173                         $single_row = ($params['limit'] == 1);
1174                 }
1175
1176                 if (isset($params['only_query']) && $params['only_query']) {
1177                         $single_row = !$params['only_query'];
1178                 }
1179
1180                 $sql = "SELECT ".$select_fields." FROM `".$table."`".$condition_string.$param_string;
1181
1182                 $result = self::p($sql, $condition);
1183
1184                 if (is_bool($result) || !$single_row) {
1185                         return $result;
1186                 } else {
1187                         $row = self::fetch($result);
1188                         self::close($result);
1189                         return $row;
1190                 }
1191         }
1192
1193
1194         /**
1195          * @brief Fills an array with data from a query
1196          *
1197          * @param object $stmt statement object
1198          * @return array Data array
1199          */
1200         public static function inArray($stmt, $do_close = true) {
1201                 if (is_bool($stmt)) {
1202                         return $stmt;
1203                 }
1204
1205                 $data = array();
1206                 while ($row = self::fetch($stmt)) {
1207                         $data[] = $row;
1208                 }
1209                 if ($do_close) {
1210                         self::close($stmt);
1211                 }
1212                 return $data;
1213         }
1214
1215         /**
1216          * @brief Returns the error number of the last query
1217          *
1218          * @return string Error number (0 if no error)
1219          */
1220         public static function errorNo() {
1221                 return self::$errorno;
1222         }
1223
1224         /**
1225          * @brief Returns the error message of the last query
1226          *
1227          * @return string Error message ('' if no error)
1228          */
1229         public static function errorMessage() {
1230                 return self::$error;
1231         }
1232
1233         /**
1234          * @brief Closes the current statement
1235          *
1236          * @param object $stmt statement object
1237          * @return boolean was the close successfull?
1238          */
1239         public static function close($stmt) {
1240                 if (!is_object($stmt)) {
1241                         return false;
1242                 }
1243
1244                 switch (self::$driver) {
1245                         case 'pdo':
1246                                 return $stmt->closeCursor();
1247                         case 'mysqli':
1248                                 return $stmt->free_result();
1249                                 return $stmt->close();
1250                         case 'mysql':
1251                                 return mysql_free_result($stmt);
1252                 }
1253         }
1254 }
1255
1256 function dbesc($str) {
1257         if (dba::$connected) {
1258                 return(dba::escape($str));
1259         } else {
1260                 return(str_replace("'","\\'",$str));
1261         }
1262 }
1263
1264 /**
1265  * @brief execute SQL query with printf style args - deprecated
1266  *
1267  * Please use the dba:: functions instead:
1268  * dba::select, dba::exists, dba::insert
1269  * dba::delete, dba::update, dba::p, dba::e
1270  *
1271  * @param $args Query parameters (1 to N parameters of different types)
1272  * @return array Query array
1273  */
1274 function q($sql) {
1275         $args = func_get_args();
1276         unset($args[0]);
1277
1278         if (!dba::$connected) {
1279                 return false;
1280         }
1281
1282         $sql = dba::clean_query($sql);
1283         $sql = dba::any_value_fallback($sql);
1284
1285         $stmt = @vsprintf($sql, $args);
1286
1287         $ret = dba::p($stmt);
1288
1289         if (is_bool($ret)) {
1290                 return $ret;
1291         }
1292
1293         $columns = dba::columnCount($ret);
1294
1295         $data = dba::inArray($ret);
1296
1297         if ((count($data) == 0) && ($columns == 0)) {
1298                 return true;
1299         }
1300
1301         return $data;
1302 }
1303
1304 function dba_timer() {
1305         return microtime(true);
1306 }