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

Question

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%'' "

mickmackusa · Accepted Answer · 2021-03-10T01:33:37.643

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

Thank you, you saved me. Right your statement about using value in sql string, I have to modify the rest of the query because I don't know how many parameter user will set, building and appending the value lightens me some of the work because I don't have to store and loop conditions and params because not all the chunk exported has the same structure. It's a server side pagination with mssql instead of mysql as the most uses. — rivaldid, Mar 10 '21 at 14:03
This is my original code: http://pastie.org/p/2vEzxRSAdvB16CxJfz6H5Y — rivaldid, Mar 10 '21 at 14:05
Based on my nopaste, do you have any suggestion to prevent input string from crash my query? Because sometimes happens, when user send me singlequote — rivaldid, Mar 10 '21 at 14:16
If your query ever crashes on single quotes, then you are not using prepared statements properly. If you implement my advice correctly, nothing should break. One disadvantage of your input syntax is that if someone wants to search for a value containing one of four symbols, then it will potentially break the intended sql logic. — mickmackusa, Mar 10 '21 at 14:32

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

1 Answers1