A better SQL string sanitization function

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?

Solution 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.

Solution 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

Solution 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).

Comments

[Mike Purcell]

Did you try htmlentities? us2.php.net/manual/en/function.htmlentities.php

[outis]

Note there's no such thing as simple sanitization; you must discuss what type of processing the data is being processed for. Preventing SQL injection is a separate concern from XSS, for example. mysql_real_escape_string is only for preparing data for use with the mysql extension, which is outdated, on its way to deprecation and shouldn't be used for new code. To prevent SQL injection, instead use prepared statements with PDO or mysqli, both of which have other important advantages over mysql.

[mahen23]

damm, just did not know that you can target a website with all forms of injections. i just wanted to protect against sql injections

[Gromski]

You should never use #2. Magic quotes are an incomplete substitute for MySQL escaping. Of course, you shouldn't use the mysql_ functions at all anymore. Also, strip_tags has an entirely different purpose from MySQL escaping.

[Your Common Sense]

@mahen23 As a matter of fact, bulk escaping of input vars IS the same thing as defamed and deprecated magic_quotes. And it WILL allow an injection.