1<?php 2/********************************************************************* 3 mysqli.php 4 5 Collection of MySQL helper interface functions. 6 7 Mostly wrappers with error/resource checking. 8 9 Peter Rotich <peter@osticket.com> 10 Jared Hancock <jared@osticket.com> 11 Copyright (c) 2006-2013 osTicket 12 http://www.osticket.com 13 14 Released under the GNU General Public License WITHOUT ANY WARRANTY. 15 See LICENSE.TXT for details. 16 17 vim: expandtab sw=4 ts=4 sts=4: 18**********************************************************************/ 19$__db = null; 20 21function db_connect($host, $user, $passwd, $options = array()) { 22 global $__db; 23 24 //Assert 25 if(!strlen($user) || !strlen($host)) 26 return NULL; 27 28 if (!($__db = mysqli_init())) 29 return NULL; 30 31 // Setup SSL if enabled 32 if (isset($options['ssl'])) 33 $__db->ssl_set( 34 $options['ssl']['key'], 35 $options['ssl']['cert'], 36 $options['ssl']['ca'], 37 null, null); 38 elseif(!$passwd) 39 return NULL; 40 41 $port = ini_get("mysqli.default_port"); 42 $socket = ini_get("mysqli.default_socket"); 43 $persistent = stripos($host, 'p:') === 0; 44 if ($persistent) 45 $host = substr($host, 2); 46 if (strpos($host, ':') !== false) { 47 list($host, $portspec) = explode(':', $host); 48 // PHP may not honor the port number if connecting to 'localhost' 49 if ($portspec && is_numeric($portspec)) { 50 if (!strcasecmp($host, 'localhost')) 51 // XXX: Looks like PHP gethostbyname() is IPv4 only 52 $host = gethostbyname($host); 53 $port = (int) $portspec; 54 } 55 elseif ($portspec) { 56 $socket = $portspec; 57 } 58 } 59 60 if ($persistent) 61 $host = 'p:' . $host; 62 63 // Connect 64 $start = microtime(true); 65 if (!@$__db->real_connect($host, $user, $passwd, null, $port, $socket)) 66 return NULL; 67 68 //Select the database, if any. 69 if(isset($options['db'])) $__db->select_db($options['db']); 70 71 //set desired encoding just in case mysql charset is not UTF-8 - Thanks to FreshMedia 72 @db_set_all(array( 73 'NAMES' => 'utf8', 74 'CHARACTER SET' => 'utf8', 75 'COLLATION_CONNECTION' => 'utf8_general_ci', 76 'SQL_MODE' => '', 77 'TIME_ZONE' => 'SYSTEM', 78 ), 'session'); 79 $__db->set_charset('utf8'); 80 81 $__db->autocommit(true); 82 83 // Use connection timing to seed the random number generator 84 Misc::__rand_seed((microtime(true) - $start) * 1000000); 85 86 return $__db; 87} 88 89function db_autocommit($enable=true) { 90 global $__db; 91 92 return $__db->autocommit($enable); 93} 94 95function db_rollback() { 96 global $__db; 97 98 return $__db->rollback(); 99} 100 101function db_close() { 102 global $__db; 103 return @$__db->close(); 104} 105 106function db_version() { 107 108 $version=0; 109 $matches = array(); 110 if(preg_match('/(\d{1,2}\.\d{1,2}\.\d{1,2})/', 111 db_result(db_query('SELECT VERSION()')), 112 $matches)) 113 $version=$matches[1]; 114 115 return $version; 116} 117 118function db_timezone() { 119 return db_get_variable('system_time_zone', 'global'); 120} 121 122function db_get_variable($variable, $type='session') { 123 $sql =sprintf('SELECT @@%s.%s', $type, $variable); 124 return db_result(db_query($sql)); 125} 126 127function db_set_variable($variable, $value, $type='session') { 128 return db_set_all(array($variable => $value), $type); 129} 130 131function db_set_all($variables, $type='session') { 132 global $__db; 133 134 $set = array(); 135 $type = strtoupper($type); 136 foreach ($variables as $k=>$v) { 137 $k = strtoupper($k); 138 $T = $type; 139 if (in_array($k, ['NAMES', 'CHARACTER SET'])) { 140 // MySQL doesn't support the session/global flag, and doesn't 141 // use an equal sign for these 142 $T = ''; 143 } 144 else { 145 $k .= ' ='; 146 } 147 $set[] = "$T $k ".($__db->real_escape_string($v) ?: "''"); 148 } 149 $sql = 'SET ' . implode(', ', $set); 150 return db_query($sql); 151} 152 153function db_select_database($database) { 154 global $__db; 155 return ($database && @$__db->select_db($database)); 156} 157 158function db_create_database($database, $charset='utf8', 159 $collate='utf8_general_ci') { 160 global $__db; 161 return @$__db->query( 162 sprintf('CREATE DATABASE %s DEFAULT CHARACTER SET %s COLLATE %s', 163 $database, $charset, $collate)); 164} 165/** 166 * Function: db_query 167 * Execute SQL query 168 * 169 * Parameters: 170 * 171 * @param string $query 172 * SQL query (with parameters) 173 * @param bool|callable $logError 174 * - (bool) true or false if error should be logged and alert email sent 175 * - (callable) to receive error number and return true or false if 176 * error should be logged and alert email sent. The callable is only 177 * invoked if the query fails. 178 * 179 * @return bool|mysqli_result 180 * mysqli_result object if SELECT query succeeds, true if an INSERT, 181 * UPDATE, or DELETE succeeds, false if the query fails. 182 */ 183function db_query($query, $logError=true, $buffered=true) { 184 global $ost, $__db; 185 186 $tries = 3; 187 do { 188 $res = $__db->query($query, 189 $buffered ? MYSQLI_STORE_RESULT : MYSQLI_USE_RESULT); 190 // Retry the query due to deadlock error (#1213) 191 // TODO: Consider retry on #1205 (lock wait timeout exceeded) 192 // TODO: Log warning 193 } while (!$res && --$tries && $__db->errno == 1213); 194 195 if(!$res && $logError && $ost) { //error reporting 196 // Allow $logError() callback to determine if logging is necessary 197 if (is_callable($logError) && !($logError($__db->errno))) 198 return $res; 199 200 $msg='['.$query.']'."\n\n".db_error(); 201 $ost->logDBError('DB Error #'.db_errno(), $msg); 202 //echo $msg; #uncomment during debugging or dev. 203 } 204 205 return $res; 206} 207 208function db_query_unbuffered($sql, $logError=false) { 209 return db_query($sql, $logError, true); 210} 211 212function db_count($query) { 213 return db_result(db_query($query)); 214} 215 216function db_result($res, $row=false) { 217 if (!$res) 218 return NULL; 219 220 if ($row !== false) 221 $res->data_seek($row); 222 223 list($value) = db_output($res->fetch_row()); 224 return $value; 225} 226 227function db_fetch_array($res, $mode=MYSQLI_ASSOC) { 228 return ($res) ? db_output($res->fetch_array($mode)) : NULL; 229} 230 231function db_fetch_row($res) { 232 return ($res) ? db_output($res->fetch_row()) : NULL; 233} 234 235function db_fetch_field($res) { 236 return ($res) ? $res->fetch_field() : NULL; 237} 238 239function db_assoc_array($res, $mode=MYSQLI_ASSOC) { 240 $result = array(); 241 if($res && db_num_rows($res)) { 242 while ($row=db_fetch_array($res, $mode)) 243 $result[]=$row; 244 } 245 return $result; 246} 247 248function db_num_rows($res) { 249 return ($res) ? $res->num_rows : 0; 250} 251 252function db_affected_rows() { 253 global $__db; 254 return $__db->affected_rows; 255} 256 257function db_data_seek($res, $row_number) { 258 return ($res && $res->data_seek($row_number)); 259} 260 261function db_data_reset($res) { 262 return db_data_seek($res, 0); 263} 264 265function db_insert_id() { 266 global $__db; 267 return $__db->insert_id; 268} 269 270function db_free_result($res) { 271 return ($res && $res->free()); 272} 273 274function db_output($var) { 275 static $no_magic_quotes = null; 276 277 if (!isset($no_magic_quotes)) 278 $no_magic_quotes = !function_exists('get_magic_quotes_runtime') || !get_magic_quotes_runtime(); 279 280 if ($no_magic_quotes) //Sucker is NOT on - thanks. 281 return $var; 282 283 if (is_array($var)) 284 return array_map('db_output', $var); 285 286 return (!is_numeric($var))?stripslashes($var):$var; 287 288} 289 290//Do not call this function directly...use db_input 291function db_real_escape($val, $quote=false) { 292 global $__db; 293 294 //Magic quotes crap is taken care of in main.inc.php 295 $val=$__db->real_escape_string($val); 296 297 return ($quote)?"'$val'":$val; 298} 299 300function db_input($var, $quote=true) { 301 302 if(is_array($var)) 303 return array_map('db_input', $var, array_fill(0, count($var), $quote)); 304 elseif($var && preg_match("/^(?:\d+\.\d+|[1-9]\d*)$/S", $var)) 305 return $var; 306 307 return db_real_escape($var, $quote); 308} 309 310function db_field_type($res, $col=0) { 311 global $__db; 312 return $res->fetch_field_direct($col); 313} 314 315function db_prepare($stmt) { 316 global $ost, $__db; 317 318 $res = $__db->prepare($stmt); 319 if (!$res && $ost) { 320 // Include a backtrace in the error email 321 $msg='['.$stmt."]\n\n".db_error(); 322 $ost->logDBError('DB Error #'.db_errno(), $msg); 323 } 324 return $res; 325} 326 327function db_connect_error() { 328 global $__db; 329 return $__db->connect_error; 330} 331 332function db_error() { 333 global $__db; 334 return $__db->error; 335} 336 337function db_errno() { 338 global $__db; 339 return $__db->errno; 340} 341?> 342