src/Database/DBA.php

   1 <?php
   2
   3 namespace Friendica\Database;
   4
   5 use Friendica\Core\Config\Cache\IConfigCache;
   6 use Friendica\Core\System;
   7 use Friendica\Util\DateTimeFormat;
   8 use Friendica\Util\Profiler;
   9 use mysqli;
  10 use mysqli_result;
  11 use mysqli_stmt;
  12 use PDO;
  13 use PDOException;
  14 use PDOStatement;
  15 use Psr\Log\LoggerInterface;
  16
  17 /**
  18  * @class MySQL database class
  19  *
  20  * This class is for the low level database stuff that does driver specific things.
  21  */
  22 class DBA
  23 {
  24         /**
  25          * Lowest possible date value
  26          */
  27         const NULL_DATE     = '0001-01-01';
  28         /**
  29          * Lowest possible datetime value
  30          */
  31         const NULL_DATETIME = '0001-01-01 00:00:00';
  32
  33         public static $connected = false;
  34
  35         /**
  36          * @var IConfigCache
  37          */
  38         private static $configCache;
  39         /**
  40          * @var Profiler
  41          */
  42         private static $profiler;
  43         /**
  44          * @var LoggerInterface
  45          */
  46         private static $logger;
  47         private static $server_info = '';
  48         private static $connection;
  49         private static $driver;
  50         private static $error = false;
  51         private static $errorno = 0;
  52         private static $affected_rows = 0;
  53         private static $in_transaction = false;
  54         private static $in_retrial = false;
  55         private static $relation = [];
  56         private static $db_serveraddr = '';
  57         private static $db_user = '';
  58         private static $db_pass = '';
  59         private static $db_name = '';
  60         private static $db_charset = '';
  61
  62         public static function connect(IConfigCache $configCache, Profiler $profiler, LoggerInterface $logger, $serveraddr, $user, $pass, $db, $charset = null)
  63         {
  64                 if (!is_null(self::$connection) && self::connected()) {
  65                         return true;
  66                 }
  67
  68                 // We are storing these values for being able to perform a reconnect
  69                 self::$configCache = $configCache;
  70                 self::$profiler = $profiler;
  71                 self::$logger = $logger;
  72                 self::$db_serveraddr = $serveraddr;
  73                 self::$db_user = $user;
  74                 self::$db_pass = $pass;
  75                 self::$db_name = $db;
  76                 self::$db_charset = $charset;
  77
  78                 $port = 0;
  79                 $serveraddr = trim($serveraddr);
  80
  81                 $serverdata = explode(':', $serveraddr);
  82                 $server = $serverdata[0];
  83
  84                 if (count($serverdata) > 1) {
  85                         $port = trim($serverdata[1]);
  86                 }
  87
  88                 $server = trim($server);
  89                 $user = trim($user);
  90                 $pass = trim($pass);
  91                 $db = trim($db);
  92                 $charset = trim($charset);
  93
  94                 if (!(strlen($server) && strlen($user))) {
  95                         return false;
  96                 }
  97
  98                 if (class_exists('\PDO') && in_array('mysql', PDO::getAvailableDrivers())) {
  99                         self::$driver = 'pdo';
 100                         $connect = "mysql:host=".$server.";dbname=".$db;
 101
 102                         if ($port > 0) {
 103                                 $connect .= ";port=".$port;
 104                         }
 105
 106                         if ($charset) {
 107                                 $connect .= ";charset=".$charset;
 108                         }
 109
 110                         try {
 111                                 self::$connection = @new PDO($connect, $user, $pass);
 112                                 self::$connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
 113                                 self::$connected = true;
 114                         } catch (PDOException $e) {
 115                                 /// @TODO At least log exception, don't ignore it!
 116                         }
 117                 }
 118
 119                 if (!self::$connected && class_exists('\mysqli')) {
 120                         self::$driver = 'mysqli';
 121
 122                         if ($port > 0) {
 123                                 self::$connection = @new mysqli($server, $user, $pass, $db, $port);
 124                         } else {
 125                                 self::$connection = @new mysqli($server, $user, $pass, $db);
 126                         }
 127
 128                         if (!mysqli_connect_errno()) {
 129                                 self::$connected = true;
 130
 131                                 if ($charset) {
 132                                         self::$connection->set_charset($charset);
 133                                 }
 134                         }
 135                 }
 136
 137                 // No suitable SQL driver was found.
 138                 if (!self::$connected) {
 139                         self::$driver = null;
 140                         self::$connection = null;
 141                 }
 142
 143                 return self::$connected;
 144         }
 145
 146         /**
 147          * Sets the logger for DBA
 148          *
 149          * @note this is necessary because if we want to load the logger configuration
 150          *       from the DB, but there's an error, we would print out an exception.
 151          *       So the logger gets updated after the logger configuration can be retrieved
 152          *       from the database
 153          *
 154          * @param LoggerInterface $logger
 155          */
 156         public static function setLogger(LoggerInterface $logger)
 157         {
 158                 self::$logger = $logger;
 159         }
 160
 161         /**
 162          * Disconnects the current database connection
 163          */
 164         public static function disconnect()
 165         {
 166                 if (is_null(self::$connection)) {
 167                         return;
 168                 }
 169
 170                 switch (self::$driver) {
 171                         case 'pdo':
 172                                 self::$connection = null;
 173                                 break;
 174                         case 'mysqli':
 175                                 self::$connection->close();
 176                                 self::$connection = null;
 177                                 break;
 178                 }
 179         }
 180
 181         /**
 182          * Perform a reconnect of an existing database connection
 183          */
 184         public static function reconnect() {
 185                 self::disconnect();
 186
 187                 $ret = self::connect(self::$configCache, self::$profiler, self::$logger, self::$db_serveraddr, self::$db_user, self::$db_pass, self::$db_name, self::$db_charset);
 188                 return $ret;
 189         }
 190
 191         /**
 192          * Return the database object.
 193          * @return PDO|mysqli
 194          */
 195         public static function getConnection()
 196         {
 197                 return self::$connection;
 198         }
 199
 200         /**
 201          * @brief Returns the MySQL server version string
 202          *
 203          * This function discriminate between the deprecated mysql API and the current
 204          * object-oriented mysqli API. Example of returned string: 5.5.46-0+deb8u1
 205          *
 206          * @return string
 207          */
 208         public static function serverInfo() {
 209                 if (self::$server_info == '') {
 210                         switch (self::$driver) {
 211                                 case 'pdo':
 212                                         self::$server_info = self::$connection->getAttribute(PDO::ATTR_SERVER_VERSION);
 213                                         break;
 214                                 case 'mysqli':
 215                                         self::$server_info = self::$connection->server_info;
 216                                         break;
 217                         }
 218                 }
 219                 return self::$server_info;
 220         }
 221
 222         /**
 223          * @brief Returns the selected database name
 224          *
 225          * @return string
 226          * @throws \Exception
 227          */
 228         public static function databaseName() {
 229                 $ret = self::p("SELECT DATABASE() AS `db`");
 230                 $data = self::toArray($ret);
 231                 return $data[0]['db'];
 232         }
 233
 234         /**
 235          * @brief Analyze a database query and log this if some conditions are met.
 236          *
 237          * @param string $query The database query that will be analyzed
 238          * @throws \Exception
 239          */
 240         private static function logIndex($query) {
 241
 242                 if (!self::$configCache->get('system', 'db_log_index')) {
 243                         return;
 244                 }
 245
 246                 // Don't explain an explain statement
 247                 if (strtolower(substr($query, 0, 7)) == "explain") {
 248                         return;
 249                 }
 250
 251                 // Only do the explain on "select", "update" and "delete"
 252                 if (!in_array(strtolower(substr($query, 0, 6)), ["select", "update", "delete"])) {
 253                         return;
 254                 }
 255
 256                 $r = self::p("EXPLAIN ".$query);
 257                 if (!self::isResult($r)) {
 258                         return;
 259                 }
 260
 261                 $watchlist = explode(',', self::$configCache->get('system', 'db_log_index_watch'));
 262                 $blacklist = explode(',', self::$configCache->get('system', 'db_log_index_blacklist'));
 263
 264                 while ($row = self::fetch($r)) {
 265                         if ((intval(self::$configCache->get('system', 'db_loglimit_index')) > 0)) {
 266                                 $log = (in_array($row['key'], $watchlist) &&
 267                                         ($row['rows'] >= intval(self::$configCache->get('system', 'db_loglimit_index'))));
 268                         } else {
 269                                 $log = false;
 270                         }
 271
 272                         if ((intval(self::$configCache->get('system', 'db_loglimit_index_high')) > 0) && ($row['rows'] >= intval(self::$configCache->get('system', 'db_loglimit_index_high')))) {
 273                                 $log = true;
 274                         }
 275
 276                         if (in_array($row['key'], $blacklist) || ($row['key'] == "")) {
 277                                 $log = false;
 278                         }
 279
 280                         if ($log) {
 281                                 $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
 282                                 @file_put_contents(self::$configCache->get('system', 'db_log_index'), DateTimeFormat::utcNow()."\t".
 283                                                 $row['key']."\t".$row['rows']."\t".$row['Extra']."\t".
 284                                                 basename($backtrace[1]["file"])."\t".
 285                                                 $backtrace[1]["line"]."\t".$backtrace[2]["function"]."\t".
 286                                                 substr($query, 0, 2000)."\n", FILE_APPEND);
 287                         }
 288                 }
 289         }
 290
 291         public static function escape($str) {
 292                 if (self::$connected) {
 293                         switch (self::$driver) {
 294                                 case 'pdo':
 295                                         return substr(@self::$connection->quote($str, PDO::PARAM_STR), 1, -1);
 296
 297                                 case 'mysqli':
 298                                         return @self::$connection->real_escape_string($str);
 299                         }
 300                 } else {
 301                         return str_replace("'", "\\'", $str);
 302                 }
 303         }
 304
 305         public static function connected() {
 306                 $connected = false;
 307
 308                 if (is_null(self::$connection)) {
 309                         return false;
 310                 }
 311
 312                 switch (self::$driver) {
 313                         case 'pdo':
 314                                 $r = self::p("SELECT 1");
 315                                 if (self::isResult($r)) {
 316                                         $row = self::toArray($r);
 317                                         $connected = ($row[0]['1'] == '1');
 318                                 }
 319                                 break;
 320                         case 'mysqli':
 321                                 $connected = self::$connection->ping();
 322                                 break;
 323                 }
 324                 return $connected;
 325         }
 326
 327         /**
 328          * @brief Replaces ANY_VALUE() function by MIN() function,
 329          *  if the database server does not support ANY_VALUE().
 330          *
 331          * Considerations for Standard SQL, or MySQL with ONLY_FULL_GROUP_BY (default since 5.7.5).
 332          * ANY_VALUE() is available from MySQL 5.7.5 https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html
 333          * A standard fall-back is to use MIN().
 334          *
 335          * @param string $sql An SQL string without the values
 336          * @return string The input SQL string modified if necessary.
 337          */
 338         public static function anyValueFallback($sql) {
 339                 $server_info = self::serverInfo();
 340                 if (version_compare($server_info, '5.7.5', '<') ||
 341                         (stripos($server_info, 'MariaDB') !== false)) {
 342                         $sql = str_ireplace('ANY_VALUE(', 'MIN(', $sql);
 343                 }
 344                 return $sql;
 345         }
 346
 347         /**
 348          * @brief beautifies the query - useful for "SHOW PROCESSLIST"
 349          *
 350          * This is safe when we bind the parameters later.
 351          * The parameter values aren't part of the SQL.
 352          *
 353          * @param string $sql An SQL string without the values
 354          * @return string The input SQL string modified if necessary.
 355          */
 356         public static function cleanQuery($sql) {
 357                 $search = ["\t", "\n", "\r", "  "];
 358                 $replace = [' ', ' ', ' ', ' '];
 359                 do {
 360                         $oldsql = $sql;
 361                         $sql = str_replace($search, $replace, $sql);
 362                 } while ($oldsql != $sql);
 363
 364                 return $sql;
 365         }
 366
 367
 368         /**
 369          * @brief Replaces the ? placeholders with the parameters in the $args array
 370          *
 371          * @param string $sql SQL query
 372          * @param array $args The parameters that are to replace the ? placeholders
 373          * @return string The replaced SQL query
 374          */
 375         private static function replaceParameters($sql, $args) {
 376                 $offset = 0;
 377                 foreach ($args AS $param => $value) {
 378                         if (is_int($args[$param]) || is_float($args[$param])) {
 379                                 $replace = intval($args[$param]);
 380                         } else {
 381                                 $replace = "'".self::escape($args[$param])."'";
 382                         }
 383
 384                         $pos = strpos($sql, '?', $offset);
 385                         if ($pos !== false) {
 386                                 $sql = substr_replace($sql, $replace, $pos, 1);
 387                         }
 388                         $offset = $pos + strlen($replace);
 389                 }
 390                 return $sql;
 391         }
 392
 393         /**
 394          * @brief Convert parameter array to an universal form
 395          * @param array $args Parameter array
 396          * @return array universalized parameter array
 397          */
 398         private static function getParam($args) {
 399                 unset($args[0]);
 400
 401                 // When the second function parameter is an array then use this as the parameter array
 402                 if ((count($args) > 0) && (is_array($args[1]))) {
 403                         return $args[1];
 404                 } else {
 405                         return $args;
 406                 }
 407         }
 408
 409         /**
 410          * @brief Executes a prepared statement that returns data
 411          * @usage Example: $r = p("SELECT * FROM `item` WHERE `guid` = ?", $guid);
 412          *
 413          * Please only use it with complicated queries.
 414          * For all regular queries please use DBA::select or DBA::exists
 415          *
 416          * @param string $sql SQL statement
 417          * @return bool|object statement object or result object
 418          * @throws \Exception
 419          */
 420         public static function p($sql) {
 421
 422                 $stamp1 = microtime(true);
 423
 424                 $params = self::getParam(func_get_args());
 425
 426                 // Renumber the array keys to be sure that they fit
 427                 $i = 0;
 428                 $args = [];
 429                 foreach ($params AS $param) {
 430                         // Avoid problems with some MySQL servers and boolean values. See issue #3645
 431                         if (is_bool($param)) {
 432                                 $param = (int)$param;
 433                         }
 434                         $args[++$i] = $param;
 435                 }
 436
 437                 if (!self::$connected) {
 438                         return false;
 439                 }
 440
 441                 if ((substr_count($sql, '?') != count($args)) && (count($args) > 0)) {
 442                         // Question: Should we continue or stop the query here?
 443                         self::$logger->warning('Query parameters mismatch.', ['query' => $sql, 'args' => $args, 'callstack' => System::callstack()]);
 444                 }
 445
 446                 $sql = self::cleanQuery($sql);
 447                 $sql = self::anyValueFallback($sql);
 448
 449                 $orig_sql = $sql;
 450
 451                 if (self::$configCache->get('system', 'db_callstack') !== null) {
 452                         $sql = "/*".System::callstack()." */ ".$sql;
 453                 }
 454
 455                 self::$error = '';
 456                 self::$errorno = 0;
 457                 self::$affected_rows = 0;
 458
 459                 // We have to make some things different if this function is called from "e"
 460                 $trace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 2);
 461
 462                 if (isset($trace[1])) {
 463                         $called_from = $trace[1];
 464                 } else {
 465                         // We use just something that is defined to avoid warnings
 466                         $called_from = $trace[0];
 467                 }
 468                 // We are having an own error logging in the function "e"
 469                 $called_from_e = ($called_from['function'] == 'e');
 470
 471                 switch (self::$driver) {
 472                         case 'pdo':
 473                                 // If there are no arguments we use "query"
 474                                 if (count($args) == 0) {
 475                                         if (!$retval = self::$connection->query($sql)) {
 476                                                 $errorInfo = self::$connection->errorInfo();
 477                                                 self::$error = $errorInfo[2];
 478                                                 self::$errorno = $errorInfo[1];
 479                                                 $retval = false;
 480                                                 break;
 481                                         }
 482                                         self::$affected_rows = $retval->rowCount();
 483                                         break;
 484                                 }
 485
 486                                 if (!$stmt = self::$connection->prepare($sql)) {
 487                                         $errorInfo = self::$connection->errorInfo();
 488                                         self::$error = $errorInfo[2];
 489                                         self::$errorno = $errorInfo[1];
 490                                         $retval = false;
 491                                         break;
 492                                 }
 493
 494                                 foreach ($args AS $param => $value) {
 495                                         if (is_int($args[$param])) {
 496                                                 $data_type = PDO::PARAM_INT;
 497                                         } else {
 498                                                 $data_type = PDO::PARAM_STR;
 499                                         }
 500                                         $stmt->bindParam($param, $args[$param], $data_type);
 501                                 }
 502
 503                                 if (!$stmt->execute()) {
 504                                         $errorInfo = $stmt->errorInfo();
 505                                         self::$error = $errorInfo[2];
 506                                         self::$errorno = $errorInfo[1];
 507                                         $retval = false;
 508                                 } else {
 509                                         $retval = $stmt;
 510                                         self::$affected_rows = $retval->rowCount();
 511                                 }
 512                                 break;
 513                         case 'mysqli':
 514                                 // There are SQL statements that cannot be executed with a prepared statement
 515                                 $parts = explode(' ', $orig_sql);
 516                                 $command = strtolower($parts[0]);
 517                                 $can_be_prepared = in_array($command, ['select', 'update', 'insert', 'delete']);
 518
 519                                 // The fallback routine is called as well when there are no arguments
 520                                 if (!$can_be_prepared || (count($args) == 0)) {
 521                                         $retval = self::$connection->query(self::replaceParameters($sql, $args));
 522                                         if (self::$connection->errno) {
 523                                                 self::$error = self::$connection->error;
 524                                                 self::$errorno = self::$connection->errno;
 525                                                 $retval = false;
 526                                         } else {
 527                                                 if (isset($retval->num_rows)) {
 528                                                         self::$affected_rows = $retval->num_rows;
 529                                                 } else {
 530                                                         self::$affected_rows = self::$connection->affected_rows;
 531                                                 }
 532                                         }
 533                                         break;
 534                                 }
 535
 536                                 $stmt = self::$connection->stmt_init();
 537
 538                                 if (!$stmt->prepare($sql)) {
 539                                         self::$error = $stmt->error;
 540                                         self::$errorno = $stmt->errno;
 541                                         $retval = false;
 542                                         break;
 543                                 }
 544
 545                                 $param_types = '';
 546                                 $values = [];
 547                                 foreach ($args AS $param => $value) {
 548                                         if (is_int($args[$param])) {
 549                                                 $param_types .= 'i';
 550                                         } elseif (is_float($args[$param])) {
 551                                                 $param_types .= 'd';
 552                                         } elseif (is_string($args[$param])) {
 553                                                 $param_types .= 's';
 554                                         } else {
 555                                                 $param_types .= 'b';
 556                                         }
 557                                         $values[] = &$args[$param];
 558                                 }
 559
 560                                 if (count($values) > 0) {
 561                                         array_unshift($values, $param_types);
 562                                         call_user_func_array([$stmt, 'bind_param'], $values);
 563                                 }
 564
 565                                 if (!$stmt->execute()) {
 566                                         self::$error = self::$connection->error;
 567                                         self::$errorno = self::$connection->errno;
 568                                         $retval = false;
 569                                 } else {
 570                                         $stmt->store_result();
 571                                         $retval = $stmt;
 572                                         self::$affected_rows = $retval->affected_rows;
 573                                 }
 574                                 break;
 575                 }
 576
 577                 // We are having an own error logging in the function "e"
 578                 if ((self::$errorno != 0) && !$called_from_e) {
 579                         // We have to preserve the error code, somewhere in the logging it get lost
 580                         $error = self::$error;
 581                         $errorno = self::$errorno;
 582
 583                         self::$logger->error('DB Error', [
 584                                 'code'      => self::$errorno,
 585                                 'error'     => self::$error,
 586                                 'callstack' => System::callstack(8),
 587                                 'params'    => self::replaceParameters($sql, $args),
 588                         ]);
 589
 590                         // On a lost connection we try to reconnect - but only once.
 591                         if ($errorno == 2006) {
 592                                 if (self::$in_retrial || !self::reconnect()) {
 593                                         // It doesn't make sense to continue when the database connection was lost
 594                                         if (self::$in_retrial) {
 595                                                 self::$logger->notice('Giving up retrial because of database error', [
 596                                                         'code'  => self::$errorno,
 597                                                         'error' => self::$error,
 598                                                 ]);
 599                                         } else {
 600                                                 self::$logger->notice('Couldn\'t reconnect after database error', [
 601                                                         'code'  => self::$errorno,
 602                                                         'error' => self::$error,
 603                                                 ]);
 604                                         }
 605                                         exit(1);
 606                                 } else {
 607                                         // We try it again
 608                                         self::$logger->notice('Reconnected after database error', [
 609                                                 'code'  => self::$errorno,
 610                                                 'error' => self::$error,
 611                                         ]);
 612                                         self::$in_retrial = true;
 613                                         $ret = self::p($sql, $args);
 614                                         self::$in_retrial = false;
 615                                         return $ret;
 616                                 }
 617                         }
 618
 619                         self::$error = $error;
 620                         self::$errorno = $errorno;
 621                 }
 622
 623                 self::$profiler->saveTimestamp($stamp1, 'database', System::callstack());
 624
 625                 if (self::$configCache->get('system', 'db_log')) {
 626                         $stamp2 = microtime(true);
 627                         $duration = (float)($stamp2 - $stamp1);
 628
 629                         if (($duration > self::$configCache->get('system', 'db_loglimit'))) {
 630                                 $duration = round($duration, 3);
 631                                 $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
 632
 633                                 @file_put_contents(self::$configCache->get('system', 'db_log'), DateTimeFormat::utcNow()."\t".$duration."\t".
 634                                                 basename($backtrace[1]["file"])."\t".
 635                                                 $backtrace[1]["line"]."\t".$backtrace[2]["function"]."\t".
 636                                                 substr(self::replaceParameters($sql, $args), 0, 2000)."\n", FILE_APPEND);
 637                         }
 638                 }
 639                 return $retval;
 640         }
 641
 642         /**
 643          * @brief Executes a prepared statement like UPDATE or INSERT that doesn't return data
 644          *
 645          * Please use DBA::delete, DBA::insert, DBA::update, ... instead
 646          *
 647          * @param string $sql SQL statement
 648          * @return boolean Was the query successfull? False is returned only if an error occurred
 649          * @throws \Exception
 650          */
 651         public static function e($sql) {
 652
 653                 $stamp = microtime(true);
 654
 655                 $params = self::getParam(func_get_args());
 656
 657                 // In a case of a deadlock we are repeating the query 20 times
 658                 $timeout = 20;
 659
 660                 do {
 661                         $stmt = self::p($sql, $params);
 662
 663                         if (is_bool($stmt)) {
 664                                 $retval = $stmt;
 665                         } elseif (is_object($stmt)) {
 666                                 $retval = true;
 667                         } else {
 668                                 $retval = false;
 669                         }
 670
 671                         self::close($stmt);
 672
 673                 } while ((self::$errorno == 1213) && (--$timeout > 0));
 674
 675                 if (self::$errorno != 0) {
 676                         // We have to preserve the error code, somewhere in the logging it get lost
 677                         $error = self::$error;
 678                         $errorno = self::$errorno;
 679
 680                         self::$logger->error('DB Error', [
 681                                 'code'      => self::$errorno,
 682                                 'error'     => self::$error,
 683                                 'callstack' => System::callstack(8),
 684                                 'params'    => self::replaceParameters($sql, $params),
 685                         ]);
 686
 687                         // On a lost connection we simply quit.
 688                         // A reconnect like in self::p could be dangerous with modifications
 689                         if ($errorno == 2006) {
 690                                 self::$logger->notice('Giving up because of database error', [
 691                                         'code'  => self::$errorno,
 692                                         'error' => self::$error,
 693                                 ]);
 694                                 exit(1);
 695                         }
 696
 697                         self::$error = $error;
 698                         self::$errorno = $errorno;
 699                 }
 700
 701                 self::$profiler->saveTimestamp($stamp, "database_write", System::callstack());
 702
 703                 return $retval;
 704         }
 705
 706         /**
 707          * @brief Check if data exists
 708          *
 709          * @param string $table     Table name
 710          * @param array  $condition array of fields for condition
 711          *
 712          * @return boolean Are there rows for that condition?
 713          * @throws \Exception
 714          */
 715         public static function exists($table, $condition) {
 716                 if (empty($table)) {
 717                         return false;
 718                 }
 719
 720                 $fields = [];
 721
 722                 if (empty($condition)) {
 723                         return DBStructure::existsTable($table);
 724                 }
 725
 726                 reset($condition);
 727                 $first_key = key($condition);
 728                 if (!is_int($first_key)) {
 729                         $fields = [$first_key];
 730                 }
 731
 732                 $stmt = self::select($table, $fields, $condition, ['limit' => 1]);
 733
 734                 if (is_bool($stmt)) {
 735                         $retval = $stmt;
 736                 } else {
 737                         $retval = (self::numRows($stmt) > 0);
 738                 }
 739
 740                 self::close($stmt);
 741
 742                 return $retval;
 743         }
 744
 745         /**
 746          * Fetches the first row
 747          *
 748          * Please use DBA::selectFirst or DBA::exists whenever this is possible.
 749          *
 750          * @brief Fetches the first row
 751          * @param string $sql SQL statement
 752          * @return array first row of query
 753          * @throws \Exception
 754          */
 755         public static function fetchFirst($sql) {
 756                 $params = self::getParam(func_get_args());
 757
 758                 $stmt = self::p($sql, $params);
 759
 760                 if (is_bool($stmt)) {
 761                         $retval = $stmt;
 762                 } else {
 763                         $retval = self::fetch($stmt);
 764                 }
 765
 766                 self::close($stmt);
 767
 768                 return $retval;
 769         }
 770
 771         /**
 772          * @brief Returns the number of affected rows of the last statement
 773          *
 774          * @return int Number of rows
 775          */
 776         public static function affectedRows() {
 777                 return self::$affected_rows;
 778         }
 779
 780         /**
 781          * @brief Returns the number of columns of a statement
 782          *
 783          * @param object Statement object
 784          * @return int Number of columns
 785          */
 786         public static function columnCount($stmt) {
 787                 if (!is_object($stmt)) {
 788                         return 0;
 789                 }
 790                 switch (self::$driver) {
 791                         case 'pdo':
 792                                 return $stmt->columnCount();
 793                         case 'mysqli':
 794                                 return $stmt->field_count;
 795                 }
 796                 return 0;
 797         }
 798         /**
 799          * @brief Returns the number of rows of a statement
 800          *
 801          * @param PDOStatement|mysqli_result|mysqli_stmt Statement object
 802          * @return int Number of rows
 803          */
 804         public static function numRows($stmt) {
 805                 if (!is_object($stmt)) {
 806                         return 0;
 807                 }
 808                 switch (self::$driver) {
 809                         case 'pdo':
 810                                 return $stmt->rowCount();
 811                         case 'mysqli':
 812                                 return $stmt->num_rows;
 813                 }
 814                 return 0;
 815         }
 816
 817         /**
 818          * @brief Fetch a single row
 819          *
 820          * @param mixed $stmt statement object
 821          * @return array current row
 822          */
 823         public static function fetch($stmt) {
 824
 825                 $stamp1 = microtime(true);
 826
 827                 $columns = [];
 828
 829                 if (!is_object($stmt)) {
 830                         return false;
 831                 }
 832
 833                 switch (self::$driver) {
 834                         case 'pdo':
 835                                 $columns = $stmt->fetch(PDO::FETCH_ASSOC);
 836                                 break;
 837                         case 'mysqli':
 838                                 if (get_class($stmt) == 'mysqli_result') {
 839                                         $columns = $stmt->fetch_assoc();
 840                                         break;
 841                                 }
 842
 843                                 // This code works, but is slow
 844
 845                                 // Bind the result to a result array
 846                                 $cols = [];
 847
 848                                 $cols_num = [];
 849                                 for ($x = 0; $x < $stmt->field_count; $x++) {
 850                                         $cols[] = &$cols_num[$x];
 851                                 }
 852
 853                                 call_user_func_array([$stmt, 'bind_result'], $cols);
 854
 855                                 if (!$stmt->fetch()) {
 856                                         return false;
 857                                 }
 858
 859                                 // The slow part:
 860                                 // We need to get the field names for the array keys
 861                                 // It seems that there is no better way to do this.
 862                                 $result = $stmt->result_metadata();
 863                                 $fields = $result->fetch_fields();
 864
 865                                 foreach ($cols_num AS $param => $col) {
 866                                         $columns[$fields[$param]->name] = $col;
 867                                 }
 868                 }
 869
 870                 self::$profiler->saveTimestamp($stamp1, 'database', System::callstack());
 871
 872                 return $columns;
 873         }
 874
 875         /**
 876          * @brief Insert a row into a table
 877          *
 878          * @param string $table               Table name
 879          * @param array  $param               parameter array
 880          * @param bool   $on_duplicate_update Do an update on a duplicate entry
 881          *
 882          * @return boolean was the insert successful?
 883          * @throws \Exception
 884          */
 885         public static function insert($table, $param, $on_duplicate_update = false) {
 886
 887                 if (empty($table) || empty($param)) {
 888                         self::$logger->info('Table and fields have to be set');
 889                         return false;
 890                 }
 891
 892                 $sql = "INSERT INTO `".self::escape($table)."` (`".implode("`, `", array_keys($param))."`) VALUES (".
 893                         substr(str_repeat("?, ", count($param)), 0, -2).")";
 894
 895                 if ($on_duplicate_update) {
 896                         $sql .= " ON DUPLICATE KEY UPDATE `".implode("` = ?, `", array_keys($param))."` = ?";
 897
 898                         $values = array_values($param);
 899                         $param = array_merge_recursive($values, $values);
 900                 }
 901
 902                 return self::e($sql, $param);
 903         }
 904
 905         /**
 906          * @brief Fetch the id of the last insert command
 907          *
 908          * @return integer Last inserted id
 909          */
 910         public static function lastInsertId() {
 911                 switch (self::$driver) {
 912                         case 'pdo':
 913                                 $id = self::$connection->lastInsertId();
 914                                 break;
 915                         case 'mysqli':
 916                                 $id = self::$connection->insert_id;
 917                                 break;
 918                 }
 919                 return $id;
 920         }
 921
 922         /**
 923          * @brief Locks a table for exclusive write access
 924          *
 925          * This function can be extended in the future to accept a table array as well.
 926          *
 927          * @param string $table Table name
 928          *
 929          * @return boolean was the lock successful?
 930          * @throws \Exception
 931          */
 932         public static function lock($table) {
 933                 // See here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html
 934                 if (self::$driver == 'pdo') {
 935                         self::e("SET autocommit=0");
 936                         self::$connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
 937                 } else {
 938                         self::$connection->autocommit(false);
 939                 }
 940
 941                 $success = self::e("LOCK TABLES `".self::escape($table)."` WRITE");
 942
 943                 if (self::$driver == 'pdo') {
 944                         self::$connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
 945                 }
 946
 947                 if (!$success) {
 948                         if (self::$driver == 'pdo') {
 949                                 self::e("SET autocommit=1");
 950                         } else {
 951                                 self::$connection->autocommit(true);
 952                         }
 953                 } else {
 954                         self::$in_transaction = true;
 955                 }
 956                 return $success;
 957         }
 958
 959         /**
 960          * @brief Unlocks all locked tables
 961          *
 962          * @return boolean was the unlock successful?
 963          * @throws \Exception
 964          */
 965         public static function unlock() {
 966                 // See here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html
 967                 self::performCommit();
 968
 969                 if (self::$driver == 'pdo') {
 970                         self::$connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
 971                 }
 972
 973                 $success = self::e("UNLOCK TABLES");
 974
 975                 if (self::$driver == 'pdo') {
 976                         self::$connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
 977                         self::e("SET autocommit=1");
 978                 } else {
 979                         self::$connection->autocommit(true);
 980                 }
 981
 982                 self::$in_transaction = false;
 983                 return $success;
 984         }
 985
 986         /**
 987          * @brief Starts a transaction
 988          *
 989          * @return boolean Was the command executed successfully?
 990          */
 991         public static function transaction() {
 992                 if (!self::performCommit()) {
 993                         return false;
 994                 }
 995
 996                 switch (self::$driver) {
 997                         case 'pdo':
 998                                 if (!self::$connection->inTransaction() && !self::$connection->beginTransaction()) {
 999                                         return false;
1000                                 }
1001                                 break;
1002
1003                         case 'mysqli':
1004                                 if (!self::$connection->begin_transaction()) {
1005                                         return false;
1006                                 }
1007                                 break;
1008                 }
1009
1010                 self::$in_transaction = true;
1011                 return true;
1012         }
1013
1014         private static function performCommit()
1015         {
1016                 switch (self::$driver) {
1017                         case 'pdo':
1018                                 if (!self::$connection->inTransaction()) {
1019                                         return true;
1020                                 }
1021
1022                                 return self::$connection->commit();
1023
1024                         case 'mysqli':
1025                                 return self::$connection->commit();
1026                 }
1027
1028                 return true;
1029         }
1030
1031         /**
1032          * @brief Does a commit
1033          *
1034          * @return boolean Was the command executed successfully?
1035          */
1036         public static function commit() {
1037                 if (!self::performCommit()) {
1038                         return false;
1039                 }
1040                 self::$in_transaction = false;
1041                 return true;
1042         }
1043
1044         /**
1045          * @brief Does a rollback
1046          *
1047          * @return boolean Was the command executed successfully?
1048          */
1049         public static function rollback() {
1050                 $ret = false;
1051
1052                 switch (self::$driver) {
1053                         case 'pdo':
1054                                 if (!self::$connection->inTransaction()) {
1055                                         $ret = true;
1056                                         break;
1057                                 }
1058                                 $ret = self::$connection->rollBack();
1059                                 break;
1060
1061                         case 'mysqli':
1062                                 $ret = self::$connection->rollback();
1063                                 break;
1064                 }
1065                 self::$in_transaction = false;
1066                 return $ret;
1067         }
1068
1069         /**
1070          * @brief Build the array with the table relations
1071          *
1072          * The array is build from the database definitions in DBStructure.php
1073          *
1074          * This process must only be started once, since the value is cached.
1075          */
1076         private static function buildRelationData() {
1077                 $definition = DBStructure::definition(self::$configCache->get('system', 'basepath'));
1078
1079                 foreach ($definition AS $table => $structure) {
1080                         foreach ($structure['fields'] AS $field => $field_struct) {
1081                                 if (isset($field_struct['relation'])) {
1082                                         foreach ($field_struct['relation'] AS $rel_table => $rel_field) {
1083                                                 self::$relation[$rel_table][$rel_field][$table][] = $field;
1084                                         }
1085                                 }
1086                         }
1087                 }
1088         }
1089
1090         /**
1091          * @brief Delete a row from a table
1092          *
1093          * @param string $table      Table name
1094          * @param array  $conditions Field condition(s)
1095          * @param array  $options
1096          *                           - cascade: If true we delete records in other tables that depend on the one we're deleting through
1097          *                           relations (default: true)
1098          * @param array  $callstack  Internal use: prevent endless loops
1099          *
1100          * @return boolean was the delete successful?
1101          * @throws \Exception
1102          */
1103         public static function delete($table, array $conditions, array $options = [], array &$callstack = [])
1104         {
1105                 if (empty($table) || empty($conditions)) {
1106                         self::$logger->info('Table and conditions have to be set');
1107                         return false;
1108                 }
1109
1110                 $commands = [];
1111
1112                 // Create a key for the loop prevention
1113                 $key = $table . ':' . json_encode($conditions);
1114
1115                 // We quit when this key already exists in the callstack.
1116                 if (isset($callstack[$key])) {
1117                         return $commands;
1118                 }
1119
1120                 $callstack[$key] = true;
1121
1122                 $table = self::escape($table);
1123
1124                 $commands[$key] = ['table' => $table, 'conditions' => $conditions];
1125
1126                 // Don't use "defaults" here, since it would set "false" to "true"
1127                 if (isset($options['cascade'])) {
1128                         $cascade = $options['cascade'];
1129                 } else {
1130                         $cascade = true;
1131                 }
1132
1133                 // To speed up the whole process we cache the table relations
1134                 if ($cascade && count(self::$relation) == 0) {
1135                         self::buildRelationData();
1136                 }
1137
1138                 // Is there a relation entry for the table?
1139                 if ($cascade && isset(self::$relation[$table])) {
1140                         // We only allow a simple "one field" relation.
1141                         $field = array_keys(self::$relation[$table])[0];
1142                         $rel_def = array_values(self::$relation[$table])[0];
1143
1144                         // Create a key for preventing double queries
1145                         $qkey = $field . '-' . $table . ':' . json_encode($conditions);
1146
1147                         // When the search field is the relation field, we don't need to fetch the rows
1148                         // This is useful when the leading record is already deleted in the frontend but the rest is done in the backend
1149                         if ((count($conditions) == 1) && ($field == array_keys($conditions)[0])) {
1150                                 foreach ($rel_def AS $rel_table => $rel_fields) {
1151                                         foreach ($rel_fields AS $rel_field) {
1152                                                 self::delete($rel_table, [$rel_field => array_values($conditions)[0]], $options, $callstack);
1153                                         }
1154                                 }
1155                                 // We quit when this key already exists in the callstack.
1156                         } elseif (!isset($callstack[$qkey])) {
1157                                 $callstack[$qkey] = true;
1158
1159                                 // Fetch all rows that are to be deleted
1160                                 $data = self::select($table, [$field], $conditions);
1161
1162                                 while ($row = self::fetch($data)) {
1163                                         self::delete($table, [$field => $row[$field]], $options, $callstack);
1164                                 }
1165
1166                                 self::close($data);
1167
1168                                 // Since we had split the delete command we don't need the original command anymore
1169                                 unset($commands[$key]);
1170                         }
1171                 }
1172
1173                 // Now we finalize the process
1174                 $do_transaction = !self::$in_transaction;
1175
1176                 if ($do_transaction) {
1177                         self::transaction();
1178                 }
1179
1180                 $compacted = [];
1181                 $counter = [];
1182
1183                 foreach ($commands AS $command) {
1184                         $conditions = $command['conditions'];
1185                         reset($conditions);
1186                         $first_key = key($conditions);
1187
1188                         $condition_string = self::buildCondition($conditions);
1189
1190                         if ((count($command['conditions']) > 1) || is_int($first_key)) {
1191                                 $sql = "DELETE FROM `" . $command['table'] . "`" . $condition_string;
1192                                 self::$logger->debug(self::replaceParameters($sql, $conditions));
1193
1194                                 if (!self::e($sql, $conditions)) {
1195                                         if ($do_transaction) {
1196                                                 self::rollback();
1197                                         }
1198                                         return false;
1199                                 }
1200                         } else {
1201                                 $key_table = $command['table'];
1202                                 $key_condition = array_keys($command['conditions'])[0];
1203                                 $value = array_values($command['conditions'])[0];
1204
1205                                 // Split the SQL queries in chunks of 100 values
1206                                 // We do the $i stuff here to make the code better readable
1207                                 $i = isset($counter[$key_table][$key_condition]) ? $counter[$key_table][$key_condition] : 0;
1208                                 if (isset($compacted[$key_table][$key_condition][$i]) && count($compacted[$key_table][$key_condition][$i]) > 100) {
1209                                         ++$i;
1210                                 }
1211
1212                                 $compacted[$key_table][$key_condition][$i][$value] = $value;
1213                                 $counter[$key_table][$key_condition] = $i;
1214                         }
1215                 }
1216                 foreach ($compacted AS $table => $values) {
1217                         foreach ($values AS $field => $field_value_list) {
1218                                 foreach ($field_value_list AS $field_values) {
1219                                         $sql = "DELETE FROM `" . $table . "` WHERE `" . $field . "` IN (" .
1220                                                 substr(str_repeat("?, ", count($field_values)), 0, -2) . ");";
1221
1222                                         self::$logger->debug(self::replaceParameters($sql, $field_values));
1223
1224                                         if (!self::e($sql, $field_values)) {
1225                                                 if ($do_transaction) {
1226                                                         self::rollback();
1227                                                 }
1228                                                 return false;
1229                                         }
1230                                 }
1231                         }
1232                 }
1233                 if ($do_transaction) {
1234                         self::commit();
1235                 }
1236                 return true;
1237         }
1238
1239         /**
1240          * @brief Updates rows
1241          *
1242          * Updates rows in the database. When $old_fields is set to an array,
1243          * the system will only do an update if the fields in that array changed.
1244          *
1245          * Attention:
1246          * Only the values in $old_fields are compared.
1247          * This is an intentional behaviour.
1248          *
1249          * Example:
1250          * We include the timestamp field in $fields but not in $old_fields.
1251          * Then the row will only get the new timestamp when the other fields had changed.
1252          *
1253          * When $old_fields is set to a boolean value the system will do this compare itself.
1254          * When $old_fields is set to "true" the system will do an insert if the row doesn't exists.
1255          *
1256          * Attention:
1257          * Only set $old_fields to a boolean value when you are sure that you will update a single row.
1258          * When you set $old_fields to "true" then $fields must contain all relevant fields!
1259          *
1260          * @param string        $table      Table name
1261          * @param array         $fields     contains the fields that are updated
1262          * @param array         $condition  condition array with the key values
1263          * @param array|boolean $old_fields array with the old field values that are about to be replaced (true = update on duplicate)
1264          *
1265          * @return boolean was the update successfull?
1266          * @throws \Exception
1267          */
1268         public static function update($table, $fields, $condition, $old_fields = []) {
1269
1270                 if (empty($table) || empty($fields) || empty($condition)) {
1271                         self::$logger->info('Table, fields and condition have to be set');
1272                         return false;
1273                 }
1274
1275                 $table = self::escape($table);
1276
1277                 $condition_string = self::buildCondition($condition);
1278
1279                 if (is_bool($old_fields)) {
1280                         $do_insert = $old_fields;
1281
1282                         $old_fields = self::selectFirst($table, [], $condition);
1283
1284                         if (is_bool($old_fields)) {
1285                                 if ($do_insert) {
1286                                         $values = array_merge($condition, $fields);
1287                                         return self::insert($table, $values, $do_insert);
1288                                 }
1289                                 $old_fields = [];
1290                         }
1291                 }
1292
1293                 $do_update = (count($old_fields) == 0);
1294
1295                 foreach ($old_fields AS $fieldname => $content) {
1296                         if (isset($fields[$fieldname])) {
1297                                 if (($fields[$fieldname] == $content) && !is_null($content)) {
1298                                         unset($fields[$fieldname]);
1299                                 } else {
1300                                         $do_update = true;
1301                                 }
1302                         }
1303                 }
1304
1305                 if (!$do_update || (count($fields) == 0)) {
1306                         return true;
1307                 }
1308
1309                 $sql = "UPDATE `".$table."` SET `".
1310                         implode("` = ?, `", array_keys($fields))."` = ?".$condition_string;
1311
1312                 $params1 = array_values($fields);
1313                 $params2 = array_values($condition);
1314                 $params = array_merge_recursive($params1, $params2);
1315
1316                 return self::e($sql, $params);
1317         }
1318
1319         /**
1320          * Retrieve a single record from a table and returns it in an associative array
1321          *
1322          * @brief Retrieve a single record from a table
1323          * @param string $table
1324          * @param array  $fields
1325          * @param array  $condition
1326          * @param array  $params
1327          * @return bool|array
1328          * @throws \Exception
1329          * @see   self::select
1330          */
1331         public static function selectFirst($table, array $fields = [], array $condition = [], $params = [])
1332         {
1333                 $params['limit'] = 1;
1334                 $result = self::select($table, $fields, $condition, $params);
1335
1336                 if (is_bool($result)) {
1337                         return $result;
1338                 } else {
1339                         $row = self::fetch($result);
1340                         self::close($result);
1341                         return $row;
1342                 }
1343         }
1344
1345         /**
1346          * @brief Select rows from a table
1347          *
1348          * @param string $table     Table name
1349          * @param array  $fields    Array of selected fields, empty for all
1350          * @param array  $condition Array of fields for condition
1351          * @param array  $params    Array of several parameters
1352          *
1353          * @return boolean|object
1354          *
1355          * Example:
1356          * $table = "item";
1357          * $fields = array("id", "uri", "uid", "network");
1358          *
1359          * $condition = array("uid" => 1, "network" => 'dspr');
1360          * or:
1361          * $condition = array("`uid` = ? AND `network` IN (?, ?)", 1, 'dfrn', 'dspr');
1362          *
1363          * $params = array("order" => array("id", "received" => true), "limit" => 10);
1364          *
1365          * $data = DBA::select($table, $fields, $condition, $params);
1366          * @throws \Exception
1367          */
1368         public static function select($table, array $fields = [], array $condition = [], array $params = [])
1369         {
1370                 if ($table == '') {
1371                         return false;
1372                 }
1373
1374                 $table = self::escape($table);
1375
1376                 if (count($fields) > 0) {
1377                         $select_fields = "`" . implode("`, `", array_values($fields)) . "`";
1378                 } else {
1379                         $select_fields = "*";
1380                 }
1381
1382                 $condition_string = self::buildCondition($condition);
1383
1384                 $param_string = self::buildParameter($params);
1385
1386                 $sql = "SELECT " . $select_fields . " FROM `" . $table . "`" . $condition_string . $param_string;
1387
1388                 $result = self::p($sql, $condition);
1389
1390                 return $result;
1391         }
1392
1393         /**
1394          * @brief Counts the rows from a table satisfying the provided condition
1395          *
1396          * @param string $table     Table name
1397          * @param array  $condition array of fields for condition
1398          *
1399          * @return int
1400          *
1401          * Example:
1402          * $table = "item";
1403          *
1404          * $condition = ["uid" => 1, "network" => 'dspr'];
1405          * or:
1406          * $condition = ["`uid` = ? AND `network` IN (?, ?)", 1, 'dfrn', 'dspr'];
1407          *
1408          * $count = DBA::count($table, $condition);
1409          * @throws \Exception
1410          */
1411         public static function count($table, array $condition = [])
1412         {
1413                 if ($table == '') {
1414                         return false;
1415                 }
1416
1417                 $condition_string = self::buildCondition($condition);
1418
1419                 $sql = "SELECT COUNT(*) AS `count` FROM `".$table."`".$condition_string;
1420
1421                 $row = self::fetchFirst($sql, $condition);
1422
1423                 return $row['count'];
1424         }
1425
1426         /**
1427          * @brief Returns the SQL condition string built from the provided condition array
1428          *
1429          * This function operates with two modes.
1430          * - Supplied with a filed/value associative array, it builds simple strict
1431          *   equality conditions linked by AND.
1432          * - Supplied with a flat list, the first element is the condition string and
1433          *   the following arguments are the values to be interpolated
1434          *
1435          * $condition = ["uid" => 1, "network" => 'dspr'];
1436          * or:
1437          * $condition = ["`uid` = ? AND `network` IN (?, ?)", 1, 'dfrn', 'dspr'];
1438          *
1439          * In either case, the provided array is left with the parameters only
1440          *
1441          * @param array $condition
1442          * @return string
1443          */
1444         public static function buildCondition(array &$condition = [])
1445         {
1446                 $condition_string = '';
1447                 if (count($condition) > 0) {
1448                         reset($condition);
1449                         $first_key = key($condition);
1450                         if (is_int($first_key)) {
1451                                 $condition_string = " WHERE (" . array_shift($condition) . ")";
1452                         } else {
1453                                 $new_values = [];
1454                                 $condition_string = "";
1455                                 foreach ($condition as $field => $value) {
1456                                         if ($condition_string != "") {
1457                                                 $condition_string .= " AND ";
1458                                         }
1459                                         if (is_array($value)) {
1460                                                 /* Workaround for MySQL Bug #64791.
1461                                                  * Never mix data types inside any IN() condition.
1462                                                  * In case of mixed types, cast all as string.
1463                                                  * Logic needs to be consistent with DBA::p() data types.
1464                                                  */
1465                                                 $is_int = false;
1466                                                 $is_alpha = false;
1467                                                 foreach ($value as $single_value) {
1468                                                         if (is_int($single_value)) {
1469                                                                 $is_int = true;
1470                                                         } else {
1471                                                                 $is_alpha = true;
1472                                                         }
1473                                                 }
1474
1475                                                 if ($is_int && $is_alpha) {
1476                                                         foreach ($value as &$ref) {
1477                                                                 if (is_int($ref)) {
1478                                                                         $ref = (string)$ref;
1479                                                                 }
1480                                                         }
1481                                                         unset($ref); //Prevent accidental re-use.
1482                                                 }
1483
1484                                                 $new_values = array_merge($new_values, array_values($value));
1485                                                 $placeholders = substr(str_repeat("?, ", count($value)), 0, -2);
1486                                                 $condition_string .= "`" . $field . "` IN (" . $placeholders . ")";
1487                                         } elseif (is_null($value)) {
1488                                                 $condition_string .= "`" . $field . "` IS NULL";
1489                                         } else {
1490                                                 $new_values[$field] = $value;
1491                                                 $condition_string .= "`" . $field . "` = ?";
1492                                         }
1493                                 }
1494                                 $condition_string = " WHERE (" . $condition_string . ")";
1495                                 $condition = $new_values;
1496                         }
1497                 }
1498
1499                 return $condition_string;
1500         }
1501
1502         /**
1503          * @brief Returns the SQL parameter string built from the provided parameter array
1504          *
1505          * @param array $params
1506          * @return string
1507          */
1508         public static function buildParameter(array $params = [])
1509         {
1510                 $groupby_string = '';
1511                 if (isset($params['group_by'])) {
1512                         $groupby_string = " GROUP BY ";
1513                         foreach ($params['group_by'] as $fields) {
1514                                 $groupby_string .= "`" . $fields . "`, ";
1515                         }
1516                         $groupby_string = substr($groupby_string, 0, -2);
1517                 }
1518
1519                 $order_string = '';
1520                 if (isset($params['order'])) {
1521                         $order_string = " ORDER BY ";
1522                         foreach ($params['order'] AS $fields => $order) {
1523                                 if ($order === 'RAND()') {
1524                                         $order_string .= "RAND(), ";
1525                                 } elseif (!is_int($fields)) {
1526                                         $order_string .= "`" . $fields . "` " . ($order ? "DESC" : "ASC") . ", ";
1527                                 } else {
1528                                         $order_string .= "`" . $order . "`, ";
1529                                 }
1530                         }
1531                         $order_string = substr($order_string, 0, -2);
1532                 }
1533
1534                 $limit_string = '';
1535                 if (isset($params['limit']) && is_numeric($params['limit'])) {
1536                         $limit_string = " LIMIT " . intval($params['limit']);
1537                 }
1538
1539                 if (isset($params['limit']) && is_array($params['limit'])) {
1540                         $limit_string = " LIMIT " . intval($params['limit'][0]) . ", " . intval($params['limit'][1]);
1541                 }
1542
1543                 return $groupby_string . $order_string . $limit_string;
1544         }
1545
1546         /**
1547          * @brief Fills an array with data from a query
1548          *
1549          * @param object $stmt statement object
1550          * @param bool   $do_close
1551          * @return array Data array
1552          */
1553         public static function toArray($stmt, $do_close = true) {
1554                 if (is_bool($stmt)) {
1555                         return $stmt;
1556                 }
1557
1558                 $data = [];
1559                 while ($row = self::fetch($stmt)) {
1560                         $data[] = $row;
1561                 }
1562                 if ($do_close) {
1563                         self::close($stmt);
1564                 }
1565                 return $data;
1566         }
1567
1568         /**
1569          * @brief Returns the error number of the last query
1570          *
1571          * @return string Error number (0 if no error)
1572          */
1573         public static function errorNo() {
1574                 return self::$errorno;
1575         }
1576
1577         /**
1578          * @brief Returns the error message of the last query
1579          *
1580          * @return string Error message ('' if no error)
1581          */
1582         public static function errorMessage() {
1583                 return self::$error;
1584         }
1585
1586         /**
1587          * @brief Closes the current statement
1588          *
1589          * @param object $stmt statement object
1590          * @return boolean was the close successful?
1591          */
1592         public static function close($stmt) {
1593
1594                 $stamp1 = microtime(true);
1595
1596                 if (!is_object($stmt)) {
1597                         return false;
1598                 }
1599
1600                 switch (self::$driver) {
1601                         case 'pdo':
1602                                 $ret = $stmt->closeCursor();
1603                                 break;
1604                         case 'mysqli':
1605                                 // MySQLi offers both a mysqli_stmt and a mysqli_result class.
1606                                 // We should be careful not to assume the object type of $stmt
1607                                 // because DBA::p() has been able to return both types.
1608                                 if ($stmt instanceof mysqli_stmt) {
1609                                         $stmt->free_result();
1610                                         $ret = $stmt->close();
1611                                 } elseif ($stmt instanceof mysqli_result) {
1612                                         $stmt->free();
1613                                         $ret = true;
1614                                 } else {
1615                                         $ret = false;
1616                                 }
1617                                 break;
1618                 }
1619
1620                 self::$profiler->saveTimestamp($stamp1, 'database', System::callstack());
1621
1622                 return $ret;
1623         }
1624
1625         /**
1626          * @brief Return a list of database processes
1627          *
1628          * @return array
1629          *      'list' => List of processes, separated in their different states
1630          *      'amount' => Number of concurrent database processes
1631          * @throws \Exception
1632          */
1633         public static function processlist()
1634         {
1635                 $ret = self::p("SHOW PROCESSLIST");
1636                 $data = self::toArray($ret);
1637
1638                 $processes = 0;
1639                 $states = [];
1640                 foreach ($data as $process) {
1641                         $state = trim($process["State"]);
1642
1643                         // Filter out all non blocking processes
1644                         if (!in_array($state, ["", "init", "statistics", "updating"])) {
1645                                 ++$states[$state];
1646                                 ++$processes;
1647                         }
1648                 }
1649
1650                 $statelist = "";
1651                 foreach ($states as $state => $usage) {
1652                         if ($statelist != "") {
1653                                 $statelist .= ", ";
1654                         }
1655                         $statelist .= $state.": ".$usage;
1656                 }
1657                 return(["list" => $statelist, "amount" => $processes]);
1658         }
1659
1660         /**
1661          * Checks if $array is a filled array with at least one entry.
1662          *
1663          * @param mixed $array A filled array with at least one entry
1664          *
1665          * @return boolean Whether $array is a filled array or an object with rows
1666          */
1667         public static function isResult($array)
1668         {
1669                 // It could be a return value from an update statement
1670                 if (is_bool($array)) {
1671                         return $array;
1672                 }
1673
1674                 if (is_object($array)) {
1675                         return self::numRows($array) > 0;
1676                 }
1677
1678                 return (is_array($array) && (count($array) > 0));
1679         }
1680
1681         /**
1682          * @brief Callback function for "esc_array"
1683          *
1684          * @param mixed   $value         Array value
1685          * @param string  $key           Array key
1686          * @param boolean $add_quotation add quotation marks for string values
1687          * @return void
1688          */
1689         private static function escapeArrayCallback(&$value, $key, $add_quotation)
1690         {
1691                 if (!$add_quotation) {
1692                         if (is_bool($value)) {
1693                                 $value = ($value ? '1' : '0');
1694                         } else {
1695                                 $value = self::escape($value);
1696                         }
1697                         return;
1698                 }
1699
1700                 if (is_bool($value)) {
1701                         $value = ($value ? 'true' : 'false');
1702                 } elseif (is_float($value) || is_integer($value)) {
1703                         $value = (string) $value;
1704                 } else {
1705                         $value = "'" . self::escape($value) . "'";
1706                 }
1707         }
1708
1709         /**
1710          * @brief Escapes a whole array
1711          *
1712          * @param mixed   $arr           Array with values to be escaped
1713          * @param boolean $add_quotation add quotation marks for string values
1714          * @return void
1715          */
1716         public static function escapeArray(&$arr, $add_quotation = false)
1717         {
1718                 array_walk($arr, 'self::escapeArrayCallback', $add_quotation);
1719         }
1720 }