Detailed explanation of MySQL anti injection function using PDO in PHP


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" >

The background processing is as follows:

  //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;
  //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
$sql="select * from login WHERE username={$username} AND password='{$password}' ";
echo $sql;
//The rowcount () method returns the number of results or the number of affected rows
  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";
//The query statement is executed through the statement object and assigned to the placeholder in the query statement in the form of array
echo $stmt->rowCount();

The placeholder can also be?

//Placeholder is?
$sql="select * from login WHERE username=? AND password=?";
//The order of the parameters in the array must be the same as that of the question marks in the query statement
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)";
//The third parameter can specify the type of parameter PDO:: param_ STR is a string, PDO:: param_ Int is an integer
//Use the bindvalue() method to bind a fixed value
$stmt->bindValue(":mail",'[email protected]');
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->bindValue(3,'[email protected]'); 
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';
  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.