function to sanitize input to Mysql database

Question

I am trying to put a general purpose function together that will sanitize input to a Mysql database. So far this is what I have:

function sanitize($input){
    if(get_magic_quotes_qpc($input)){

        $input = trim($input); // get rid of white space left and right
        $input = htmlentities($input); // convert symbols to html entities
        return $input;
    } else {

        $input = htmlentities($input); // convert symbols to html entities
        $input = addslashes($input); // server doesn't add slashes, so we will add them to escape ',",\,NULL
        $input = mysql_real_escape_string($input); // escapes \x00, \n, \r, \, ', " and \x1a
        return $input;
    }
}

If i understood the definition of get_magic_quotes_qpc(). This is set by the php server to automatically escape characters instead of needing to use addslashes().

Have I used addslashes() and mysql_real_escape_string() correctly together and is there anything else I could add to increase the sanitization.

Thanks

Answer 1

htmlentities() is unnecessary to make data safe for SQL. It's used when echoing data values to HTML output, to avoid XSS vulnerabilities. That's also an important security issue you need to be mindful of, but it's not related to SQL.

addslashes() is redundant with mysql_real_escape_string. You'll end up with literal backslashes in your strings in the database.

Don't use magic quotes. This feature has been deprecated for many years. Don't deploy PHP code to an environment where magic quotes is enabled. If it's enabled, turn it off. If it's a hosted environment and they won't turn off magic quotes, get a new hosting provider.

Don't use ext/mysql. It doesn't support query parameters, transactions, or OO usage.

Update: ext/mysql was deprecated in PHP 5.5.0 (2013-06-20), and removed in PHP 7.0.0 (2015-12-03). You really can't use it.

Use PDO, and make your queries safer by using prepared queries.

For more details about writing safe SQL, read my presentation SQL Injection Myths and Fallacies.