The most practical and effective PHP to prevent SQL injection

Problem Description:

If the data entered by the user is inserted into an SQL query without processing, the application is likely to suffer a SQL injection attack, as in the following example:

The code is as follows
$unsafe _variable = $_post[' user_input ']; mysql_query (' INSERT into ' table ' (' column ') VALUES ('). $unsafe _variable. "')");

Because the user's input may be like this:

The code is as follows
Value '); DROP TABLE table;--

Then the SQL query becomes the following:

The code is as follows
INSERT into ' table ' (' column ') VALUES (' value '); DROP TABLE table;--')

What effective methods should be taken to prevent SQL injection?



Best Answer (from Theo):

Use preprocessing statements and parameterized queries. The preprocessing statements and parameters are sent to the database server for resolution, and the parameters are treated as normal characters. This approach makes it impossible for an attacker to inject malicious SQL. You have two options to implement this method:

1, the use of PDO:

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, the use of 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

Note that the default use of PDO does not allow the MySQL database to perform a true preprocessing statement (cause see below). To solve this problem, you should prohibit PDO analog preprocessing statements. An example of a proper use of PDO to create a database connection 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 example above, the error mode (Attr_errmode) is not required, but it is recommended. In this way, when a fatal error (Fatal error) occurs, the script does not stop running, but it gives the programmer an opportunity to capture the pdoexceptions in order to properly handle the error. However, the first setattribute () call is required, which prohibits PDO analog preprocessing statements, and the use of a true preprocessing statement, in which MySQL executes the preprocessing statement. This ensures that statements and parameters are not processed by PHP before being sent to MySQL, which will allow attackers to inject malicious SQL. To understand why, refer to this blog post: PDO analysis of the principle of anti-injection and the use of PDO considerations. Note that in the old version of PHP (<5.3.6), you cannot set the character set on the DSN of the PDO constructor, reference: silently ignored the charset parameter.

resolves

What happens when you send an SQL statement to the database server for preprocessing and parsing? Tell the database engine where you want to filter by specifying a placeholder (a? or a name named in the example above). When you invoke execute, the preprocessing statement will be combined with the parameter values you specify. The key point is here: The value of the parameter is combined with the parsed SQL statement, not the SQL string. SQL injection is a malicious string that is used to construct an SQL statement by triggering a script. So, by separating the SQL statements from the arguments, you prevent the risk of SQL injection. Any parameter you send will be treated as a normal string without being parsed by the database server. BackThe above example, if the value of the $name variable is ' Sarah '; DELETE from employees, the actual query would be to find the Name field value ' Sarah ' in employees; Deletes the record from employees. Another advantage of using preprocessing statements is that if you execute the same statement many times in the same database connection session, it will only be parsed once, which can increase the speed of execution. If you want to ask what to do with inserts, look at the following example (using PDO):
 

code as follows
$preparedStatement = $db->prepare (' INSERT into table (col umn) VALUES (: Column) '); $preparedStatement->execute (Array (' column ' => $unsafeValue));