|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
/** |
|
4
|
|
|
* This file has all the main functions in it that relate to the database. |
|
5
|
|
|
* |
|
6
|
|
|
* Simple Machines Forum (SMF) |
|
7
|
|
|
* |
|
8
|
|
|
* @package SMF |
|
9
|
|
|
* @author Simple Machines http://www.simplemachines.org |
|
10
|
|
|
* @copyright 2018 Simple Machines and individual contributors |
|
11
|
|
|
* @license http://www.simplemachines.org/about/smf/license.php BSD |
|
12
|
|
|
* |
|
13
|
|
|
* @version 2.1 Beta 4 |
|
14
|
|
|
*/ |
|
15
|
|
|
|
|
16
|
|
|
if (!defined('SMF')) |
|
17
|
|
|
die('No direct access...'); |
|
18
|
|
|
|
|
19
|
|
|
/** |
|
20
|
|
|
* Maps the implementations in this file (smf_db_function_name) |
|
21
|
|
|
* to the $smcFunc['db_function_name'] variable. |
|
22
|
|
|
* |
|
23
|
|
|
* @param string $db_server The database server |
|
24
|
|
|
* @param string $db_name The name of the database |
|
25
|
|
|
* @param string $db_user The database username |
|
26
|
|
|
* @param string $db_passwd The database password |
|
27
|
|
|
* @param string $db_prefix The table prefix |
|
28
|
|
|
* @param array $db_options An array of database options |
|
29
|
|
|
* @return null|resource Returns null on failure if $db_options['non_fatal'] is true or a MySQL connection resource handle if the connection was successful. |
|
30
|
|
|
*/ |
|
31
|
|
|
function smf_db_initiate($db_server, $db_name, $db_user, $db_passwd, $db_prefix, $db_options = array()) |
|
32
|
|
|
{ |
|
33
|
|
|
global $smcFunc; |
|
34
|
|
|
|
|
35
|
|
|
// Map some database specific functions, only do this once. |
|
36
|
|
|
if (!isset($smcFunc['db_fetch_assoc'])) |
|
37
|
|
|
$smcFunc += array( |
|
38
|
|
|
'db_query' => 'smf_db_query', |
|
39
|
|
|
'db_quote' => 'smf_db_quote', |
|
40
|
|
|
'db_fetch_assoc' => 'mysqli_fetch_assoc', |
|
41
|
|
|
'db_fetch_row' => 'mysqli_fetch_row', |
|
42
|
|
|
'db_free_result' => 'mysqli_free_result', |
|
43
|
|
|
'db_insert' => 'smf_db_insert', |
|
44
|
|
|
'db_insert_id' => 'smf_db_insert_id', |
|
45
|
|
|
'db_num_rows' => 'mysqli_num_rows', |
|
46
|
|
|
'db_data_seek' => 'mysqli_data_seek', |
|
47
|
|
|
'db_num_fields' => 'mysqli_num_fields', |
|
48
|
|
|
'db_escape_string' => 'smf_db_escape_string', |
|
49
|
|
|
'db_unescape_string' => 'stripslashes', |
|
50
|
|
|
'db_server_info' => 'smf_db_get_server_info', |
|
51
|
|
|
'db_affected_rows' => 'smf_db_affected_rows', |
|
52
|
|
|
'db_transaction' => 'smf_db_transaction', |
|
53
|
|
|
'db_error' => 'mysqli_error', |
|
54
|
|
|
'db_select_db' => 'smf_db_select', |
|
55
|
|
|
'db_title' => 'MySQLi', |
|
56
|
|
|
'db_sybase' => false, |
|
57
|
|
|
'db_case_sensitive' => false, |
|
58
|
|
|
'db_escape_wildcard_string' => 'smf_db_escape_wildcard_string', |
|
59
|
|
|
'db_is_resource' => 'smf_is_resource', |
|
60
|
|
|
'db_mb4' => false, |
|
61
|
|
|
'db_ping' => 'mysqli_ping', |
|
62
|
|
|
'db_fetch_all' => 'smf_db_fetch_all', |
|
63
|
|
|
'db_error_insert' => 'smf_db_error_insert', |
|
64
|
|
|
'db_custom_order' => 'smf_db_custom_order', |
|
65
|
|
|
'db_native_replace' => 'smf_db_native_replace', |
|
66
|
|
|
'db_cte_support' => 'smf_db_cte_support', |
|
67
|
|
|
); |
|
68
|
|
|
|
|
69
|
|
|
if (!empty($db_options['persist'])) |
|
70
|
|
|
$db_server = 'p:' . $db_server; |
|
71
|
|
|
|
|
72
|
|
|
// We are not going to make it very far without these. |
|
73
|
|
|
if (!function_exists('mysqli_init') || !function_exists('mysqli_real_connect')) |
|
74
|
|
|
display_db_error(); |
|
75
|
|
|
|
|
76
|
|
|
$connection = mysqli_init(); |
|
77
|
|
|
|
|
78
|
|
|
$flags = 2; //MYSQLI_CLIENT_FOUND_ROWS = 2 |
|
79
|
|
|
|
|
80
|
|
|
$success = false; |
|
81
|
|
|
|
|
82
|
|
|
if ($connection) |
|
83
|
|
|
{ |
|
84
|
|
|
if (!empty($db_options['port'])) |
|
85
|
|
|
$success = mysqli_real_connect($connection, $db_server, $db_user, $db_passwd, null, $db_options['port'], null, $flags); |
|
86
|
|
|
else |
|
87
|
|
|
$success = mysqli_real_connect($connection, $db_server, $db_user, $db_passwd, null, 0, null, $flags); |
|
88
|
|
|
} |
|
89
|
|
|
|
|
90
|
|
|
// Something's wrong, show an error if its fatal (which we assume it is) |
|
91
|
|
|
if ($success === false) |
|
92
|
|
|
{ |
|
93
|
|
|
if (!empty($db_options['non_fatal'])) |
|
94
|
|
|
return null; |
|
95
|
|
|
else |
|
96
|
|
|
display_db_error(); |
|
97
|
|
|
} |
|
98
|
|
|
|
|
99
|
|
|
// Select the database, unless told not to |
|
100
|
|
|
if (empty($db_options['dont_select_db']) && !@mysqli_select_db($connection, $db_name) && empty($db_options['non_fatal'])) |
|
101
|
|
|
display_db_error(); |
|
102
|
|
|
|
|
103
|
|
|
mysqli_query($connection, 'SET SESSION sql_mode = \'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION\''); |
|
104
|
|
|
|
|
105
|
|
|
if (!empty($db_options['db_mb4'])) |
|
106
|
|
|
$smcFunc['db_mb4'] = (bool) $db_options['db_mb4']; |
|
107
|
|
|
|
|
108
|
|
|
return $connection; |
|
109
|
|
|
} |
|
110
|
|
|
|
|
111
|
|
|
/** |
|
112
|
|
|
* Extend the database functionality. It calls the respective file's init |
|
113
|
|
|
* to add the implementations in that file to $smcFunc array. |
|
114
|
|
|
* |
|
115
|
|
|
* @param string $type Indicates which additional file to load. ('extra', 'packages') |
|
116
|
|
|
*/ |
|
117
|
|
|
function db_extend($type = 'extra') |
|
118
|
|
|
{ |
|
119
|
|
|
global $sourcedir; |
|
120
|
|
|
|
|
121
|
|
|
// we force the MySQL files as nothing syntactically changes with MySQLi |
|
122
|
|
|
require_once($sourcedir . '/Db' . strtoupper($type[0]) . substr($type, 1) . '-mysql.php'); |
|
123
|
|
|
$initFunc = 'db_' . $type . '_init'; |
|
124
|
|
|
$initFunc(); |
|
125
|
|
|
} |
|
126
|
|
|
|
|
127
|
|
|
/** |
|
128
|
|
|
* Fix up the prefix so it doesn't require the database to be selected. |
|
129
|
|
|
* |
|
130
|
|
|
* @param string &$db_prefix The table prefix |
|
131
|
|
|
* @param string $db_name The database name |
|
132
|
|
|
*/ |
|
133
|
|
|
function db_fix_prefix(&$db_prefix, $db_name) |
|
134
|
|
|
{ |
|
135
|
|
|
$db_prefix = is_numeric(substr($db_prefix, 0, 1)) ? $db_name . '.' . $db_prefix : '`' . $db_name . '`.' . $db_prefix; |
|
136
|
|
|
} |
|
137
|
|
|
|
|
138
|
|
|
/** |
|
139
|
|
|
* Wrap mysqli_select_db so the connection does not need to be specified |
|
140
|
|
|
* |
|
141
|
|
|
* @param string &$database The database |
|
142
|
|
|
* @param object $connection The connection object (if null, $db_connection is used) |
|
143
|
|
|
* @return bool Whether the database was selected |
|
144
|
|
|
*/ |
|
145
|
|
|
function smf_db_select($database, $connection = null) |
|
146
|
|
|
{ |
|
147
|
|
|
global $db_connection; |
|
148
|
|
|
return mysqli_select_db($connection === null ? $db_connection : $connection, $database); |
|
149
|
|
|
} |
|
150
|
|
|
|
|
151
|
|
|
/** |
|
152
|
|
|
* Wrap mysqli_get_server_info so the connection does not need to be specified |
|
153
|
|
|
* |
|
154
|
|
|
* @param object $connection The connection to use (if null, $db_connection is used) |
|
155
|
|
|
* @return string The server info |
|
156
|
|
|
*/ |
|
157
|
|
|
function smf_db_get_server_info($connection = null) |
|
158
|
|
|
{ |
|
159
|
|
|
global $db_connection; |
|
160
|
|
|
return mysqli_get_server_info($connection === null ? $db_connection : $connection); |
|
161
|
|
|
} |
|
162
|
|
|
|
|
163
|
|
|
/** |
|
164
|
|
|
* Callback for preg_replace_callback on the query. |
|
165
|
|
|
* It allows to replace on the fly a few pre-defined strings, for convenience ('query_see_board', 'query_wanna_see_board', etc), with |
|
166
|
|
|
* their current values from $user_info. |
|
167
|
|
|
* In addition, it performs checks and sanitization on the values sent to the database. |
|
168
|
|
|
* |
|
169
|
|
|
* @param array $matches The matches from preg_replace_callback |
|
170
|
|
|
* @return string The appropriate string depending on $matches[1] |
|
171
|
|
|
*/ |
|
172
|
|
|
function smf_db_replacement__callback($matches) |
|
173
|
|
|
{ |
|
174
|
|
|
global $db_callback, $user_info, $db_prefix, $smcFunc; |
|
175
|
|
|
|
|
176
|
|
|
list ($values, $connection) = $db_callback; |
|
177
|
|
|
if (!is_object($connection)) |
|
178
|
|
|
display_db_error(); |
|
179
|
|
|
|
|
180
|
|
|
if ($matches[1] === 'db_prefix') |
|
181
|
|
|
return $db_prefix; |
|
182
|
|
|
|
|
183
|
|
|
if (isset($user_info[$matches[1]]) && strpos($matches[1], 'query_') !== false) |
|
184
|
|
|
return $user_info[$matches[1]]; |
|
185
|
|
|
|
|
186
|
|
|
if ($matches[1] === 'empty') |
|
187
|
|
|
return '\'\''; |
|
188
|
|
|
|
|
189
|
|
|
if (!isset($matches[2])) |
|
190
|
|
|
smf_db_error_backtrace('Invalid value inserted or no type specified.', '', E_USER_ERROR, __FILE__, __LINE__); |
|
191
|
|
|
|
|
192
|
|
|
if ($matches[1] === 'literal') |
|
193
|
|
|
return '\'' . mysqli_real_escape_string($connection, $matches[2]) . '\''; |
|
194
|
|
|
|
|
195
|
|
|
if (!isset($values[$matches[2]])) |
|
196
|
|
|
smf_db_error_backtrace('The database value you\'re trying to insert does not exist: ' . (isset($smcFunc['htmlspecialchars']) ? $smcFunc['htmlspecialchars']($matches[2]) : htmlspecialchars($matches[2])), '', E_USER_ERROR, __FILE__, __LINE__); |
|
197
|
|
|
|
|
198
|
|
|
$replacement = $values[$matches[2]]; |
|
199
|
|
|
|
|
200
|
|
|
switch ($matches[1]) |
|
201
|
|
|
{ |
|
202
|
|
|
case 'int': |
|
203
|
|
|
if (!is_numeric($replacement) || (string) $replacement !== (string) (int) $replacement) |
|
204
|
|
|
smf_db_error_backtrace('Wrong value type sent to the database. Integer expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__); |
|
205
|
|
|
return (string) (int) $replacement; |
|
206
|
|
|
break; |
|
207
|
|
|
|
|
208
|
|
|
case 'string': |
|
209
|
|
|
case 'text': |
|
210
|
|
|
return sprintf('\'%1$s\'', mysqli_real_escape_string($connection, $replacement)); |
|
211
|
|
|
break; |
|
212
|
|
|
|
|
213
|
|
|
case 'array_int': |
|
214
|
|
|
if (is_array($replacement)) |
|
215
|
|
|
{ |
|
216
|
|
|
if (empty($replacement)) |
|
217
|
|
|
smf_db_error_backtrace('Database error, given array of integer values is empty. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__); |
|
218
|
|
|
|
|
219
|
|
|
foreach ($replacement as $key => $value) |
|
220
|
|
|
{ |
|
221
|
|
|
if (!is_numeric($value) || (string) $value !== (string) (int) $value) |
|
222
|
|
|
smf_db_error_backtrace('Wrong value type sent to the database. Array of integers expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__); |
|
223
|
|
|
|
|
224
|
|
|
$replacement[$key] = (string) (int) $value; |
|
225
|
|
|
} |
|
226
|
|
|
|
|
227
|
|
|
return implode(', ', $replacement); |
|
228
|
|
|
} |
|
229
|
|
|
else |
|
230
|
|
|
smf_db_error_backtrace('Wrong value type sent to the database. Array of integers expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__); |
|
231
|
|
|
|
|
232
|
|
|
break; |
|
233
|
|
|
|
|
234
|
|
|
case 'array_string': |
|
235
|
|
|
if (is_array($replacement)) |
|
236
|
|
|
{ |
|
237
|
|
|
if (empty($replacement)) |
|
238
|
|
|
smf_db_error_backtrace('Database error, given array of string values is empty. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__); |
|
239
|
|
|
|
|
240
|
|
|
foreach ($replacement as $key => $value) |
|
241
|
|
|
$replacement[$key] = sprintf('\'%1$s\'', mysqli_real_escape_string($connection, $value)); |
|
242
|
|
|
|
|
243
|
|
|
return implode(', ', $replacement); |
|
244
|
|
|
} |
|
245
|
|
|
else |
|
246
|
|
|
smf_db_error_backtrace('Wrong value type sent to the database. Array of strings expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__); |
|
247
|
|
|
break; |
|
248
|
|
|
|
|
249
|
|
|
case 'date': |
|
250
|
|
|
if (preg_match('~^(\d{4})-([0-1]?\d)-([0-3]?\d)$~', $replacement, $date_matches) === 1) |
|
251
|
|
|
return sprintf('\'%04d-%02d-%02d\'', $date_matches[1], $date_matches[2], $date_matches[3]); |
|
252
|
|
|
else |
|
253
|
|
|
smf_db_error_backtrace('Wrong value type sent to the database. Date expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__); |
|
254
|
|
|
break; |
|
255
|
|
|
|
|
256
|
|
|
case 'time': |
|
257
|
|
|
if (preg_match('~^([0-1]?\d|2[0-3]):([0-5]\d):([0-5]\d)$~', $replacement, $time_matches) === 1) |
|
258
|
|
|
return sprintf('\'%02d:%02d:%02d\'', $time_matches[1], $time_matches[2], $time_matches[3]); |
|
259
|
|
|
else |
|
260
|
|
|
smf_db_error_backtrace('Wrong value type sent to the database. Time expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__); |
|
261
|
|
|
break; |
|
262
|
|
|
|
|
263
|
|
|
case 'datetime': |
|
264
|
|
|
if (preg_match('~^(\d{4})-([0-1]?\d)-([0-3]?\d) ([0-1]?\d|2[0-3]):([0-5]\d):([0-5]\d)$~', $replacement, $datetime_matches) === 1) |
|
265
|
|
|
return 'str_to_date('. |
|
266
|
|
|
sprintf('\'%04d-%02d-%02d %02d:%02d:%02d\'', $datetime_matches[1], $datetime_matches[2], $datetime_matches[3], $datetime_matches[4], $datetime_matches[5] ,$datetime_matches[6]). |
|
267
|
|
|
',\'%Y-%m-%d %h:%i:%s\')'; |
|
268
|
|
|
else |
|
269
|
|
|
smf_db_error_backtrace('Wrong value type sent to the database. Datetime expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__); |
|
270
|
|
|
break; |
|
271
|
|
|
|
|
272
|
|
|
case 'float': |
|
273
|
|
|
if (!is_numeric($replacement)) |
|
274
|
|
|
smf_db_error_backtrace('Wrong value type sent to the database. Floating point number expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__); |
|
275
|
|
|
return (string) (float) $replacement; |
|
276
|
|
|
break; |
|
277
|
|
|
|
|
278
|
|
|
case 'identifier': |
|
279
|
|
|
// Backticks inside identifiers are supported as of MySQL 4.1. We don't need them for SMF. |
|
280
|
|
|
return '`' . strtr($replacement, array('`' => '', '.' => '`.`')) . '`'; |
|
281
|
|
|
break; |
|
282
|
|
|
|
|
283
|
|
|
case 'raw': |
|
284
|
|
|
return $replacement; |
|
285
|
|
|
break; |
|
286
|
|
|
|
|
287
|
|
|
case 'inet': |
|
288
|
|
|
if ($replacement == 'null' || $replacement == '') |
|
289
|
|
|
return 'null'; |
|
290
|
|
|
if (!isValidIP($replacement)) |
|
291
|
|
|
smf_db_error_backtrace('Wrong value type sent to the database. IPv4 or IPv6 expected.(' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__); |
|
292
|
|
|
//we don't use the native support of mysql > 5.6.2 |
|
293
|
|
|
return sprintf('unhex(\'%1$s\')', bin2hex(inet_pton($replacement))); |
|
294
|
|
|
|
|
295
|
|
|
case 'array_inet': |
|
296
|
|
|
if (is_array($replacement)) |
|
297
|
|
|
{ |
|
298
|
|
|
if (empty($replacement)) |
|
299
|
|
|
smf_db_error_backtrace('Database error, given array of IPv4 or IPv6 values is empty. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__); |
|
300
|
|
|
|
|
301
|
|
|
foreach ($replacement as $key => $value) |
|
302
|
|
|
{ |
|
303
|
|
|
if ($replacement == 'null' || $replacement == '') |
|
304
|
|
|
$replacement[$key] = 'null'; |
|
305
|
|
|
if (!isValidIP($value)) |
|
306
|
|
|
smf_db_error_backtrace('Wrong value type sent to the database. IPv4 or IPv6 expected.(' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__); |
|
307
|
|
|
$replacement[$key] = sprintf('unhex(\'%1$s\')', bin2hex(inet_pton($value))); |
|
308
|
|
|
} |
|
309
|
|
|
|
|
310
|
|
|
return implode(', ', $replacement); |
|
311
|
|
|
} |
|
312
|
|
|
else |
|
313
|
|
|
smf_db_error_backtrace('Wrong value type sent to the database. Array of IPv4 or IPv6 expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__); |
|
314
|
|
|
break; |
|
315
|
|
|
|
|
316
|
|
|
default: |
|
317
|
|
|
smf_db_error_backtrace('Undefined type used in the database query. (' . $matches[1] . ':' . $matches[2] . ')', '', false, __FILE__, __LINE__); |
|
318
|
|
|
break; |
|
319
|
|
|
} |
|
320
|
|
|
} |
|
321
|
|
|
|
|
322
|
|
|
/** |
|
323
|
|
|
* Just like the db_query, escape and quote a string, but not executing the query. |
|
324
|
|
|
* |
|
325
|
|
|
* @param string $db_string The database string |
|
326
|
|
|
* @param array $db_values An array of values to be injected into the string |
|
327
|
|
|
* @param resource $connection = null The connection to use (null to use $db_connection) |
|
328
|
|
|
* @return string The string with the values inserted |
|
329
|
|
|
*/ |
|
330
|
|
|
function smf_db_quote($db_string, $db_values, $connection = null) |
|
331
|
|
|
{ |
|
332
|
|
|
global $db_callback, $db_connection; |
|
333
|
|
|
|
|
334
|
|
|
// Only bother if there's something to replace. |
|
335
|
|
|
if (strpos($db_string, '{') !== false) |
|
336
|
|
|
{ |
|
337
|
|
|
// This is needed by the callback function. |
|
338
|
|
|
$db_callback = array($db_values, $connection === null ? $db_connection : $connection); |
|
339
|
|
|
|
|
340
|
|
|
// Do the quoting and escaping |
|
341
|
|
|
$db_string = preg_replace_callback('~{([a-z_]+)(?::([a-zA-Z0-9_-]+))?}~', 'smf_db_replacement__callback', $db_string); |
|
342
|
|
|
|
|
343
|
|
|
// Clear this global variable. |
|
344
|
|
|
$db_callback = array(); |
|
345
|
|
|
} |
|
346
|
|
|
|
|
347
|
|
|
return $db_string; |
|
348
|
|
|
} |
|
349
|
|
|
|
|
350
|
|
|
/** |
|
351
|
|
|
* Do a query. Takes care of errors too. |
|
352
|
|
|
* |
|
353
|
|
|
* @param string $identifier An identifier. Only used in Postgres when we need to do things differently... |
|
354
|
|
|
* @param string $db_string The database string |
|
355
|
|
|
* @param array $db_values = array() The values to be inserted into the string |
|
356
|
|
|
* @param resource $connection = null The connection to use (null to use $db_connection) |
|
357
|
|
|
* @return resource|bool Returns a MySQL result resource (for SELECT queries), true (for UPDATE queries) or false if the query failed |
|
358
|
|
|
*/ |
|
359
|
|
|
function smf_db_query($identifier, $db_string, $db_values = array(), $connection = null) |
|
360
|
|
|
{ |
|
361
|
|
|
global $db_cache, $db_count, $db_connection, $db_show_debug, $time_start; |
|
362
|
|
|
global $db_unbuffered, $db_callback, $modSettings; |
|
363
|
|
|
|
|
364
|
|
|
// Comments that are allowed in a query are preg_removed. |
|
365
|
|
|
static $allowed_comments_from = array( |
|
366
|
|
|
'~\s+~s', |
|
367
|
|
|
'~/\*!40001 SQL_NO_CACHE \*/~', |
|
368
|
|
|
'~/\*!40000 USE INDEX \([A-Za-z\_]+?\) \*/~', |
|
369
|
|
|
'~/\*!40100 ON DUPLICATE KEY UPDATE id_msg = \d+ \*/~', |
|
370
|
|
|
); |
|
371
|
|
|
static $allowed_comments_to = array( |
|
372
|
|
|
' ', |
|
373
|
|
|
'', |
|
374
|
|
|
'', |
|
375
|
|
|
'', |
|
376
|
|
|
); |
|
377
|
|
|
|
|
378
|
|
|
// Special queries that need processing. |
|
379
|
|
|
$replacements = array( |
|
380
|
|
|
'managePrivacyNew' => array( |
|
381
|
|
|
'/\\A.*\\z/ms' => ' |
|
382
|
|
|
UPDATE {db_prefix}themes c |
|
383
|
|
|
JOIN ( |
|
384
|
|
|
SELECT a.id_member |
|
385
|
|
|
FROM {db_prefix}themes a |
|
386
|
|
|
LEFT JOIN {db_prefix}themes b ON (a.id_member = b.id_member and b.variable = {string:bvar}) |
|
387
|
|
|
WHERE a.variable = {string:avar} and a.value = {string:aval} |
|
388
|
|
|
AND ( b.value != {string:bval} or b.value is null) |
|
389
|
|
|
) d ON (d.id_member = c.id_member) |
|
390
|
|
|
SET c.value = {string:value} |
|
391
|
|
|
WHERE c.variable = {string:avar}', |
|
392
|
|
|
), |
|
393
|
|
|
'managePrivacyInvalid' => array( |
|
394
|
|
|
'/\\A.*\\z/ms' => ' |
|
395
|
|
|
UPDATE {db_prefix}themes c |
|
396
|
|
|
JOIN ( |
|
397
|
|
|
SELECT a.id_member |
|
398
|
|
|
FROM {db_prefix}themes a |
|
399
|
|
|
LEFT JOIN {db_prefix}themes b ON (a.id_member = b.id_member and b.variable = {string:bvar}) |
|
400
|
|
|
WHERE a.variable = {string:avar} and a.value = {string:aval} |
|
401
|
|
|
AND b.value = {string:bval} |
|
402
|
|
|
) d ON (d.id_member = c.id_member) |
|
403
|
|
|
SET value = {string:value} |
|
404
|
|
|
WHERE c.variable = {string:avar}', |
|
405
|
|
|
'managePrivacyEmptyusers' => array( |
|
406
|
|
|
'/\\A.*\\z/ms' => ' |
|
407
|
|
|
UPDATE {db_prefix}themes c |
|
408
|
|
|
JOIN ( |
|
409
|
|
|
SELECT a.id_member |
|
410
|
|
|
FROM {db_prefix}themes a |
|
411
|
|
|
LEFT JOIN {db_prefix}themes b ON (a.id_member = b.id_member and b.variable = {string:bvar}) |
|
412
|
|
|
WHERE a.variable = {string:avar} and a.value = {int:aval} |
|
413
|
|
|
AND b.value = {string:bval} |
|
414
|
|
|
) d ON (d.id_member = c.id_member) |
|
415
|
|
|
SET c.value = {string:value} |
|
416
|
|
|
WHERE c.variable = {string:avar}', |
|
417
|
|
|
), |
|
418
|
|
|
); |
|
|
|
|
|
|
419
|
|
|
|
|
420
|
|
|
if (isset($replacements[$identifier])) |
|
421
|
|
|
$db_string = preg_replace(array_keys($replacements[$identifier]), array_values($replacements[$identifier]), $db_string); |
|
422
|
|
|
|
|
423
|
|
|
// Decide which connection to use. |
|
424
|
|
|
$connection = $connection === null ? $db_connection : $connection; |
|
425
|
|
|
|
|
426
|
|
|
// One more query.... |
|
427
|
|
|
$db_count = !isset($db_count) ? 1 : $db_count + 1; |
|
428
|
|
|
|
|
429
|
|
|
if (empty($modSettings['disableQueryCheck']) && strpos($db_string, '\'') !== false && empty($db_values['security_override'])) |
|
430
|
|
|
smf_db_error_backtrace('Hacking attempt...', 'Illegal character (\') used in query...', true, __FILE__, __LINE__); |
|
431
|
|
|
|
|
432
|
|
|
// Use "ORDER BY null" to prevent Mysql doing filesorts for Group By clauses without an Order By |
|
433
|
|
|
if (strpos($db_string, 'GROUP BY') !== false && strpos($db_string, 'ORDER BY') === false && preg_match('~^\s+SELECT~i', $db_string)) |
|
434
|
|
|
{ |
|
435
|
|
|
// Add before LIMIT |
|
436
|
|
|
if ($pos = strpos($db_string, 'LIMIT ')) |
|
437
|
|
|
$db_string = substr($db_string, 0, $pos) . "\t\t\tORDER BY null\n" . substr($db_string, $pos, strlen($db_string)); |
|
438
|
|
|
else |
|
439
|
|
|
// Append it. |
|
440
|
|
|
$db_string .= "\n\t\t\tORDER BY null"; |
|
441
|
|
|
} |
|
442
|
|
|
|
|
443
|
|
|
if (empty($db_values['security_override']) && (!empty($db_values) || strpos($db_string, '{db_prefix}') !== false)) |
|
444
|
|
|
{ |
|
445
|
|
|
// Pass some values to the global space for use in the callback function. |
|
446
|
|
|
$db_callback = array($db_values, $connection); |
|
447
|
|
|
|
|
448
|
|
|
// Inject the values passed to this function. |
|
449
|
|
|
$db_string = preg_replace_callback('~{([a-z_]+)(?::([a-zA-Z0-9_-]+))?}~', 'smf_db_replacement__callback', $db_string); |
|
450
|
|
|
|
|
451
|
|
|
// This shouldn't be residing in global space any longer. |
|
452
|
|
|
$db_callback = array(); |
|
453
|
|
|
} |
|
454
|
|
|
|
|
455
|
|
|
// First, we clean strings out of the query, reduce whitespace, lowercase, and trim - so we can check it over. |
|
456
|
|
|
if (empty($modSettings['disableQueryCheck'])) |
|
457
|
|
|
{ |
|
458
|
|
|
$clean = ''; |
|
459
|
|
|
$old_pos = 0; |
|
460
|
|
|
$pos = -1; |
|
461
|
|
|
// Remove the string escape for better runtime |
|
462
|
|
|
$db_string_1 = str_replace('\\\'','',$db_string); |
|
463
|
|
|
while (true) |
|
464
|
|
|
{ |
|
465
|
|
|
$pos = strpos($db_string_1, '\'', $pos + 1); |
|
466
|
|
|
if ($pos === false) |
|
467
|
|
|
break; |
|
468
|
|
|
$clean .= substr($db_string_1, $old_pos, $pos - $old_pos); |
|
469
|
|
|
|
|
470
|
|
|
while (true) |
|
471
|
|
|
{ |
|
472
|
|
|
$pos1 = strpos($db_string_1, '\'', $pos + 1); |
|
473
|
|
|
$pos2 = strpos($db_string_1, '\\', $pos + 1); |
|
474
|
|
|
if ($pos1 === false) |
|
475
|
|
|
break; |
|
476
|
|
|
elseif ($pos2 === false || $pos2 > $pos1) |
|
477
|
|
|
{ |
|
478
|
|
|
$pos = $pos1; |
|
479
|
|
|
break; |
|
480
|
|
|
} |
|
481
|
|
|
|
|
482
|
|
|
$pos = $pos2 + 1; |
|
483
|
|
|
} |
|
484
|
|
|
$clean .= ' %s '; |
|
485
|
|
|
|
|
486
|
|
|
$old_pos = $pos + 1; |
|
487
|
|
|
} |
|
488
|
|
|
$clean .= substr($db_string_1, $old_pos); |
|
489
|
|
|
$clean = trim(strtolower(preg_replace($allowed_comments_from, $allowed_comments_to, $clean))); |
|
490
|
|
|
|
|
491
|
|
|
// Comments? We don't use comments in our queries, we leave 'em outside! |
|
492
|
|
|
if (strpos($clean, '/*') > 2 || strpos($clean, '--') !== false || strpos($clean, ';') !== false) |
|
493
|
|
|
$fail = true; |
|
494
|
|
|
// Trying to change passwords, slow us down, or something? |
|
495
|
|
|
elseif (strpos($clean, 'sleep') !== false && preg_match('~(^|[^a-z])sleep($|[^[_a-z])~s', $clean) != 0) |
|
496
|
|
|
$fail = true; |
|
497
|
|
|
elseif (strpos($clean, 'benchmark') !== false && preg_match('~(^|[^a-z])benchmark($|[^[a-z])~s', $clean) != 0) |
|
498
|
|
|
$fail = true; |
|
499
|
|
|
|
|
500
|
|
|
if (!empty($fail) && function_exists('log_error')) |
|
501
|
|
|
smf_db_error_backtrace('Hacking attempt...', 'Hacking attempt...' . "\n" . $db_string, E_USER_ERROR, __FILE__, __LINE__); |
|
502
|
|
|
} |
|
503
|
|
|
|
|
504
|
|
|
// Debugging. |
|
505
|
|
|
if (isset($db_show_debug) && $db_show_debug === true) |
|
506
|
|
|
{ |
|
507
|
|
|
// Get the file and line number this function was called. |
|
508
|
|
|
list ($file, $line) = smf_db_error_backtrace('', '', 'return', __FILE__, __LINE__); |
|
509
|
|
|
|
|
510
|
|
|
// Initialize $db_cache if not already initialized. |
|
511
|
|
|
if (!isset($db_cache)) |
|
512
|
|
|
$db_cache = array(); |
|
513
|
|
|
|
|
514
|
|
|
if (!empty($_SESSION['debug_redirect'])) |
|
515
|
|
|
{ |
|
516
|
|
|
$db_cache = array_merge($_SESSION['debug_redirect'], $db_cache); |
|
517
|
|
|
$db_count = count($db_cache) + 1; |
|
518
|
|
|
$_SESSION['debug_redirect'] = array(); |
|
519
|
|
|
} |
|
520
|
|
|
|
|
521
|
|
|
// Don't overload it. |
|
522
|
|
|
$db_cache[$db_count]['q'] = $db_count < 50 ? $db_string : '...'; |
|
523
|
|
|
$db_cache[$db_count]['f'] = $file; |
|
524
|
|
|
$db_cache[$db_count]['l'] = $line; |
|
525
|
|
|
$db_cache[$db_count]['s'] = ($st = microtime(true)) - $time_start; |
|
526
|
|
|
} |
|
527
|
|
|
|
|
528
|
|
|
if (empty($db_unbuffered)) |
|
529
|
|
|
$ret = @mysqli_query($connection, $db_string); |
|
530
|
|
|
else |
|
531
|
|
|
$ret = @mysqli_query($connection, $db_string, MYSQLI_USE_RESULT); |
|
532
|
|
|
|
|
533
|
|
|
if ($ret === false && empty($db_values['db_error_skip'])) |
|
534
|
|
|
$ret = smf_db_error($db_string, $connection); |
|
535
|
|
|
|
|
536
|
|
|
// Debugging. |
|
537
|
|
|
if (isset($db_show_debug) && $db_show_debug === true) |
|
538
|
|
|
$db_cache[$db_count]['t'] = microtime(true) - $st; |
|
539
|
|
|
|
|
540
|
|
|
return $ret; |
|
541
|
|
|
} |
|
542
|
|
|
|
|
543
|
|
|
/** |
|
544
|
|
|
* affected_rows |
|
545
|
|
|
* @param resource $connection A connection to use (if null, $db_connection is used) |
|
546
|
|
|
* @return int The number of rows affected by the last query |
|
547
|
|
|
*/ |
|
548
|
|
|
function smf_db_affected_rows($connection = null) |
|
549
|
|
|
{ |
|
550
|
|
|
global $db_connection; |
|
551
|
|
|
|
|
552
|
|
|
return mysqli_affected_rows($connection === null ? $db_connection : $connection); |
|
553
|
|
|
} |
|
554
|
|
|
|
|
555
|
|
|
/** |
|
556
|
|
|
* Gets the ID of the most recently inserted row. |
|
557
|
|
|
* |
|
558
|
|
|
* @param string $table The table (only used for Postgres) |
|
559
|
|
|
* @param string $field = null The specific field (not used here) |
|
560
|
|
|
* @param resource $connection = null The connection (if null, $db_connection is used) |
|
561
|
|
|
* @return int The ID of the most recently inserted row |
|
562
|
|
|
*/ |
|
563
|
|
|
function smf_db_insert_id($table, $field = null, $connection = null) |
|
564
|
|
|
{ |
|
565
|
|
|
global $db_connection; |
|
566
|
|
|
|
|
567
|
|
|
// MySQL doesn't need the table or field information. |
|
568
|
|
|
return mysqli_insert_id($connection === null ? $db_connection : $connection); |
|
569
|
|
|
} |
|
570
|
|
|
|
|
571
|
|
|
/** |
|
572
|
|
|
* Do a transaction. |
|
573
|
|
|
* |
|
574
|
|
|
* @param string $type The step to perform (i.e. 'begin', 'commit', 'rollback') |
|
575
|
|
|
* @param resource $connection The connection to use (if null, $db_connection is used) |
|
576
|
|
|
* @return bool True if successful, false otherwise |
|
577
|
|
|
*/ |
|
578
|
|
|
function smf_db_transaction($type = 'commit', $connection = null) |
|
579
|
|
|
{ |
|
580
|
|
|
global $db_connection; |
|
581
|
|
|
|
|
582
|
|
|
// Decide which connection to use |
|
583
|
|
|
$connection = $connection === null ? $db_connection : $connection; |
|
584
|
|
|
|
|
585
|
|
|
if ($type == 'begin') |
|
586
|
|
|
return @mysqli_query($connection, 'BEGIN'); |
|
587
|
|
|
elseif ($type == 'rollback') |
|
588
|
|
|
return @mysqli_query($connection, 'ROLLBACK'); |
|
589
|
|
|
elseif ($type == 'commit') |
|
590
|
|
|
return @mysqli_query($connection, 'COMMIT'); |
|
591
|
|
|
|
|
592
|
|
|
return false; |
|
593
|
|
|
} |
|
594
|
|
|
|
|
595
|
|
|
/** |
|
596
|
|
|
* Database error! |
|
597
|
|
|
* Backtrace, log, try to fix. |
|
598
|
|
|
* |
|
599
|
|
|
* @param string $db_string The DB string |
|
600
|
|
|
* @param object $connection The connection to use (if null, $db_connection is used) |
|
601
|
|
|
*/ |
|
602
|
|
|
function smf_db_error($db_string, $connection = null) |
|
603
|
|
|
{ |
|
604
|
|
|
global $txt, $context, $sourcedir, $webmaster_email, $modSettings; |
|
605
|
|
|
global $db_connection, $db_last_error, $db_persist; |
|
606
|
|
|
global $db_server, $db_user, $db_passwd, $db_name, $db_show_debug, $ssi_db_user, $ssi_db_passwd; |
|
607
|
|
|
global $smcFunc; |
|
608
|
|
|
|
|
609
|
|
|
// Get the file and line numbers. |
|
610
|
|
|
list ($file, $line) = smf_db_error_backtrace('', '', 'return', __FILE__, __LINE__); |
|
611
|
|
|
|
|
612
|
|
|
// Decide which connection to use |
|
613
|
|
|
$connection = $connection === null ? $db_connection : $connection; |
|
614
|
|
|
|
|
615
|
|
|
// This is the error message... |
|
616
|
|
|
$query_error = mysqli_error($connection); |
|
617
|
|
|
$query_errno = mysqli_errno($connection); |
|
618
|
|
|
|
|
619
|
|
|
// Error numbers: |
|
620
|
|
|
// 1016: Can't open file '....MYI' |
|
621
|
|
|
// 1030: Got error ??? from table handler. |
|
622
|
|
|
// 1034: Incorrect key file for table. |
|
623
|
|
|
// 1035: Old key file for table. |
|
624
|
|
|
// 1205: Lock wait timeout exceeded. |
|
625
|
|
|
// 1213: Deadlock found. |
|
626
|
|
|
|
|
627
|
|
|
// Log the error. |
|
628
|
|
|
if ($query_errno != 1213 && $query_errno != 1205 && function_exists('log_error')) |
|
629
|
|
|
log_error($txt['database_error'] . ': ' . $query_error . (!empty($modSettings['enableErrorQueryLogging']) ? "\n\n$db_string" : ''), 'database', $file, $line); |
|
630
|
|
|
|
|
631
|
|
|
// Database error auto fixing ;). |
|
632
|
|
|
if (function_exists('cache_get_data') && (!isset($modSettings['autoFixDatabase']) || $modSettings['autoFixDatabase'] == '1')) |
|
633
|
|
|
{ |
|
634
|
|
|
// Force caching on, just for the error checking. |
|
635
|
|
|
$old_cache = @$modSettings['cache_enable']; |
|
636
|
|
|
$modSettings['cache_enable'] = '1'; |
|
637
|
|
|
|
|
638
|
|
|
if (($temp = cache_get_data('db_last_error', 600)) !== null) |
|
639
|
|
|
$db_last_error = max(@$db_last_error, $temp); |
|
640
|
|
|
|
|
641
|
|
|
if (@$db_last_error < time() - 3600 * 24 * 3) |
|
642
|
|
|
{ |
|
643
|
|
|
// We know there's a problem... but what? Try to auto detect. |
|
644
|
|
|
if ($query_errno == 1030 && strpos($query_error, ' 127 ') !== false) |
|
645
|
|
|
{ |
|
646
|
|
|
preg_match_all('~(?:[\n\r]|^)[^\']+?(?:FROM|JOIN|UPDATE|TABLE) ((?:[^\n\r(]+?(?:, )?)*)~s', $db_string, $matches); |
|
647
|
|
|
|
|
648
|
|
|
$fix_tables = array(); |
|
649
|
|
|
foreach ($matches[1] as $tables) |
|
650
|
|
|
{ |
|
651
|
|
|
$tables = array_unique(explode(',', $tables)); |
|
652
|
|
|
foreach ($tables as $table) |
|
653
|
|
|
{ |
|
654
|
|
|
// Now, it's still theoretically possible this could be an injection. So backtick it! |
|
655
|
|
|
if (trim($table) != '') |
|
656
|
|
|
$fix_tables[] = '`' . strtr(trim($table), array('`' => '')) . '`'; |
|
657
|
|
|
} |
|
658
|
|
|
} |
|
659
|
|
|
|
|
660
|
|
|
$fix_tables = array_unique($fix_tables); |
|
661
|
|
|
} |
|
662
|
|
|
// Table crashed. Let's try to fix it. |
|
663
|
|
|
elseif ($query_errno == 1016) |
|
664
|
|
|
{ |
|
665
|
|
|
if (preg_match('~\'([^\.\']+)~', $query_error, $match) != 0) |
|
666
|
|
|
$fix_tables = array('`' . $match[1] . '`'); |
|
667
|
|
|
} |
|
668
|
|
|
// Indexes crashed. Should be easy to fix! |
|
669
|
|
|
elseif ($query_errno == 1034 || $query_errno == 1035) |
|
670
|
|
|
{ |
|
671
|
|
|
preg_match('~\'([^\']+?)\'~', $query_error, $match); |
|
672
|
|
|
$fix_tables = array('`' . $match[1] . '`'); |
|
673
|
|
|
} |
|
674
|
|
|
} |
|
675
|
|
|
|
|
676
|
|
|
// Check for errors like 145... only fix it once every three days, and send an email. (can't use empty because it might not be set yet...) |
|
677
|
|
|
if (!empty($fix_tables)) |
|
678
|
|
|
{ |
|
679
|
|
|
// Subs-Admin.php for updateSettingsFile(), Subs-Post.php for sendmail(). |
|
680
|
|
|
require_once($sourcedir . '/Subs-Admin.php'); |
|
681
|
|
|
require_once($sourcedir . '/Subs-Post.php'); |
|
682
|
|
|
|
|
683
|
|
|
// Make a note of the REPAIR... |
|
684
|
|
|
cache_put_data('db_last_error', time(), 600); |
|
685
|
|
|
if (($temp = cache_get_data('db_last_error', 600)) === null) |
|
686
|
|
|
updateSettingsFile(array('db_last_error' => time())); |
|
687
|
|
|
|
|
688
|
|
|
// Attempt to find and repair the broken table. |
|
689
|
|
|
foreach ($fix_tables as $table) |
|
690
|
|
|
$smcFunc['db_query']('', " |
|
691
|
|
|
REPAIR TABLE $table", false, false); |
|
692
|
|
|
|
|
693
|
|
|
// And send off an email! |
|
694
|
|
|
sendmail($webmaster_email, $txt['database_error'], $txt['tried_to_repair'], null, 'dberror'); |
|
695
|
|
|
|
|
696
|
|
|
$modSettings['cache_enable'] = $old_cache; |
|
697
|
|
|
|
|
698
|
|
|
// Try the query again...? |
|
699
|
|
|
$ret = $smcFunc['db_query']('', $db_string, false, false); |
|
700
|
|
|
if ($ret !== false) |
|
701
|
|
|
return $ret; |
|
702
|
|
|
} |
|
703
|
|
|
else |
|
704
|
|
|
$modSettings['cache_enable'] = $old_cache; |
|
705
|
|
|
|
|
706
|
|
|
// Check for the "lost connection" or "deadlock found" errors - and try it just one more time. |
|
707
|
|
|
if (in_array($query_errno, array(1205, 1213))) |
|
708
|
|
|
{ |
|
709
|
|
|
if ($db_connection) |
|
710
|
|
|
{ |
|
711
|
|
|
// Try a deadlock more than once more. |
|
712
|
|
|
for ($n = 0; $n < 4; $n++) |
|
713
|
|
|
{ |
|
714
|
|
|
$ret = $smcFunc['db_query']('', $db_string, false, false); |
|
715
|
|
|
|
|
716
|
|
|
$new_errno = mysqli_errno($db_connection); |
|
717
|
|
|
if ($ret !== false || in_array($new_errno, array(1205, 1213))) |
|
718
|
|
|
break; |
|
719
|
|
|
} |
|
720
|
|
|
|
|
721
|
|
|
// If it failed again, shucks to be you... we're not trying it over and over. |
|
722
|
|
|
if ($ret !== false) |
|
723
|
|
|
return $ret; |
|
724
|
|
|
} |
|
725
|
|
|
} |
|
726
|
|
|
// Are they out of space, perhaps? |
|
727
|
|
|
elseif ($query_errno == 1030 && (strpos($query_error, ' -1 ') !== false || strpos($query_error, ' 28 ') !== false || strpos($query_error, ' 12 ') !== false)) |
|
728
|
|
|
{ |
|
729
|
|
|
if (!isset($txt)) |
|
730
|
|
|
$query_error .= ' - check database storage space.'; |
|
731
|
|
|
else |
|
732
|
|
|
{ |
|
733
|
|
|
if (!isset($txt['mysql_error_space'])) |
|
734
|
|
|
loadLanguage('Errors'); |
|
735
|
|
|
|
|
736
|
|
|
$query_error .= !isset($txt['mysql_error_space']) ? ' - check database storage space.' : $txt['mysql_error_space']; |
|
737
|
|
|
} |
|
738
|
|
|
} |
|
739
|
|
|
} |
|
740
|
|
|
|
|
741
|
|
|
// Nothing's defined yet... just die with it. |
|
742
|
|
|
if (empty($context) || empty($txt)) |
|
743
|
|
|
die($query_error); |
|
744
|
|
|
|
|
745
|
|
|
// Show an error message, if possible. |
|
746
|
|
|
$context['error_title'] = $txt['database_error']; |
|
747
|
|
|
if (allowedTo('admin_forum')) |
|
748
|
|
|
$context['error_message'] = nl2br($query_error) . '<br>' . $txt['file'] . ': ' . $file . '<br>' . $txt['line'] . ': ' . $line; |
|
749
|
|
|
else |
|
750
|
|
|
$context['error_message'] = $txt['try_again']; |
|
751
|
|
|
|
|
752
|
|
|
if (allowedTo('admin_forum') && isset($db_show_debug) && $db_show_debug === true) |
|
753
|
|
|
{ |
|
754
|
|
|
$context['error_message'] .= '<br><br>' . nl2br($db_string); |
|
755
|
|
|
} |
|
756
|
|
|
|
|
757
|
|
|
// It's already been logged... don't log it again. |
|
758
|
|
|
fatal_error($context['error_message'], false); |
|
759
|
|
|
} |
|
760
|
|
|
|
|
761
|
|
|
/** |
|
762
|
|
|
* Inserts data into a table |
|
763
|
|
|
* |
|
764
|
|
|
* @param string $method The insert method - can be 'replace', 'ignore' or 'insert' |
|
765
|
|
|
* @param string $table The table we're inserting the data into |
|
766
|
|
|
* @param array $columns An array of the columns we're inserting the data into. Should contain 'column' => 'datatype' pairs |
|
767
|
|
|
* @param array $data The data to insert |
|
768
|
|
|
* @param array $keys The keys for the table |
|
769
|
|
|
* @param int returnmode 0 = nothing(default), 1 = last row id, 2 = all rows id as array |
|
770
|
|
|
* @param object $connection The connection to use (if null, $db_connection is used) |
|
771
|
|
|
* @return mixed value of the first key, behavior based on returnmode. null if no data. |
|
772
|
|
|
*/ |
|
773
|
|
|
function smf_db_insert($method = 'replace', $table, $columns, $data, $keys, $returnmode = 0, $connection = null) |
|
774
|
|
|
{ |
|
775
|
|
|
global $smcFunc, $db_connection, $db_prefix; |
|
776
|
|
|
|
|
777
|
|
|
$connection = $connection === null ? $db_connection : $connection; |
|
778
|
|
|
|
|
779
|
|
|
$return_var = null; |
|
780
|
|
|
|
|
781
|
|
|
// With nothing to insert, simply return. |
|
782
|
|
|
if (empty($data)) |
|
783
|
|
|
return; |
|
784
|
|
|
|
|
785
|
|
|
// Replace the prefix holder with the actual prefix. |
|
786
|
|
|
$table = str_replace('{db_prefix}', $db_prefix, $table); |
|
787
|
|
|
|
|
788
|
|
|
$with_returning = false; |
|
789
|
|
|
|
|
790
|
|
|
if (!empty($keys) && (count($keys) > 0) && $returnmode > 0) |
|
791
|
|
|
{ |
|
792
|
|
|
$with_returning = true; |
|
793
|
|
|
if ($returnmode == 2) |
|
794
|
|
|
$return_var = array(); |
|
795
|
|
|
} |
|
796
|
|
|
|
|
797
|
|
|
// Inserting data as a single row can be done as a single array. |
|
798
|
|
|
if (!is_array($data[array_rand($data)])) |
|
799
|
|
|
$data = array($data); |
|
800
|
|
|
|
|
801
|
|
|
// Create the mold for a single row insert. |
|
802
|
|
|
$insertData = '('; |
|
803
|
|
|
foreach ($columns as $columnName => $type) |
|
804
|
|
|
{ |
|
805
|
|
|
// Are we restricting the length? |
|
806
|
|
|
if (strpos($type, 'string-') !== false) |
|
807
|
|
|
$insertData .= sprintf('SUBSTRING({string:%1$s}, 1, ' . substr($type, 7) . '), ', $columnName); |
|
808
|
|
|
else |
|
809
|
|
|
$insertData .= sprintf('{%1$s:%2$s}, ', $type, $columnName); |
|
810
|
|
|
} |
|
811
|
|
|
$insertData = substr($insertData, 0, -2) . ')'; |
|
812
|
|
|
|
|
813
|
|
|
// Create an array consisting of only the columns. |
|
814
|
|
|
$indexed_columns = array_keys($columns); |
|
815
|
|
|
|
|
816
|
|
|
// Here's where the variables are injected to the query. |
|
817
|
|
|
$insertRows = array(); |
|
818
|
|
|
foreach ($data as $dataRow) |
|
819
|
|
|
$insertRows[] = smf_db_quote($insertData, array_combine($indexed_columns, $dataRow), $connection); |
|
820
|
|
|
|
|
821
|
|
|
// Determine the method of insertion. |
|
822
|
|
|
$queryTitle = $method == 'replace' ? 'REPLACE' : ($method == 'ignore' ? 'INSERT IGNORE' : 'INSERT'); |
|
823
|
|
|
|
|
824
|
|
|
// Sanity check for replace is key part of the columns array |
|
825
|
|
|
if ($method == 'replace' && count(array_intersect_key($columns, array_flip($keys))) !== count($keys)) |
|
826
|
|
|
smf_db_error_backtrace('Primary Key field missing in insert call', |
|
827
|
|
|
'Change the method of db insert to insert or add the pk field to the columns array', E_USER_ERROR, __FILE__, __LINE__); |
|
828
|
|
|
|
|
829
|
|
|
if (!$with_returning || $method != 'ingore') |
|
830
|
|
|
{ |
|
831
|
|
|
// Do the insert. |
|
832
|
|
|
$smcFunc['db_query']('', ' |
|
833
|
|
|
' . $queryTitle . ' INTO ' . $table . '(`' . implode('`, `', $indexed_columns) . '`) |
|
834
|
|
|
VALUES |
|
835
|
|
|
' . implode(', |
|
836
|
|
|
', $insertRows), |
|
837
|
|
|
array( |
|
838
|
|
|
'security_override' => true, |
|
839
|
|
|
'db_error_skip' => $table === $db_prefix . 'log_errors', |
|
840
|
|
|
), |
|
841
|
|
|
$connection |
|
842
|
|
|
); |
|
843
|
|
|
} |
|
844
|
|
|
else //special way for ignore method with returning |
|
845
|
|
|
{ |
|
846
|
|
|
$count = count($insertRows); |
|
847
|
|
|
$ai = 0; |
|
848
|
|
|
for($i = 0; $i < $count; $i++) |
|
849
|
|
|
{ |
|
850
|
|
|
$old_id = $smcFunc['db_insert_id'](); |
|
851
|
|
|
|
|
852
|
|
|
$smcFunc['db_query']('', ' |
|
853
|
|
|
' . $queryTitle . ' INTO ' . $table . '(`' . implode('`, `', $indexed_columns) . '`) |
|
854
|
|
|
VALUES |
|
855
|
|
|
' . $insertRows[$i], |
|
856
|
|
|
array( |
|
857
|
|
|
'security_override' => true, |
|
858
|
|
|
'db_error_skip' => $table === $db_prefix . 'log_errors', |
|
859
|
|
|
), |
|
860
|
|
|
$connection |
|
861
|
|
|
); |
|
862
|
|
|
$new_id = $smcFunc['db_insert_id'](); |
|
863
|
|
|
|
|
864
|
|
|
if ($last_id != $new_id) //the inserted value was new |
|
865
|
|
|
{ |
|
866
|
|
|
$ai = $new_id; |
|
867
|
|
|
} |
|
868
|
|
|
else // the inserted value already exists we need to find the pk |
|
869
|
|
|
{ |
|
870
|
|
|
$where_string = ''; |
|
871
|
|
|
$count2 = count($indexed_columns); |
|
872
|
|
|
for ($x = 0; $x < $count2; $x++) |
|
873
|
|
|
{ |
|
874
|
|
|
$where_string += key($indexed_columns[$x]) . ' = '. $insertRows[$i][$x]; |
|
875
|
|
|
if (($x + 1) < $count2) |
|
876
|
|
|
$where_string += ' AND '; |
|
877
|
|
|
} |
|
878
|
|
|
|
|
879
|
|
|
$request = $smcFunc['db_query']('',' |
|
880
|
|
|
SELECT `'. $keys[0] . '` FROM ' . $table .' |
|
881
|
|
|
WHERE ' . $where_string . ' LIMIT 1', |
|
882
|
|
|
array() |
|
883
|
|
|
); |
|
884
|
|
|
|
|
885
|
|
|
if ($request !== false && $smcFunc['db_num_rows']($request) == 1) |
|
886
|
|
|
{ |
|
887
|
|
|
$row = $smcFunc['db_fetch_assoc']($request); |
|
888
|
|
|
$ai = $row[$keys[0]]; |
|
889
|
|
|
} |
|
890
|
|
|
} |
|
891
|
|
|
|
|
892
|
|
|
if ($returnmode == 1) |
|
893
|
|
|
$return_var = $ai; |
|
894
|
|
|
else if ($returnmode == 2) |
|
895
|
|
|
$return_var[] = $ai; |
|
896
|
|
|
} |
|
897
|
|
|
} |
|
898
|
|
|
|
|
899
|
|
|
|
|
900
|
|
|
if ($with_returning) |
|
901
|
|
|
{ |
|
902
|
|
|
if ($returnmode == 1 && empty($return_var)) |
|
903
|
|
|
$return_var = smf_db_insert_id($table, $keys[0]) + count($insertRows) - 1; |
|
904
|
|
|
else if ($returnmode == 2 && empty($return_var)) |
|
905
|
|
|
{ |
|
906
|
|
|
$return_var = array(); |
|
907
|
|
|
$count = count($insertRows); |
|
908
|
|
|
$start = smf_db_insert_id($table, $keys[0]); |
|
909
|
|
|
for ($i = 0; $i < $count; $i++ ) |
|
910
|
|
|
$return_var[] = $start + $i; |
|
911
|
|
|
} |
|
912
|
|
|
return $return_var; |
|
913
|
|
|
} |
|
914
|
|
|
} |
|
915
|
|
|
|
|
916
|
|
|
/** |
|
917
|
|
|
* This function tries to work out additional error information from a back trace. |
|
918
|
|
|
* |
|
919
|
|
|
* @param string $error_message The error message |
|
920
|
|
|
* @param string $log_message The message to log |
|
921
|
|
|
* @param string|bool $error_type What type of error this is |
|
922
|
|
|
* @param string $file The file the error occurred in |
|
923
|
|
|
* @param int $line What line of $file the code which generated the error is on |
|
924
|
|
|
* @return void|array Returns an array with the file and line if $error_type is 'return' |
|
925
|
|
|
*/ |
|
926
|
|
|
function smf_db_error_backtrace($error_message, $log_message = '', $error_type = false, $file = null, $line = null) |
|
927
|
|
|
{ |
|
928
|
|
|
if (empty($log_message)) |
|
929
|
|
|
$log_message = $error_message; |
|
930
|
|
|
|
|
931
|
|
|
foreach (debug_backtrace() as $step) |
|
932
|
|
|
{ |
|
933
|
|
|
// Found it? |
|
934
|
|
|
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) |
|
935
|
|
|
{ |
|
936
|
|
|
$log_message .= '<br>Function: ' . $step['function']; |
|
937
|
|
|
break; |
|
938
|
|
|
} |
|
939
|
|
|
|
|
940
|
|
|
if (isset($step['line'])) |
|
941
|
|
|
{ |
|
942
|
|
|
$file = $step['file']; |
|
943
|
|
|
$line = $step['line']; |
|
944
|
|
|
} |
|
945
|
|
|
} |
|
946
|
|
|
|
|
947
|
|
|
// A special case - we want the file and line numbers for debugging. |
|
948
|
|
|
if ($error_type == 'return') |
|
949
|
|
|
return array($file, $line); |
|
950
|
|
|
|
|
951
|
|
|
// Is always a critical error. |
|
952
|
|
|
if (function_exists('log_error')) |
|
953
|
|
|
log_error($log_message, 'critical', $file, $line); |
|
954
|
|
|
|
|
955
|
|
|
if (function_exists('fatal_error')) |
|
956
|
|
|
{ |
|
957
|
|
|
fatal_error($error_message, false); |
|
958
|
|
|
|
|
959
|
|
|
// Cannot continue... |
|
960
|
|
|
exit; |
|
961
|
|
|
} |
|
962
|
|
|
elseif ($error_type) |
|
963
|
|
|
trigger_error($error_message . ($line !== null ? '<em>(' . basename($file) . '-' . $line . ')</em>' : ''), $error_type); |
|
964
|
|
|
else |
|
965
|
|
|
trigger_error($error_message . ($line !== null ? '<em>(' . basename($file) . '-' . $line . ')</em>' : '')); |
|
966
|
|
|
} |
|
967
|
|
|
|
|
968
|
|
|
/** |
|
969
|
|
|
* Escape the LIKE wildcards so that they match the character and not the wildcard. |
|
970
|
|
|
* |
|
971
|
|
|
* @param string $string The string to escape |
|
972
|
|
|
* @param bool $translate_human_wildcards If true, turns human readable wildcards into SQL wildcards. |
|
973
|
|
|
* @return string The escaped string |
|
974
|
|
|
*/ |
|
975
|
|
|
function smf_db_escape_wildcard_string($string, $translate_human_wildcards = false) |
|
976
|
|
|
{ |
|
977
|
|
|
$replacements = array( |
|
978
|
|
|
'%' => '\%', |
|
979
|
|
|
'_' => '\_', |
|
980
|
|
|
'\\' => '\\\\', |
|
981
|
|
|
); |
|
982
|
|
|
|
|
983
|
|
|
if ($translate_human_wildcards) |
|
984
|
|
|
$replacements += array( |
|
985
|
|
|
'*' => '%', |
|
986
|
|
|
); |
|
987
|
|
|
|
|
988
|
|
|
return strtr($string, $replacements); |
|
989
|
|
|
} |
|
990
|
|
|
|
|
991
|
|
|
/** |
|
992
|
|
|
* Validates whether the resource is a valid mysqli instance. |
|
993
|
|
|
* Mysqli uses objects rather than resource. https://bugs.php.net/bug.php?id=42797 |
|
994
|
|
|
* |
|
995
|
|
|
* @param mixed $result The string to test |
|
996
|
|
|
* @return bool True if it is, false otherwise |
|
997
|
|
|
*/ |
|
998
|
|
|
function smf_is_resource($result) |
|
999
|
|
|
{ |
|
1000
|
|
|
if ($result instanceof mysqli_result) |
|
1001
|
|
|
return true; |
|
1002
|
|
|
|
|
1003
|
|
|
return false; |
|
1004
|
|
|
} |
|
1005
|
|
|
|
|
1006
|
|
|
/** |
|
1007
|
|
|
* Fetches all rows from a result as an array |
|
1008
|
|
|
* |
|
1009
|
|
|
* @param resource $request A MySQL result resource |
|
1010
|
|
|
* @return array An array that contains all rows (records) in the result resource |
|
1011
|
|
|
*/ |
|
1012
|
|
|
function smf_db_fetch_all($request) |
|
1013
|
|
|
{ |
|
1014
|
|
|
// Return the right row. |
|
1015
|
|
|
return mysqli_fetch_all($request); |
|
1016
|
|
|
} |
|
1017
|
|
|
|
|
1018
|
|
|
/** |
|
1019
|
|
|
* Function to save errors in database in a safe way |
|
1020
|
|
|
* |
|
1021
|
|
|
* @param array with keys in this order id_member, log_time, ip, url, message, session, error_type, file, line |
|
1022
|
|
|
* @return void |
|
1023
|
|
|
*/ |
|
1024
|
|
|
function smf_db_error_insert($error_array) |
|
1025
|
|
|
{ |
|
1026
|
|
|
global $db_prefix, $db_connection; |
|
1027
|
|
|
static $mysql_error_data_prep; |
|
1028
|
|
|
|
|
1029
|
|
|
// without database we can't do anything |
|
1030
|
|
|
if (empty($db_connection)) |
|
1031
|
|
|
return; |
|
1032
|
|
|
|
|
1033
|
|
|
if (empty($mysql_error_data_prep)) |
|
1034
|
|
|
$mysql_error_data_prep = mysqli_prepare($db_connection, |
|
1035
|
|
|
'INSERT INTO ' . $db_prefix . 'log_errors(id_member, log_time, ip, url, message, session, error_type, file, line, backtrace) |
|
1036
|
|
|
VALUES( ?, ?, unhex(?), ?, ?, ?, ?, ?, ?, ?)' |
|
1037
|
|
|
); |
|
1038
|
|
|
|
|
1039
|
|
|
if (filter_var($error_array[2], FILTER_VALIDATE_IP) !== false) |
|
1040
|
|
|
$error_array[2] = bin2hex(inet_pton($error_array[2])); |
|
1041
|
|
|
else |
|
1042
|
|
|
$error_array[2] = null; |
|
1043
|
|
|
mysqli_stmt_bind_param($mysql_error_data_prep, 'iissssssis', |
|
1044
|
|
|
$error_array[0], $error_array[1], $error_array[2], $error_array[3], $error_array[4], $error_array[5], $error_array[6], |
|
1045
|
|
|
$error_array[7], $error_array[8], $error_array[9]); |
|
1046
|
|
|
mysqli_stmt_execute ($mysql_error_data_prep); |
|
1047
|
|
|
} |
|
1048
|
|
|
|
|
1049
|
|
|
/** |
|
1050
|
|
|
* Function which constructs an optimize custom order string |
|
1051
|
|
|
* as an improved alternative to find_in_set() |
|
1052
|
|
|
* |
|
1053
|
|
|
* @param string $field name |
|
1054
|
|
|
* @param array $array_values Field values sequenced in array via order priority. Must cast to int. |
|
1055
|
|
|
* @param boolean $desc default false |
|
1056
|
|
|
* @return string case field when ... then ... end |
|
1057
|
|
|
*/ |
|
1058
|
|
|
function smf_db_custom_order($field, $array_values, $desc = false) |
|
1059
|
|
|
{ |
|
1060
|
|
|
$return = 'CASE '. $field . ' '; |
|
1061
|
|
|
$count = count($array_values); |
|
1062
|
|
|
$then = ($desc ? ' THEN -' : ' THEN '); |
|
1063
|
|
|
|
|
1064
|
|
|
for ($i = 0; $i < $count; $i++) |
|
1065
|
|
|
$return .= 'WHEN ' . (int) $array_values[$i] . $then . $i . ' '; |
|
1066
|
|
|
|
|
1067
|
|
|
$return .= 'END'; |
|
1068
|
|
|
return $return; |
|
1069
|
|
|
} |
|
1070
|
|
|
|
|
1071
|
|
|
/** |
|
1072
|
|
|
* Function which return the information if the database supports native replace inserts |
|
1073
|
|
|
* |
|
1074
|
|
|
* @return boolean true or false |
|
1075
|
|
|
*/ |
|
1076
|
|
|
function smf_db_native_replace() |
|
1077
|
|
|
{ |
|
1078
|
|
|
return true; |
|
1079
|
|
|
} |
|
1080
|
|
|
|
|
1081
|
|
|
/** |
|
1082
|
|
|
* Function which return the information if the database supports cte with recursive |
|
1083
|
|
|
* |
|
1084
|
|
|
* @return boolean true or false |
|
1085
|
|
|
*/ |
|
1086
|
|
|
function smf_db_cte_support() |
|
1087
|
|
|
{ |
|
1088
|
|
|
global $smcFunc; |
|
1089
|
|
|
static $return; |
|
1090
|
|
|
|
|
1091
|
|
|
if (isset($return)) |
|
1092
|
|
|
return $return; |
|
1093
|
|
|
|
|
1094
|
|
|
db_extend('extra'); |
|
1095
|
|
|
|
|
1096
|
|
|
$version = $smcFunc['db_get_version'](); |
|
1097
|
|
|
|
|
1098
|
|
|
if (strpos(strtolower($version), 'mariadb') !== false) |
|
1099
|
|
|
$return = version_compare($version, '10.2.2', '>='); |
|
1100
|
|
|
else //mysql |
|
1101
|
|
|
$return = version_compare($version, '8.0.1', '>='); |
|
1102
|
|
|
|
|
1103
|
|
|
return $return; |
|
1104
|
|
|
} |
|
1105
|
|
|
|
|
1106
|
|
|
/** |
|
1107
|
|
|
* Function which return the escaped string |
|
1108
|
|
|
* |
|
1109
|
|
|
* @param string the unescaped text |
|
1110
|
|
|
* @param resource $connection = null The connection to use (null to use $db_connection) |
|
1111
|
|
|
* @return string escaped string |
|
1112
|
|
|
*/ |
|
1113
|
|
|
function smf_db_escape_string($string, $connection = null) |
|
1114
|
|
|
{ |
|
1115
|
|
|
global $db_connection; |
|
1116
|
|
|
|
|
1117
|
|
|
return mysqli_real_escape_string($connection === null ? $db_connection : $connection, $string); |
|
1118
|
|
|
} |
|
1119
|
|
|
|
|
1120
|
|
|
?> |
SimpleMachines /
SMF2.1
Loading history...