1: <?php
2: /**
3: * MySQL access using MySQLi extension
4: *
5: * You may not change or alter any portion of this comment or credits
6: * of supporting developers from this source code or any supporting source code
7: * which is considered copyrighted (c) material of the original comment or credit authors.
8: * This program is distributed in the hope that it will be useful,
9: * but WITHOUT ANY WARRANTY; without even the implied warranty of
10: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
11: *
12: * @copyright (c) 2000-2016 XOOPS Project (www.xoops.org)
13: * @license GNU GPL 2 (http://www.gnu.org/licenses/gpl-2.0.html)
14: * @package class
15: * @subpackage database
16: * @since 1.0.0
17: * @author Kazumi Ono <onokazu@xoops.org>
18: * @author Rodney Fulk <redheadedrod@hotmail.com>
19: */
20: defined('XOOPS_ROOT_PATH') || die('Restricted access');
21:
22: include_once XOOPS_ROOT_PATH . '/class/database/database.php';
23:
24: /**
25: * connection to a mysql database using MySQLi extension
26: *
27: * @abstract
28: * @author Kazumi Ono <onokazu@xoops.org>
29: * @copyright (c) 2000-2016 XOOPS Project (www.xoops.org)
30: * @package class
31: * @subpackage database
32: */
33: class XoopsMySQLDatabase extends XoopsDatabase
34: {
35: /**
36: * Database connection
37: *
38: * @var XoopsDatabase|mysqli
39: */
40: public $conn;
41:
42: /**
43: * connect to the database
44: *
45: * @param bool $selectdb select the database now?
46: * @return bool successful?
47: */
48: public function connect($selectdb = true)
49: {
50: if (!extension_loaded('mysqli')) {
51: trigger_error('notrace:mysqli extension not loaded', E_USER_ERROR);
52:
53: return false;
54: }
55:
56: $this->allowWebChanges = ($_SERVER['REQUEST_METHOD'] !== 'GET');
57:
58: if ($selectdb) {
59: $dbname = constant('XOOPS_DB_NAME');
60: } else {
61: $dbname = '';
62: }
63: if (XOOPS_DB_PCONNECT == 1) {
64: $this->conn = new mysqli('p:' . XOOPS_DB_HOST, XOOPS_DB_USER, XOOPS_DB_PASS, $dbname);
65: } else {
66: $this->conn = new mysqli(XOOPS_DB_HOST, XOOPS_DB_USER, XOOPS_DB_PASS, $dbname);
67: }
68:
69: // errno is 0 if connect was successful
70: if (0 !== $this->conn->connect_errno) {
71: return false;
72: }
73:
74: if (defined('XOOPS_DB_CHARSET') && ('' !== XOOPS_DB_CHARSET)) {
75: // $this->queryF("SET NAMES '" . XOOPS_DB_CHARSET . "'");
76: $this->conn->set_charset(XOOPS_DB_CHARSET);
77: }
78: $this->queryF('SET SQL_BIG_SELECTS = 1');
79:
80: return true;
81: }
82:
83: /**
84: * generate an ID for a new row
85: *
86: * This is for compatibility only. Will always return 0, because MySQL supports
87: * autoincrement for primary keys.
88: *
89: * @param string $sequence name of the sequence from which to get the next ID
90: * @return int always 0, because mysql has support for autoincrement
91: */
92: public function genId($sequence)
93: {
94: return 0; // will use auto_increment
95: }
96:
97: /**
98: * Get a result row as an enumerated array
99: *
100: * @param mysqli_result $result
101: *
102: * @return array|false false on end of data
103: */
104: public function fetchRow($result)
105: {
106: $row = @mysqli_fetch_row($result);
107: return (null === $row) ? false : $row;
108: }
109:
110: /**
111: * Fetch a result row as an associative array
112: *
113: * @param mysqli_result $result
114: *
115: * @return array|false false on end of data
116: */
117: public function fetchArray($result)
118: {
119: $row = @mysqli_fetch_assoc($result);
120: return (null === $row) ? false : $row;
121:
122: }
123:
124: /**
125: * Fetch a result row as an associative array
126: *
127: * @param mysqli_result $result
128: *
129: * @return array|false false on end of data
130: */
131: public function fetchBoth($result)
132: {
133: $row = @mysqli_fetch_array($result, MYSQLI_BOTH);
134: return (null === $row) ? false : $row;
135: }
136:
137: /**
138: * XoopsMySQLiDatabase::fetchObjected()
139: *
140: * @param mixed $result
141: * @return stdClass|false false on end of data
142: */
143: public function fetchObject($result)
144: {
145: $row = @mysqli_fetch_object($result);
146: return (null === $row) ? false : $row;
147: }
148:
149: /**
150: * Get the ID generated from the previous INSERT operation
151: *
152: * @return int
153: */
154: public function getInsertId()
155: {
156: return mysqli_insert_id($this->conn);
157: }
158:
159: /**
160: * Get number of rows in result
161: *
162: * @param mysqli_result $result
163: *
164: * @return int
165: */
166: public function getRowsNum($result)
167: {
168: return @mysqli_num_rows($result);
169: }
170:
171: /**
172: * Get number of affected rows
173: *
174: * @return int
175: */
176: public function getAffectedRows()
177: {
178: return mysqli_affected_rows($this->conn);
179: }
180:
181: /**
182: * Close MySQL connection
183: *
184: * @return void
185: */
186: public function close()
187: {
188: mysqli_close($this->conn);
189: }
190:
191: /**
192: * will free all memory associated with the result identifier result.
193: *
194: * @param mysqli_result $result result
195: *
196: * @return void
197: */
198: public function freeRecordSet($result)
199: {
200: mysqli_free_result($result);
201: }
202:
203: /**
204: * Returns the text of the error message from previous MySQL operation
205: *
206: * @return string Returns the error text from the last MySQL function, or '' (the empty string) if no error occurred.
207: */
208: public function error()
209: {
210: return @mysqli_error($this->conn);
211: }
212:
213: /**
214: * Returns the numerical value of the error message from previous MySQL operation
215: *
216: * @return int Returns the error number from the last MySQL function, or 0 (zero) if no error occurred.
217: */
218: public function errno()
219: {
220: return @mysqli_errno($this->conn);
221: }
222:
223: /**
224: * Returns escaped string text with single quotes around it to be safely stored in database
225: *
226: * @param string $str unescaped string text
227: * @return string escaped string text with single quotes around
228: */
229: public function quoteString($str)
230: {
231: return $this->quote($str);
232: }
233:
234: /**
235: * Quotes a string for use in a query.
236: *
237: * @param string $string string to quote/escape for use in query
238: *
239: * @return string
240: */
241: public function quote($string)
242: {
243: $quoted = $this->escape($string);
244: return "'{$quoted}'";
245: }
246:
247: /**
248: * Escapes a string for use in a query. Does not add surrounding quotes.
249: *
250: * @param string $string string to escape
251: *
252: * @return string
253: */
254: public function escape($string)
255: {
256: return mysqli_real_escape_string($this->conn, $string);
257: }
258:
259: /**
260: * perform a query on the database
261: *
262: * @param string $sql a valid MySQL query
263: * @param int $limit number of records to return
264: * @param int $start offset of first record to return
265: * @return mysqli_result|bool query result or FALSE if successful
266: * or TRUE if successful and no result
267: */
268: public function queryF($sql, $limit = 0, $start = 0)
269: {
270: if (!empty($limit)) {
271: if (empty($start)) {
272: $start = 0;
273: }
274: $sql = $sql . ' LIMIT ' . (int)$start . ', ' . (int)$limit;
275: }
276: $this->logger->startTime('query_time');
277: $result = mysqli_query($this->conn, $sql);
278: $this->logger->stopTime('query_time');
279: $query_time = $this->logger->dumpTime('query_time', true);
280: if ($result) {
281: $this->logger->addQuery($sql, null, null, $query_time);
282:
283: return $result;
284: } else {
285: $this->logger->addQuery($sql, $this->error(), $this->errno(), $query_time);
286:
287: return false;
288: }
289: }
290:
291: /**
292: * perform a query
293: *
294: * This method is empty and does nothing! It should therefore only be
295: * used if nothing is exactly what you want done! ;-)
296: *
297: * @param string $sql a valid MySQL query
298: * @param int $limit number of records to return
299: * @param int $start offset of first record to return
300: * @abstract
301: */
302: public function query($sql, $limit = 0, $start = 0)
303: {
304: }
305:
306: /**
307: * perform queries from SQL dump file in a batch
308: *
309: * @param string $file file path to an SQL dump file
310: * @return bool FALSE if failed reading SQL file or TRUE if the file has been read and queries executed
311: */
312: public function queryFromFile($file)
313: {
314: if (false !== ($fp = fopen($file, 'r'))) {
315: include_once XOOPS_ROOT_PATH . '/class/database/sqlutility.php';
316: $sql_queries = trim(fread($fp, filesize($file)));
317: SqlUtility::splitMySqlFile($pieces, $sql_queries);
318: foreach ($pieces as $query) {
319: // [0] contains the prefixed query
320: // [4] contains unprefixed table name
321: $prefixed_query = SqlUtility::prefixQuery(trim($query), $this->prefix());
322: if ($prefixed_query != false) {
323: $this->query($prefixed_query[0]);
324: }
325: }
326:
327: return true;
328: }
329:
330: return false;
331: }
332:
333: /**
334: * Get field name
335: *
336: * @param mysqli_result $result query result
337: * @param int $offset numerical field index
338: *
339: * @return string
340: */
341: public function getFieldName($result, $offset)
342: {
343: return $result->fetch_field_direct($offset)->name;
344: }
345:
346: /**
347: * Get field type
348: *
349: * @param mysqli_result $result query result
350: * @param int $offset numerical field index
351: *
352: * @return string
353: */
354: public function getFieldType($result, $offset)
355: {
356: $typecode = $result->fetch_field_direct($offset)->type;
357: switch ($typecode) {
358: case MYSQLI_TYPE_DECIMAL:
359: case MYSQLI_TYPE_NEWDECIMAL:
360: $type = 'decimal';
361: break;
362: case MYSQLI_TYPE_BIT:
363: $type = 'bit';
364: break;
365: case MYSQLI_TYPE_TINY:
366: case MYSQLI_TYPE_CHAR:
367: $type = 'tinyint';
368: break;
369: case MYSQLI_TYPE_SHORT:
370: $type = 'smallint';
371: break;
372: case MYSQLI_TYPE_LONG:
373: $type = 'int';
374: break;
375: case MYSQLI_TYPE_FLOAT:
376: $type = 'float';
377: break;
378: case MYSQLI_TYPE_DOUBLE:
379: $type = 'double';
380: break;
381: case MYSQLI_TYPE_NULL:
382: $type = 'NULL';
383: break;
384: case MYSQLI_TYPE_TIMESTAMP:
385: $type = 'timestamp';
386: break;
387: case MYSQLI_TYPE_LONGLONG:
388: $type = 'bigint';
389: break;
390: case MYSQLI_TYPE_INT24:
391: $type = 'mediumint';
392: break;
393: case MYSQLI_TYPE_NEWDATE:
394: case MYSQLI_TYPE_DATE:
395: $type = 'date';
396: break;
397: case MYSQLI_TYPE_TIME:
398: $type = 'time';
399: break;
400: case MYSQLI_TYPE_DATETIME:
401: $type = 'datetime';
402: break;
403: case MYSQLI_TYPE_YEAR:
404: $type = 'year';
405: break;
406: case MYSQLI_TYPE_INTERVAL:
407: $type = 'interval';
408: break;
409: case MYSQLI_TYPE_ENUM:
410: $type = 'enum';
411: break;
412: case MYSQLI_TYPE_SET:
413: $type = 'set';
414: break;
415: case MYSQLI_TYPE_TINY_BLOB:
416: $type = 'tinyblob';
417: break;
418: case MYSQLI_TYPE_MEDIUM_BLOB:
419: $type = 'mediumblob';
420: break;
421: case MYSQLI_TYPE_LONG_BLOB:
422: $type = 'longblob';
423: break;
424: case MYSQLI_TYPE_BLOB:
425: $type = 'blob';
426: break;
427: case MYSQLI_TYPE_VAR_STRING:
428: $type = 'varchar';
429: break;
430: case MYSQLI_TYPE_STRING:
431: $type = 'char';
432: break;
433: case MYSQLI_TYPE_GEOMETRY:
434: $type = 'geometry';
435: break;
436: default:
437: $type = 'unknown';
438: break;
439: }
440:
441: return $type;
442: }
443:
444: /**
445: * Get number of fields in result
446: *
447: * @param mysqli_result $result query result
448: *
449: * @return int
450: */
451: public function getFieldsNum($result)
452: {
453: return mysqli_num_fields($result);
454: }
455:
456: /**
457: * getServerVersion get version of the mysql server
458: *
459: * @return string
460: */
461: public function getServerVersion()
462: {
463: return mysqli_get_server_info($this->conn);
464: }
465: }
466:
467: /**
468: * Safe Connection to a MySQL database.
469: *
470: * @author Kazumi Ono <onokazu@xoops.org>
471: * @copyright (c) 2000-2016 XOOPS Project (www.xoops.org)
472: * @package kernel
473: * @subpackage database
474: */
475: class XoopsMySQLDatabaseSafe extends XoopsMySQLDatabase
476: {
477: /**
478: * perform a query on the database
479: *
480: * @param string $sql a valid MySQL query
481: * @param int $limit number of records to return
482: * @param int $start offset of first record to return
483: * @return resource query result or FALSE if successful
484: * or TRUE if successful and no result
485: */
486: public function query($sql, $limit = 0, $start = 0)
487: {
488: return $this->queryF($sql, $limit, $start);
489: }
490: }
491:
492: /**
493: * Read-Only connection to a MySQL database.
494: *
495: * This class allows only SELECT queries to be performed through its
496: * {@link query()} method for security reasons.
497: *
498: * @author Kazumi Ono <onokazu@xoops.org>
499: * @copyright (c) 2000-2016 XOOPS Project (www.xoops.org)
500: * @package class
501: * @subpackage database
502: */
503: class XoopsMySQLDatabaseProxy extends XoopsMySQLDatabase
504: {
505: /**
506: * perform a query on the database
507: *
508: * this method allows only SELECT queries for safety.
509: *
510: * @param string $sql a valid MySQL query
511: * @param int $limit number of records to return
512: * @param int $start offset of first record to return
513: * @return resource query result or FALSE if unsuccessful
514: */
515: public function query($sql, $limit = 0, $start = 0)
516: {
517: $sql = ltrim($sql);
518: if (!$this->allowWebChanges && strtolower(substr($sql, 0, 6)) !== 'select') {
519: trigger_error('Database updates are not allowed during processing of a GET request', E_USER_WARNING);
520:
521: return false;
522: }
523:
524: return $this->queryF($sql, $limit, $start);
525: }
526: }
527: