Form security. PHP MYSQL

Question

Ok I have a user form that has values submitted to the database.

My values are as follows,

$type = $_POST['type'];
$username = mysql_real_escape_string($_POST['username']);
$gender = $_POST['gender'];
$email = mysql_real_escape_string($_POST['email']);
$password = mysql_real_escape_string($_POST['password']);

I then insert them, as follows,

mysql_query("INSERT INTO accounts (username, email, password, gender, type) VALUES ('$username', '$email', '$password', '$gender', '$type')");

Now I was totally fine with this then I do some research, and something called Bobby Tables gets me all worried about security, then I find out that mysql_real_escape_string should be used for radio buttons as well, and then I find out that I need to define if an input value is an integer and check for it?? Now this has got me all worried as my site I thought was secure, which it is obviously not, so with some help can you please explain bobby tables and how to secure even more than using the standard real escape?

Thanks

Answer 1

On the surface of it, sure, your radio buttons define a constrained set of inputs and your user can't choose any option that you haven't predefined. So you know that your radio button inputs are safe.

Except... you don't. It's trivial to modify your HTML to submit some different value to your server-side PHP script. You could just write the input manually in telnet.

For your web script to be secure, it should be secure as a module; regardless of what validation you performed in the browser, it should be impossible to hack your PHP script.

This means applying mysql_real_escape_string to ALL your inputs, numeric or otherwise. Treat them ALL as user-provided strings.

Superior alternatives include PDO; this is worth looking up.

_{BTW, loved the comment about Bobby Tables. That's a reference to an edition of [used-to-be-more]popular webcomic xkcd, in which a character was named Bobby Tables by his parents in an attempt to perform SQL Injection on their child's school's database systems. The real term is SQL Injection.}

Answer 2

This is called SQL injection, and is an important lesson of programming web inputs. You should never trust your users' input. Always use mysql_real_escape_string and htmlspecialchars when dealing with user input (the latter helps keep your site safe from XSS; if you do want to allow HTML, use a library like HTMLPurifier). I always make this into a function whenever I'm dealing with user input to make my life easier.

If you want, you can make a database class to pass your variables directly instead of using them directly in a database query.

Also, LOL at the XKCD reference.

Answer 3

The real issue you are referencing is called SQL injection which is a huge deal.

My goto advice is to use the php PDO to bind your variables instead of running them directly.

Here's the class I wrote to handle that for me: Database.php.

Basically, you can't trust anything from the browser. Let the libraries handle the security for you.

Answer 4

It's the concept of sanitizing your data, especially unpredictable inputs that are public facing.

Answer 5

Escaping all your parameters will do the trick, because that will make sure all your special chars will be escaped. So just trust your mysql_real_escape_string :-)

Answer 6

Rule #1 of web security: never trust your input.

mysql_real_escape_string will usually be good enough. You can do other fun things like check to see that something that should be a number is (it's easier to do that than to call mysql_real_escape_string).

Keep in mind too that if you use your input for things OTHER than SQL queries, you need to be extra paranoid. For example I had a script that took in a name as a parameter, and used it to look up a file on the disk. However if someone did something tricky, they might be able to access a file they shouldn't using my script. So I made sure that the filenames looked correct by checking it against a regular expression.