1
$id = $_GET['id'];
$id = str_replace("'", "", $id);
$sql = "select name from test where id='$id'";
$stmt = $conn->query($sql);

As the php code snippet above, it removes all single quotes from the user input, and then put it in a sql query, whaterver it gets from the user is not able to escape from the quotes surrounding them, it seems safe from sql injection. I am quite curious about how to inject sql code to this.

Some question said about escaping quotes, which has examples to exploit it. But in my occasion, removing all quotes isn't really the same as escaping.

I know there is the parameterized query way to prevent sql injection. And yes, it's possible that single quotes may be contained in legitimate data. I am asking this question out of curiosity while learing sql injection, all I want to know is there are any examples to exploit this code?

ryan
  • 66
  • 9
  • 2
    If you're contemplating doing this, don't. The *known safe* way to prevent SQL injection is to use parameters, and pretty well any language binding these days will have some means for you to provide parameters. Anything else where you're still mixing *data* and *code* by string concatenation is *potentially* vulnerable, even if nobody can *demonstrate* a particular vulnerability to you today. – Damien_The_Unbeliever May 03 '17 at 13:30
  • 2
    Not to mention, of course, that *quote characters may be legitimate data*. Telling someone that their surname isn't allowed to be `O'Brien` just doesn't work well. – Damien_The_Unbeliever May 03 '17 at 13:31
  • http://stackoverflow.com/questions/5520840/sql-injection-after-removing-all-single-quotes-and-dash-characters – Richard Hansell May 03 '17 at 13:54
  • 1
    Possible duplicate of [Can I protect against SQL Injection by escaping single-quote and surrounding user input with single-quotes?](http://stackoverflow.com/questions/139199/can-i-protect-against-sql-injection-by-escaping-single-quote-and-surrounding-use) – F. Stephen Q May 03 '17 at 14:49
  • @RichardHansell Yeah, I have read this question before I ask. I don't think those answers really solved the question. – ryan May 03 '17 at 15:03
  • @F.StephenQ No, it's not a duplication. *Removing* is different from *escaping*, and escaping quotes can be injected, as those answers showed. – ryan May 03 '17 at 15:07
  • 1
    Indeed, there was no accepted answer on the question I linked. However, I think the basic principle still applies, i.e. just because nobody can produce an example doesn't imply that this is "safe", and also the fact that people's names, etc. often have legitimate quotes in them. – Richard Hansell May 03 '17 at 15:10

1 Answers1

0

If you don't want to use parameterization or escaping, and assuming your id is an integer, you could use typecasting. This is totally safe, and faster code than calling a function to do string replacement:

$id = (int) $_GET['id'];
$sql = "select name from test WHERE id=$id";
$stmt = $conn->query($sql);

But you should just get into the habit of using parameters. It's simple and fast. It works for strings, even if the strings contain special characters like quotes.

It's super easy in PDO, and it takes just one extra line of code:

$id = $_GET['id'];
$sql = "select name from test WHERE id=?";
$stmt = $conn->prepare($sql);
$stmt->execute([$id]);

my question [is], is it safe to remove quotes for string fields?

No. Don't do this. You don't know enough to do this safely. For example, what about backslash (\)? And other special characters?

Do you know why the internal MySQL API https://dev.mysql.com/doc/refman/5.7/en/mysql-real-escape-string.html escapes more than just the quotes? This is the list of characters that are escaped: 

\, ', ", NUL (ASCII 0), \n, \r, and Control+Z

So is it enough to remove these characters? No, you still have to think about character sets and hex-encoded characters.

There's no reason to do this. You already have more reliable solutions to make SQL queries safe.

Don't get fixated on your remove-the-quote solution. It's not sufficient.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828