What is the best way to prevent SQL injection in php? _ php instance

This article provides a detailed analysis on how to prevent SQL injection in php. if you want to insert a query directly into an SQL statement, applications are vulnerable to SQL injection, for example, the following example:

The code is as follows:

$ Unsafe_variable = $ _ POST ['User _ input'];
Mysql_query ("insert into table (column) VALUES ('". $ unsafe_variable ."')");

This is because you can enter a TABLE similar to VALUE "); drop table;-to convert the query:

The code is as follows:

Insert into table (column) VALUES ('value'); drop table table ;'

How should we prevent this situation?See the following
Use predefine statements and parameterized queries. SQL statements with any parameters will be sent to the database server and parsed! It is impossible for attackers to maliciously inject SQL statements!
There are basically two options to achieve this goal:
1. use PDO (PHP Data Objects ):

The code is as follows:

$ Stmt = $ pdo-> prepare ('select * FROM employees WHERE name =: name ');
$ Stmt-> execute (array (': name' => $ name ));
Foreach ($ stmt as $ row ){
// Do something with $ row
}

2. use mysqli:

The code is as follows:

$ Stmt = $ dbConnection-> prepare ('select * FROM employees WHERE name =? ');
$ Stmt-> bind_param ('s ', $ name );
$ Stmt-> execute ();
$ Result = $ stmt-> get_result ();
While ($ row = $ result-> fetch_assoc ()){
// Do something with $ row
}

PDO (PHP Data object)
Note that when you use PDO to access the MySQL database, the true pre-definition statements are not used by default! To solve this problem, you must disable the statements prepared by simulation.
An example of using PDO to create a connection is as follows:

The code is as follows:

$ DbConnection = new PDO ('MySQL: dbname = dbtest; host = 127.0.0.1; charset = utf8', 'user', 'pass ');
$ DbConnection-> setAttribute (PDO: ATTR_EMULATE_PREPARES, false );
$ DbConnection-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION );

In the preceding example, the error mode ERRMODE is not strictly required, but we recommend that you add it. This method does not stop when an error occurs and a fatal error occurs. And give developers the opportunity to capture any errors (when a PDOException exception is thrown ).
The setAttribute () line is mandatory. it tells PDO to disable the simulation of the pre-definition statement and use the real pre-definition statement. This ensures that the statements and values are not parsed by PHP before being sent to the MySQL database server (attackers have no chance to inject malicious SQL statements ).
Of course, you can set character set parameters in the constructor options. Note that the 'old' PHP version (5.3.6) ignores character set parameters in DSN.

Explanation)
What happens when the SQL prepared statements you pass are parsed and compiled by the database server? Use the specified character (in the preceding example, it is like? Or: name) tells the database engine what you want to filter, and then calls execute to execute the prepared statement combined with the parameter value you specified.

The most important thing here is that this parameter value is combined with a pre-compiled statement, rather than an SQL string. the operating principle of SQL injection is that SQL scripts created by spoofing methods include malicious strings sent to the database. therefore, by sending actual separate SQL parameters, you will reduce the risk. when using prepared statements, any parameters you send will only be treated as strings (although the database engine may optimize some parameters, of course, it may eventually be numbers ). in the above example, if the variable $ name contains 'Sara'; DELETE * FROM employees, the result will only be a search string "'Sara'; DELETE * FROM employees ", you will not get an empty table.

Another benefit of using prepared statements is that if you execute the same statement multiple times in the same session, this will only be parsed and compiled once, giving you some speed increase.
Oh, since you asked how to insert, here is an example (using PDO ):

The code is as follows:

$ PreparedStatement = $ db-> prepare ('Insert INTO table (column) VALUES (: column )');
$ PreparedStatement-> execute (array (': column' => $ unsafeValue ));