An article analyzing why SQL parameterized queries can prevent SQL injection

This article brings you relevant knowledge about mysql. It mainly talks about why SQL parameterized queries can prevent SQL injection. Friends who are interested can take a look below. I hope it will be helpful to everyone. .

Why can SQL parameterized queries prevent SQL injection?

1. What is SQL injection?

Insert SQL commands into the query string of form submission or input domain name or page request, tricking the server into executing malicious SQL command.

 -- 正常的查询语句
 select * from users where username = 'a';

 -- 恶意的查询语句
 select * from users where username = 'a' or 1==1;

2. What is parameterized query

Parameterized query refers to using parameters to give values ​​where data needs to be filled in when querying the database.

set @id = 1;
SELECT * from users WHERE id = @id ;

3. Execution processing of SQL statements

There are two types of SQL statements according to the processing flow: real-time SQL and preprocessing SQL.

• Real-time SQL

Real-time SQL is received from the DB and returned after the final execution is completed. The general process is as follows:

  a. 词法和语义解析
  b. 优化sql语句，制定执行计划
  c. 执行并返回结果

Features : Compile once, run once.

• Preprocessing SQL

A certain sql in the program may be called repeatedly, or only individual values ​​may be different each time it is executed. If you look at the real-time SQL process every time, the efficiency is relatively low.

At this time, you can replace the values ​​in SQL with placeholders. First generate the SQL template, and then bind the parameters. When you execute the statement repeatedly, you only need to replace the parameters without having to perform lexical and Semantic Analysis. Can be considered as SQL statement templated or parameterized.

Features: Compile once and run multiple times, eliminating multiple parsing and other processes. (Multiple runs refer to executing the same statement again in the same session, so it will not be parsed and compiled again)

  -- 语法
  # 定义预处理语句
  PREPARE stmt_name FROM preparable_stmt;
  # 执行预处理语句
  EXECUTE stmt_name [USING @var_name [, @var_name] ...];
  # 删除(释放)定义
  {DROP | DEALLOCATE} PREPARE stmt_name;

4. How does preprocessing SQL prevent SQL injection

The SQL to be executed is compiled and stored in the cache pool. When the DB executes execute, it will not compile it again. Instead, it will find the SQL template, pass the parameters to it and then execute it. Therefore, commands similar to or 1==1 will be passed as parameters and will not be semantically parsed and executed.

 -- 预处理编译 SQL ，会占用资源
 PREPARE stmt1 from 'SELECT COUNT(*) FROM users WHERE PASSWORD = ? AND user_name = ?';

 set [@a](https://learnku.com/users/16347) = 'name1 OR 1 = 1';
 set @b = 'pwd1';

 EXECUTE stmt1 USING @b,[@a](https://learnku.com/users/16347);

 -- 使用 DEALLOCATE PREPARE 释放资源
 DEALLOCATE PREPARE stmt1;

Recommended learning: "MySQL Video Tutorial"

The above is the detailed content of An article analyzing why SQL parameterized queries can prevent SQL injection. For more information, please follow other related articles on the PHP Chinese website!