PDO's mechanism to prevent sql injection
When using PDO to access the MySQL database, real prepared statements are not used by default. To solve this problem, you must disable the emulation effects of prepared statements. Here is an example of using PDO to create a link:
The code is as follows:
$dbh = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass'); $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
setAttribute() This line is mandatory and will tell PDO to disable impersonation Prepare statements and use real parepared statements. This ensures that the SQL statement and corresponding values are not parsed by PHP before being passed to the mysql server (disabling all possible malicious SQL injection attacks). Although you can set the character set attribute (charset=utf8) in the configuration file, it is important to note that older versions of PHP (< 5.3.6) ignore character parameters in DSN.
Let’s take a look at a complete code usage example:
The code is as follows:
$dbh = new PDO("mysql:host=localhost; dbname=dbtest", "user", "pass");
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); //禁用prepared statements的仿真效果
$dbh->exec("set names 'utf8'");
$sql="select * from test where name = ? and password = ?";
$stmt = $dbh->prepare($sql);
$exeres = $stmt->execute(array($testname, $pass));
if ($exeres) {
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
print_r($row);
}
}
$dbh = null;
The above paragraph The code can prevent sql injection. why?
When prepare() is called, the query statement has been sent to the database server. At this time, only the placeholder? is sent, and there is no data submitted by the user; When execute() is called, the values submitted by the user will be transmitted to the database. They are transmitted separately. The two are independent, and SQL attackers have no chance.
But what we need to pay attention to is the following situations. PDO cannot help you prevent SQL injection
1. You cannot let placeholders? replace a set of values , such as:
The code is as follows:
SELECT * FROM blog WHERE userid IN ( ? );
2. You cannot let placeholders replace the data table name or column name, such as:
The code is as follows:
SELECT * FROM blog ORDER BY ?;
3. You cannot let the placeholder ? replace any other SQL syntax, such as:
The code is as follows:
SELECT EXTRACT( ? FROM datetime_column) AS variable_datetime_element FROM blog;
The above is the content of PDO’s mechanism to prevent SQL injection. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
PHP and PDO: How to perform bulk inserts and updates
Jul 28, 2023 pm 07:41 PM
PHP and PDO: How to perform batch inserts and updates Introduction: When using PHP to write database-related applications, you often encounter situations where you need to batch insert and update data. The traditional approach is to use loops to perform multiple database operations, but this method is inefficient. PHP's PDO (PHPDataObject) provides a more efficient way to perform batch insert and update operations. This article will introduce how to use PDO to implement batch insert and update operations. 1. Introduction to PDO: PDO is PH
PHP and PDO: How to perform paging queries and display data
Jul 29, 2023 pm 04:10 PM
PHP and PDO: How to query and display data in pages When developing web applications, querying and displaying data in pages is a very common requirement. Through paging, we can display a certain amount of data at a time, improving page loading speed and user experience. In PHP, the functions of paging query and display of data can be easily realized using the PHP Data Object (PDO) library. This article will introduce how to use PDO in PHP to query and display data by page, and provide corresponding code examples. 1. Create database and data tables
PHP PDO vs. mysqli: compare and contrast
Feb 19, 2024 pm 12:24 PM
PDOPDO is an object-oriented database access abstraction layer that provides a unified interface for PHP, allowing you to use the same code to interact with different databases (such as Mysql, postgresql, oracle). PDO hides the complexity of underlying database connections and simplifies database operations. Advantages and Disadvantages Advantages: Unified interface, supports multiple databases, simplifies database operations, reduces development difficulty, provides prepared statements, improves security, supports transaction processing Disadvantages: performance may be slightly lower than native extensions, relies on external libraries, may increase overhead, demo code uses PDO Connect to mysql database: $db=newPDO("mysql:host=localhost;dbnam
PHP and PDO: How to perform a full-text search in a database
Jul 30, 2023 pm 04:33 PM
PHP and PDO: How to perform a full-text search in a database In modern web applications, the database is a very important component. Full-text search is a very useful feature when we need to search for specific information from large amounts of data. PHP and PDO (PHPDataObjects) provide a simple yet powerful way to perform full-text searches in databases. This article will introduce how to use PHP and PDO to implement full-text search, and provide some sample code to demonstrate the process. first
PHP and PDO: How to handle JSON data in a database
Jul 29, 2023 pm 05:17 PM
PHP and PDO: How to handle JSON data in databases In modern web development, processing and storing large amounts of data is a very important task. With the popularity of mobile applications and cloud computing, more and more data are stored in databases in JSON (JavaScript Object Notation) format. As a commonly used server-side language, PHP's PDO (PHPDataObject) extension provides a convenient way to process and operate databases. Book
PHP and PDO: How to perform database backup and restore operations
Jul 29, 2023 pm 06:54 PM
PHP and PDO: How to perform database backup and restore operations When developing web applications, database backup and restore are very important tasks. As a popular server-side scripting language, PHP provides a wealth of libraries and extensions, among which PDO (PHP Data Objects) is a powerful database access abstraction layer. This article will introduce how to use PHP and PDO to perform database backup and restore operations. Step 1: Connect to the database Before actual operation, we need to establish a connection to the database. Use PDO pair
Detection and repair of PHP SQL injection vulnerabilities
Aug 08, 2023 pm 02:04 PM
Overview of detection and repair of PHP SQL injection vulnerabilities: SQL injection refers to an attack method in which attackers use web applications to maliciously inject SQL code into the input. PHP, as a scripting language widely used in web development, is widely used to develop dynamic websites and applications. However, due to the flexibility and ease of use of PHP, developers often ignore security, resulting in the existence of SQL injection vulnerabilities. This article will introduce how to detect and fix SQL injection vulnerabilities in PHP and provide relevant code examples. check
Laravel Development Notes: Methods and Techniques to Prevent SQL Injection
Nov 22, 2023 pm 04:56 PM
Laravel Development Notes: Methods and Techniques to Prevent SQL Injection With the development of the Internet and the continuous advancement of computer technology, the development of web applications has become more and more common. During the development process, security has always been an important issue that developers cannot ignore. Among them, preventing SQL injection attacks is one of the security issues that requires special attention during the development process. This article will introduce several methods and techniques commonly used in Laravel development to help developers effectively prevent SQL injection. Using parameter binding Parameter binding is Lar
