A better SQL string sanitization function [duplicate]

Question

I am currently using below function to sanitize my $_POST and $_GET against SQL injection. Unfortunately, I cannot post code through it, for example: "<a href test". How does Twitter do it?

 function _secinput($variable)
 {return filter_var(mysql_real_escape_string($variable), FILTER_SANITIZE_STRING); }

Plus, can anyone tell suggest me if I can improve it in any ways?

Answer 1

There can never and will never be one function to sanitize everything. You must choose the right tool for the job.

1) htmlspecialchars($var,ENT_QUOTES) works well for most xss.

2) Parametrized query libraries like PDO and MySQLi work best for sql injection.

3) For CRLF injection, just remove new lines: str_replace("\n","",$var)

4) For Command injection use escapeshellarg()

And there are many other forms of injection.

Answer 2

i just wanted to protect against sql injections

You merely can't "sanitize" all incoming data even against sql-injection only (and you shouldn't).

Even in this distinct case you SHOULD NOT "sanitize" your input variables altogether. There are different rules for the different parts of the query: you can't escape identifier the same way as data.

See this my answer with full explanation: https://stackoverflow.com/a/8255054/285587

Answer 3

It depends on what you want to do. If you want to be able to safely display HTML characters in an HTML page, you'd want to escape them - which FILTER_SANITIZE_SPECIAL_CHARS would do (see here for more details).

Answer 4

Here is a function that I have used in providing multiple forms of sanitizing based on the context. Like people have mentioned, there is not one way to sanitize every type of content. You can use this or something like it and build upon it to suit your needs:

function sanitize($var, $type)
{
        switch($type) {
                case 'html':
                        $safe = htmlspecialchars($var);
                        break;
                case 'sql':
                        $safe = mysql_real_escape_string($var);
                        break;
                case 'file':
                        $safe = preg_replace('/(\/|-|_)/','',$var);
                        break;
                case 'shell':
                        $safe = escapeshellcmd($var);
                        break;
                default:
                        $safe = htmlspecialchars($var);
        }
        return $safe;
}

Here is an example of its use in a SQL query:

$query = sprintf("SELECT firstName FROM users WHERE userName = '%s'",
                 sanitize($_GET['userName'],'sql'));

Here is its use in HTML output:

<h1>Welcome <?php echo sanitize($firstName,'html');?></h1>

Answer 5

filter_var fails at many levels, so i suggest you to do like this

use this

1. strip_tags($var);
2. $sanitized_string = (get_magic_quotes_gpc()) ? $var : mysql_real_escape_string($var);
3. // If using MySQL
   $var = mysql_real_escape_string($var);

note : magic_quotes_gpc feature has been DEPRECATED as of PHP 5.3.0.