How do I prevent SQL injection in PHP?

Problem Description:

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

123	$unsafe_variable= $_POST[‘user_input‘]; mysql_query("INSERT INTO `table` (`column`) VALUES (‘" . $unsafe_variable. "‘)");

Because the user's input might be this:

1	value‘); DROP TABLE table;--

Then the SQL query will become as follows:

1	INSERTINTO `table` (`column`) VALUES(‘value‘); DROP TABLEtable;--‘)

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 parsing, and the parameters are treated as ordinary characters. This approach prevented attackers from injecting malicious SQL. You have two options to implement this method:

1. Using PDO:

1234567

$stmt = $pdo ->prepare ( ' SELECT * FROM Employees WHERE name =: Name '   $stmt ->execute ( array ( ' name ' => Code class= "PHP variable" > $name  foreach ( $stmt as $row ) {      //do something with $ Row }

2, using mysqli:

123456789

$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 using PDO by default does not allow the MySQL database to execute a true preprocessing statement (for reasons below). To solve this problem, you should prohibit PDO from simulating pre-processing statements. A good example of using PDO to create a database connection is as follows:

1234	$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 (Attr_errmode) is not required, but it is recommended to add it. Thus, when a fatal error (Fatal error) occurs, the script does not stop running, but instead gives the programmer a chance to capture pdoexceptions in order to properly handle the error. However, the first setattribute () call is mandatory, and it prohibits PDO from simulating pre-processing statements, while using a true preprocessing statement that has MySQL to execute a preprocessing statement. This ensures that statements and parameters are not processed by PHP before they are sent to MySQL, which will make it impossible for an attacker to inject malicious SQL. To understand why, refer to this blog post: PDO anti-injection principle analysis and considerations for using PDO. Note in the old version of PHP (<5.3.6), you cannot set the CharSet on the DSN of the Pdo constructor, refer to: Silently ignored the charset parameter.

Analytical

What happens when you send SQL statements to the database server for preprocessing and parsing? Tell the database engine where you want to filter by specifying a placeholder (one? or the name in the example above: name). When you invoke execute, the preprocessing statement will be combined with the value of the parameter you specified. 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 included when the SQL statement is constructed by triggering a script. So, by separating the SQL statements and parameters, you prevent the risk of SQL injection. The value of any parameter you send will be treated as a normal string without being parsed by the database server. Back to the example above, if the value of the $name variable is ' Sarah '; DELETE from employees, then the actual query would be to look in employees for the Name field value ' Sarah '; DELETE from Employees Records. 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 how to insert it, take a look at the following example (using PDO):

123	$preparedStatement = $db->prepare(‘INSERT INTO table (column) VALUES (:column)‘);$preparedStatement->execute(array(‘column‘=> $unsafeValue));

Original link: StackOverflow translation: Bole online-rokety

How do I prevent SQL injection in PHP?