The best solution to prevent SQL injection in PHP _php tutorial

If the user enters a query that is inserted directly into an SQL statement, the application is vulnerable to SQL injection, such as the following example:
Copy CodeThe code is as follows:
$unsafe _variable = $_post[' user_input ');
mysql_query ("INSERT into table (column) VALUES (')." $unsafe _variable. "')");

This is because the user can enter similar value "); DROP table tables; -To make the query into:
Copy CodeThe code is as follows:
INSERT into table (column) VALUES (' VALUE '); DROP table table;


What should we do to prevent this? Let's take a look at Theo's answer.

Use a pre-defined statement and a parameterized query. SQL statements with any parameters will be sent to the database server and parsed! It is impossible for an attacker to inject SQL into a malicious way!

There are two basic options for achieving this goal:

1. Using PDO (PHP Data Objects)
Copy the Code code 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. Using mysqli
Copy the Code code 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 When using PDO to access the MySQL database, the true pre-defined statement is not used by default! To solve this problem, you must disable the prepared statements for emulation. Examples of creating a connection using PDO are as follows:
Copy the Code code 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 above example the error mode Errmode is not strictly required, but it is recommended to add it. This method script does not stop when there is a fatal error in running an error. and give developers the opportunity to catch any errors (when throwing pdoexception exceptions).

SetAttribute () That line is mandatory, it tells PDO to disable the emulation of the pre-defined statements, using the real pre-defined statements. This ensures that statements and values are not parsed by PHP before being sent to the MySQL database server (the attacker has no opportunity to inject malicious SQL).

Of course you can set the character set parameters in the constructor options, paying special attention to the ' old ' PHP version (5.3.6) that ignores the character set parameters in the DSN.

Explanation (explanation)

What happens when you pass a SQL pre-defined statement that is parsed and compiled by the database server? Tell the database engine what you want to filter by specifying the character (like a in the example above, or like: name). Then call execute to execute the combination of the well-prepared statement and the parameter value you specified.

The most important thing here is that the parameter value is combined with a precompiled statement instead of a SQL string. SQL injection works by spoofing the creation of SQL scripts that include malicious strings sent to the database. Therefore, by sending the actual separate SQL parameters, You will reduce your risk. When you use prepared statements, any parameters that you send will be treated as strings only (although the database engine may do some optimization of the parameters, which of course will eventually be numeric). In the example above, if the variable $name contains ' Sarah ';D elete * from Employees, the result will only be a search string "' Sarah ';D elete * FROM Employees" and you won't get an empty table.

Another benefit of using prepared statements is that if you execute the same statement more than once in the same session, it will only be parsed and compiled once, giving you some speed growth.
Well, since you're asking how to insert, here's an example (using PDO):
Copy the Code code as follows:
$preparedStatement = $db->prepare (' INSERT into table (column) VALUES (: Column) ');
$preparedStatement->execute (Array (': column ' = $unsafeValue));

http://www.bkjia.com/PHPjc/326796.html www.bkjia.com true http://www.bkjia.com/PHPjc/326796.html techarticle If the user enters a query that is inserted directly into an SQL statement, the application is vulnerable to SQL injection, such as the following example: Copy code code as follows: $unsafe _variable = ...

