Issue with searching for special characters % and _

[jeffersonsalvador]

Description:

Currently, when performing searches using the LIKE or ILIKE operator in MySQL, we are not correctly handling the special characters % and _, which are used as wildcards. These characters are interpreted by MySQL as wildcards, leading to unexpected results during searches.

Problem:
When the search value contains % or _, MySQL interprets these characters as wildcards, which can result in more records being returned than expected. This happens even when the user intends to search for the literal % or _ characters.

Example:

• If a user searches for "20%", MySQL might interpret this as "any value starting with 20", leading to unexpected results.
• The same issue occurs with the _, which represents any single character. If the user searches for "name_1" it also will retrieve "name01" or similar.

Proposed Solution:

To resolve this issue, we can use PHP's addcslashes() function to escape the % and _ characters before sending the query to MySQL. The addcslashes() function will add a backslash (\) before these special characters, ensuring that MySQL treats them as literals instead of wildcards.

Suggested Code Changes:

• Before applying the search value to the LIKE or ILIKE operator, we should use addcslashes() to escape the % and _ characters.

if (isset($searchData[$field])) {
    $searchData[$field] = addcslashes($searchData[$field], '%_');
    $value = ($condition == "like" || $condition == "ilike") ? "%{$searchData[$field]}%" : $searchData[$field];
} else {
    if (!is_null($search) && !in_array($condition, ['in', 'between'])) {
        $search = addcslashes($search, '%_');
        $value = ($condition == "like" || $condition == "ilike") ? "%{$search}%" : $search;
    }
}

This ensures that % and _ are treated as literal characters rather than wildcards, providing more accurate search results.