How can I remove a word from a string?

I am carrying out a little experiment, appending the string JERRY to every sql keyword in the sql statement by checking from an array of SQL keywords which I specify. I want to remove the string JERRY from the search variable, so that if I typed a' UNION (SELECT 1, fname, username, password FROM users);-- in the search input field, the printed sql statement should look like this;

SELECTJERRY * FROMJERRY shopping WHEREJERRY title LIKEJERRY '%a' UNION (SELECT 1, fname, username, password FROM users);-- %'

The goal is that I don't want the SQL Keywords in the input search variable to have the string JERRY.

BUT right now, this is what I get;

SELECTJERRY * FROMJERRY shopping WHEREJERRY title LIKEJERRY '%a' UNIONJERRY (SELECT 1, fname, username, password FROMJERRY users);-- %'

How can i achieve this?

$search = $_GET['search'];
if (empty($search)) {
    echo "Please fill in the search bar";

    exit();
}

$keywords = ["SELECT", "FROM", "WHERE", "LIKE", "AND", "OR", "ON","UNION", "JOIN"];
$sql = "SELECT * FROM shopping WHERE title LIKE '%$search%'";
$splittedSql = explode(" ", $sql);

foreach ($splittedSql as $sl) {

    if (in_array($sl, $keywords)) {

        $newstatement = $sl . "JERRY" . ' ';
    } else {
        $newstatement = $sl . ' ';
    }
    echo $newstatement;
}

Solution

The issue is that you're checking your static query with a user supplied $search value included in it. Achieving your desired results would require a limit on the keyword replacements.

One approach would be to first check the user supplied $search value for the specified keywords, If it exists, alter your static query. Then you can apply the user supplied $search value after-the-fact, which can be accomplished easily using sprintf.

Instead of exploding the query, you can use preg_replace to apply the keyword values all at once, using a capture group () and replacement value of $1JERRY. You can use word boundaries \b on the pattern to avoid false positives on words like sAND, tON,lORe, etc.

Lastly using stripos to check the $search value as opposed to in_array() and the /i regex modifier, will allow the $keyword matching and replacements to be case-insensitive.

Approach 1: https://3v4l.org/ie2Mj

$search = 'a\' UNION (SELECT 1, fname, username, password FROM users);--';

$keywords = ["SELECT", "FROM", "WHERE", "LIKE", "AND", "OR", "ON","UNION", "JOIN"];

//sprintf requires textual percent signs to be escaped as %%
$query = 'SELECT * FROM shopping WHERE title LIKE \'%%%s%%\'';
foreach ($keywords as $w) {
    if (false !== stripos($search, $w)) {
        //found a keyword build the replacement capture groups.
        $patterns = '/\b(' . implode('|', $keywords) . ')\b/i';
        $query = preg_replace($patterns, '$1JERRY', $query);
        break;
    }
}
printf($query, $search);

An alternative to iterating over the $keywords would be to use preg_match to determine if the $search value contains a keyword value.

Approach 2: https://3v4l.org/iVbBc

$search = 'a\' UNION (SELECT 1, fname, username, password FROM users);--';
$keywords = ["SELECT", "FROM", "WHERE", "LIKE", "AND", "OR", "ON","UNION", "JOIN"];
$patterns = '/\b(' . implode('|', $keywords) . ')\b/i';
$query = 'SELECT * FROM shopping WHERE title LIKE \'%%%s%%\'';

if (preg_match($patterns, $search)) {
    $query = preg_replace($patterns, '$1JERRY', $query);
}
printf($query, $search);

Results for both approaches:

SELECTJERRY * FROMJERRY shopping WHEREJERRY title LIKEJERRY '%a' UNION (SELECT 1, fname, username, password FROM users);--%'