Filtering SQL query with array

Question

I am currently working on a page, where I need to send a query to a MySQL database based on data chosen by the user.

OK, to be more specific. I have a webpage, where the user can select countries. Using javascript I get the countries selected as a string. The countries are abbreviated by their 2-letter code, so I get for example a string "US,GB,AU", which would indicate the user selected US, UK and Australia.

Now, I send this to a PHP page, which gets this using the GET parameter.

So now I have this PHP page, which first obtains the string and then, using the explode function I split it into an array based on the commas, so I have an array containing 3 elements, in this case, US, UK and AU.

And now that part that I don't know how to do. Apart from the country,the user can also select population. This however is simple, because it is always just one number.

What I do then is I want to send a query to the MySQL database, which contains many cities and choose some based on the user selected population and country.

So I have the SQL query and then the crucial bit is the WHERE statement, where I have:

WHERE Population>$population AND Country=

now this is where I don't know how to continue. What I want is to get all the cities from the selected countries. Each city is a row in the database, which contains its name, population and the country code.

If it was just one country it would be simple, it would be the same as for the population, just Country='$country', but how do I make it so that it chooses all the cities which correspond to any of the user selected values, stored in the array.

Thanks

You'd simplify this a bit if you were passing the country list through as an array — Mark Baker
– Mark Baker, Commented Aug 14, 2013 at 22:31

O. Jones · Accepted Answer · 2013-08-14 23:07:24Z

4

You want the SQL construct

 WHERE Population>$population AND Country IN ('US','GB','AU')

This one is hard to do with prepared statements from PHP. So you need to be supercareful to avoid SQL injection hazards.

Edit

If you have a php string called $country that looks like "US,UK,AU,DE" you can use php to format it like this.

    $country = "US,UK,AU,DE";
    $list = explode(',',$country);
    $instring = "('" . implode("','", $list) . "')";

Then $instring looks like this: "('US','UK','AU','DE')"

You can make it safe against SQL injection like this:

        $country = 'US,UK,AU,DE';
        $list = array();
        foreach (explode(',',$country) as $item) {
            if (preg_match("/^[A-Z][A-Z]$/", $item) ) $list[] = $item;
        }
        $instring = "('" . implode("','", $list) . "')";

This bursts the list of countries, checks that each country code is two upper-case ASCII letters, and then combines the list into an appropriately quote / comma delimited and parentheses wrapped expression.

It would prevent a country list something like this "US,;DROP TABLE whatever;" from hosing your system. Believe me, there are badguys out there who just want to do this kind of thing to you.

You could use the MySQL FIND_IN_SET function, but this would defeat any index you might have on your Country column.

edited Aug 14, 2013 at 23:07

answered Aug 14, 2013 at 22:31

O. Jones

110k17 gold badges134 silver badges187 bronze badges

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

3 Comments

Jachym Over a year ago

I see, but how would I do this if I have this saved as a string lets say $countries. Do I just put IN ('$countries')?

cmorrissey Over a year ago

I would use FIND_IN_SET much easier and you don't need to exploded the GET string

Mike Brant Over a year ago

@ChristopherMorrissey That is not what FIND_IN_SET() does. that function will return an index position of a substring value within a set defined within a string.

kaan_atakan · Accepted Answer · 2013-08-14 22:56:42Z

0

You can use OR:

WHERE POPULATION>$population AND (COUNTRY="US" OR COUNTRY="AU" OR COUNTRY="UK")

Do you need help iterating through the array and building the query? Also it would be wise to look into prepared statements or proper escaping if you haven't already. Consider what would happen if some one sent

0; INSERT INTO USERS (USERNAME, PASSWORD) VALUES ("HACKER", "PWNED"); SELECT * FROM CITIES WHERE 1

inside $ population. Depending on your php it might not be hard to do.

edited Aug 14, 2013 at 22:56

answered Aug 14, 2013 at 22:48

kaan_atakan

4,1071 gold badge37 silver badges66 bronze badges

Collectives™ on Stack Overflow

Filtering SQL query with array

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related