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