Home Database Mysql Tutorial An article analyzing why SQL parameterized queries can prevent SQL injection

An article analyzing why SQL parameterized queries can prevent SQL injection

Mar 17, 2023 pm 04:09 PM
mysql 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. .

An article analyzing why SQL parameterized queries can prevent SQL injection

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;
Copy after login

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 ;
Copy after login

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. 执行并返回结果
Copy after login

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;
Copy after login

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;
Copy after login

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!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Roblox: Bubble Gum Simulator Infinity - How To Get And Use Royal Keys
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Nordhold: Fusion System, Explained
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Mandragora: Whispers Of The Witch Tree - How To Unlock The Grappling Hook
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Java Tutorial
1666
14
PHP Tutorial
1273
29
C# Tutorial
1255
24
Laravel Introduction Example Laravel Introduction Example Apr 18, 2025 pm 12:45 PM

Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.

MySQL and phpMyAdmin: Core Features and Functions MySQL and phpMyAdmin: Core Features and Functions Apr 22, 2025 am 12:12 AM

MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.

MySQL vs. Other Programming Languages: A Comparison MySQL vs. Other Programming Languages: A Comparison Apr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Solve database connection problem: a practical case of using minii/db library Solve database connection problem: a practical case of using minii/db library Apr 18, 2025 am 07:09 AM

I encountered a tricky problem when developing a small application: the need to quickly integrate a lightweight database operation library. After trying multiple libraries, I found that they either have too much functionality or are not very compatible. Eventually, I found minii/db, a simplified version based on Yii2 that solved my problem perfectly.

Laravel framework installation method Laravel framework installation method Apr 18, 2025 pm 12:54 PM

Article summary: This article provides detailed step-by-step instructions to guide readers on how to easily install the Laravel framework. Laravel is a powerful PHP framework that speeds up the development process of web applications. This tutorial covers the installation process from system requirements to configuring databases and setting up routing. By following these steps, readers can quickly and efficiently lay a solid foundation for their Laravel project.

Solve MySQL mode problem: The experience of using the TheliaMySQLModesChecker module Solve MySQL mode problem: The experience of using the TheliaMySQLModesChecker module Apr 18, 2025 am 08:42 AM

When developing an e-commerce website using Thelia, I encountered a tricky problem: MySQL mode is not set properly, causing some features to not function properly. After some exploration, I found a module called TheliaMySQLModesChecker, which is able to automatically fix the MySQL pattern required by Thelia, completely solving my troubles.

Explain the purpose of foreign keys in MySQL. Explain the purpose of foreign keys in MySQL. Apr 25, 2025 am 12:17 AM

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

Compare and contrast MySQL and MariaDB. Compare and contrast MySQL and MariaDB. Apr 26, 2025 am 12:08 AM

The main difference between MySQL and MariaDB is performance, functionality and license: 1. MySQL is developed by Oracle, and MariaDB is its fork. 2. MariaDB may perform better in high load environments. 3.MariaDB provides more storage engines and functions. 4.MySQL adopts a dual license, and MariaDB is completely open source. The existing infrastructure, performance requirements, functional requirements and license costs should be taken into account when choosing.

See all articles