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; }