Parsing Placeholders as SQL Arguments in PHP

Question

I am trying to find a good way of parsing SQL arguments, the way I am accomplishing it now seems like it can use a lot of improvement. I am trying to convert arguments that are split apart by ', & <= =>' from a string into SQL arguments like name = 'ABC' OR name ='John Doe'.

So

parseSQLOperators('dan,john&steven', 'username');

Will create

WHERE username= 'dan' OR username = 'john' AND username ='steven'

I have an example how this is being accomplished below but I am wondering if there is a better way for this. I know this code is ugly and I've been trying things like preg_splits but to no avail.

public static function parseSQLOperators($string, $content_term, $encapsulate = TRUE) {

        $string = trim($string);
        $length = strlen($string);

        $ADD_PREFIX = true;
        $output = '';
        for ($i = 0; $i < $length; $i++) {

            if ($string[$i] == '!') {

                $output .= ' ' . $content_term . '!=\'';

                if ($i == 0) {
                    $ADD_PREFIX = false;
                }
            } else if ($string[$i] == '+') {
                if (@$string[$i + 1] != '!') {
                    $output .= ' AND ' . $content_term . '=\'';
                } else {
                    $output .= ' AND ';
                }
            } else if ($string[$i] == ',') {
                if (@$string[$i + 1] != '!') {
                    $output .= ' OR ' . $content_term . '=\'';

                } else {
                    $output .= ' OR ';
                }

            }

            if ($string[$i] != '!' && $string[$i] != '+' && $string[$i] != ',') {

                $output .= $string[$i];

                if (@$string[$i + 1] == ',' || @$string[$i + 1] == '+' || @$string[$i + 1] == '!' || $i == $length || $i == $length - 1) {
                    $output .= '\'';
                }
            }
        }//end for

        if ($ADD_PREFIX == true) {
            $output = $content_term . '=\'' . $output;
        }

        if ($encapsulate) {
            $output = '(' . $output . ')';
        }

        return $output;
    }//end parseSQLOperator

Answer 1

If this is a whole SQL class of sorts, then I think you can improve your design a bit. What if you want people who aren't archived and names match certain things? 'dan,john&steven', 'username' doesn't really work. You need something more like:

$sql
    ->select(...)
    ->from(...)
    ->where('=dan|=john&=steven', 'username')
    ->andWhere('>0', 'archived')
    ->execute();

Notice that I didn't use your grammar. I'd recommend the following shorthand grammar notation:

//& means AND
//| means OR
//~ means LIKE

//operator means &, |
//comparator means things like !=, =, <>, <, >, <=, >=, ~
//identifier means things like dan, john, or jo%

//The pseudo regular expession:
//{comparator} {identifier} ({operator} {comparator} {identifier})*

Doing that, you can turn: '=john | !=jamison & =james | ~%jo_n' into name = 'john' OR name != 'jamison' AND name = 'james' OR name LIKE '%jo_n' quite easily:

    function parse($string, $name) {
            //add in quote marks
            $string = preg_replace('/[A-Za-z0-9%_-]+/', "'$0'", $string);

            //add in name:
            $string = preg_replace('/\=|\!\=|<>|<|<=|>|>=|~/', "$name $0 ", $string);

            //replace ~ with LIKE
            $string = str_replace('~', 'LIKE', $string);

            //expand '&' and ',' to ' AND '
            $string = preg_replace('/\&|\,/', ' AND ', $string);

            //expand '|' to ' OR '
            $string = str_replace('|', ' OR ', $string);

            return $string;

    }

Also note that you should be able to escape things properly. We don't want SQL injection going on 'round here! I'd recommend a syntax like:

where('=? | =? & =?', array('dan', 'john', 'steven'), 'username')

The function I provided could easily be modified to do that. You simply use the mysqli or PDO engine to do the replacing. You just have to store the arguments in your structure so you can do it proper.

Answer 2

Are you sure that you need this custom format (and parsing) at all?

Using some helper functions would be easier to handle, for example:

$sql->select(...)
    ->from(...)
    ->where(
        or(
            eq('dan', 'username'), 
            and(eq('john', 'username'), 
                eq('steven', 'username')
               )
           )
    )
    ->execute();

Some proof of concept implementations:

function eq($value, $col) {
    return " ('$value' = $col) ";
}

function or($arg1, $arg2) {
    return " ($arg1 OR $arg2) ";
}

function and($arg1, $arg2) {
    return " ($arg1 AND $arg2) ";
}

Maybe a framework with similar functionality already exists.

Answer 3

Consider this readable solution which enables you to use prepared statements easily:

$condition = array('username = :u and password =:p', array(':u' => $user, ':p' => $pass));
$result = $database->fetch_row('users', $condition);