Dynamic SQL Quieres Naming Post keys

Question

It is bad to name my form fields the same as my column names? I mean do people really do:

 <?php
 $id = intval($_POST['user_unique_key']);
 $name = mysql_real_escape_string($_POST['name_of_user']);
 $email = mysql_real_escape_string($_POST['user_mail_thing']);
 $address = mysql_real_escape_string($_POST['user_place_of_living']);
 //....
 $sql = "INSERT INTO `users`('id','name','email','address') VALUES($id,$name,$email,$address)"
 ?>

Also note that the above data validation is horrible! I would never use it. My Data Validation

Should I use a table prefix like: 'secret_prefix_'?

The final solution which I don't really like is that I could use a two way hash to hash the fieldnams. so have the input name be

 <?php echo $field_name = base64_encode(mcrypt_encrypt( MCRYPT_RIJNDAEL_128, $key, $key, MCRYPT_MODE_ECB, $iv ));?>

and then decode the field name once it is posted

 <?php
  foreach($_POST as $name=> $value)
  {
  $input[base64_decode(mcrypt_decrypt( MCRYPT_RIJNDAEL_128, $key, $name, MCRYPT_MODE_ECB, $iv ))] = $value;
  }?>

Answer 1

If your cleanup/validation code on the server-side is strong, then it doesn't really matter that your form fields are identical/close to the table names.

Don't waste time trying to obfuscate it, if you're confident with your clean method, use the regular field names.

Answer 2

It is bad to name my form fields the same as my column names?

The short answer is Not always, changing the names of the columns/databases/tables can make the application more resistant to attack if your database is configured correctly. By default an attacker can obtain this data if he has sql injection, there for it doesn't really matter if you give it to him.

If you do want to obscure these names then you must also revoke access to the INFORMATION_SCHEMA database:

REVOKE SELECT ON INFORMATION_SCHEMA.* TO mysql_app_user

And here is the reason why:

A common SQL Injection attack is to select juicy data from another table. In MySQL you cannot "stack quires" as in (insert into ...; select * from ...; grant ...). So you have to use a "union select".

For instance:

mysql_query("select path,type from images where id=".$_GET[id]);

The corresponding exploit is as follows:

http://localhost/sql_inj.php?id=0 union select username,password from users where id=1

In this case the first select statement is going to be empty, no primary key will be zero. The 2nd select statement will select the username and password from the user table in the same database, this will grab the primary key of 1 which 99% of the time is the administrator. In order to pull this off you need to know the EXACT name of the table and the columns you need.

Well, in mysql there is the information_schema database winch provides this data. So if the column and database names where obscured then they could still be obtained by the following:

http://localhost/sql_inj.php?id=0 union select COLUMN_NAME,"junk" from FROM INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME like "%user"

This query will defeat any security prefix. However, this same attack could be used to obtain ALL columns, database and tables in the MySQL database. If you revoke select rights to the web application's mysql user account, then he can't do this. You should also revoke FILE privileges as these can be used to upload a backdoor or read configuration files.

Answer 3

First, I wouldn't rely on obfuscating form field names as your solution for web security. It's much better to use multiple methods for security:

1. Filtering (like your use of intval())
2. Validation
3. Escaping (if you interpolate content into SQL strings)
4. Prepared queries using parameters (you need to use ext/mysqli or ext/pdo_mysql)

That said, obfuscating the field names couldn't hurt. I wouldn't go to the extreme example you show, of hashing column names. That's an unnecessary expense. Do you want to call hashing functions to encode column names every time you generate a form, and decode the POST field names every time you process a form?

You don't even have to obfuscate every form field name in order for the obfuscation to achieve its effect. If even some of your form fields don't match the database column names, then an attacker wouldn't know which ones match and which ones don't! :-)