Bored?  Looking to kill some time?  Want to chat with other SMF users?  Join us in IRC chat or Discord

Main Menu

SMF 2.0.x database error when searching posts by username " "

Started by Dav999, December 19, 2020, 01:38:48 PM

Previous topic - Next topic


I just found a few "scary" errors in my log made by a guest on action=search2:

Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3

SELECT id_member
FROM smf_members
WHERE real_name LIKE

(I have "Include database query in the error log" turned on)

I managed to reproduce this by going to Search, and for "By user", filling in one or more space characters (if you leave the field completely blank, it gets filled with * again). What also works is surrounding space characters in double quotes.

I'm using SMF 2.0.17 with MariaDB 10.3.25 on PHP 7.3, but it seems to not be MariaDB-specific or a recent regression, since I also tested it on SMF 2.0.7 with MySQL 7.3.24 on PHP 5.6. It also happens on this site, and it happens in fresh installations without mods.

The context of the error is Sources/Search.php line 445 (the last statement in the code block):

    // If there's no specific user, then don't mention it in the main query.
    if (empty($search_params['userspec']))
        $userQuery = '';
        $userString = strtr($smcFunc['htmlspecialchars']($search_params['userspec'], ENT_QUOTES), array('"' => '"'));
        $userString = strtr($userString, array('%' => '\%', '_' => '\_', '*' => '%', '?' => '_'));

        preg_match_all('~"([^"]+)"~', $userString, $matches);
        $possible_users = array_merge($matches[1], explode(',', preg_replace('~"[^"]+"~', '', $userString)));

        for ($k = 0, $n = count($possible_users); $k < $n; $k++)
            $possible_users[$k] = trim($possible_users[$k]);

            if (strlen($possible_users[$k]) == 0)

        // Create a list of database-escaped search names.
        $realNameMatches = array();
        foreach ($possible_users as $possible_user)
            $realNameMatches[] = $smcFunc['db_quote'](
                    'possible_user' => $possible_user

        // Retrieve a list of possible members.
        $request = $smcFunc['db_query']('', '
            SELECT id_member
            FROM {db_prefix}members
            WHERE {raw:match_possible_users}',
                'match_possible_users' => 'real_name LIKE ' . implode(' OR real_name LIKE ', $realNameMatches),


Confirmed for 2.0.  No promises whether there will be a patch or not in the 2.0.x line.

2.1 seems OK.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp


Just encountered this issue on one of my forums - SMF 2.0 fix for 'By User' search field that ONLY contains spaces OR single/double quoted spaces:

In ./Sources/Search.php
    if (empty($search_params['userspec']))Replace With:
    if (empty($search_params['userspec']) || ctype_space((str_replace(['"',"'"], "", $search_params['userspec']))))
Life doesn't have to be perfect to be wonderful ...