Lots of $_GET's in one script

Question

I want to pass about 20 variables to mysql table, using PHP scripts. Is there any better way than just doing:

$var1= preg_replace('/[^\w\.\-\& ]/', '', $_GET["var1"]);
$var2= preg_replace('/[^\w\.\-\& ]/', '',($_GET["var2"]));
$var3= preg_replace('/[^\w\.\-\& ]/', '',($_GET["var3"]));

etc.

And then having a massive line of code with:

$query = "INSERT INTO tbl_users (var1, var2, var3 etc.) VALUES('$var1', '$var2', '$var3' etc.)"

Thanks in advance, Sondar

Aside from sql injection issues, what you have is pretty much the way to do it, unless you want to turn on `register_globals` (which is probably a very bad idea). — Eric Petroelje, Jan 04 '11 at 22:11
To clarify, the variables aren't actually called var 1, var 2, var 3 etc. They're called stuffy like GAME_ID, USER_SCORE, USER_COMMENT (i.e.they're different data types and have very different names) — Sondar, Jan 04 '11 at 22:15
http://stackoverflow.com/questions/3773406/insert-update-helper-function-using-pdo — Your Common Sense, Jan 04 '11 at 22:18

score 1 · Answer 1 · answered Jan 04 '11 at 22:10

1

Don't worry about the number of fields, 20 is not large.

However, you should use the database supplied escape function. If you're using mysql_* functions, you can use mysql_real_escape_string

If you're using PDO (recommended), use bound parameters.

answered Jan 04 '11 at 22:10

webbiedave

48,414
8
88
101

score 0 · Answer 2 · answered Jan 04 '11 at 22:12

0

$a = $_GET;
foreach($i = 0;$i<count($a); $i++) {
    $b[] = preg_replace('/[^\w.-\& ]/', '', $a[$i]);
}

This should loop through all the post variables, validate them using your expression and dump it into $b. But I do not know any way to shorten $query = "INSERT INTO tbl_users (var1, var2, var3 etc.) VALUES('$var1', '$var2', '$var3' etc.)"

answered Jan 04 '11 at 22:12

Colum

3,844
1
22
26

1

don't you mean for() instead of foreach? actually you could just do `foreach($_GET as $var)` instead of your increment counting classic for() loop. – jondavidjohn Jan 04 '11 at 22:14

score 0 · Answer 3 · answered Jan 04 '11 at 22:14

0

$get_values = array();
foreach($_GET as $name=>$val) {
    $get_values[$name] = preg_replace('/[^\w\.\-\& ]/', '', $val);
}

$query = 'INSERT INTO tbl_users ('.implode(', ', array_keys($get_values)).') VALUES('.implode(', ', $get_values).')';

answered Jan 04 '11 at 22:14

PeeHaa

71,436
58
190
262

Tesserex · Answer 4 · 2011-01-04T22:25:20.017

There are probably many ways. This one will assume that your $_GET variable names and the table field names match exactly.

$var_list = array('var1', 'var2', 'var3'); // as many as you want

// this filters GET to be just your variables
$params = array_intersect_key($_GET, array_fill_keys($var_list, ''));

$query = "INSERT INTO tbl_users (" . implode(',', array_keys($params)) . ") VALUES (";

foreach ($params as $val) {
    $query .= mysql_real_escape_string($val);
}
$query .= ")";

Edit:

As noted in comments, you need to be careful with the keys here. The general method of just sticking the keys into the query like this is very bad, if you don't filter them as I have done here. If instead of array_keys($params) it was something like array_keys($_GET) then you would be wide open for SQL injection. Make sure you're sanitizing the input, both keys and values.

from? everything that came from GET is being run through mysql_real_escape_string. the keys aren't, but he's specifying those himself. Please give an example of how to inject this. Note that just using GET keys it would be bad, yes - but my code is filtering them to only keys he allows. — Tesserex, Jan 04 '11 at 22:21

score 0 · Answer 5 · answered Jan 04 '11 at 22:41

As outlined by everyone, the obvious way to simplify it is to use helper code. I have solved both problems with for example:

foreach ($_GET->keys() as $id) {
    $var[$id] = $_GET->text[$id];
}

Where $_GET->text["var12"] automatically applies the regex filter.

Or for insertion without worrying about manual SQL escaping it's advisable to use prepared statements. But you still need special support for inserting rows:

db("INSERT INTO users (:?) VALUES (??)", array_keys($var),
                                         array_values($var));

Use a simple ORM to avoid manual SQL handicrafting. Idiorm is nice.

Lots of $_GET's in one script

5 Answers5