How do I prevent SQL injection in PHP? , PHP how SQL injection _php tutorial

How do I prevent SQL injection in PHP? , PHP how SQL injection

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 ' => $ Name foreach ( $stmt as Code class= "PHP variable" > $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 to prevent SQL injection in PHP

I translated the question and the answer that I agree to the most. Question: If the user's input can be inserted directly into the SQL statement, then the application will be susceptible to SQL injection attacks, for example: $unsafe _variable = $_post[' user_input '); Mysqli_query ("INSERT into table (column) VALUES (')." $unsafe _variable. "')"); user can enter such as: value '); DROP table table,--, SQL statement: INSERT into TABLE (column) VALUES (' value '); DROP table table;--') (Translator Note: The result of this is to delete table table) What can we do to stop this situation? Answer: Use prepared statements (preprocessing statements) and parameterized queries. These SQL statements are sent to the database server, and its parameters are all parsed separately. Using this approach, it is impossible for an attacker to inject malicious SQL. There are two main ways to achieve this: 1. Using PDO: $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: $stmt = $dbConnection->prepare (' SELECT * FROM employees WHERE name =? '); $stmt->bind_param (' s ', $name); $stmt->execute (); $result = $stmt->get_result (); while ($row = $result->fetch_assoc ()) {//does something with $row}pdo It is important to note that when using PDO to access the MySQL database, the real prepared statements default Case is not used. To solve this problem, you need to disable the simulated prepared statements. Here is an example of creating a connection using PDO: $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 reporting pattern is not mandatory and necessary, but it is recommended that you add it. In this way, the script will not be terminated by a fatal error at the time of the problem, but it will throw the PDO Exceptions, which gives the developer the chance to catch the error. However, the first line of SetAttribute () is mandatory, which enables PDO to disable the impersonation of the prepared ... Remaining full text >>

How to prevent SQL injection in PHP

A more efficient way to put it in a public profile. 360safe.php
Error number:</b> [$errno],error on line $errline in $errfile
"; 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 >>

http://www.bkjia.com/PHPjc/904705.html www.bkjia.com true http://www.bkjia.com/PHPjc/904705.html techarticle How do I prevent SQL injection in PHP? , PHP How SQL injection problem Description: If the user input data is inserted into an SQL query statement without processing, then the application will be very ...

