Passing The Table To Query From The Address Bar

[JustinK101]

Hello, is there a risk passing the table to do a mysql query on via the query string. For example I have the following:

"SELECT * FROM " . $_GET['tb'] . "";

I usually do smart_quote() on all my passed in parameters, but you cant quote the table name or else the query will fail. So are their any ways to lock this down a bit? Thanks.

[Orio]

I dont know what smart_quote() is, but I guess it's a functions that escapes strings. For every charater it escapes, the string length grows by one. You probbly dont have charaters that need to be escaped in your table names, so a check like this one can prevent SQL-Injections. I dont know if 100% secure, but that's what I'd do-
[code]<?php

if(strlen(smart_quote($_GET['tb'])) != strlen($_GET['tb'])) die("Injection attempt.");

?>[/code]

Orio.

[JustinK101]

Yeah I use:

function mysql_smart_quote($var)
{
  if (get_magic_quotes_gpc())
  {
      $var = stripslashes($var);
  }
 
  if (!is_numeric($var))
  {
      $var = "'" . mysql_real_escape_string($var) . "'";
  }
  return $var;
}

Just wondering though about passing in the table name in the query string. Somebody could simply change the variable and access different tables.

[Orio]

Yes they can. It's always good to avoid using GET if there's another option.


Maybe creating another table that has two columns:
1) Table name
2) Random hash

Then when you generate links to use diffrent tables you use the random hash. When you need to pull out the table, search the hash in and match it to the required table in your database. Once again, the hash can be manually changed by people who want to try and view other tables, but this will be much more secure.


Orio.

[JustinK101]

Yeah I can just trick them, wont md5($table_name) do the same trick, no need to store the value in a mysql table, just md5() the table name, and undo it when you need to reference correct?

[Orio]

The problem is- you cant undo md5()'s affect :) It's a hashing function, you cannot decrypt it.
That's why you can use my idea.

Orio.

[JustinK101]

hummm md5 wont work, because I cant undo. How can I scramble the text, then be able to undo the scrambled and get back my orginal input?

I don't want to create a whole table for this purpose. The scrambling doesnst have to be insane, just something where your typical trouble maker wont be able to figure out.

[HeyRay2]

I would say the most secure way to do this is to change to using $_POST variables for your table names?

Is there a specific reason you are passing this information by way of $_GET?

[.josh]

if you insist on using $_GET, then here is some advice:

keep an array of explicitly allowed field names.  if the $_GET variable is not holding that allowed name, then do not run the query.

example:

[code]
<?php
  $allowed = array('field1','field2','field3');
  $field =  (in_array($_GET['fieldid'], $allowed)) ? $_GET['fieldid'] : 'field1';
  $sql = "select $field from table...";
?>
[/code]
okay depending on what your script is actually doing, you might want to alter what returns from the ternary operator, should the condition be false.  The code above sets the "default" to 'field1' so if you want it to default to a certain field, then put that fieldname there.  If you are wanting it to not do anything at all, then putt NULL instead of 'field1' and then do a condition on your sql like

if ($field != NULL) {
  $sql = "...";
} else {
  echo "some error message or hack attempt! or something";
}

[JustinK101]

Yeah has to be get, I just used an encrypt and decrypt function works like charm, nobody going to be able to change the table name.