How to Prevent SQL Injection in PHP ?, Php SQL Injection

How to Prevent SQL Injection in PHP ?, Php SQL Injection
Problem description:

If the data entered by the user is inserted into an SQL query statement without being processed, the application may be vulnerable to SQL injection attacks, as shown in the following example:

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

Because the user input may be like this:

1	value'); DROP TABLE table;--

The SQL query will be changed to the following:

1	INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')

What effective methods should be taken to prevent SQL injection?

Best Answer (from Theo ):

Use pre-processing statements and parameterized queries. The pre-processing statements and parameters are sent to the database server for resolution. The parameters are processed as common characters. This method prevents attackers from injecting malicious SQL statements. You have two options to implement this method:

1. Use PDO:

1234567

$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:

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 the use of PDO by default does not allow the MySQL database to execute the true pre-processing statement (the reason is described below ). To solve this problem, you should disable PDO to simulate preprocessing statements. An 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 preceding example, the error reporting mode (ATTR_ERRMODE) is not required, but we recommend that you add it. In this way, when a Fatal Error occurs, the script does not stop running, but gives the programmer a chance to capture PDOExceptions to properly handle the Error. However, the first setAttribute () call is required. It prohibits PDO from simulating preprocessing statements. Instead, it uses a true preprocessing statement, that is, MySQL executes preprocessing statements. This ensures that the statements and parameters have not been processed by PHP before being sent to MySQL, which prevents attackers from injecting malicious SQL statements. For the reason, refer to this blog post: Analysis of the PDO anti-injection principle and precautions for using PDO. Note that in earlier versions of PHP (<5.3.6), you cannot set the character set on the DSN of the PDO constructor. For details, refer to: silently ignored the charset parameter.

Analysis

What happens when you send SQL statements to the database server for preprocessing and parsing? By specifying a placeholder (? Or name: name) in the preceding example to tell the database engine where you want to filter. When you call execute, the pre-processing statement will be combined with the parameter value you specified. The key point is here: the parameter value is combined with the parsed SQL statement, rather than the SQL string. SQL Injection contains malicious strings when constructing SQL statements by triggering scripts. Therefore, separating SQL statements from parameters prevents the risk of SQL injection. Any parameter value you send will be treated as a normal string and will not be parsed by the database server. Return to the example above. If the value of the $ name variable is 'sara'; delete from employees, the actual query is to find that the value of the name field in employees is 'sara '; DELETE records FROM employees. Another advantage of using pre-processing statements is that if you execute the same statement multiple times in the same database connection session, it will be parsed only once, which improves the execution speed. If you want to know how to insert data, see the following example (using PDO ):

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

StackOverflow Translation: bole online-rokety

How to Prevent SQL Injection in PHP

I have translated the most frequently asked questions and answers. Question: If your input can be directly inserted into an SQL statement, this application is vulnerable to SQL injection attacks. For example: $ unsafe_variable = $ _ POST ['user _ input']; mysqli_query ("insert into table (column) VALUES ('". $ unsafe_variable. "')"); you can enter such as: value'); drop table table; --, the SQL statement becomes like this: INSERT INTO table (column) VALUES ('value'); drop table table? A: prepared statements (preprocessing statement) and parameterized query are used. These SQL statements are sent to the database server, and all their parameters are parsed independently. Using this method, attackers cannot inject malicious SQL statements. There are two main ways to achieve this: 1. use PDO: $ 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: $ 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 when using PDO to access the MySQL database, real prepared statements are not used by default. To solve this problem, you must disable the simulated prepared statements. The following is an example of using PDO to create a connection: $ 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 report mode is not mandatory or mandatory, but we recommend that you add it. In this way, the script will not be terminated by a fatal error when there is a problem, but will throw PDO Exceptions, which gives developers the opportunity to capture this error. However, the setAttribute () in the first line is mandatory, which enables PDO to disable simulated prepared ...... the remaining full text>

How to Prevent SQL Injection in PHP

This method is more effective and put in a public configuration file. 360safe. php
<? Php // Code By Safe3 function customError ($ errno, $ errstr, $ errfile, $ errline) {echo "<B> Error number: </B> [$ errno], error on line $ errline in $ errfile <br/> "; die ();} set_error_handler (" customError ", E_ERROR); $ getfilter =" '| (and | or) \ B. +? (>|<|=| In | like) |\/\\ *. +? \ * \/| <\ S * script \ B | \ bEXEC \ B | UNION. +? SELECT | UPDATE. +? SET | INSERT \ s + INTO. +? VALUES | (SELECT | DELETE). +? FROM | (CREATE | ALTER | DROP | TRUNCATE) \ s + (TABLE | DATABASE) "; $ postfilter =" \ B (and | or) \ B. {1, 6 }? (=|>|<|\\ Bin \ B |\\ blike \ B) |\\/ \\ *. +? \ * \/| <\ S * script \ B | \ bEXEC \ B | UNION. +? SELECT | UPDATE. +? SET | INSERT \ s + INTO. +? VALUES | (SELECT | DELETE). +? FROM | (CREATE | ALTER | DROP | TRUNCATE) \ s + (TABLE | DATABASE) "; $ cookiefilter =" \ B (and | or) \ B. {1, 6 }? (=|>|<|\\ Bin \ B |\\ blike \ B) |\\/ \\ *. +? \ * \/| <\ S * script \ B | \ bEXEC \ B | UNION. +? SELECT | UPDATE. +? SET | INSERT \ s + INTO. +? VALUES | (SELECT | DELETE). +? FROM | (CREATE | ALTER | DROP | TRUNCATE) \ s + (TABLE | DATABASE) "; function StopAttack ($ StrFiltKey, $ StrFiltValue, $ ArrFiltReq) {if (is_array ($ StrFiltValue) {$ StrFiltVa ...... remaining full text>