search
HomeBackend DevelopmentPHP TutorialHow to prevent SQL injection in PHP? _PHP Tutorial

Problem description:

If the data entered by the user is inserted into a SQL query statement without processing, then the application will likely be subject to SQL injection attacks, as in the following example:

<ol class="dp-sql"><li class="alt"><span><span>$unsafe_variable = $_POST[</span><span class="string">'user_input'</span><span>];   </span></span></li><li><span>   </span></li><li class="alt"><span>mysql_query(</span><span class="string">"INSERT INTO `table` (`column`) VALUES ('"</span><span> . $unsafe_variable . </span><span class="string">"')"</span><span>);   </span></li></ol>

Because the user’s input may look like this:

<ol class="dp-sql"><li class="alt"><span><span>value'); </span><span class="keyword">DROP</span><span> </span><span class="keyword">TABLE</span><span> </span><span class="keyword">table</span><span>;</span><span class="comment">--</span><span> </span></span></li></ol>

Then the SQL query will become as follows:

<ol class="dp-sql"><li class="alt"><span><span class="keyword">INSERT</span><span> </span><span class="keyword">INTO</span><span> `</span><span class="keyword">table</span><span>` (`</span><span class="keyword">column</span><span>`) </span><span class="keyword">VALUES</span><span>(</span><span class="string">'value'</span><span>); </span><span class="keyword">DROP</span><span> </span><span class="keyword">TABLE</span><span> </span><span class="keyword">table</span><span>;</span><span class="comment">--')</span><span> </span></span></li></ol>

What effective methods should be taken to prevent SQL injection?

The best answer comes from Theo):

Use prepared statements and parameterized queries. The prepared statements and parameters are sent to the database server for parsing respectively, and the parameters will be treated as ordinary characters. This approach prevents attackers from injecting malicious SQL. You have two options to implement this method:

1. Use PDO:

<ol class="dp-sql"><li class="alt"><span><span>$stmt = $pdo-></span><span class="keyword">prepare</span><span>(</span><span class="string">'SELECT * FROM employees WHERE name = :name'</span><span>);  </span></span></li><li><span>   </span></li><li class="alt"><span>$stmt-></span><span class="keyword">execute</span><span>(array(</span><span class="string">'name'</span><span> => $</span><span class="keyword">name</span><span>));  </span></li><li><span>   </span></li><li class="alt"><span>foreach ($stmt </span><span class="keyword">as</span><span> $row) {  </span></li><li><span>    // do something </span><span class="keyword">with</span><span> $row  </span></li><li class="alt"><span>} </span></li></ol>

2. Use mysqli:

<ol class="dp-sql"><li class="alt"><span><span>$stmt = $dbConnection-></span><span class="keyword">prepare</span><span>(</span><span class="string">'SELECT * FROM employees WHERE name = ?'</span><span>);  </span></span></li><li><span>$stmt->bind_param(</span><span class="string">'s'</span><span>, $</span><span class="keyword">name</span><span>);  </span></li><li class="alt"><span>   </span></li><li><span>$stmt-></span><span class="keyword">execute</span><span>();  </span></li><li class="alt"><span>   </span></li><li><span>$result = $stmt->get_result();  </span></li><li class="alt"><span>while ($row = $result->fetch_assoc()) {  </span></li><li><span>    // do something </span><span class="keyword">with</span><span> $row  </span></li><li class="alt"><span>} </span></li></ol>
PDO

Note that using PDO by default does not allow the MySQL database to execute real prepared statements (see below). To solve this problem, you should disable PDO emulation of prepared statements. An example of correctly using PDO to create a database connection is as follows:

<ol class="dp-sql"><li class="alt"><span><span>$dbConnection = new PDO(</span><span class="string">'mysql:dbname=dbtest;host=127.0.0.1;charset=utf8'</span><span>, </span><span class="string">'user'</span><span>, </span><span class="string">'pass'</span><span>);  </span></span></li><li><span>   </span></li><li class="alt"><span>$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, </span><span class="keyword">false</span><span>);  </span></li><li><span>$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); </span></li></ol>

In the above example, the error reporting mode (ATTR_ERRMODE) is not necessary, but it is recommended to add it. In this way, when a fatal error (Fatal Error) occurs, the script will not stop running, but gives the programmer an opportunity to catch PDOExceptions so that the error can be properly handled. However, the first setAttribute() call is required, which disables PDO from simulating prepared statements and uses real prepared statements, i.e. MySQL executes prepared statements. This ensures that statements and parameters have not been processed by PHP before being sent to MySQL, which will prevent attackers from injecting malicious SQL. To understand the reason, please refer to this blog post: Analysis of PDO anti-injection principle and precautions for using PDO . Note that in older versions of PHP (silently ignored the charset parameter.

Analysis

What happens when you send a SQL statement to the database server for preprocessing and parsing? Tell the database engine where you want to filter by specifying the placeholder a ? or a :name as in the example above). When you call execute, the prepared statement will be combined with the parameter values ​​you specify. The key point is here: the parameter value is combined with the parsed SQL statement, not the SQL string. SQL injection is triggered by scripts that contain malicious strings when constructing SQL statements. So, by separating SQL statements and parameters, you prevent the risk of SQL injection. Any parameter values ​​you send will be treated as ordinary strings and will not be parsed by the database server. Going back to the above example, if the value of the $name variable is 'Sarah'; DELETE FROM employees, then the actual query will be to find records in employees where the name field value is 'Sarah'; DELETE FROM employees. Another benefit of using prepared statements is that if you execute the same statement many times in the same database connection session, it will only be parsed once, which can improve execution speed a bit. If you want to ask how to do insertion, please see the following example using PDO):

<ol class="dp-sql"><li class="alt"><span><span>$preparedStatement = $db-></span><span class="keyword">prepare</span><span>(</span><span class="string">'INSERT INTO table (column) VALUES (:column)'</span><span>);  </span></span></li><li><span>   </span></li><li class="alt"><span>$preparedStatement-></span><span class="keyword">execute</span><span>(array(</span><span class="string">'column'</span><span> => $unsafeValue)); </span></li></ol>

Translation link: http://blog.jobbole.com/67875/

www.bkjia.comtruehttp: //www.bkjia.com/PHPjc/778655.htmlTechArticleProblem description: If the data entered by the user is inserted into a SQL query statement without processing, then the application It is very likely to be subject to SQL injection attacks, as in the following example: $...
Statement
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
What is PDO in PHP?What is PDO in PHP?Apr 28, 2025 pm 04:51 PM

The article discusses PHP Data Objects (PDO), an extension for database access in PHP. It highlights PDO's role in enhancing security through prepared statements and its benefits over MySQLi, including database abstraction and better error handling.

What is Memcache and Memcached in PHP? Is it possible to share a single instance of a Memcache between several projects of PHP?What is Memcache and Memcached in PHP? Is it possible to share a single instance of a Memcache between several projects of PHP?Apr 28, 2025 pm 04:47 PM

Memcache and Memcached are PHP caching systems that speed up web apps by reducing database load. A single instance can be shared among projects with careful key management.

What are the steps to create a new database using MySQL and PHP?What are the steps to create a new database using MySQL and PHP?Apr 28, 2025 pm 04:44 PM

Article discusses steps to create and manage MySQL databases using PHP, focusing on connection, creation, common errors, and security measures.

Does JavaScript interact with PHP?Does JavaScript interact with PHP?Apr 28, 2025 pm 04:43 PM

The article discusses how JavaScript and PHP interact indirectly through HTTP requests due to their different environments. It covers methods for sending data from JavaScript to PHP and highlights security considerations like data validation and prot

How to execute a PHP script from the command line?How to execute a PHP script from the command line?Apr 28, 2025 pm 04:41 PM

The article discusses executing PHP scripts from the command line, including steps, common options, troubleshooting errors, and security considerations.

What is PEAR in PHP?What is PEAR in PHP?Apr 28, 2025 pm 04:38 PM

PEAR is a PHP framework for reusable components, enhancing development with package management, coding standards, and community support.

What are the uses of PHP?What are the uses of PHP?Apr 28, 2025 pm 04:37 PM

PHP is a versatile scripting language used mainly for web development, creating dynamic pages, and can also be utilized for command-line scripting, desktop apps, and API development.

What was the old name of PHP?What was the old name of PHP?Apr 28, 2025 pm 04:36 PM

The article discusses PHP's evolution from "Personal Home Page Tools" in 1995 to "PHP: Hypertext Preprocessor" in 1998, reflecting its expanded use beyond personal websites.

See all articles

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 Tools

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool