通过替换单引号来防止SQL注入

Question

I've read that replacing user input isn't safe for the SQL injection. I would like to know (possibly with an example) what's wrong in something like this (PHP):

function formatsql($testo){
    return str_replace("'", "'", $testo);
}

$username = formatsql($_POST["username"]);
$password = formatsql($_POST["password"]);

$query = "SELECT id FROM utenti WHERE user='$username' AND password='$password'";

Answer 1

I would like to know what's wrong in something like this (PHP)

The idea.

It has been proved to be flawed long time ago.

First of all, this code does alter the data. It is good approach for the abstract musings but it will crash your application in the real life. As a matter of fact, it's unacceptable behavior. However, you could escape your quotes instead of replacing them.

Second, you are (like most of PHP folks) under the delusion that replacing some characters makes your data safe. While it is not. Every PHP user who cares to reinvent a wheel in the field of injection protection always assume that only strings being added to the query. They never realize it explicitly though, nor imagine any other parts exists in SQL query. While such a replacement would be as harmless for any other SQL literal as a chicken. And the very name of your function is a sure proof for my words.
Say, you have a code like this

$limit = formatsql($_POST["limit"]);
$query = "SELECT id FROM utenti LIMIT $limit";

which will welcome any script-kiddie to play with your db.

Also, there is a term "user input" in your reasoning, which is a sure sign of the second order injection.

Taking a step further, let's observe two kinds of applications: some sort of silly home page script and relatively big web application. Although your code is quite all right for the former one, in a latter one rules change. Sometimes we can have these two parts of the code

$username = formatsql($_POST["username"]);
$password = formatsql($_POST["password"]);

and

$query = "SELECT id FROM utenti WHERE user='$username' AND password='$password'";

dramatically separated from each other. And here we can slip into many and many troubles, such as double escaping, wrong escaping, no escaping at all.

This is why manual escaping has been considered bad practice already long time ago.

Instead, prepared statements have to be used, as they guarantee that

• complete formatting applied instead of silly "escaping" or "replacing"
• different formatting applied for the different data types.
• formatting applied right in place where it have to be - not sooner nor later.
• proper formatting applied unconditionally, independently from developer's will or air.

This is why prepared statements considered the only proper way long time ago already.