Dynamically created WHERE clause PHP/MySQL

Question

I am dynamically generating the WHERE portion of a MySQL query. My code below works perfectly so far

PLEASE NOTE: _GET strings are all validated elswhere in my code but in order to keep this code down to a reasonable length for this question I have put them in directly below. For anyone looking to do something similar to what I am doing and is using my code here as a base, please be sure to validate your strings to avoid mysql injections.

  /* Loop through each column in the table */

        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
             /* check if the column has been marked as searchable and that the param sent from the client contains data */
            if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
            {
                if ( $sWhere == "" )
                {
                    $sWhere = "WHERE ";
                }
                else
                {
                    $sWhere .= " AND ";
                }



/* RANGE FILTER CODE - This part is not important to this question but included for completenes */

                $columnFilterValue = mysql_real_escape_string($_GET['sSearch_' . $i]);
                // check for values range
                $rangeSeparator = "~";
                if (!empty($rangeSeparator) && strstr($columnFilterValue, $rangeSeparator)) {
                    // get min and max

                     $columnFilterRangeMatches =  explode('~', $columnFilterValue);

                    // get filter
                    if (empty($columnFilterRangeMatches[0]) && empty($columnFilterRangeMatches[1]))
                        $sWhere .= " 0 = 0 ";
                    else if (!empty($columnFilterRangeMatches[0]) && !empty($columnFilterRangeMatches[1]))
                        $sWhere .= $aColumns[$i] . " BETWEEN '" . $columnFilterRangeMatches[0] . "' and '" . $columnFilterRangeMatches[1] . "' ";
                    else if (empty($columnFilterRangeMatches[0]) && !empty($columnFilterRangeMatches[1]))
                        $sWhere .= $aColumns[$i] . " < '" . $columnFilterRangeMatches[1] . "' ";
                    else if (!empty($columnFilterRangeMatches[0]) && empty($columnFilterRangeMatches[1]))
                        $sWhere .= $aColumns[$i] . " > '" . $columnFilterRangeMatches[0] . "' ";
                } else {



    /* Begin building WHERE clause */


        $sWhere = "WHERE (";

                $aORs = array();

                    for ( $i=0 ; $i<count($aColumns) ; $i++ )
                            {
                                if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
                                        {
                                                    $value = $_GET['sSearch_'.$i];

                                                        array_push($aORs, $aColumns[$i]." IN ($value)");


                                        }
                            }
                $sWhere .= implode(" OR ",$aORs);   

        $sWhere .= ')';
                }
            }
        }

Now what this code does it it takes strings of comma seperated values sent from the client and builds the WHERE clase based on those.

$aColumns is an array containing the columns in the table

EXAMPLE:-

If the parameters...

sSearch_1 contains a value of 1,3,5,6
sSearch_2 contains a value of 1,2,3
sSearch_4 cotains a value of 4,5,6
sSearch 6 cntains a value of 7,8,9

Then the following WHERE clause would be generated by this code:

 WHERE genre_id IN (1,3,5,6) OR instruments IN (1,2,3) OR emotions IN (4,5,6) OR ratings IN (7,8,9)

This works fine however I want to make the OR's or AND's dynamic also by sending another string containing a list of OR's and AND's in the correct sequential order.

so for instance if $_GET['filtertype'] = a string like this:-

OR,OR,AND

then instead of the above it should return:

WHERE genre_id IN (1,3,5,6) OR instruments IN (1,2,3) OR emotions IN (4,5,6) OR ratings IN (7,8,9)

As you can see in my code above I am currently injecting the OR's in to my array via the implode function. (The relevant part of code is repeated below)

  $sWhere = "WHERE (";

                $aORs = array();

                    for ( $i=0 ; $i<count($aColumns) ; $i++ )
                            {
                                if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
                                        {
                                                    $value = $_GET['sSearch_'.$i];

                                                        array_push($aORs, $aColumns[$i]." IN ($value)");


                                        }
                            }
                $sWhere .= implode(" OR ",$aORs); 

        $sWhere .= ')';

How can I modify this to sequrntially add the correct AND or OR based on the correct loop?

Why reinvent the wheel? There are plenty of ORM libraries that will do this... — user684934
– user684934, Commented Jan 28, 2012 at 10:19
As silly as it sounds I wasn't even aware they existed! I am relatively new to MySQL and to be honest only need a slight modification to my personal code anywy. If you could suggest something which would do the job better than my own solution that would be great. :-) — gordyr
– gordyr, Commented Jan 28, 2012 at 10:20
i see potential mysql injections! try validating the values you get from $_GET! use something like if (preg_match('/^[0-9,]+$/s', $subject)) { } to check — Spidfire
– Spidfire, Commented Jan 28, 2012 at 10:22
@Spidfire - Thanks I should have been clearer, they are all validated elsewhere and stored as variables.... I have just shortened it for post here so I put the _GET's in directly. Thanks for mentioning it though :-) — gordyr
– gordyr, Commented Jan 28, 2012 at 10:26
Code like that populated by client data is just asking for SQL injection attacks. You really need to check every single value you add to the string. I suspect a CSV line similar to ); DELETE * FROM table; SELECT ( would really ruin your day. EDIT: If you validate your data elsewhere that's great but you really shouldn't post it in its current form as others may copy your code without realising the risk. — SpliFF
– SpliFF, Commented Jan 28, 2012 at 10:28

Arjan · Accepted Answer · 2012-01-28 11:33:55Z

3

Instead of creating a where string, I would first create an array with where parts.

$whereParts = array();
foreach($aColumns as $i => $column)
{
    <logic goes here>
    $whereParts[] = 'genre_id IN (1,3,5,6)'; // sample :)
}

$where = 'WHERE ' . implode(' OR ', $whereParts); // note the spaces around OR

Then it's easy to replace ' OR ' with ' AND '

It's easy to allow users to select between AND and OR for all where parts, but not if you want to do that for each individual item. That's also a logic issue. When the user specifies a OR b AND c, would he want (a OR b) AND c or a OR (b AND c)?

edited Jan 28, 2012 at 11:33

answered Jan 28, 2012 at 10:48

Arjan

9,8941 gold badge33 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gordyr Over a year ago

Good idea, ill probably adopt the approac you suggested however, your assumption that I wanted them to be able to do it for all parts is correct. And you're correct I hadnt thought the logic through. The string would have to denote which parts are OR or AND Correctly

Collectives™ on Stack Overflow

Dynamically created WHERE clause PHP/MySQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related