Parse symbol-string pairs to build conditional expressions for sql's WHERE clause

I need return pairs of symbol+string. My application has symbols with special meanings. There for four symbols currently in use: &, !, %, @ and they can be repeated in a sequence of pairs.

Strings sample:

%foo!bar
@baz
!bam@bat@bar
%bee@baa

I am trying to translate these symbol+string sequences into into WHERE condition expressions for my sql.

Here a demo of my code: http://sandbox.onlinephpfunctions.com/code/e8d723fea139f3e8b683bcb6831a094a64a0ca53

I was trying preg_split() which does something close to what I need, but I'm encoutering several problems with this technique. Also the subsequent for() loop and array_map() don't work per my needs.

After this try, my code is commented out, the foreach() that divides the first char that will be mapped as operator and the following non-symbol characters that will be the string.

In case the above link doesn't work, this is my code:

$individual_search = "";
$in = "%ciaooooooooo@noooooooo!sii";
$field = "provola";

if (strlen($in)>0) {
    switch($in[0]) {
        case "&":
        case "!":
        case "%":
        case "@":
            $ins = preg_split('/(&|!|%|@)/', $in, -1, PREG_SPLIT_DELIM_CAPTURE | PREG_SPLIT_NO_EMPTY);
            //preg_match('/(&)|(!)|(%)|(@)/', $in, $ins);
            //var_dump($ins);
            /*for ($i=0; $i<6; $i=$i+2) {
                  echo $ins($i);
                  echo $ins($i+1);
              }*/
                        
            array_map(function ($x) {
                $i=0;
                echo $x[$i], $x[$i+1];
                $i=$i+2;
            }, $ins);
                     
            /*
                //var_dump($ins);
                foreach ($ins as $inss) {
                    $val = (substr($inss, 1));
                    switch($inss[0]) {
                        case "&":
                            $individual_search .= "AND {$field} = ''{$val}'' ";
                            break;
                        case "!":
                            $individual_search .= "AND ({$field} IS NULL OR {$field} != ''{$val}'') ";
                            break;
                        case "%":
                            $individual_search .= "AND {$field} LIKE ''{$val}%'' ";
                            break;
                        case "@":
                            $individual_search .= "AND {$field} NOT LIKE ''{$val}%'' ";
                            break;
                    } // end switch
                }; // end foreach
            */
            break;
    } // end switch
} // end if
// echo $individual_search;

Desired Output for each string:

%foo!bar      => "AND provola LIKE ''foo%'' AND (provola IS NULL OR provola != ''bar'') "
@baz          => "AND provola NOT LIKE ''baz%'' "
!bam@bat@bar  => "AND (provola IS NULL OR provola != ''bam'') AND provola NOT LIKE ''bat%'' AND provola NOT LIKE ''bar%'' "
%bee@baa      => "AND provola LIKE ''bee%'' AND provola NOT LIKE ''baa%'' "

Solution

You must not directly inject values into your query. You need to set up your script to use prepared statements with placeholders and bound parameters.

Match the two parts of the substrings and populate the necessary arrays.

Code: (Demo)

$strings = [
    '%foo!bar',
    '@baz',
    '!bam@bat@bar',
    '%bee@baa',
];
$field = 'your_column';
$conditionTemplates = [
    '&' => '%1$s = ?',
    '!' => '(%1$s IS NULL OR %1$s != ?)',
    '%' => '%1$s LIKE ?',
    '@' => '%1$s NOT LIKE ?',
];
$needsTrailingWildcard = ['%', '@'];

foreach ($strings as $string) {
    $conditions = [];  // reset for demo
    $params = [];  // reset for demo
    preg_match_all('~([&%@!])([^&%@!]+)~', $string, $matches, PREG_SET_ORDER);
    foreach ($matches as $match) {
        $conditions[] = sprintf($conditionTemplates[$match[1]], $field);
        $params[] = $match[2] . (in_array($match[1], $needsTrailingWildcard) ? '%' : '');
    }
    var_export([implode(' AND ', $conditions), $params]);
    echo "\n";
}

Output:

array (
  0 => 'your_column LIKE ? AND (your_column IS NULL OR your_column != ?)',
  1 => 
  array (
    0 => 'foo%',
    1 => 'bar',
  ),
)
array (
  0 => 'your_column NOT LIKE ?',
  1 => 
  array (
    0 => 'baz%',
  ),
)
array (
  0 => '(your_column IS NULL OR your_column != ?) AND your_column NOT LIKE ? AND your_column NOT LIKE ?',
  1 => 
  array (
    0 => 'bam',
    1 => 'bat%',
    2 => 'bar%',
  ),
)
array (
  0 => 'your_column LIKE ? AND your_column NOT LIKE ?',
  1 => 
  array (
    0 => 'bee%',
    1 => 'baa%',
  ),
)

Now that you have an AND delimited string of conditions and an array of parameters, you can follow advice similar to this: https://stackoverflow.com/a/51036322/2943403