function smf_db_query($identifier, $db_string, $db_values = array(), $connection = null)
{
global $db_cache, $db_count, $db_connection, $db_show_debug, $time_start;
global $db_unbuffered, $db_callback, $modSettings;
// Decide which connection to use.
$connection = $connection == null ? $db_connection : $connection;
// Special queries that need processing.
$replacements = array('birthday_array' => array('~DATE_FORMAT\\(([^,]+),\\s*([^\\)]+)\\s*\\)~' => 'strftime($2, $1)'), 'substring' => array('~SUBSTRING~' => 'SUBSTR'), 'truncate_table' => array('~TRUNCATE~i' => 'DELETE FROM'), 'user_activity_by_time' => array('~HOUR\\(FROM_UNIXTIME\\((poster_time\\s+\\+\\s+\\{int:.+\\})\\)\\)~' => 'strftime(\'%H\', datetime($1, \'unixepoch\'))'), 'unread_fetch_topic_count' => array('~\\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)'), 'alter_table_boards' => array('~(.+)~' => ''), 'get_random_number' => array('~RAND~' => 'RANDOM'), 'set_character_set' => array('~(.+)~' => ''), 'themes_count' => array('~\\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'), 'attach_download_increase' => array('~LOW_PRIORITY~' => ''), 'pm_conversation_list' => array('~ORDER BY id_pm~' => 'ORDER BY MAX(pm.id_pm)'), 'boardindex_fetch_boards' => array('~(.)$~' => '$1 ORDER BY b.board_order'), 'messageindex_fetch_boards' => array('~(.)$~' => '$1 ORDER BY b.board_order'), 'order_by_board_order' => array('~(.)$~' => '$1 ORDER BY b.board_order'), 'spider_check' => array('~(.)$~' => '$1 ORDER BY LENGTH(user_agent) DESC'));
if (isset($replacements[$identifier])) {
$db_string = preg_replace(array_keys($replacements[$identifier]), array_values($replacements[$identifier]), $db_string);
}
// SQLite doesn't support count(distinct).
$db_string = trim($db_string);
$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);
// Or RLIKE.
$db_string = preg_replace('~AND\\s*(.+?)\\s*RLIKE\\s*(\\{string:.+?\\})~', 'AND REGEXP(\\1, \\2)', $db_string);
// INSTR? No support for that buddy :(
if (preg_match('~INSTR\\((.+?),\\s(.+?)\\)~', $db_string, $matches) === 1) {
$db_string = preg_replace('~INSTR\\((.+?),\\s(.+?)\\)~', '$1 LIKE $2', $db_string);
list(, $search) = explode(':', substr($matches[2], 1, -1));
$db_values[$search] = '%' . $db_values[$search] . '%';
}
// Lets remove ASC and DESC from GROUP BY clause.
if (preg_match('~GROUP BY .*? (?:ASC|DESC)~is', $db_string, $matches)) {
$replace = str_replace(array('ASC', 'DESC'), '', $matches[0]);
$db_string = str_replace($matches[0], $replace, $db_string);
}
// We need to replace the SUBSTRING in the sort identifier.
if ($identifier == 'substring_membergroups' && isset($db_values['sort'])) {
$db_values['sort'] = preg_replace('~SUBSTRING~', 'SUBSTR', $db_values['sort']);
}
// 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.
$db_string = preg_replace('~\\(TO_DAYS\\(([^)]+)\\) - TO_DAYS\\(([^)]+)\\)\\) AS span~', '(julianday($1) - julianday($2)) AS span', $db_string);
// One more query....
$db_count = !isset($db_count) ? 1 : $db_count + 1;
// Overriding security? This is evil!
$security_override = $db_values === 'security_override' || !empty($db_values['security_override']);
if (empty($modSettings['disableQueryCheck']) && strpos($db_string, '\'') !== false && !$security_override) {
smf_db_error_backtrace('Hacking attempt...', 'Illegal character (\') used in query...', true, __FILE__, __LINE__);
}
if (!$security_override && (!empty($db_values) || strpos($db_string, '{db_prefix}') !== false)) {
// Pass some values to the global space for use in the callback function.
$db_callback = array($db_values, $connection);
// Inject the values passed to this function.
$db_string = preg_replace_callback('~{([a-z_]+)(?::([a-zA-Z0-9_-]+))?}~', 'smf_db_replacement__callback', $db_string);
// This shouldn't be residing in global space any longer.
$db_callback = array();
}
// Debugging.
if (isset($db_show_debug) && $db_show_debug === true) {
// Get the file and line number this function was called.
list($file, $line) = smf_db_error_backtrace('', '', 'return', __FILE__, __LINE__);
// Initialize $db_cache if not already initialized.
if (!isset($db_cache)) {
$db_cache = array();
}
if (!empty($_SESSION['debug_redirect'])) {
$db_cache = array_merge($_SESSION['debug_redirect'], $db_cache);
$db_count = count($db_cache) + 1;
$_SESSION['debug_redirect'] = array();
}
$st = microtime();
// Don't overload it.
$db_cache[$db_count]['q'] = $db_count < 50 ? $db_string : '...';
$db_cache[$db_count]['f'] = $file;
$db_cache[$db_count]['l'] = $line;
$db_cache[$db_count]['s'] = array_sum(explode(' ', $st)) - array_sum(explode(' ', $time_start));
}
$ret = @sqlite_query($db_string, $connection, SQLITE_BOTH, $err_msg);
if ($ret === false && empty($db_values['db_error_skip'])) {
$ret = smf_db_error($db_string . '#!#' . $err_msg, $connection);
}
// Debugging.
if (isset($db_show_debug) && $db_show_debug === true) {
$db_cache[$db_count]['t'] = array_sum(explode(' ', microtime())) - array_sum(explode(' ', $st));
}
return $ret;
}
function smf_db_query($identifier, $db_string, $db_values = array(), $connection = null)
{
global $db_cache, $db_count, $db_connection, $db_show_debug, $time_start;
global $db_unbuffered, $db_callback, $modSettings;
// Comments that are allowed in a query are preg_removed.
static $allowed_comments_from = array('~\\s+~s', '~/\\*!40001 SQL_NO_CACHE \\*/~', '~/\\*!40000 USE INDEX \\([A-Za-z\\_]+?\\) \\*/~', '~/\\*!40100 ON DUPLICATE KEY UPDATE id_msg = \\d+ \\*/~');
static $allowed_comments_to = array(' ', '', '', '');
// Decide which connection to use.
$connection = $connection == null ? $db_connection : $connection;
// One more query....
$db_count = !isset($db_count) ? 1 : $db_count + 1;
if (empty($modSettings['disableQueryCheck']) && strpos($db_string, '\'') !== false && empty($db_values['security_override'])) {
smf_db_error_backtrace('Hacking attempt...', 'Illegal character (\') used in query...', true, __FILE__, __LINE__);
}
// Use "ORDER BY null" to prevent Mysql doing filesorts for Group By clauses without an Order By
if (strpos($db_string, 'GROUP BY') !== false && strpos($db_string, 'ORDER BY') === false && strpos($db_string, 'INSERT INTO') === false) {
// Add before LIMIT
if ($pos = strpos($db_string, 'LIMIT ')) {
$db_string = substr($db_string, 0, $pos) . "\t\t\tORDER BY null\n" . substr($db_string, $pos, strlen($db_string));
} else {
// Append it.
$db_string .= "\n\t\t\tORDER BY null";
}
}
if (empty($db_values['security_override']) && (!empty($db_values) || strpos($db_string, '{db_prefix}') !== false)) {
// Pass some values to the global space for use in the callback function.
$db_callback = array($db_values, $connection);
// Inject the values passed to this function.
$db_string = preg_replace_callback('~{([a-z_]+)(?::([a-zA-Z0-9_-]+))?}~', 'smf_db_replacement__callback', $db_string);
// This shouldn't be residing in global space any longer.
$db_callback = array();
}
// Debugging.
if (isset($db_show_debug) && $db_show_debug === true) {
// Get the file and line number this function was called.
list($file, $line) = smf_db_error_backtrace('', '', 'return', __FILE__, __LINE__);
// Initialize $db_cache if not already initialized.
if (!isset($db_cache)) {
$db_cache = array();
}
if (!empty($_SESSION['debug_redirect'])) {
$db_cache = array_merge($_SESSION['debug_redirect'], $db_cache);
$db_count = count($db_cache) + 1;
$_SESSION['debug_redirect'] = array();
}
$st = microtime();
// Don't overload it.
$db_cache[$db_count]['q'] = $db_count < 50 ? $db_string : '...';
$db_cache[$db_count]['f'] = $file;
$db_cache[$db_count]['l'] = $line;
$db_cache[$db_count]['s'] = array_sum(explode(' ', $st)) - array_sum(explode(' ', $time_start));
}
// First, we clean strings out of the query, reduce whitespace, lowercase, and trim - so we can check it over.
if (empty($modSettings['disableQueryCheck'])) {
$clean = '';
$old_pos = 0;
$pos = -1;
while (true) {
$pos = strpos($db_string, '\'', $pos + 1);
if ($pos === false) {
break;
}
$clean .= substr($db_string, $old_pos, $pos - $old_pos);
while (true) {
$pos1 = strpos($db_string, '\'', $pos + 1);
$pos2 = strpos($db_string, '\\', $pos + 1);
if ($pos1 === false) {
break;
} elseif ($pos2 == false || $pos2 > $pos1) {
$pos = $pos1;
break;
}
$pos = $pos2 + 1;
}
$clean .= ' %s ';
$old_pos = $pos + 1;
}
$clean .= substr($db_string, $old_pos);
$clean = trim(strtolower(preg_replace($allowed_comments_from, $allowed_comments_to, $clean)));
// We don't use UNION in SMF, at least so far. But it's useful for injections.
if (strpos($clean, 'union') !== false && preg_match('~(^|[^a-z])union($|[^[a-z])~s', $clean) != 0) {
$fail = true;
} elseif (strpos($clean, '/*') > 2 || strpos($clean, '--') !== false || strpos($clean, ';') !== false) {
$fail = true;
} elseif (strpos($clean, 'sleep') !== false && preg_match('~(^|[^a-z])sleep($|[^[_a-z])~s', $clean) != 0) {
$fail = true;
} elseif (strpos($clean, 'benchmark') !== false && preg_match('~(^|[^a-z])benchmark($|[^[a-z])~s', $clean) != 0) {
$fail = true;
} elseif (preg_match('~\\([^)]*?select~s', $clean) != 0) {
$fail = true;
}
if (!empty($fail) && function_exists('log_error')) {
smf_db_error_backtrace('Hacking attempt...', 'Hacking attempt...' . "\n" . $db_string, E_USER_ERROR, __FILE__, __LINE__);
}
}
if (empty($db_unbuffered)) {
$ret = @mysql_query($db_string, $connection);
} else {
$ret = @mysql_unbuffered_query($db_string, $connection);
}
if ($ret === false && empty($db_values['db_error_skip'])) {
$ret = smf_db_error($db_string, $connection);
}
// Debugging.
if (isset($db_show_debug) && $db_show_debug === true) {
$db_cache[$db_count]['t'] = array_sum(explode(' ', microtime())) - array_sum(explode(' ', $st));
}
return $ret;
}
/**
* Do a query. Takes care of errors too.
* Special queries may need additional replacements to be appropriate
* for PostgreSQL.
*/
function smf_db_query($identifier, $db_string, $db_values = array(), $connection = null)
{
global $db_cache, $db_count, $db_connection, $db_show_debug, $time_start;
global $db_unbuffered, $db_callback, $db_last_result, $db_replace_result, $modSettings;
// Decide which connection to use.
$connection = $connection === null ? $db_connection : $connection;
// Special queries that need processing.
$replacements = array('alter_table_boards' => array('~(.+)~' => ''), 'alter_table_icons' => array('~(.+)~' => ''), 'alter_table_smileys' => array('~(.+)~' => ''), 'alter_table_spiders' => array('~(.+)~' => ''), 'ban_suggest_error_ips' => array('~RLIKE~' => '~', '~\\.~' => '\\.'), 'ban_suggest_message_ips' => array('~RLIKE~' => '~', '~\\.~' => '\\.'), 'consolidate_spider_stats' => array('~MONTH\\(log_time\\), DAYOFMONTH\\(log_time\\)~' => 'MONTH(CAST(CAST(log_time AS abstime) AS timestamp)), DAYOFMONTH(CAST(CAST(log_time AS abstime) AS timestamp))'), 'delete_subscription' => array('~LIMIT 1~' => ''), 'display_get_post_poster' => array('~GROUP BY id_msg\\s+HAVING~' => 'AND'), 'attach_download_increase' => array('~LOW_PRIORITY~' => ''), 'boardindex_fetch_boards' => array('~IFNULL\\(lb.id_msg, 0\\) >= b.id_msg_updated~' => 'CASE WHEN IFNULL(lb.id_msg, 0) >= b.id_msg_updated THEN 1 ELSE 0 END', '~(.)$~' => '$1 ORDER BY b.board_order'), 'get_random_number' => array('~RAND~' => 'RANDOM'), 'insert_log_search_topics' => array('~NOT RLIKE~' => '!~'), 'insert_log_search_results_no_index' => array('~NOT RLIKE~' => '!~'), 'insert_log_search_results_subject' => array('~NOT RLIKE~' => '!~'), 'messageindex_fetch_boards' => array('~(.)$~' => '$1 ORDER BY b.board_order'), 'select_message_icons' => array('~(.)$~' => '$1 ORDER BY icon_order'), 'set_character_set' => array('~SET\\s+NAMES\\s([a-zA-Z0-9\\-_]+)~' => 'SET NAMES \'$1\''), 'pm_conversation_list' => array('~ORDER\\s+BY\\s+\\{raw:sort\\}~' => 'ORDER BY ' . (isset($db_values['sort']) ? $db_values['sort'] === 'pm.id_pm' ? 'MAX(pm.id_pm)' : $db_values['sort'] : '')), 'top_topic_starters' => array('~ORDER BY FIND_IN_SET\\(id_member,(.+?)\\)~' => 'ORDER BY STRPOS(\',\' || $1 || \',\', \',\' || id_member|| \',\')'), 'order_by_board_order' => array('~(.)$~' => '$1 ORDER BY b.board_order'), 'spider_check' => array('~(.)$~' => '$1 ORDER BY LENGTH(user_agent) DESC'), 'unread_replies' => array('~SELECT\\s+DISTINCT\\s+t.id_topic~' => 'SELECT t.id_topic, {raw:sort}'), 'profile_board_stats' => array('~COUNT\\(\\*\\) \\/ MAX\\(b.num_posts\\)~' => 'CAST(COUNT(*) AS DECIMAL) / CAST(b.num_posts AS DECIMAL)'), 'set_smiley_order' => array('~(.+)~' => ''));
if (isset($replacements[$identifier])) {
$db_string = preg_replace(array_keys($replacements[$identifier]), array_values($replacements[$identifier]), $db_string);
}
// Limits need to be a little different.
$db_string = preg_replace('~\\sLIMIT\\s(\\d+|{int:.+}),\\s*(\\d+|{int:.+})\\s*$~i', 'LIMIT $2 OFFSET $1', $db_string);
if (trim($db_string) == '') {
return false;
}
// Comments that are allowed in a query are preg_removed.
static $allowed_comments_from = array('~\\s+~s', '~/\\*!40001 SQL_NO_CACHE \\*/~', '~/\\*!40000 USE INDEX \\([A-Za-z\\_]+?\\) \\*/~', '~/\\*!40100 ON DUPLICATE KEY UPDATE id_msg = \\d+ \\*/~');
static $allowed_comments_to = array(' ', '', '', '');
// One more query....
$db_count = !isset($db_count) ? 1 : $db_count + 1;
$db_replace_result = 0;
if (empty($modSettings['disableQueryCheck']) && strpos($db_string, '\'') !== false && empty($db_values['security_override'])) {
smf_db_error_backtrace('Hacking attempt...', 'Illegal character (\') used in query...', true, __FILE__, __LINE__);
}
if (empty($db_values['security_override']) && (!empty($db_values) || strpos($db_string, '{db_prefix}') !== false)) {
// Pass some values to the global space for use in the callback function.
$db_callback = array($db_values, $connection);
// Inject the values passed to this function.
$db_string = preg_replace_callback('~{([a-z_]+)(?::([a-zA-Z0-9_-]+))?}~', 'smf_db_replacement__callback', $db_string);
// This shouldn't be residing in global space any longer.
$db_callback = array();
}
// Debugging.
if (isset($db_show_debug) && $db_show_debug === true) {
// Get the file and line number this function was called.
list($file, $line) = smf_db_error_backtrace('', '', 'return', __FILE__, __LINE__);
// Initialize $db_cache if not already initialized.
if (!isset($db_cache)) {
$db_cache = array();
}
if (!empty($_SESSION['debug_redirect'])) {
$db_cache = array_merge($_SESSION['debug_redirect'], $db_cache);
$db_count = count($db_cache) + 1;
$_SESSION['debug_redirect'] = array();
}
$st = microtime();
// Don't overload it.
$db_cache[$db_count]['q'] = $db_count < 50 ? $db_string : '...';
$db_cache[$db_count]['f'] = $file;
$db_cache[$db_count]['l'] = $line;
$db_cache[$db_count]['s'] = array_sum(explode(' ', $st)) - array_sum(explode(' ', $time_start));
}
// First, we clean strings out of the query, reduce whitespace, lowercase, and trim - so we can check it over.
if (empty($modSettings['disableQueryCheck'])) {
$clean = '';
$old_pos = 0;
$pos = -1;
while (true) {
$pos = strpos($db_string, '\'', $pos + 1);
if ($pos === false) {
break;
}
$clean .= substr($db_string, $old_pos, $pos - $old_pos);
while (true) {
$pos1 = strpos($db_string, '\'', $pos + 1);
$pos2 = strpos($db_string, '\\', $pos + 1);
if ($pos1 === false) {
break;
} elseif ($pos2 == false || $pos2 > $pos1) {
$pos = $pos1;
break;
}
$pos = $pos2 + 1;
}
$clean .= ' %s ';
$old_pos = $pos + 1;
}
$clean .= substr($db_string, $old_pos);
$clean = trim(strtolower(preg_replace($allowed_comments_from, $allowed_comments_to, $clean)));
// We don't use UNION in SMF, at least so far. But it's useful for injections.
if (strpos($clean, 'union') !== false && preg_match('~(^|[^a-z])union($|[^[a-z])~s', $clean) != 0) {
$fail = true;
} elseif (strpos($clean, '/*') > 2 || strpos($clean, '--') !== false || strpos($clean, ';') !== false) {
$fail = true;
} elseif (strpos($clean, 'sleep') !== false && preg_match('~(^|[^a-z])sleep($|[^[_a-z])~s', $clean) != 0) {
$fail = true;
} elseif (strpos($clean, 'benchmark') !== false && preg_match('~(^|[^a-z])benchmark($|[^[a-z])~s', $clean) != 0) {
$fail = true;
} elseif (preg_match('~\\([^)]*?select~s', $clean) != 0) {
$fail = true;
}
if (!empty($fail) && function_exists('log_error')) {
smf_db_error_backtrace('Hacking attempt...', 'Hacking attempt...' . "\n" . $db_string, E_USER_ERROR, __FILE__, __LINE__);
}
}
$db_last_result = @pg_query($connection, $db_string);
if ($db_last_result === false && empty($db_values['db_error_skip'])) {
$db_last_result = smf_db_error($db_string, $connection);
}
// Debugging.
if (isset($db_show_debug) && $db_show_debug === true) {
$db_cache[$db_count]['t'] = array_sum(explode(' ', microtime())) - array_sum(explode(' ', $st));
}
return $db_last_result;
}
