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?

Did you try `htmlentities`? http://us2.php.net/manual/en/function.htmlentities.php — Mike Purcell, Dec 12 '11 at 06:42
possible duplicate of [Are these two functions overkill for sanitization?](http://stackoverflow.com/questions/2940030/), [Is mysql_real_escape_string sufficient for cleaning user input?](http://stackoverflow.com/questions/2353666/), [PHP Sanitize Data](http://stackoverflow.com/questions/5863508/), [Function to sanitize input values PHP](http://stackoverflow.com/questions/3009005/) — outis, Dec 12 '11 at 06:51
any question about string sanitization in PHP is a duplicate of the above questions. — mahen23, Dec 12 '11 at 06:58
See also [When to sanitize PHP & MySQL code before being stored in the database or when its being displayed?](http://stackoverflow.com/questions/3327974/), [How do I HTML Encode all the output in a web application?: prepare the data just before it's sent somewhere](http://stackoverflow.com/a/60690/90527) — outis, Dec 12 '11 at 07:01
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. — outis, Dec 12 '11 at 07:05

score 13 · Accepted Answer · edited Nov 03 '19 at 04:10

13

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.

edited Nov 03 '19 at 04:10

QmlnR2F5

934
10
17

answered Dec 12 '11 at 07:10

rook

66,304
38
162
239

3

damm, just did not know that you can target a website with all forms of injections. i just wanted to protect against sql injections – mahen23 Dec 12 '11 at 07:13

score 2 · Answer 2 · edited May 23 '17 at 12:14

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

score 1 · Answer 3 · answered Dec 12 '11 at 06:42

1

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

answered Dec 12 '11 at 06:42

Amber

507,862
82
626
550

dchrastil · Answer 4 · 2011-12-12T14:49:24.550

0

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>

edited Dec 12 '11 at 14:49

answered Dec 12 '11 at 07:23

dchrastil

582
3
5

1

this function makes very little sense. For example, it doesn't add quotes for the mysql values - thus, it is useless and error prone. – Your Common Sense Dec 12 '11 at 08:55
looks like you hacked this script in less than 15 minutes. – mahen23 Dec 12 '11 at 10:17
I think it needed some context to make sense Col. Shrapnel. There is no need to quotes to the variable while sanitizing, this should be part of the parameterized query. Please point out the flaws you see. I didn't include a case for integers as that is easiely done by `(int)$userId` – dchrastil Dec 12 '11 at 14:52
when I added this to my code it now inputs blank records in my table – RL.AdmiralX Nov 24 '15 at 22:22
@dchrastil fix it or somehting – RL.AdmiralX Nov 24 '15 at 22:22

score -1 · Answer 5 · answered Dec 12 '11 at 06:49

-1

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

use this

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

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

answered Dec 12 '11 at 06:49

xkeshav

53,360
44
177
245

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. – deceze Dec 12 '11 at 07:16
1

@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. – Your Common Sense Dec 12 '11 at 08:58

A better SQL string sanitization function

5 Answers5