Prevent SQL Injection

I read some files and questions about preventing SQL Injection on the Internet, such as using preprocessing and filtering sensitive characters. What I keep wondering is that it is very simple to solve the ssql injection problem. Just escape the content entered by the user, that is, the content entered by the user. & #039 ;, & quot; convert to & #039;, & quot... I read some files and questions about preventing SQL Injection on the Internet, such as using preprocessing and filtering sensitive characters. What I keep wondering is that it is very simple to solve the ssql injection problem. Just escape the content entered by the user, that is, the user input ', "convert to \ ', \". Why is it so complicated? Or can I avoid all SQL Injection in this way?

Reply content:

I read some files and questions about preventing SQL Injection on the Internet, such as using preprocessing and filtering sensitive characters. What I keep wondering is that it is very simple to solve the ssql injection problem. Just escape the content entered by the user, that is, the user input ', "convert to \ ', \". Why is it so complicated? Or can I avoid all SQL Injection in this way?

Use htmlspecialchars/HTMLPurifier to defend against XSS injection, and use pre-processing parameterized queries to defend against SQL injection.

Call HTMLPurifier to filter XSS and output HTML:

require dirname(__FILE__).'/htmlpurifier/library/HTMLPurifier.auto.php';$purifier = new HTMLPurifier();echo $purifier->purify($html);

Query MySQLi binding parameters:

$ Db = @ new mysqli (); $ stmt = $ db-> prepare ('select * FROM posts WHERE id =? '); // Pre-processing $ stmt-> bind_param (' I ', $ id); // bind the parameter $ stmt-> execute (); // query var_export ($ stmt-> get_result ()-> fetch_all ());

Use tcp. port = 3306 in WireShark to filter and analyze the communication between PHP and MySQL.

  set_charset('utf8');$stmt = $mysqli->prepare("SELECT `username` FROM `pb_users` WHERE `id`=?");$stmt->bind_param('i', $id);$stmt->execute();$stmt->store_result();$stmt->bind_result($username);while ($stmt->fetch()) echo $username;$stmt->close();$mysqli->close();
  query('SET NAMES utf8');$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);$sth = $dbh->prepare("SELECT `username` FROM `pb_users` WHERE `id`=?");$sth->bindParam(1, $id, PDO::PARAM_INT);$sth->execute();print_r($sth->fetchAll(PDO::FETCH_ASSOC));$sth = null;$dbh = null;

In the Request Prepare Statement, you can see the SELECTusernameFROMpb_usersWHEREid=?
In the Request Execute Statement, we can see that the Parameter content is:
Type: FIELD_TYPE_LONGLONG (8)
Unsigned: 0
Value: 1
It can be seen that PHP sends the SQL template and variable to MySQL twice, and MySQL completes variable escape processing.
Since the SQL template and variables are sent twice, there is no SQL Injection problem.

In MySQL general_log, we can see that:
Prepare SELECTusernameFROMpb_usersWHEREid=?
Execute SELECTusernameFROMpb_usersWHEREid= 1
If the ID is bound to a string, the id value assigned to Execute is as follows:id= '1'
If PDO does not disable simulated preprocessing, you can see that:
Query SELECTusernameFROMpb_usersWHEREid= 1

Solve the injection, or PDO is more thorough, the worst off also needs mysqli, bind, add type.

You only need to escape the content entered by the user, that is, the ', "converted to \', \" entered by the user. Why is it so complicated? Or can I avoid all SQL Injection in this way?

You certainly don't know that there is a kind of injection called wide character injection, int injection, orderBy injection,
Second, injection is actually very easy to defend against. The main trouble is that xss