Security Question

pianoparadise

I am using the $_GET to store data in a mysql database. What measures should I take to prevent SQL injections and other malicious attacks?

Currently I only use the strip_tags() function. Is this enough?

NogDog

First validate that the $_GET value contains the correct type and amount of data. This is entirely dependent upon what it is supposed to represent. You can use [man]strlen/man to check the length and the [man]ctype[/man] functions or [man]preg_match/man to check what it actually contains. If it is invalid, then you should simply reject the request with an error.

Second, and separate from the input validation, is to prevent SQL injection (whether deliberate or accidental) either by using the appropriate escaping mechanism for the DBMS interface being used (e.g. mysql_real_escape_string() for MySQL) or by the use of prepared queries and bound variables (as can be done within the MySQLi extension or PDO extensions).

This follows the general security principle of "filtering inputs and escaping outputs". (The $_GET value is an input to your application, the SQL query is an output to the DBMS.)

pianoparadise

Thanks for the reply NogDog!

If I have magic quotes enabled, do I still need to use mysql_real_escape_string() ?

laserlight

If I have magic quotes enabled, do I still need to use mysql_real_escape_string() ?

Yes. You should disable magic_quotes_gpc, or if that is not possible, then test for it with [man]get_magic_quotes_gpc/man. If it is on, use [man]stripslashes/man, followed by mysql_real_escape_string().

pianoparadise

Okay I did just as you said. Would it be problematic if I use the strip_tags() function after stripslashes() and before mysql_real_escape_string()?

laserlight

Would it be problematic if I use the strip_tags() function after stripslashes() and before mysql_real_escape_string()?

No, except that strip_tags() is often too destructive. You may want to use htmlspecialchars() after retrieving the data from the database instead.

NogDog

strip_tags() and htmlspecialchars() are normally for escaping output to the browser, not to the database. The database couldn't care less if the text being inserted contains HTML tags or any other characters which HTML considers "special". (It just cares that you have escaped any special SQL characters.) I generally prefer to have the raw data in the database so that I can manipulate it, search it, sort it, etc. by what was intended to be stored; then when you actually output it to a browser, you can use any of those HTML-related escaping mechanisms as is appropriate.

However, if you do not want to allow any or all HTML tags in the data, then it should be tested for as part of the input filtering, and if found rejected with an error to the user, rather than silently stripping it out and inserting what's left into the database, e.g.:

if(strip_tags($_GET['field']) != $_GET['field'])
{
   // reject with an error and do not process this request.
}

So the process is:
1. Filter the inputs from the user ($GET), and reject any invalid input as an error.
2. If you have valid input, then if storing it in a DB use an applicable escaping mechanism for that DBMS.
3. If outputting the data to a browser, either directly from $GET or after retrieving it from the DB, escape it with an applicable mechanism for [X]HTML output.