Analysis of SQL preprocessing principle and implementation method of PHP tips to prevent SQL injection

Time:2021-11-30

This paper describes the SQL preprocessing principle and implementation method of PHP tips to prevent SQL injection. Share with you for your reference, as follows:

We can think of SQL preprocessing as a compiled template of the SQL we want to run, which can be customized using variable parameters.

Let’s look at the benefits:

  • The preprocessing statement greatly reduces the analysis time and makes only one query (although the statement is executed many times).
  • Binding parameters reduces the server bandwidth. You only need to send the parameters of the query, not the whole statement.
  • Preprocessing statements are very useful for SQL injection, because different protocols are used after parameter values are sent to ensure the legitimacy of data.

This preprocessing can be done in two ways. This time we want to talk about mysqli. It ensures that applications can use the same data access mode at any time, which is more practical than PDO.

There are two kinds of preprocessing statements, one is DML statement, the other is DQL statement. Let’s start with the first one:


<?php
header('Content-type:text/html;charset=utf-8');
$mysqli = new mysqli("127.0.0.1","root","root","test");
$mysqli->query('set names utf8');
$insert = $mysqli->prepare("insert admins (title,cookies,sta,lid) values (?,?,?,?)");
$title = "cuijinpeng";
$cookies = "luyaran201314";
$sta = "1";
$lid = 1;
$insert->bind_param("sssi",$title,$cookies,$sta,$lid);
$res = $insert->execute();
if($res){
  echo 1;
}else{
  echo $insert->error;
  echo 0;
}
$insert->close();
$mysqli->close();

For the second, the code is as follows:


<?php
header('Content-type:text/html;charset=utf-8');
$mysqli = new mysqli("127.0.0.1","root","root","test");
$mysqli->query('set names utf8');
$select = $mysqli->prepare("select id,title,cookies,sta,lid from admins where id > ?");
$id = "1";
$select->bind_param("i",$id);
$select->bind_result($id,$title,$cookies,$sta,$lid);
$select->execute();
while ($select->fetch()) {
  echo $id."---".$title."---".$cookies."---".$sta."---".$lid."<br>";
}
$select->close();
$mysqli->close();

Next, let’s take a look at what kind of SQL these two statements support respectively.

The first one supports three types of SQL: insert, update and delete. The second one is query statements.

After that, bind_ The I in param is the type of the parameter we passed in. The details are as follows:

  • I – integer
  • D – double (double precision floating point)
  • S – string (string)
  • B – blob (binary large object)

Each parameter we pass in needs to specify a class, which can reduce the risk of SQL injection by telling the data type of database parameters.

Well, that’s all for this record.

For more information about PHP, readers who are interested can see the topics on this site: PHP programming security tutorial, summary of PHP security filtering skills, summary of PHP operation and operator usage, introduction to PHP basic syntax, introduction to PHP object-oriented programming, summary of PHP string usage, and introduction to PHP + MySQL database operation And “PHP common database operation skills summary”

I hope this article will help you in PHP programming.