what are the best practices to prevent sql injections

Question

I have done some research and still confused, This is my outcome of that research. Can someone please comment and advise to how I can make these better or if there is a rock solid implementation already out there I can use?

Method 1:

array_map('trim', $_GET);
array_map('stripslashes', $_GET);
array_map('mysql_real_escape_string', $_GET);

Method 2:

function filter($data) {
    $data = trim(htmlentities(strip_tags($data)));

    if (get_magic_quotes_gpc())
        $data = stripslashes($data);

    $data = mysql_real_escape_string($data);

    return $data;
}

foreach($_GET as $key => $value) {
    $data[$key] = filter($value);
}

Answer 1

Both methods you show are not recommendable

Blanket "sanitizing" data is counter-productive, because data needs to be sanitised in different ways depending on how it is going to be used: Using it in a database query needs different sanitation from outputting it in HTML, or from using it as parameters in a command line call, etc. etc.

The best way to do sanitation is immediately before the data is being used. That way, it is easy for the programmer to see whether all data is actually getting sanitized.

If you use the mysql_* family of functions, do a mysql_real_escape_string() on every argument you use in a query:

$safe_name = mysql_real_escape_string($_POST["name"]);
$safe_address = mysql_real_escape_string($_POST["address"]);

$result = mysql_query ("INSERT INTO table VALUES '$safe_name', '$safe_address'");

If you use the PDO or mysqli families of functions, you can make use of parametrized queries, which eliminate most of the SQL injection woes - all everyday ones at any rate.

It is perfectly possible to write safe queries with mysql_*, and it is also perfectly possible to introduce a security hole in a PDO query, but if you are starting from scratch, consider using PDO or mysqli straight away.

Use strip_tags() only if you are planning to output user entered data in HTML; note that you need to do an additional htmlspecialchars() to reliably prevent XSS attacks.

The only blanket sanitation method that has some merit is the

 if (get_magic_quotes_gpc())
    $data = stripslashes($data); 

call which filters out the layer of escaping added by the now-deprecated "magic quotes" feature of earlier versions of PHP.