Analysis Summary of Comprehensive Prevention of SQL Injection Attacks in PHP_PHP Tutorial

1. Introduction

PHP is a powerful but easy-to-learn server-side scripting language. Even programmers with little experience can use it to create complex and dynamic web site. However, it often has many difficulties in realizing the confidentiality and security of Internet services. In this series of articles, we will introduce readers to the security background necessary for web development as well as PHP-specific knowledge and code - you can use it to protect the security and consistency of your own web applications. First, we briefly review server security issues - showing how you can access private information in a shared hosting environment, keep developers off production servers, maintain up-to-date software, provide encrypted channels, and control access to your systems. access.

We then discuss common vulnerabilities in PHP script implementations. We'll explain how to protect your scripts from SQL injection, prevent cross-site scripting and remote execution, and prevent "hijacking" of temporary files and sessions.

In the last article, we will implement a secure web application. You'll learn how to authenticate users, authorize and track application usage, avoid data loss, safely execute high-risk system commands, and use web services securely. Whether you have sufficient experience in PHP security development or not, this series of articles will provide a wealth of information to help you build more secure online applications.

2. What is SQL injection

If you plan to never use certain data, then there is no point in storing it in a database; because the database It is designed to facilitate access and manipulation of data in the database. However, simply doing so can lead to potential disaster. This is not the case primarily because you yourself may accidentally delete everything in the database; it is because, while you are trying to complete some "innocent" task, you may be "hijacked" by someone - using himself of destructive data to replace your own data. We call this replacement "injection".

In fact, every time you ask a user for input to construct a database query, you are allowing that user to participate in constructing a command to access the database server. A friendly user might feel satisfied implementing such an operation; however, a malicious user will try to find a way to twist the command, causing the twisted command to delete data or even do something more dangerous. things. As a programmer, your task is to find a way to avoid such malicious attacks.

3. How SQL injection works

Constructing a database query is a very straightforward process. Typically, it will be implemented along the following lines. Just to illustrate the problem, we will assume that you have a wine database table "wines" with a field "variety" (i.e. wine type):

　1. Provide a form - allow the user to submit certain requirements What to search for. Let's assume the user chooses to search for wines of type "lagrein".

2. Retrieve the user's search term and save it - by assigning it to a variable like this:

Here is the code snippet:

$variety = $_POST['variety'];

Therefore, the value of variable $variety is now:

lagrein

3. Then, use this variable in the WHERE subsection Construct a database query in the sentence:

The following is the code snippet:

$query = "SELECT * FROM wines WHERE variety='$variety'";

Therefore, variables The value of $query now looks like this:

The following is the code snippet:

SELECT * FROM wines WHERE variety='lagrein'

4. Submit the query to MySQL server.

5. MySQL returns all records in the wines table - among them, the value of field variety is "lagrein".

By now, this should be a familiar and very easy process. Unfortunately, sometimes the processes we are familiar with and comfortable with can easily lead to complacency. Now, let's reanalyze the query we just constructed.

1. The fixed part of the query you create ends with a single quote, which you will use to describe the beginning of the variable value:

Here is the code snippet:

$query = " SELECT * FROM wines WHERE variety = '";

　2. Use the original fixed part and contain the value of the variable submitted by the user:

　The following is a code snippet :

$query .= $variety;

3. You then use another single quote to concatenate this result - describing the end of the variable value:

Here is Code snippet:

$ query .= "'";

Therefore, the value of $query is as follows:

The following is a code snippet:

SELECT * FROM wines WHERE variety = 'lagrein'

　 The success of this construct relies on user input. In this example, you are using a single word (or possibly a group of words) to specify a type of wine.Therefore, the query is constructed without any problems, and the result is what you would expect - a list of wines with the wine type "lagrein". Now, let's imagine that instead of entering a simple wine type of type "lagrein", your user enters the following (note the two punctuation marks included):

　lagrein' or 1=1;

Now, you continue to construct your query using the previously fixed parts (here, we only show the result value of the $query variable):

SELECT * FROM wines WHERE variety = '

You then concatenate it with the value of the variable containing the user input (shown here in bold):

SELECT * FROM wines WHERE variety = 'lagrein ' or 1=1;

Finally, add the lower quotes:

SELECT * FROM wines WHERE variety = 'lagrein' or 1=1;'

So , the query results will be quite different from what you expect. In fact, your query now contains not one but two instructions, because the final semicolon entered by the user has ended the first instruction (performing record selection) and started a new instruction. In this case, the second instruction means nothing other than a simple single quote; however, the first instruction is not what you want to implement either. When the user puts a single quote in the middle of his input, he ends up expecting the value of the variable and introduces another condition. So instead of retrieving those records whose variety is "lagrein", we are retrieving records that satisfy either of two criteria (the first is yours and the second is his - variety is "lagrein" or 1 Records equal to 1). Since 1 is always 1, therefore, you will retrieve all records!

You may object: Wouldn’t I use double quotes instead of single quotes to describe user-submitted variables? Yes, this can at least reduce Slow attacks by malicious users. (In a previous article, we reminded you that all error notification messages to the user should be disabled. If an error message is generated here, it may just help the attacker - provide an explanation as to why his attack failed. Specific explanation. )

In practice, enabling your users to see all records rather than just some of them may seem like a hassle at first, but in reality, it does; see Retrieving all the records would easily provide him with information about the internal structure of the table, thus providing him with an important reference for further more nefarious purposes. The situation just described is especially true if your database contains, instead of the apparently innocuous information about alcohol, a list of employees' annual earnings.

From a theoretical perspective, this kind of attack is indeed a terrible thing. By injecting unexpected content into your query, this user is able to transform your database access for his own purposes. So now, your database is open to him - as it is to you.

IV. PHP and MySQL Injection

As we described earlier, PHP, by design, does not do anything special - except follow your instructions beyond the instructions. Therefore, if used by a malicious user, it would only "allow" specially designed attacks - such as the one we described earlier - as required.

We're going to assume that you don't intentionally or even accidentally construct a database query that has destructive effects - so we're going to assume that the problem is with the input from your users. Now, let's take a closer look at the various ways users might provide information to your script.

5. Types of user input

Today, the ways users can influence the behavior of your scripts have become increasingly complex.

The most obvious source of user input is, of course, a text input field on a form. By using a field like this, you are literally encouraging a user to enter arbitrary data. Furthermore, you provide the user with a large input range; there is no way for you to limit in advance the type of data a user can enter (although you can choose to limit its length). This is why the vast majority of injection attacks originate from unsuspecting form fields.

However, there are other sources of attacks, and if you think about it for a moment, you will think of a technology hidden behind the form - the POST method! By simply analyzing the content displayed in the browser's navigation toolbar URI, an observant user can easily see what information is passed to a script. Although such URIs are typically generated programmatically, there is nothing to prevent a malicious user from simply entering a URI with an inappropriate variable value into a browser - and potentially Opens a database that could be abused.

A common strategy for limiting user input is to provide a select box in a form instead of an input box. This type of control can force the user to choose from a predefined set of values ​​and can prevent the user from entering unexpected content to a certain extent.But just as an attacker might "spoof" a URI (i.e., create one that mimics a trustworthy but invalid URI), he might also be able to mimic your form and create his own version of it, and therefore create a new version of the URI in the option box. Use illegal instead of predefined safe options. To achieve this is extremely simple; he just needs to look at the source code, then cut and paste the source code of the form - and then everything opens up for him.

After modifying the selection, he will be able to submit the form and his invalid commands will be accepted as if they were the original commands. Therefore, the user can use many different methods to attempt to inject malicious code into a script.