2 require_once("dbm.php");
3 require_once('include/datetime.php');
6 * @class MySQL database class
8 * For debugging, insert 'dbg(1);' anywhere in the program flow.
9 * dbg(0); will turn it off. Logging is performed at LOGGER_DATA level.
10 * When logging, all binary info is converted to text and html entities are escaped so that
11 * the debugging stream is safe to view within both terminals and web pages.
21 public $connected = false;
22 public $error = false;
23 private $_server_info = '';
25 function __construct($server, $user, $pass, $db, $install = false) {
28 $stamp1 = microtime(true);
30 $server = trim($server);
35 if (!(strlen($server) && strlen($user))) {
36 $this->connected = false;
42 if (strlen($server) && ($server !== 'localhost') && ($server !== '127.0.0.1')) {
43 if (! dns_get_record($server, DNS_A + DNS_CNAME + DNS_PTR)) {
44 $this->error = sprintf(t('Cannot locate DNS info for database server \'%s\''), $server);
45 $this->connected = false;
52 if (class_exists('\PDO') && in_array('mysql', PDO::getAvailableDrivers())) {
53 $this->driver = 'pdo';
54 $connect = "mysql:host=".$server.";dbname=".$db;
55 if (isset($a->config["system"]["db_charset"])) {
56 $connect .= ";charset=".$a->config["system"]["db_charset"];
58 $this->db = @new PDO($connect, $user, $pass);
59 if (!$this->db->errorCode()) {
60 $this->connected = true;
62 } elseif (class_exists('mysqli')) {
63 $this->driver = 'mysqli';
64 $this->db = @new mysqli($server,$user,$pass,$db);
65 if (!mysqli_connect_errno()) {
66 $this->connected = true;
68 if (isset($a->config["system"]["db_charset"])) {
69 $this->db->set_charset($a->config["system"]["db_charset"]);
72 } elseif (function_exists('mysql_connect')) {
73 $this->driver = 'mysql';
74 $this->db = mysql_connect($server,$user,$pass);
75 if ($this->db && mysql_select_db($db,$this->db)) {
76 $this->connected = true;
78 if (isset($a->config["system"]["db_charset"])) {
79 mysql_set_charset($a->config["system"]["db_charset"], $this->db);
83 // No suitable SQL driver was found.
89 if (!$this->connected) {
95 $a->save_timestamp($stamp1, "network");
99 * @brief Returns the MySQL server version string
101 * This function discriminate between the deprecated mysql API and the current
102 * object-oriented mysqli API. Example of returned string: 5.5.46-0+deb8u1
106 public function server_info() {
107 if ($this->_server_info == '') {
108 switch ($this->driver) {
110 $this->_server_info = $this->db->getAttribute(PDO::ATTR_SERVER_VERSION);
113 $this->_server_info = $this->db->server_info;
116 $this->_server_info = mysql_get_server_info($this->db);
120 return $this->_server_info;
124 * @brief Returns the selected database name
128 public function database_name() {
129 $r = $this->q("SELECT DATABASE() AS `db`");
135 * @brief Returns the number of rows
139 public function num_rows() {
140 if (!$this->result) {
144 switch ($this->driver) {
146 $rows = $this->result->rowCount();
149 $rows = $this->result->num_rows;
152 $rows = mysql_num_rows($this->result);
159 * @brief Analyze a database query and log this if some conditions are met.
161 * @param string $query The database query that will be analyzed
163 public function log_index($query) {
166 if ($a->config["system"]["db_log_index"] == "") {
170 // Don't explain an explain statement
171 if (strtolower(substr($query, 0, 7)) == "explain") {
175 // Only do the explain on "select", "update" and "delete"
176 if (!in_array(strtolower(substr($query, 0, 6)), array("select", "update", "delete"))) {
180 $r = $this->q("EXPLAIN ".$query);
181 if (!dbm::is_result($r)) {
185 $watchlist = explode(',', $a->config["system"]["db_log_index_watch"]);
186 $blacklist = explode(',', $a->config["system"]["db_log_index_blacklist"]);
188 foreach ($r AS $row) {
189 if ((intval($a->config["system"]["db_loglimit_index"]) > 0)) {
190 $log = (in_array($row['key'], $watchlist) AND
191 ($row['rows'] >= intval($a->config["system"]["db_loglimit_index"])));
196 if ((intval($a->config["system"]["db_loglimit_index_high"]) > 0) AND ($row['rows'] >= intval($a->config["system"]["db_loglimit_index_high"]))) {
200 if (in_array($row['key'], $blacklist) OR ($row['key'] == "")) {
205 $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
206 @file_put_contents($a->config["system"]["db_log_index"], datetime_convert()."\t".
207 $row['key']."\t".$row['rows']."\t".$row['Extra']."\t".
208 basename($backtrace[1]["file"])."\t".
209 $backtrace[1]["line"]."\t".$backtrace[2]["function"]."\t".
210 substr($query, 0, 2000)."\n", FILE_APPEND);
215 public function q($sql, $onlyquery = false) {
218 if (!$this->db || !$this->connected) {
224 $connstr = ($this->connected() ? "Connected" : "Disonnected");
226 $stamp1 = microtime(true);
230 if (x($a->config,'system') && x($a->config['system'], 'db_callstack')) {
231 $sql = "/*".$a->callstack()." */ ".$sql;
236 switch ($this->driver) {
238 $result = @$this->db->query($sql);
239 // Is used to separate between queries that returning data - or not
240 if (!is_bool($result)) {
241 $columns = $result->columnCount();
245 $result = @$this->db->query($sql);
248 $result = @mysql_query($sql,$this->db);
251 $stamp2 = microtime(true);
252 $duration = (float)($stamp2-$stamp1);
254 $a->save_timestamp($stamp1, "database");
256 if (strtolower(substr($orig_sql, 0, 6)) != "select") {
257 $a->save_timestamp($stamp1, "database_write");
259 if (x($a->config,'system') && x($a->config['system'],'db_log')) {
260 if (($duration > $a->config["system"]["db_loglimit"])) {
261 $duration = round($duration, 3);
262 $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
263 @file_put_contents($a->config["system"]["db_log"], datetime_convert()."\t".$duration."\t".
264 basename($backtrace[1]["file"])."\t".
265 $backtrace[1]["line"]."\t".$backtrace[2]["function"]."\t".
266 substr($sql, 0, 2000)."\n", FILE_APPEND);
270 switch ($this->driver) {
272 $errorInfo = $this->db->errorInfo();
274 $this->error = $errorInfo[2];
275 $this->errorno = $errorInfo[1];
279 if ($this->db->errno) {
280 $this->error = $this->db->error;
281 $this->errorno = $this->db->errno;
285 if (mysql_errno($this->db)) {
286 $this->error = mysql_error($this->db);
287 $this->errorno = mysql_errno($this->db);
291 if (strlen($this->error)) {
292 logger('DB Error ('.$connstr.') '.$this->errorno.': '.$this->error);
299 if ($result === false) {
301 } elseif ($result === true) {
304 switch ($this->driver) {
306 $mesg = $result->rowCount().' results'.EOL;
309 $mesg = $result->num_rows.' results'.EOL;
312 $mesg = mysql_num_rows($result).' results'.EOL;
317 $str = 'SQL = ' . printable($sql) . EOL . 'SQL returned ' . $mesg
318 . (($this->error) ? ' error: ' . $this->error : '')
321 logger('dba: ' . $str );
325 * If dbfail.out exists, we will write any failed calls directly to it,
326 * regardless of any logging that may or may nor be in effect.
327 * These usually indicate SQL syntax errors that need to be resolved.
330 if ($result === false) {
331 logger('dba: ' . printable($sql) . ' returned false.' . "\n" . $this->error);
332 if (file_exists('dbfail.out')) {
333 file_put_contents('dbfail.out', datetime_convert() . "\n" . printable($sql) . ' returned false' . "\n" . $this->error . "\n", FILE_APPEND);
337 if (is_bool($result)) {
341 $this->result = $result;
346 switch ($this->driver) {
348 while ($x = $result->fetch(PDO::FETCH_ASSOC)) {
351 $result->closeCursor();
354 while ($x = $result->fetch_array(MYSQLI_ASSOC)) {
357 $result->free_result();
360 while ($x = mysql_fetch_array($result, MYSQL_ASSOC)) {
363 mysql_free_result($result);
367 // PDO doesn't return "true" on successful operations - like mysqli does
368 // Emulate this behaviour by checking if the query returned data and had columns
369 // This should be reliable enough
370 if (($this->driver == 'pdo') AND (count($r) == 0) AND ($columns == 0)) {
374 //$a->save_timestamp($stamp1, "database");
377 logger('dba: ' . printable(print_r($r, true)));
382 public function qfetch() {
386 switch ($this->driver) {
388 $x = $this->result->fetch(PDO::FETCH_ASSOC);
391 $x = $this->result->fetch_array(MYSQLI_ASSOC);
394 $x = mysql_fetch_array($this->result, MYSQL_ASSOC);
401 public function qclose() {
403 switch ($this->driver) {
405 $this->result->closeCursor();
408 $this->result->free_result();
411 mysql_free_result($this->result);
417 public function dbg($dbg) {
421 public function escape($str) {
422 if ($this->db && $this->connected) {
423 switch ($this->driver) {
425 return substr(@$this->db->quote($str, PDO::PARAM_STR), 1, -1);
427 return @$this->db->real_escape_string($str);
429 return @mysql_real_escape_string($str,$this->db);
434 function connected() {
435 switch ($this->driver) {
437 // Not sure if this really is working like expected
438 $connected = ($this->db->getAttribute(PDO::ATTR_CONNECTION_STATUS) != "");
441 $connected = $this->db->ping();
444 $connected = mysql_ping($this->db);
450 function insert_id() {
451 switch ($this->driver) {
453 $id = $this->db->lastInsertId();
456 $id = $this->db->insert_id;
459 $id = mysql_insert_id($this->db);
465 function __destruct() {
467 switch ($this->driver) {
475 mysql_close($this->db);
482 * @brief Replaces ANY_VALUE() function by MIN() function,
483 * if the database server does not support ANY_VALUE().
485 * Considerations for Standard SQL, or MySQL with ONLY_FULL_GROUP_BY (default since 5.7.5).
486 * ANY_VALUE() is available from MySQL 5.7.5 https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html
487 * A standard fall-back is to use MIN().
489 * @param string $sql An SQL string without the values
490 * @return string The input SQL string modified if necessary.
492 public function any_value_fallback($sql) {
493 $server_info = $this->server_info();
494 if (version_compare($server_info, '5.7.5', '<') ||
495 (stripos($server_info, 'MariaDB') !== false)) {
496 $sql = str_ireplace('ANY_VALUE(', 'MIN(', $sql);
502 function printable($s) {
503 $s = preg_replace("~([\x01-\x08\x0E-\x0F\x10-\x1F\x7F-\xFF])~",".", $s);
504 $s = str_replace("\x00",'.',$s);
505 if (x($_SERVER,'SERVER_NAME')) {
506 $s = escape_tags($s);
511 // Procedural functions
512 function dbg($state) {
520 function dbesc($str) {
523 if ($db && $db->connected) {
524 return($db->escape($str));
526 return(str_replace("'","\\'",$str));
530 // Function: q($sql,$args);
531 // Description: execute SQL query with printf style args.
532 // Example: $r = q("SELECT * FROM `%s` WHERE `uid` = %d",
536 $args = func_get_args();
539 if ($db && $db->connected) {
540 $sql = $db->any_value_fallback($sql);
541 $stmt = @vsprintf($sql,$args); // Disabled warnings
542 //logger("dba: q: $stmt", LOGGER_ALL);
544 logger('dba: vsprintf error: ' . print_r(debug_backtrace(),true), LOGGER_DEBUG);
546 $db->log_index($stmt);
548 return $db->q($stmt);
553 * This will happen occasionally trying to store the
554 * session data after abnormal program termination
557 logger('dba: no database: ' . print_r($args,true));
562 * @brief Performs a query with "dirty reads"
564 * By doing dirty reads (reading uncommitted data) no locks are performed
565 * This function can be used to fetch data that doesn't need to be reliable.
567 * @param $args Query parameters (1 to N parameters of different types)
568 * @return array Query array
573 $args = func_get_args();
576 if ($db && $db->connected) {
577 $sql = $db->any_value_fallback($sql);
578 $stmt = @vsprintf($sql,$args); // Disabled warnings
580 logger('dba: vsprintf error: ' . print_r(debug_backtrace(),true), LOGGER_DEBUG);
582 $db->log_index($stmt);
584 $db->q("SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
585 $retval = $db->q($stmt);
586 $db->q("SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;");
592 * This will happen occasionally trying to store the
593 * session data after abnormal program termination
596 logger('dba: no database: ' . print_r($args,true));
602 * Raw db query, no arguments
608 if ($db && $db->connected) {
616 // Caller is responsible for ensuring that any integer arguments to
617 // dbesc_array are actually integers and not malformed strings containing
618 // SQL injection vectors. All integer array elements should be specifically
619 // cast to int to avoid trouble.
620 function dbesc_array_cb(&$item, $key) {
621 if (is_string($item))
622 $item = dbesc($item);
625 function dbesc_array(&$arr) {
626 if (is_array($arr) && count($arr)) {
627 array_walk($arr,'dbesc_array_cb');
631 function dba_timer() {
632 return microtime(true);