Subs-Db-sqlite3.php 26 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980
  1. <?php
  2. /**
  3. * This file has all the main functions in it that relate to the database.
  4. *
  5. * Simple Machines Forum (SMF)
  6. *
  7. * @package SMF
  8. * @author Simple Machines http://www.simplemachines.org
  9. * @copyright 2013 Simple Machines and individual contributors
  10. * @license http://www.simplemachines.org/about/smf/license.php BSD
  11. *
  12. * @version 2.1 Alpha 1
  13. */
  14. if (!defined('SMF'))
  15. die('No direct access...');
  16. /**
  17. * Maps the implementations in this file (smf_db_function_name)
  18. * to the $smcFunc['db_function_name'] variable.
  19. *
  20. * @param string $db_server
  21. * @param string $db_name
  22. * @param string $db_user
  23. * @param string $db_passwd
  24. * @param string $db_prefix
  25. * @param array $db_options
  26. */
  27. function smf_db_initiate($db_server, $db_name, $db_user, $db_passwd, $db_prefix, $db_options = array())
  28. {
  29. global $smcFunc, $mysql_set_mode, $db_in_transact, $sqlite_error;
  30. // Map some database specific functions, only do this once.
  31. // SQLite3 does not have any procedural functions, so we need to map them all...
  32. if (!isset($smcFunc['db_fetch_assoc']) || $smcFunc['db_fetch_assoc'] != 'smf_db_fetch_array')
  33. $smcFunc += array(
  34. 'db_query' => 'smf_db_query',
  35. 'db_quote' => 'smf_db_quote',
  36. 'db_fetch_assoc' => 'smf_db_fetch_array',
  37. 'db_fetch_row' => 'smf_db_fetch_row',
  38. 'db_free_result' => 'smf_db_free_result',
  39. 'db_insert' => 'smf_db_insert',
  40. 'db_insert_id' => 'smf_db_insert_id',
  41. 'db_num_rows' => 'smf_db_num_rows',
  42. 'db_data_seek' => 'smf_db_seek',
  43. 'db_num_fields' => 'smf_db_num_fields',
  44. 'db_escape_string' => 'smf_db_escape_string',
  45. 'db_unescape_string' => 'smf_db_unescape_string',
  46. 'db_server_info' => 'smf_db_libversion',
  47. 'db_affected_rows' => 'smf_db_affected_rows',
  48. 'db_transaction' => 'smf_db_transaction',
  49. 'db_error' => 'smf_db_last_error',
  50. 'db_select_db' => '',
  51. 'db_title' => 'SQLite3',
  52. 'db_sybase' => true,
  53. 'db_case_sensitive' => true,
  54. 'db_escape_wildcard_string' => 'smf_db_escape_wildcard_string',
  55. );
  56. if (substr($db_name, -3) != '.db')
  57. $db_name .= '.db';
  58. // SQLite3 doesn't support persistent connections...
  59. $connection = new SQLite3($db_name);
  60. // Something's wrong, show an error if its fatal (which we assume it is)
  61. if (!$connection)
  62. {
  63. if (!empty($db_options['non_fatal']))
  64. return null;
  65. else
  66. display_db_error();
  67. }
  68. $db_in_transact = false;
  69. // This is frankly stupid - stop SQLite returning alias names!
  70. @$connection->exec('PRAGMA short_column_names = 1');
  71. // Make some user defined functions!
  72. smf_db_define_udfs($connection);
  73. return $connection;
  74. }
  75. /**
  76. * Extend the database functionality. It calls the respective file's init
  77. * to add the implementations in that file to $smcFunc array.
  78. *
  79. * @param string $type indicated which additional file to load. ('extra', 'packages')
  80. */
  81. function db_extend($type = 'extra')
  82. {
  83. global $sourcedir, $db_type;
  84. require_once($sourcedir . '/Db' . strtoupper($type[0]) . substr($type, 1) . '-' . $db_type . '.php');
  85. $initFunc = 'db_' . $type . '_init';
  86. $initFunc();
  87. }
  88. /**
  89. * Verify that we have a valid connection
  90. *
  91. * @param resource $connection
  92. * Thanks to tinoest for this idea
  93. */
  94. function smf_db_check_connection($connection)
  95. {
  96. global $db_name;
  97. if (!is_object($connection) || is_null($connection))
  98. {
  99. if (substr($db_name, -3) != '.db')
  100. $db_name .= '.db';
  101. $connection = new Sqlite3($db_name);
  102. smf_db_define_udfs($connection);
  103. }
  104. return $connection;
  105. }
  106. /**
  107. * Define user-defined functions for SQLite3
  108. * @param resource $connection
  109. */
  110. function smf_db_define_udfs(&$connection)
  111. {
  112. $connection->createFunction('unix_timestamp', 'smf_udf_unix_timestamp', 0);
  113. $connection->createFunction('inet_aton', 'smf_udf_inet_aton', 1);
  114. $connection->createFunction('inet_ntoa', 'smf_udf_inet_ntoa', 1);
  115. $connection->createFunction('find_in_set', 'smf_udf_find_in_set', 2);
  116. $connection->createFunction('year', 'smf_udf_year', 1);
  117. $connection->createFunction('month', 'smf_udf_month', 1);
  118. $connection->createFunction('dayofmonth', 'smf_udf_dayofmonth', 1);
  119. $connection->createFunction('concat', 'smf_udf_concat');
  120. $connection->createFunction('locate', 'smf_udf_locate', 2);
  121. $connection->createFunction('regexp', 'smf_udf_regexp', 2);
  122. }
  123. /**
  124. * Fix db prefix if necessary.
  125. * SQLite doesn't actually need this!
  126. *
  127. * @param type $db_prefix
  128. * @param type $db_name
  129. */
  130. function db_fix_prefix(&$db_prefix, $db_name)
  131. {
  132. return false;
  133. }
  134. /**
  135. * Callback for preg_replace_calback on the query.
  136. * It allows to replace on the fly a few pre-defined strings, for
  137. * convenience ('query_see_board', 'query_wanna_see_board'), with
  138. * their current values from $user_info.
  139. * In addition, it performs checks and sanitization on the values
  140. * sent to the database.
  141. *
  142. * @param $matches
  143. */
  144. function smf_db_replacement__callback($matches)
  145. {
  146. global $db_callback, $user_info, $db_prefix;
  147. list ($values, $connection) = $db_callback;
  148. if ($matches[1] === 'db_prefix')
  149. return $db_prefix;
  150. if ($matches[1] === 'query_see_board')
  151. return $user_info['query_see_board'];
  152. if ($matches[1] === 'query_wanna_see_board')
  153. return $user_info['query_wanna_see_board'];
  154. if ($matches[1] === 'empty')
  155. return '\'\'';
  156. if (!isset($matches[2]))
  157. smf_db_error_backtrace('Invalid value inserted or no type specified.', '', E_USER_ERROR, __FILE__, __LINE__);
  158. if ($matches[1] === 'literal')
  159. return SQLite::escapeString($matches[2]);
  160. if (!isset($values[$matches[2]]))
  161. smf_db_error_backtrace('The database value you\'re trying to insert does not exist: ' . htmlspecialchars($matches[2]), '', E_USER_ERROR, __FILE__, __LINE__);
  162. $replacement = $values[$matches[2]];
  163. switch ($matches[1])
  164. {
  165. case 'int':
  166. if (!is_numeric($replacement) || (string) $replacement !== (string) (int) $replacement)
  167. smf_db_error_backtrace('Wrong value type sent to the database. Integer expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
  168. return (string) (int) $replacement;
  169. break;
  170. case 'string':
  171. case 'text':
  172. return sprintf('\'%1$s\'', SQLite3::escapeString($replacement));
  173. break;
  174. case 'array_int':
  175. if (is_array($replacement))
  176. {
  177. if (empty($replacement))
  178. smf_db_error_backtrace('Database error, given array of integer values is empty. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
  179. foreach ($replacement as $key => $value)
  180. {
  181. if (!is_numeric($value) || (string) $value !== (string) (int) $value)
  182. smf_db_error_backtrace('Wrong value type sent to the database. Array of integers expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
  183. $replacement[$key] = (string) (int) $value;
  184. }
  185. return implode(', ', $replacement);
  186. }
  187. else
  188. smf_db_error_backtrace('Wrong value type sent to the database. Array of integers expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
  189. break;
  190. case 'array_string':
  191. if (is_array($replacement))
  192. {
  193. if (empty($replacement))
  194. smf_db_error_backtrace('Database error, given array of string values is empty. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
  195. foreach ($replacement as $key => $value)
  196. $replacement[$key] = sprintf('\'%1$s\'', SQLite3::escapeString($value));
  197. return implode(', ', $replacement);
  198. }
  199. else
  200. smf_db_error_backtrace('Wrong value type sent to the database. Array of strings expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
  201. break;
  202. case 'date':
  203. if (preg_match('~^(\d{4})-([0-1]?\d)-([0-3]?\d)$~', $replacement, $date_matches) === 1)
  204. return sprintf('\'%04d-%02d-%02d\'', $date_matches[1], $date_matches[2], $date_matches[3]);
  205. else
  206. smf_db_error_backtrace('Wrong value type sent to the database. Date expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
  207. break;
  208. case 'float':
  209. if (!is_numeric($replacement))
  210. smf_db_error_backtrace('Wrong value type sent to the database. Floating point number expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
  211. return (string) (float) $replacement;
  212. break;
  213. case 'identifier':
  214. return '`' . strtr($replacement, array('`' => '', '.' => '')) . '`';
  215. break;
  216. case 'raw':
  217. return $replacement;
  218. break;
  219. default:
  220. smf_db_error_backtrace('Undefined type used in the database query. (' . $matches[1] . ':' . $matches[2] . ')', '', false, __FILE__, __LINE__);
  221. break;
  222. }
  223. }
  224. /**
  225. * Just like the db_query, escape and quote a string,
  226. * but not executing the query.
  227. *
  228. * @param string $db_string
  229. * @param string $db_values
  230. * @param resource $connection
  231. */
  232. function smf_db_quote($db_string, $db_values, $connection = null)
  233. {
  234. global $db_callback, $db_connection;
  235. // Only bother if there's something to replace.
  236. if (strpos($db_string, '{') !== false)
  237. {
  238. // This is needed by the callback function.
  239. $db_callback = array($db_values, $connection === null ? $db_connection : $connection);
  240. // Do the quoting and escaping
  241. $db_string = preg_replace_callback('~{([a-z_]+)(?::([a-zA-Z0-9_-]+))?}~', 'smf_db_replacement__callback', $db_string);
  242. // Clear this global variable.
  243. $db_callback = array();
  244. }
  245. return $db_string;
  246. }
  247. /**
  248. *
  249. * @param resource $handle
  250. */
  251. function smf_db_fetch_array($handle)
  252. {
  253. return $handle->fetchArray();
  254. }
  255. /**
  256. * Do a query. Takes care of errors too.
  257. *
  258. * @param string $identifier
  259. * @param string $db_string
  260. * @param string $db_values
  261. * @param resource $connection
  262. */
  263. function smf_db_query($identifier, $db_string, $db_values = array(), $connection = null)
  264. {
  265. global $db_cache, $db_count, $db_connection, $db_show_debug, $time_start;
  266. global $db_unbuffered, $db_callback, $modSettings;
  267. // Decide which connection to use.
  268. $connection = smf_db_check_connection($connection === null ? $db_connection : $connection);
  269. // Special queries that need processing.
  270. $replacements = array(
  271. 'birthday_array' => array(
  272. '~DATE_FORMAT\(([^,]+),\s*([^\)]+)\s*\)~' => 'strftime($2, $1)'
  273. ),
  274. 'substring' => array(
  275. '~SUBSTRING~' => 'SUBSTR',
  276. ),
  277. 'truncate_table' => array(
  278. '~TRUNCATE~i' => 'DELETE FROM',
  279. ),
  280. 'user_activity_by_time' => array(
  281. '~HOUR\(FROM_UNIXTIME\((poster_time\s+\+\s+\{int:.+\})\)\)~' => 'strftime(\'%H\', datetime($1, \'unixepoch\'))',
  282. ),
  283. 'unread_fetch_topic_count' => array(
  284. '~\s*SELECT\sCOUNT\(DISTINCT\st\.id_topic\),\sMIN\(t\.id_last_msg\)(.+)$~is' => 'SELECT COUNT(id_topic), MIN(id_last_msg) FROM (SELECT DISTINCT t.id_topic, t.id_last_msg $1)',
  285. ),
  286. 'alter_table_boards' => array(
  287. '~(.+)~' => '',
  288. ),
  289. 'get_random_number' => array(
  290. '~RAND~' => 'RANDOM',
  291. ),
  292. 'set_character_set' => array(
  293. '~(.+)~' => '',
  294. ),
  295. 'themes_count' => array(
  296. '~\s*SELECT\sCOUNT\(DISTINCT\sid_member\)\sAS\svalue,\sid_theme.+FROM\s(.+themes)(.+)~is' => 'SELECT COUNT(id_member) AS value, id_theme FROM (SELECT DISTINCT id_member, id_theme, variable FROM $1) $2',
  297. ),
  298. 'attach_download_increase' => array(
  299. '~LOW_PRIORITY~' => '',
  300. ),
  301. 'pm_conversation_list' => array(
  302. '~ORDER BY id_pm~' => 'ORDER BY MAX(pm.id_pm)',
  303. ),
  304. 'boardindex_fetch_boards' => array(
  305. '~(.)$~' => '$1 ORDER BY b.board_order',
  306. ),
  307. 'messageindex_fetch_boards' => array(
  308. '~(.)$~' => '$1 ORDER BY b.board_order',
  309. ),
  310. 'order_by_board_order' => array(
  311. '~(.)$~' => '$1 ORDER BY b.board_order',
  312. ),
  313. 'spider_check' => array(
  314. '~(.)$~' => '$1 ORDER BY LENGTH(user_agent) DESC',
  315. ),
  316. );
  317. if (isset($replacements[$identifier]))
  318. $db_string = preg_replace(array_keys($replacements[$identifier]), array_values($replacements[$identifier]), $db_string);
  319. // SQLite doesn't support count(distinct).
  320. $db_string = trim($db_string);
  321. $db_string = preg_replace('~^\s*SELECT\s+?COUNT\(DISTINCT\s+?(.+?)\)(\s*AS\s*(.+?))*\s*(FROM.+)~is', 'SELECT COUNT(*) $2 FROM (SELECT DISTINCT $1 $4)', $db_string);
  322. // Or RLIKE.
  323. $db_string = preg_replace('~AND\s*(.+?)\s*RLIKE\s*(\{string:.+?\})~', 'AND REGEXP(\1, \2)', $db_string);
  324. // INSTR? No support for that buddy :(
  325. if (preg_match('~INSTR\((.+?),\s(.+?)\)~', $db_string, $matches) === 1)
  326. {
  327. $db_string = preg_replace('~INSTR\((.+?),\s(.+?)\)~', '$1 LIKE $2', $db_string);
  328. list(, $search) = explode(':', substr($matches[2], 1, -1));
  329. $db_values[$search] = '%' . $db_values[$search] . '%';
  330. }
  331. // Lets remove ASC and DESC from GROUP BY clause.
  332. if (preg_match('~GROUP BY .*? (?:ASC|DESC)~is', $db_string, $matches))
  333. {
  334. $replace = str_replace(array('ASC', 'DESC'), '', $matches[0]);
  335. $db_string = str_replace($matches[0], $replace, $db_string);
  336. }
  337. // We need to replace the SUBSTRING in the sort identifier.
  338. if ($identifier == 'substring_membergroups' && isset($db_values['sort']))
  339. $db_values['sort'] = preg_replace('~SUBSTRING~', 'SUBSTR', $db_values['sort']);
  340. // SQLite doesn't support TO_DAYS but has the julianday function which can be used in the same manner. But make sure it is being used to calculate a span.
  341. $db_string = preg_replace('~\(TO_DAYS\(([^)]+)\) - TO_DAYS\(([^)]+)\)\) AS span~', '(julianday($1) - julianday($2)) AS span', $db_string);
  342. // One more query....
  343. $db_count = !isset($db_count) ? 1 : $db_count + 1;
  344. if (empty($modSettings['disableQueryCheck']) && strpos($db_string, '\'') !== false && empty($db_values['security_override']))
  345. smf_db_error_backtrace('Hacking attempt...', 'Illegal character (\') used in query...', true, __FILE__, __LINE__);
  346. if (empty($db_values['security_override']) && (!empty($db_values) || strpos($db_string, '{db_prefix}') !== false))
  347. {
  348. // Pass some values to the global space for use in the callback function.
  349. $db_callback = array($db_values, $connection);
  350. // Inject the values passed to this function.
  351. $db_string = preg_replace_callback('~{([a-z_]+)(?::([a-zA-Z0-9_-]+))?}~', 'smf_db_replacement__callback', $db_string);
  352. // This shouldn't be residing in global space any longer.
  353. $db_callback = array();
  354. }
  355. // Debugging.
  356. if (isset($db_show_debug) && $db_show_debug === true)
  357. {
  358. // Get the file and line number this function was called.
  359. list ($file, $line) = smf_db_error_backtrace('', '', 'return', __FILE__, __LINE__);
  360. // Initialize $db_cache if not already initialized.
  361. if (!isset($db_cache))
  362. $db_cache = array();
  363. if (!empty($_SESSION['debug_redirect']))
  364. {
  365. $db_cache = array_merge($_SESSION['debug_redirect'], $db_cache);
  366. $db_count = count($db_cache) + 1;
  367. $_SESSION['debug_redirect'] = array();
  368. }
  369. $st = microtime();
  370. // Don't overload it.
  371. $db_cache[$db_count]['q'] = $db_count < 50 ? $db_string : '...';
  372. $db_cache[$db_count]['f'] = $file;
  373. $db_cache[$db_count]['l'] = $line;
  374. $db_cache[$db_count]['s'] = array_sum(explode(' ', $st)) - array_sum(explode(' ', $time_start));
  375. }
  376. $ret = @$connection->query($db_string);
  377. if ($ret === false && empty($db_values['db_error_skip']))
  378. {
  379. $err_msg = $connection->lastErrorMsg();
  380. $ret = smf_db_error($db_string . '#!#' . $err_msg, $connection);
  381. }
  382. // Debugging.
  383. if (isset($db_show_debug) && $db_show_debug === true)
  384. $db_cache[$db_count]['t'] = array_sum(explode(' ', microtime())) - array_sum(explode(' ', $st));
  385. return $ret;
  386. }
  387. /**
  388. * affected_rows
  389. *
  390. * @param resource $connection
  391. */
  392. function smf_db_affected_rows($connection = null)
  393. {
  394. global $db_connection;
  395. $connection = smf_db_check_connection($connection == null ? $db_connection : $connection);
  396. return $connection->changes();
  397. }
  398. /**
  399. * insert_id
  400. *
  401. * @param string $table
  402. * @param string $field = null
  403. * @param resource $connection = null
  404. */
  405. function smf_db_insert_id($table, $field = null, $connection = null)
  406. {
  407. global $db_connection, $db_prefix;
  408. $table = str_replace('{db_prefix}', $db_prefix, $table);
  409. $connection = smf_db_check_connection($connection == null ? $db_connection : $connection);
  410. // SQLite doesn't need the table or field information.
  411. return $connection->lastInsertRowid();
  412. }
  413. /**
  414. * Number of rows returned by a query.
  415. *
  416. * @param resource $handle
  417. */
  418. function smf_db_num_rows($handle)
  419. {
  420. $num_rows = 0;
  421. // For some stupid reason, the sqlite3 extension doesn't have a numRows function
  422. while ($handle->fetchArray())
  423. $num_rows++;
  424. return $num_rows;
  425. }
  426. /**
  427. * Seek to a specific row in the result set
  428. *
  429. * @param resource $handle
  430. * @param int $row
  431. */
  432. function smf_db_seek(&$handle, $row)
  433. {
  434. if ($row === 0)
  435. {
  436. $handle->Reset();
  437. }
  438. else
  439. {
  440. $i = 0;
  441. do
  442. {
  443. $handle->fetchArray();
  444. $i++;
  445. } while ($i < $row);
  446. }
  447. return $handle;
  448. }
  449. /**
  450. *
  451. * @param resource $handle
  452. */
  453. function smf_db_num_fields($handle)
  454. {
  455. return $handle->numColumns();
  456. }
  457. /**
  458. *
  459. * @param string $string
  460. */
  461. function smf_db_escape_string($string)
  462. {
  463. return SQLite3::escapeString($string);
  464. }
  465. /**
  466. * Last error on SQLite
  467. */
  468. function smf_db_last_error()
  469. {
  470. global $db_connection, $sqlite_error;
  471. $query_errno = $db_connection->lastErrorCode();
  472. return $query_errno || empty($sqlite_error) ? $db_connection->lastErrorMsg() : $sqlite_error;
  473. }
  474. /**
  475. * Do a transaction.
  476. *
  477. * @param string $type - the step to perform (i.e. 'begin', 'commit', 'rollback')
  478. * @param resource $connection = null
  479. */
  480. function smf_db_transaction($type = 'commit', $connection = null)
  481. {
  482. global $db_connection, $db_in_transact;
  483. // Decide which connection to use
  484. $connection = smf_db_check_connection($connection === null ? $db_connection : $connection);
  485. if ($type == 'begin')
  486. {
  487. $db_in_transact = true;
  488. return @$connection->exec('BEGIN');
  489. }
  490. elseif ($type == 'rollback')
  491. {
  492. $db_in_transact = false;
  493. return @$connection->exec('ROLLBACK');
  494. }
  495. elseif ($type == 'commit')
  496. {
  497. $db_in_transact = false;
  498. return @$connection->exec('COMMIT');
  499. }
  500. return false;
  501. }
  502. /**
  503. * Database error!
  504. * Backtrace, log, try to fix.
  505. *
  506. * @param string $db_string
  507. * @param resource $connection = null
  508. */
  509. function smf_db_error($db_string, $connection = null)
  510. {
  511. global $txt, $context, $sourcedir, $webmaster_email, $modSettings;
  512. global $forum_version, $db_connection, $db_last_error, $db_persist;
  513. global $db_server, $db_user, $db_passwd, $db_name, $db_show_debug, $ssi_db_user, $ssi_db_passwd;
  514. global $smcFunc;
  515. // We'll try recovering the file and line number the original db query was called from.
  516. list ($file, $line) = smf_db_error_backtrace('', '', 'return', __FILE__, __LINE__);
  517. // Decide which connection to use
  518. $connection = smf_db_check_connection($connection === null ? $db_connection : $connection);
  519. // This is the error message...
  520. $query_errno = $connection->lastErrorCode();
  521. $query_error = $connection->lastErrorMsg();
  522. // Get the extra error message.
  523. $errStart = strrpos($db_string, '#!#');
  524. $query_error .= '<br />' . substr($db_string, $errStart + 3);
  525. $db_string = substr($db_string, 0, $errStart);
  526. // Log the error.
  527. if (function_exists('log_error'))
  528. log_error($txt['database_error'] . ': ' . $query_error . (!empty($modSettings['enableErrorQueryLogging']) ? "\n\n" .$db_string : ''), 'database', $file, $line);
  529. // Sqlite optimizing - the actual error message isn't helpful or user friendly.
  530. if (strpos($query_error, 'no_access') !== false || strpos($query_error, 'database schema has changed') !== false)
  531. {
  532. if (!empty($context) && !empty($txt) && !empty($txt['error_sqlite_optimizing']))
  533. fatal_error($txt['error_sqlite_optimizing'], false);
  534. else
  535. {
  536. // Don't cache this page!
  537. header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
  538. header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
  539. header('Cache-Control: no-cache');
  540. // Send the right error codes.
  541. header('HTTP/1.1 503 Service Temporarily Unavailable');
  542. header('Status: 503 Service Temporarily Unavailable');
  543. header('Retry-After: 3600');
  544. die('Sqlite is optimizing the database, the forum can not be accessed until it has finished. Please try refreshing this page momentarily.');
  545. }
  546. }
  547. // Nothing's defined yet... just die with it.
  548. if (empty($context) || empty($txt))
  549. die($query_error);
  550. // Show an error message, if possible.
  551. $context['error_title'] = $txt['database_error'];
  552. if (allowedTo('admin_forum'))
  553. $context['error_message'] = nl2br($query_error) . '<br />' . $txt['file'] . ': ' . $file . '<br />' . $txt['line'] . ': ' . $line;
  554. else
  555. $context['error_message'] = $txt['try_again'];
  556. if (allowedTo('admin_forum') && isset($db_show_debug) && $db_show_debug === true)
  557. {
  558. $context['error_message'] .= '<br /><br />' . nl2br($db_string);
  559. }
  560. // It's already been logged... don't log it again.
  561. fatal_error($context['error_message'], false);
  562. }
  563. /**
  564. * insert
  565. *
  566. * @param string $method, options 'replace', 'ignore', 'insert'
  567. * @param $table
  568. * @param $columns
  569. * @param $data
  570. * @param $keys
  571. * @param bool $disable_trans = false
  572. * @param resource $connection = null
  573. */
  574. function smf_db_insert($method = 'replace', $table, $columns, $data, $keys, $disable_trans = false, $connection = null)
  575. {
  576. global $db_in_transact, $db_connection, $smcFunc, $db_prefix;
  577. $connection = $connection === null ? $db_connection : $connection;
  578. if (empty($data))
  579. return;
  580. if (!is_array($data[array_rand($data)]))
  581. $data = array($data);
  582. // Replace the prefix holder with the actual prefix.
  583. $table = str_replace('{db_prefix}', $db_prefix, $table);
  584. $priv_trans = false;
  585. if (count($data) > 1 && !$db_in_transact && !$disable_trans)
  586. {
  587. $smcFunc['db_transaction']('begin', $connection);
  588. $priv_trans = true;
  589. }
  590. if (!empty($data))
  591. {
  592. // Create the mold for a single row insert.
  593. $insertData = '(';
  594. foreach ($columns as $columnName => $type)
  595. {
  596. // Are we restricting the length?
  597. if (strpos($type, 'string-') !== false)
  598. $insertData .= sprintf('SUBSTR({string:%1$s}, 1, ' . substr($type, 7) . '), ', $columnName);
  599. else
  600. $insertData .= sprintf('{%1$s:%2$s}, ', $type, $columnName);
  601. }
  602. $insertData = substr($insertData, 0, -2) . ')';
  603. // Create an array consisting of only the columns.
  604. $indexed_columns = array_keys($columns);
  605. // Here's where the variables are injected to the query.
  606. $insertRows = array();
  607. foreach ($data as $dataRow)
  608. $insertRows[] = smf_db_quote($insertData, array_combine($indexed_columns, $dataRow), $connection);
  609. foreach ($insertRows as $entry)
  610. // Do the insert.
  611. $smcFunc['db_query']('',
  612. (($method === 'replace') ? 'REPLACE' : (' INSERT' . ($method === 'ignore' ? ' OR IGNORE' : ''))) . ' INTO ' . $table . '(' . implode(', ', $indexed_columns) . ')
  613. VALUES
  614. ' . $entry,
  615. array(
  616. 'security_override' => true,
  617. 'db_error_skip' => $table === $db_prefix . 'log_errors',
  618. ),
  619. $connection
  620. );
  621. }
  622. if ($priv_trans)
  623. $smcFunc['db_transaction']('commit', $connection);
  624. }
  625. /**
  626. * free_result
  627. *
  628. * @param resource $handle = false
  629. */
  630. function smf_db_free_result($handle = false)
  631. {
  632. return $handle->finalize();
  633. }
  634. /**
  635. * fetch_row
  636. * Make sure we return no string indexes!
  637. *
  638. * @param $handle
  639. */
  640. function smf_db_fetch_row($handle)
  641. {
  642. return $handle->fetchArray(SQLITE3_NUM);
  643. }
  644. /**
  645. * Unescape an escaped string!
  646. *
  647. * @param $string
  648. */
  649. function smf_db_unescape_string($string)
  650. {
  651. return strtr($string, array('\'\'' => '\''));
  652. }
  653. /**
  654. * This function tries to work out additional error information from a back trace.
  655. *
  656. * @param $error_message
  657. * @param $log_message
  658. * @param $error_type
  659. * @param $file
  660. * @param $line
  661. */
  662. function smf_db_error_backtrace($error_message, $log_message = '', $error_type = false, $file = null, $line = null)
  663. {
  664. if (empty($log_message))
  665. $log_message = $error_message;
  666. foreach (debug_backtrace() as $step)
  667. {
  668. // Found it?
  669. if (strpos($step['function'], 'query') === false && !in_array(substr($step['function'], 0, 7), array('smf_db_', 'preg_re', 'db_erro', 'call_us')) && strpos($step['function'], '__') !== 0)
  670. {
  671. $log_message .= '<br />Function: ' . $step['function'];
  672. break;
  673. }
  674. if (isset($step['line']))
  675. {
  676. $file = $step['file'];
  677. $line = $step['line'];
  678. }
  679. }
  680. // A special case - we want the file and line numbers for debugging.
  681. if ($error_type == 'return')
  682. return array($file, $line);
  683. // Is always a critical error.
  684. if (function_exists('log_error'))
  685. log_error($log_message, 'critical', $file, $line);
  686. if (function_exists('fatal_error'))
  687. {
  688. fatal_error($error_message, $error_type);
  689. // Cannot continue...
  690. exit;
  691. }
  692. elseif ($error_type)
  693. trigger_error($error_message . ($line !== null ? '<em>(' . basename($file) . '-' . $line . ')</em>' : ''), $error_type);
  694. else
  695. trigger_error($error_message . ($line !== null ? '<em>(' . basename($file) . '-' . $line . ')</em>' : ''));
  696. }
  697. /**
  698. * Emulate UNIX_TIMESTAMP.
  699. */
  700. function smf_udf_unix_timestamp()
  701. {
  702. return strftime('%s', 'now');
  703. }
  704. /**
  705. * Emulate INET_ATON.
  706. *
  707. * @param $ip
  708. */
  709. function smf_udf_inet_aton($ip)
  710. {
  711. $chunks = explode('.', $ip);
  712. return @$chunks[0] * pow(256, 3) + @$chunks[1] * pow(256, 2) + @$chunks[2] * 256 + @$chunks[3];
  713. }
  714. /**
  715. * Emulate INET_NTOA.
  716. *
  717. * @param $n
  718. */
  719. function smf_udf_inet_ntoa($n)
  720. {
  721. $t = array(0, 0, 0, 0);
  722. $msk = 16777216.0;
  723. $n += 0.0;
  724. if ($n < 1)
  725. return '0.0.0.0';
  726. for ($i = 0; $i < 4; $i++)
  727. {
  728. $k = (int) ($n / $msk);
  729. $n -= $msk * $k;
  730. $t[$i] = $k;
  731. $msk /= 256.0;
  732. };
  733. $a = join('.', $t);
  734. return $a;
  735. }
  736. /**
  737. * Emulate FIND_IN_SET.
  738. *
  739. * @param $find
  740. * @param $groups
  741. */
  742. function smf_udf_find_in_set($find, $groups)
  743. {
  744. foreach (explode(',', $groups) as $key => $group)
  745. {
  746. if ($group == $find)
  747. return $key + 1;
  748. }
  749. return 0;
  750. }
  751. /**
  752. * Emulate YEAR.
  753. *
  754. * @param $date
  755. */
  756. function smf_udf_year($date)
  757. {
  758. return substr($date, 0, 4);
  759. }
  760. /**
  761. * Emulate MONTH.
  762. *
  763. * @param $date
  764. */
  765. function smf_udf_month($date)
  766. {
  767. return substr($date, 5, 2);
  768. }
  769. /**
  770. * Emulate DAYOFMONTH.
  771. *
  772. * @param $date
  773. */
  774. function smf_udf_dayofmonth($date)
  775. {
  776. return substr($date, 8, 2);
  777. }
  778. /**
  779. * We need this since sqlite_libversion() doesn't take any parameters.
  780. *
  781. * @param $void
  782. */
  783. function smf_db_libversion($void = null)
  784. {
  785. $version = SQLite3::version();
  786. return $version['versionString'];
  787. }
  788. /**
  789. * This function uses variable argument lists so that it can handle more then two parameters.
  790. * Emulates the CONCAT function.
  791. */
  792. function smf_udf_concat()
  793. {
  794. // Since we didn't specify any arguments we must get them from PHP.
  795. $args = func_get_args();
  796. // It really doesn't matter if there were 0 to 100 arguments, just slap them all together.
  797. return implode('', $args);
  798. }
  799. /**
  800. * We need to use PHP to locate the position in the string.
  801. *
  802. * @param string $find
  803. * @param string $string
  804. */
  805. function smf_udf_locate($find, $string)
  806. {
  807. return strpos($string, $find);
  808. }
  809. /**
  810. * This is used to replace RLIKE.
  811. *
  812. * @param string $exp
  813. * @param string $search
  814. */
  815. function smf_udf_regexp($exp, $search)
  816. {
  817. if (preg_match($exp, $match))
  818. return 1;
  819. return 0;
  820. }
  821. /**
  822. * Escape the LIKE wildcards so that they match the character and not the wildcard.
  823. * The optional second parameter turns human readable wildcards into SQL wildcards.
  824. */
  825. function smf_db_escape_wildcard_string($string, $translate_human_wildcards=false)
  826. {
  827. $replacements = array(
  828. '%' => '\%',
  829. '\\' => '\\\\',
  830. );
  831. if ($translate_human_wildcards)
  832. $replacements += array(
  833. '*' => '%',
  834. );
  835. return strtr($string, $replacements);
  836. }
  837. ?>