SQL, hacking attempt

[dodos26]

It works without problems when my own mysql connection but I try to do it using smc I get "hacking attempt":

Code Select Expand

$sql = "SELECT
am.id_msg,
am.avg_value,
am.num_ratings,
am.id_topic,
am.num_views,
(am.avg_value - (am.avg_value - 2.5) * POWER(2, -LOG10(am.num_ratings + 1))) AS steam_db_rate,
MAX(am.date) AS last_rating_date,
am.topic_cre_date AS topic_creation_date,
am.attach_tor_ids,
am.attach_img_ids,
am.subject,
am.mem_real_name,
am.id_parent,
am.id_member,
am.id_board,
am.id_parent,
am.board_name,
am.is_ratings,
am.num_replies,
am.is_read,
am.new_from
FROM
(SELECT
m.id_msg,
m.subject,
AVG(DISTINCT mr.value) AS avg_value,
COUNT(DISTINCT mr.value) AS num_ratings,
m.poster_time AS topic_cre_date,
t.id_topic,
t.num_views,
mr.date,
GROUP_CONCAT(DISTINCT at.id_attach) AS attach_tor_ids,
GROUP_CONCAT(DISTINCT ai.id_attach) AS attach_img_ids,
mem.real_name as mem_real_name,

m.id_member, b.id_board, b.id_parent, b.name AS board_name, t.is_ratings, t.num_replies,
" . ($user_info['is_guest'] ? "1 AS is_read, 0 AS new_from" : "
IFNULL(lt.id_msg, IFNULL(lmr.id_msg, 0)) >= m.id_msg_modified AS is_read,
IFNULL(lt.id_msg, IFNULL(lmr.id_msg, -1)) + 1 AS new_from") . "
FROM
`jiv_messages` m
LEFT JOIN
`jiv_topics` t ON t.id_first_msg = m.id_msg
LEFT JOIN
jiv_boards AS b ON b.id_board = t.id_board
LEFT JOIN
`jiv_message_ratings` mr ON mr.id_msg = t.id_topic
LEFT JOIN
`jiv_attachments` at ON (m.id_msg = at.id_msg AND at.approved = 1 AND at.attachment_type = 0 AND at.fileext = 'torrent')
LEFT JOIN
`jiv_attachments` ai ON (m.id_msg = ai.id_msg AND ai.approved = 1 AND ai.attachment_type = 0 AND ai.fileext IN ('jpg', 'gif', 'jpeg', 'x-ms-bmp', 'png', 'psd', 'tiff', 'iff'))
LEFT JOIN
`jiv_members` AS mem ON (mem.id_member = m.id_member)";
if (!$user_info['is_guest']) {
$sql .= "
LEFT JOIN jiv_log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = ".$user_info['id'].")
LEFT JOIN jiv_log_mark_read AS lmr ON (lmr.id_board = b.id_board AND lmr.id_member = ".$user_info['id'].")";
}

$sql .= "WHERE". ($modSettings['postmod_active'] ? " t.approved = 1 AND " : '') ."
b.id_cat = 2
AND t.locked != 1
AND at.fileext = 'torrent'";
if (!empty($conditions)) {
$sql .= " AND " . implode(" AND ", $conditions);
}

$sql .= " GROUP BY t.id_first_msg LIMIT ". $limit .") am
GROUP BY am.id_msg
ORDER BY $sorting $ord_by, RAND()";

[dodos26]

How about this solution?

Code (Yes, Yes I frist clean requests) Select Expand

function generateSearchQuery($inputString, $colum_name) {
$cleanedString = preg_replace('/[^\w\s]/u', ' ', $inputString);
$words = preg_split('/\s+/', $cleanedString, -1, PREG_SPLIT_NO_EMPTY);

global $smcFunc;

$real_search_words = array();
foreach ($words as $word)
$real_search_words[] = $smcFunc['db_quote'](
'{string:search_word}',
array(
'search_word' => '%'. $word .'%'
)
);
$query = $colum_name . ' LIKE ' . implode(' AND ' . $colum_name . ' LIKE ', $real_search_words);

if (!empty($query))
return $query;
else
return '';
}

Code ("Example sql code:") Select Expand

        $request = $smcFunc['db_query']('','
            SELECT
                am.id_msg
            FROM
                (SELECT
                    m.id_msg
                FROM
                    {db_prefix}messages m
                LIMIT 1
                ) am
            ',
            array('random_bypas_4718' => true)
        );

        $smcFunc['db_free_result']($request);

Code ("Found: (Subs/Subs-Db-mysql)") Select Expand

class public function query($identifier, $db_string, $db_values = array(), $connection = null)


Code ("Found inside: (Subs/Subs-Db-mysql)") Select Expand

elseif (preg_match('~\([^)]*?select~s', $clean) != 0)


Code ("Replace to: (Subs/Subs-Db-mysql)") Select Expand

elseif (isset($db_values['random_bypas_4718']) && $db_values['random_bypas_4718'] !== true) if (preg_match('~\([^)]*?select~s', $clean) != 0)


[vbgamer45]

Try setting
$modSettings['disableQueryCheck'] = 1;

Before running the query.

[live627]

To expand on the above suggestion, SMF 2.0.x does not allow subqueries, which used to matter in the past. The suggested workaround disables that check.

[dodos26]

Yes, I change my sql request and now work. If anyone needed this, this workaround also worked.

I wonder if my new query is as optimized as possible. Are they willing to look through them?