Pass comma separated values through php script into sql query

Question

I'm passing an argument to my php code using a web brower in the following format:

www.site.php?age=1&sex=2

Then in the code accessing those arguments:

'age' => $_GET['age']
'sex' => $_GET['sex']

One argument needs to be a set of comma separated values (ie, "1,2,3,4,5"). When I do the following, only the first value gets interpreted:

www.site.php?age=1&sex=2&nums=1,2,3,4,5

I've tried with parens, brackets, etc., which all break the code. Any thoughts? Thanks,

EDIT: I incorrectly assumed when the error occurred. The error occurs when trying to actually make the sql call:

This works:

$sql = <<<SQL
    SELECT * from db
    WHERE age=:age AND nums in (1,2)
SQL;
$query = $dbs['base']['connection']->prepare($sql);

$query->execute(array(
    ':age'      => $_GET['age'],

));

But it fails when I pass in the nums:

$sql = <<<SQL
    SELECT * from db
    WHERE age=:age AND nums in (:nums)
SQL;
$query->execute(array(
        ':age'      => $_GET['age'],
            ':nums'  => $_GET['nums']
    ));

When I just print $_GET['nums'], the comma separated values come in just fine. How can I properly pass the 'nums' argument to the sql query?

What is the value of `$_GET['nums']`? Just get it from that element and use `explode`? — slugonamission, Jan 26 '13 at 02:51
The value that I'm trying to get is "1,2,3,4,6", commas included — mike, Jan 26 '13 at 02:53
then just use `explode` to split it. Passing it straight to the DB is bad practise without escaping it first. — slugonamission, Jan 26 '13 at 02:54
The problem is that only the first value is coming through when I pass the comma separated values into the web browser. — mike, Jan 26 '13 at 02:56
Can't be right. Use `print_r($_GET);` or $_SERVER to debug. Probably your database query which is built wrong. — mario, Jan 26 '13 at 03:00
Yep, figured that out just before reading this -- how can I properly pass into the sql query? — mike, Jan 26 '13 at 03:06
see [PDO and SQL IN statements](http://stackoverflow.com/questions/2681281/pdo-and-sql-in-statements) — peterm, Jan 26 '13 at 04:32

Kerem · Answer 1 · 2013-01-26T02:59:32.240

1

Did you try urlencode?

// set
$nums = urlencode(join(',', array(1,2,3,4,5)));

// get
$nums = explode(',', urldecode($_GET['nums']));
// i think needs sanitize
$nums = array_map('intval', $nums);

edited Jan 26 '13 at 02:59

answered Jan 26 '13 at 02:54

Kerem

11,377
5
59
58

The screen just prints "array" when I try that:$nums = explode(',', urldecode($_GET['nums'])); print($nums); What am I missing? – mike Jan 26 '13 at 03:25
But when I just do $nums = urldecode($_GET['causes']), I get "1,2,3,4,5" – mike Jan 26 '13 at 03:26
Yes, but you need to filter `$nums` for security. After filter, can use `join()` to get `1,2,3`. – Kerem Jan 26 '13 at 20:21

score 1 · Answer 2 · answered Jan 26 '13 at 03:01

1

Convert the comma's into something more friendly on the sending page

$nums = "1,2,3,4,5";
$nums = str_replace(',','|',$nums);

In the page receiving the GET do the following:

$nums = str_replace('|',',',$nums);

answered Jan 26 '13 at 03:01

Jason Small

1,064
1
13
29

score 0 · Answer 3 · answered Jan 26 '13 at 02:51

URL encode the commas if you want a string?

www.site.php?age=1&sex=2&nums=1%2C2%2C3%2C4%2C5

Also, HTTP has an array format for parameters that PHP could interpret automatically for you. I forget the syntax offhand, but it's pretty straightforward.

Edit 2: Nevermind, you want the string.

score 0 · Answer 4 · answered Jan 26 '13 at 03:01

Not clear on why a test like this doesn’t work for you. Note the <pre> and print_r() are strictly there for debugging. explode(',', $_GET['nums']) is the key.

echo '<pre>';
$nums_array = explode(',', $_GET['nums']);
print_r($nums_array);
echo '</pre>';

Maybe you need to share part of the parsing code with us to better understand?

score 0 · Answer 5 · answered Jan 26 '13 at 03:18

With your current database interface, you can't. It uses prepared statements. Your list will be converted into a string, thus no longer be a value list to the IN clause.

This is why I'm for example using something like this:

db("SELECT * from t1 WHERE id=? AND x IN (??)", $id, $array);

You'll however have to manually create some ?,?,?,?,? placeholders ¹, depending on the input list length (after the mentioned explode, or better str_getcsv splitting). And yes you need enumerated parameters, not :named ones.

¹ Precisely because it's boring/tedious I'm not providing sample code here.

Pass comma separated values through php script into sql query

5 Answers5