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/

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.

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.

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

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

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

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

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.

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.


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

WebStorm Mac version
Useful JavaScript development tools

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
Powerful PHP integrated development environment

SublimeText3 Chinese version
Chinese version, very easy to use

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool
