Detailed explanation of MySQL anti injection function using PDO in PHP

Time:2021-11-16

This article describes how PHP uses PDO to realize MySQL anti injection function. Share with you for your reference, as follows:

1. What is injection attack

For example, the following example:

There is a submission form at the front:

<form action="test.php" method="post">
    Name: < input name = "username" type = "text" >
    Password: < input name = "password" type = "password" >
    < input type = "submit" value = "login" >
  </form>

The background processing is as follows:

<?php
  $username=$_POST["username"];
  $password=$_POST["password"];
  $age=$_POST["age"];
  //Connect to the database and create a new PDO object
  $pdo=new PDO("mysql:host=localhost;dbname=phpdemo","root","1234");
  
  $sql="select * from login WHERE username='{$username}' AND password='{$password}' ";
  echo $sql;
  $stmt=$pdo->query($sql);
  //The rowcount () method returns the number of results or the number of affected rows
  If ($stmt - > rowcount() > 0) {echo "login succeeded!"};

Under normal circumstances, if you enter the name Xiao Wang and the password Xiao Wang, the login will succeed. The SQL statement is as follows:Select * from login where username = 'Xiao Wang' and password ='xiao Wang 'Login succeeded!

However, if you enter the name ‘or 1 = 1 #, and enter any password, the login will succeed. The SQL statement is:select * from login WHERE username='' or 1=1 #' AND password='xiaowang'Login succeeded!

You can see that username = ” or 1 = 1, # comment is called to the password statement after that. Since 1 = 1 is always true, this statement will return a result set greater than 1 to pass the verification.

2. Use quote to filter special characters to prevent injection

Adding a line before the SQL statement to filter the ‘and other special characters in the username variable can prevent injection

//Through the quote method, return the quoted string and filter the special characters
$username=$pdo->quote($username);
$sql="select * from login WHERE username={$username} AND password='{$password}' ";
echo $sql;
$stmt=$pdo->query($sql);
//The rowcount () method returns the number of results or the number of affected rows
if($stmt->rowCount()>0){
  Echo "login succeeded!";
};

The SQL statement is:select * from login WHERE username='\' or 1=1 #' AND password='xiaowang'

You can see that “‘” is escaped \’, and the variable $username is automatically quoted

3. Pass parameters through preprocessing statements to prevent injection

//Pass values through placeholders: username and: password to prevent injection
$sql="select * from login WHERE username=:username AND password=:password";
$stmt=$pdo->prepare($sql);
//The query statement is executed through the statement object and assigned to the placeholder in the query statement in the form of array
$stmt->execute(array(':username'=>$username,':password'=>$password));
echo $stmt->rowCount();

The placeholder can also be?

//Placeholder is?
$sql="select * from login WHERE username=? AND password=?";
$stmt=$pdo->prepare($sql);
//The order of the parameters in the array must be the same as that of the question marks in the query statement
$stmt->execute(array($username,$password));
echo $stmt->rowCount();

4. Bind parameters through bind

The bindparam() method binds a variable to the parameter in the query statement:

$sql="insert login(username,password,upic,mail) values(:username,:password,:age,:mail)";
$stmt=$pdo->prepare($sql);
//The third parameter can specify the type of parameter PDO:: param_ STR is a string, PDO:: param_ Int is an integer
$stmt->bindParam(":username",$username,PDO::PARAM_STR);
$stmt->bindParam(":password",$password,PDO::PARAM_STR);
$stmt->bindParam(":age",$age,PDO::PARAM_INT);
//Use the bindvalue() method to bind a fixed value
$stmt->bindValue(":mail",'[email protected]');
$stmt->execute();
echo $stmt->rowCount();

Use question marks as placeholders:

$sql="insert login(username,password,mail) values(?,?,?)";// Note that it is not a question mark in Chinese? 
$stmt=$pdo->prepare($sql); // According to? Sequential binding parameter values for 
$stmt->bindParam(1,$username); 
$stmt->bindParam(2,$password); 
$stmt->bindValue(3,'[email protected]'); 
$stmt->execute(); 
echo $stmt->rowCount();

Bind a constant ‘to the third placeholder using the bindvalue() method[email protected]‘it does not change with the change of the variable.

The bindcolumn() method binds a column of the returned result set to a variable:

$sql='SELECT * FROM user';
$stmt=$pdo->prepare($sql);
$stmt->execute();
$stmt->bindColumn(2,$username);
$stmt->bindColumn(4,$email);
while($stmt->fetch(PDO::FETCH_BOUND)){
  Echo 'Username:. $username. ", email:. $email.' < HR / > ';
}

For more information about PHP, readers who are interested can see the special topics on this site: summary of PHP database operation skills based on PDO, summary of PHP + MySQL I database programming skills, introduction to PHP object-oriented programming, summary of PHP string usage, introduction to PHP + MySQL database operation and summary of common PHP database operation skills

I hope this article will help you in PHP programming.