php - SQL Injection prevention by replacing single-quote -

i've read replacing user input isn't safe sql injection. know (possibly example) what's wrong in (php):

function formatsql($testo){     return str_replace("'", "'", $testo); }  $username = formatsql($_post["username"]); $password = formatsql($_post["password"]);  $query = "select id utenti user='$username' , password='$password'"; 

i know what's wrong in (php)

the idea.

it has been proved flawed long time ago.

first of all, this code alter data. approach abstract musings crash application in real life. matter of fact, it's unacceptable behavior. however, escape quotes instead of replacing them.

second, (like of php folks) under delusion replacing characters makes data safe. while not. every php user cares reinvent wheel in field of injection protection assume strings being added query. never realize explicitly though, nor imagine other parts exists in sql query. while such replacement harmless other sql literal chicken. , name of function sure proof words.
say, have code this

$limit = formatsql($_post["limit"]); $query = "select id utenti limit $limit"; 

which welcome script-kiddie play db.

also, there term "user input" in reasoning, sure sign of second order injection.

taking step further, let's observe 2 kinds of applications: sort of silly home page script , relatively big web application. although code quite right former one, in latter 1 rules change. can have these 2 parts of code

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

and

$query = "select id utenti user='$username' , password='$password'"; 

dramatically separated each other. , here can slip many , many troubles, such double escaping, wrong escaping, no escaping @ all.

this why manual escaping has been considered bad practice long time ago.

instead, prepared statements have used, guarantee

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

this why prepared statements considered the proper way long time ago already.