3
\$\begingroup\$

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
\$\endgroup\$
2
  • \$\begingroup\$ Interesting idea. I'll take a look. Give me some time. \$\endgroup\$ Nov 20 '11 at 6:24
  • \$\begingroup\$ How would you feel about changing your grammar up? Namely: dan,john&steven becomes =dan | =john & =steven? This has LOTS of advantages. First, you are using normal meanings for things. , normally means and or union. I've never seen it mean or. Secondly, it has a very specific structure: {comparator}{identifier}({operator}{comparator}{identifier})* It's VERY easily parsed by a regular expression. \$\endgroup\$ Nov 20 '11 at 6:39
4
\$\begingroup\$

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.

\$\endgroup\$
1
  • \$\begingroup\$ Very nice solution \$\endgroup\$ Jan 17 '12 at 20:43
0
\$\begingroup\$

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.

\$\endgroup\$
0
\$\begingroup\$

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);
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.