3

if i remove all the special characters from a string than is there any chance of sql injection??(i don't need any special characters in my input)

i am using following code to remove all special characters

    $unsafe = $_GET["tag"];
$safe = preg_replace('/[^a-zA-Z0-9 ]/s', '', $unsafe);
$safe = mysql_real_escape_string($safe);

is there still any chances of having sql injection??

Badal
  • 3,738
  • 4
  • 33
  • 60
  • 2
    even with the special characters, just running it through `mysql_real_escape_string` should be enough. However, why even take the chance of sql injection? Just use pdo/mysqli and bind the parameters. It is simple. – Jonathan Kuhn Mar 05 '13 at 20:36
  • 1
    At some point, you *will* need the unsafe characters. Just do it properly to start with. You'll save yourself many many headaches. – TRiG Mar 05 '13 at 20:36
  • thanks but i have a ready application with mysql so converting it to PDO will be a big headache thats why using this. – Badal Mar 05 '13 at 20:44

1 Answers1

6

If you use mysql_real_escape_string, then there's no reason to remove "unsafe characters", since it will make them all safe.

However, the mysql_* functions are not recommended, since they're hard to use correctly. The PHP developers now recommend using PDO or the mysqli_* functions instead. See this answer for how to use parameterized queries with PDO or mysqli.

Community
  • 1
  • 1
Brendan Long
  • 53,280
  • 21
  • 146
  • 188
  • 4
    The mysql_real_escape_string function is not a catch-all. It only escapes special characters, so SELECT * FROM users WHERE score = $var is still vulnerable to $var = "1 OR 1 = 1" – Badal Mar 05 '13 at 20:38
  • 1
    @BadalSurana So always quote your variables (`SELECT * FROM users WHERE score = '$var'`), or even better -- **use parametrized queries with PDO or mysqli** (I just added a link to an answer showing some examples). – Brendan Long Mar 05 '13 at 20:40
  • thank you but i have a ready application with mysql so converting it to PDO will be a big headache thats why using this. – Badal Mar 05 '13 at 20:43
  • @BadalSurana Then I already answered your question -- removing unsafe characters won't do you any good if you're already using `mysql_real_escape_string`. `mysql_real_escape_string` can only help you if your input is being treated as strings (hence the name). If you're passing in variables unquoted then you have a much bigger security problem (since all input is being treated as raw SQL). – Brendan Long Mar 05 '13 at 20:45
  • okay so quoting variables and using `mysql_real_escape_string` will make it sql injection free?? – Badal Mar 05 '13 at 20:48
  • 1
    @BadalSurana As long as you do it *every single time*. I highly recommend learning to use PDO or mysqli as soon as you have time though, since parametrized queries are much easier to do correctly. – Brendan Long Mar 05 '13 at 21:00
  • 1
    I converted from mysql_ to PDO on a very large application a couple of years back and it only took an afternoon. What made it a bit easier was that I had an abstraction layer already, and I kept all my database access in my "model" (kept my database access code separate to my logic or view code). I even parameterised all my queries. I had probably 100-200 queries that I did this for. Unfortunately if you are maintaining someone else's app and it's terribly written (random direct calls to mysql_query() all over the place) then it's going to be more difficult... – thomasrutter Mar 05 '13 at 23:52