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:

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

Because the user input may be like this:

value'); DROP TABLE table;--

The SQL query will be changed to the following:

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:

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

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

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

StackOverflow Translation: bole online-rokety

How does php prevent SQL injection?

Well, this is the answer my teacher gave me.

A: Filter some common database operation keywords,
Select, insert, update, delete, and, *, or filter the content through the system function addslashes.
In the php configuration file, register_globals = off; it is set to disabled. (To disable registration of global variables). For example, if the value of the POST form is received, $ _ POST ['user'] is used. If it is set to ON, $ user receives the value.
When writing SQL statements, try not to omit small quotation marks (the one on the tab) and single quotation marks.
Improve database naming skills and name important fields based on program characteristics to make them difficult to guess
Encapsulate common methods to avoid direct leakage of SQL statements
Enable PHP security mode safe_mode = on
Enable magic_quotes_gpc to prevent SQL injection. The function is disabled by default. After it is enabled, the system automatically converts the SQL query statement submitted by the user "\'"
Control Error message output, disable error message prompts, and write error messages to system logs.
Use MYSQLI or PDO preprocessing

What is the best way to prevent SQL Injection in php?

If you input a query directly inserted into an SQL statement, the application will be vulnerable to SQL injection. For example: $ unsafe_variable = $ _ POST ['user _ input']; mysql_query ("insert into table (column) VALUES ('". $ unsafe_variable. "')"); this is because you can enter a VALUE similar to "); drop table;-To convert the query to: Use predefine statements and parameterized queries. SQL statements with any parameters will be sent to the database server and parsed! It is impossible for attackers to maliciously inject SQL statements! There are basically two options to achieve this goal: 1. use PDO (PHP Data Objects): $ 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 (PHP Data Object) note that when you use PDO to access the MySQL database, the true pre-definition statements are not used by default! To solve this problem, you must disable the statements prepared by simulation. 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 mode ERRMODE is not strictly required, but we recommend that you add it. This method does not stop when an error occurs and a fatal error occurs. And give developers the opportunity to capture any errors (when a PDOException exception is thrown ). The setAttribute () line is mandatory. It tells PDO to disable the simulation of the pre-Definition Statement and use the real pre-Definition Statement. This ensures that the statements and values are not parsed by PHP before being sent to the MySQL database server (attackers have no chance to inject malicious SQL statements ). of course, you can set character set parameters in the constructor options. Note that the 'old' PHP version (5.3.6) ignores character set parameters in DSN. The most important thing here is that this parameter value is combined with a pre-compiled statement, rather than an SQL string. the operating principle of SQL injection is that the SQL script created by deception includes malicious string sending ...... remaining full text>