Keep a record of how to learn PDO technology to prevent SQL injection

Recently learned how to use PDO technology to prevent SQL injection, in the blog as a note. If there is a new sentiment to add a pen, after all, is just beginning to learn.
One, what is PDO

PDO全名PHP Data ObjectPHP 数据对象 （PDO） 扩展为PHP访问数据库定义了一个轻量级的一致接口。PDO 提供了一个数据访问抽象层，这意味着，不管使用哪种数据库，都可以用相同的函数（方法）来查询和获取数据。

second, how to use PDO to prevent SQL injection ?
/
Protect against SQL injection use the Quate () method to filter special characters and to prevent SQL injection by using preprocessing methods and Bindparameter () method binding parameters
/
Third, use the Quate () method to prevent SQL injection.

Quate()方法返回带引号的字符串，过滤特殊字符

1, first create a login interface to submit user name and password.

</!DOCTYPE html>

2. Then create a background processing PHP file, get the user name and password, and query in the database. The basic Query method to query.

<?phpheader(‘content-type=text/html;charset=utf-8‘);$username=$_POST[‘username‘];$password=$_POST[‘password‘];try{ $pdo=new PDO(‘mysql:host=localhost;dbname=pdotest‘,‘root‘,‘123‘); $sql="select * from user where name=‘{$username}’ and password=‘{$password}’"; echo $sql."</br>"; $row=$pdo->query($sql); foreach ($row as $key => $value) { print_r($value); }}catch(POOException $e){ echo $e->getMessage();}

This processing interface is a SQL-injected interface.
3 . Let's test it.
(1) Enter the correct user name and password in the text box to return to the normal information

But when we do inject, the information returned is:
(2) User name input ' or 1=1 #, enter the password at random

The user information in the database is displayed in the form of an array.
(3) We execute this string of query statements in the database

As you can see, the database is shown as usual, which is also a great hazard to SQL injection.
4. Next we use the Quate () method to prevent SQL injection
Using the following code, the object of new $pdo is returned by calling the Quate () method to return the user-entered string, and then querying the data in the database by querying the statement.

<?phpheader(‘content-type=text/html;charset=utf-8‘);$username=$_POST[‘username‘];$password=$_POST[‘password‘];try{ $pdo=new PDO(‘mysql:host=localhost;dbname=pdotest‘,‘root‘,‘123‘); $username=$pdo->quote($username); $password=$pdo->quote($password); $sql="select * from user where name=‘{$username}‘ and password=‘{$password}‘"; echo $sql."</br>"; $row=$pdo->query($sql); foreach ($row as $key => $value) { print_r($value); }}catch(POOException $e){ echo $e->getMessage();}

5, we use the same method to test if we can prevent SQL injection?
(1) Correct user name and correct password authentication:

(2) Correct user name and wrong password access

Cannot query data
(3) Using SQL injection:

Query failed, we can clearly see the quotation marks we entered in the front automatically added ' \ ', the quotation marks to escape, lost the original effect.
(4) At the same time we execute this statement in the database.

Query error. So the Quate () method can effectively prevent SQL injection
Iv. using preprocessing statements to prevent SQL injection
A placeholder form in a preprocessing statement to prevent SQL injection. There are two forms of placeholders, one by naming parameters and the other by greeting placeholders
1. Prevent injection by naming parameters
(1) First, the original base of the source code to correct the query statement. Switch
Select * from where Name=:username and password=:p assword
The entire source code:

<?phpheader(‘content-type:text/html;charset=utf-8‘);$username=$_POST[‘username‘];$password=$_POST[‘password‘];try{ $pdo=new PDO(‘mysql:host=localhost;dbname=pdotest‘,‘root‘,‘123‘); $sql=‘select * from user where name=:username and password=:password‘; $stmt=$pdo->prepare($sql); $stmt->execute(array(":username"=>$username,":password"=>$password)); echo $stmt->rowCount();}catch(PDOException $e){ echo $e->getMessage();}?>

Explain:
A): Name User name parameter: username password: password.
b): By calling the RowCount () method to view the number of rows returned by the SQL statement, the execution of the 0 statement fails, greater than or equal to 1, and the statement execution succeeds.
Test:
(1) Normal access: User name Zhangsan, password: 123

(2) Error password access:

(3) SQL injection statement access:

Prevent injection failure

2, through the question mark (? ) placeholder prevents injection
(1) Modify the SQL query statement:
Select * from user where name=? and password=?
Full code:

<?header(‘content-type:text/html;charset=utf-8‘);$username=$_POST[‘username‘];$password=$_POST[‘password‘];try{ $pdo=new PDO(‘mysql:host=localhost;dbname=pdotest‘,‘root‘,‘123‘); $sql="select * from user where name=? and password=?"; $stmt=$pdo->prepare($sql); $stmt->execute(array($username,$password)); echo $stmt->rowCount();}catch(PDOException $e){ echo $e->getMessage();}?>

Explanation: Use the Execute () method to pass an array of arrays ($username, $password) directly to the SQL statement query.
Test:
(1) Normal access: User name: Lisi, Password: ABC

(2) Incorrect user name or password access

(3) SQL injection:

Injection failure
The Bindparam () method binds the parameters to prevent SQL injection.

<?phpheader(‘content-type:text/html;charset=utf-8‘);$username=$_POST[‘username‘];$password=$_POST[‘password‘];try{ $pdo=new PDO(‘mysql:host=localhost;dbname=pdotest‘,‘root‘,‘123‘); $sql=‘select * from user where name=:username and password=:password‘; $stmt=$pdo->prepare($sql); $stmt->bindParam(":username",$username,PDO::PARAM_STR); $stmt->bindParam(":password",$password,PDO::PARAM_STR); $stmt->execute(); echo $stmt->rowCount();}catch(PDOException $e){ echo $e->getMessage();}?>

(1) Key code:
$stmt->bindparam (": Username", $username, PDO::P aram_str);
$stmt->bindparam (":p assword", $password, PDO::P aram_str);

Explain:
A):: Username and: password for named parameters
b): $username; $password the variable that is obtained, that is, the user name and password.
c): PDO::P Aram_str, indicating that the value of the parameter variable must be a string, that is, the binding parameter type is a string. In the Bindparam () method, the parameter type of the default binding is the string. When you want to accept XXX, you can bind the parameter to PDO::P Aram_int.
Test:
(1) Normal access test:


(2) test SQL injection:

As a note it is also a lot of knowledge points did not write out, and then slowly add it!!!

Keep a record of how to learn PDO technology to prevent SQL injection