How to prevent sql injection in php?

SQL injection (SQLi) is an injection attack that can execute malicious SQL statements. It gives attackers complete control over the database server behind a web application by inserting arbitrary SQL code into database queries.

An attacker can use SQL injection vulnerabilities to bypass application security measures; can bypass authentication and authorization of a web page or web application, and retrieve the contents of the entire SQL database ; SQL injection can also be used to add, modify and delete records in the database.

How to prevent sql injection in php?

1. When are you most vulnerable to SQL injection attacks?

When the application uses input content to construct dynamic SQL statements to access the database SQL injection attacks will occur

2. How to prevent SQL injection

a.Never trust user input and verify user input , you can use regular expressions or limit the length to convert single quotes and "_"

Deprecated:

//$user = htmlspecialchars(addslashes($user));
 //$pwd = htmlspecialchars(addslashes($pwd));
 //$yzm = htmlspecialchars(addslashes($yzm));

Should be used:

$user = htmlspecialchars(addslashes($user));
$pwd = htmlspecialchars(addslashes($pwd));
$yzm = htmlspecialchars(addslashes($yzm));

b, Never use dynamic assembly of SQL, you can use parameterized SQL or directly use stored procedures for data query and access

Not recommended:

// $sql = "select * from user where user='$user' and pwd ='$pwd'";

Should be used :

$sql = "select * from user where user=? and pwd =?";

c, Never use a database connection with administrator privileges, use a separate data connection with limited permissions for each application

$conn = @new mysqli('localhost','root','','myschool');
if($conn->connect_error){
die('连接数据库失败');
}
 
$conn->set_charset('utf8');

d, Do not store confidential information directly, encrypt or HASH passwords and sensitive information

e, The detection method of QL injection is generally to use auxiliary software or website platform detection, software Generally, the SQL injection detection tool jsky is used. The website platform has Yisi. The website platform detection tool

3 and php mysqli extension preprocessing

are often used in mysqli operations. Involving its three main classes: MYSQLI class, MYSQL_STMT class, MYSQLI_RESULT class, preprocessing is mainly completed using the MYSQL_STMT class.

Preprocessing is an important means to prevent SQL injection and is of great significance to improving website security. The working principle of preprocessing statements:

(1) Preprocessing: Create a SQL statement template and send it to the database. The reserved values ​​are marked with parameters?

Example: insert into myguests(firstname,lastname,email) values ​​(?,?,?)

(2) Database analysis, compilation, query optimization on SQL statement template, and storage The result is not output

if ($stmt = $conn->prepare($sql)) {
 
$stmt -> bind_param("ss",$user,$pwd);
$stmt -> execute();
$stmt -> store_result();
    //$res = $conn ->query($sql);
  if ($stmt->num_rows==0) {
show_error('输入的用户名或密码错误','demo1.html');
   }
   $stmt -> close();
}
$conn ->close();

(3) Execution. Finally, the value bound by the application is passed to the parameter ("?" mark). The database executes the statement. The application can execute the statement multiple times if the value of the parameter is different.

For more PHP related knowledge, please visit PHP Chinese website!

The above is the detailed content of How to prevent sql injection in php?. For more information, please follow other related articles on the PHP Chinese website!