The best way to prevent SQL injection in PHP _php tips

If the user entered a query that was inserted directly into an SQL statement, the application would be vulnerable to SQL injection, such as the following example:

Copy Code code as follows:

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

This is because the user can enter a similar value "); The DROP table table; -To make the query into:

Copy Code code as follows:

INSERT into table (column) VALUES (' VALUE '); DROP table table; '

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

Use predefined statements and parameterized queries. SQL statements with any parameters will be sent to the database server and parsed! It is impossible for an attacker to inject SQL with malicious intent!

There are basically two options for achieving this goal:

1. Use PDO (PHP Data Objects)

Copy 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. Use of mysqli

Copy 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 that when using PDO to access the MySQL database, the true predefined statements are not used by default! To solve this problem, you must disable the prepared statements for simulation. Examples of creating connections using PDO are as follows:

Copy 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 example above, the error pattern Errmode is not strictly necessary, but it is recommended to add it. This method script does not stop when a fatal error is generated by a run error. And give the developer the opportunity to catch any errors (when Pdoexception exception is thrown).

The setattribute () line is mandatory, and it tells PDO to disable simulation-ready statements and use real predefined statements. This ensures that statements and values are not parsed by PHP before being sent to the MySQL database server (the attacker has no chance to inject malicious SQL).

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

Explanation (interpretation)

What happens when the SQL predefined statements you pass are parsed and compiled by the database server? Tells the database engine what you want to filter by using the specified character (like a in the above example, or like: name). Then invoke execute to execute the combined predefined statements and the parameter values you specify.

The most important thing here is that the parameter value is combined with the precompiled statement, not with an SQL string. SQL injection works by spoofing the SQL script created including malicious strings sent to the database. So by sending the actual separate SQL argument, You will reduce the risk. When you use prepared statements, any arguments you send will only be treated as strings (although the database engine might do some tuning of the parameters, which, of course, may end up as numbers). In the above example, if the variable $name contains ' Sarah ';D elete * from Employees, the result will only be a search of the 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 multiple times in the same session, it will only be parsed and compiled once, giving you some speed growth.
Well, now that you're asking how to insert, here's an example (using PDO):

Copy Code code as follows:

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