One of the complete bans on SQL injection attacks in PHP

In this series of articles, we will comprehensively discuss how to completely prohibit SQL injection attacks in the PHP development environment, and give a specific development example.

　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 dynamic web sites. However, it often has many difficulties in achieving 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, keep software up to date, provide encrypted channels, and control access to your systems. access.

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

In the last article, we will implement a secure web exploit. You'll learn how to authenticate users, authorize and track applications, avoid data loss, securely 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 them in a database; because the design goal of the database is to easily access and manipulate the database The data. However, if you do this simply, it may lead to potential disasters. This situation is not important 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 - the application himself corrupted data to replace your own data. We call this substitution 'injection'.

In fact, every time you ask the user for input to construct a database query, you are allowing the user to participate in constructing a command to access the database server. A friendly user might be content to achieve such control; however, a malicious user will try to invent 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

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

　　1. Provide a form - allow users to submit something to search Content. Let's assume the user chooses to search for wines of type 'lagrein'.

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

　$variety = $_POST['variety'];

　Therefore, the variable The value of $variety is now:

lagrein

3. Then, use this variable to structure a database query in the WHERE clause:

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

So, the value of the variable $query now looks like this:

　　SELECT * FROM wines WHERE variety='lagrein'

　　4. Submit the query to the 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 easy process. Unfortunately, sometimes processes we are familiar with and comfortable with can easily lead to feelings of pride. 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:

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

　 2. Application The original fixed part contains the value of the user-submitted variable:

　 $query .= $variety;

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

　 $ query .= ''';

　 Therefore, the value of $query is as follows:

　 SELECT * FROM wines WHERE variety = 'lagrein'

　 The success of this structure depends on the user's input. In this example, you are using a single word (or possibly a group of words) to designate a type of wine. Therefore, the query is constructed without any headers, 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 use the previously fixed parts to structure your query (here, we only show the result value of the $query variable): SELECT * FROM wines WHERE variety = '

Then, you use the containing The value of the user-entered variable is concatenated with it (here, shown in bold):

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

Finally, add lower and lower quotes:

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

　 Therefore, the results of this query will be quite different from what you expected. In fact, your query now contains not one but two instructions, because the final semicolon entered by the user has ended the first instruction (record selection) and started a new instruction. In this case, the second instruction has no meaning 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 looking at 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 That is the record of 1). Since 1 is always 1, therefore, you will retrieve all records!

You may object: Should I use double quotes instead of single quotes to describe user-submitted variables? Yes, this can at least slow down attacks from malicious users. (In a previous article, we reminded you that all error notification messages to users 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 Instructions )

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 all records. He could easily be provided with the internal structure of the table, thereby providing him with an important reference for achieving more sinister goals in the future. The situation just described would be especially true if your database contained, 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 can transform your database access into achieving his own goals. So now, your database is open to him - as it is to you.

　IV. PHP and MySQL Injection

　As we described earlier, PHP, by its own design, does not do anything special - except to operate according to your instructions. Therefore, if used by a malicious user, it would only 'agree' to a specially designed attack on request - such as the one we described earlier.

We are going to assume that you would not intentionally or even accidentally construct a database query with damaging consequences - so we will assume that the problem lies in the input from your users. Now, let's take a closer look at the various ways users can provide information to your script.

　5. Types of User Input

　 Nowadays, the actions that users can take to affect your scripts have become increasingly complex.

The most obvious source of user input is of course a text input field on a form. Using such a field, you are literally encouraging a user to enter arbitrary data. Moreover, 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 most injection attacks originate from undefended form fields.

　However, there are other sources of attacks, and if you think about it for a moment, you will think of a technique hidden in the background of the form - the POST method! By briefly analyzing the URI displayed in the browser's navigation toolbar, 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 thus lurking to open a database that could potentially be abused.

A common strategy for limiting user input is to provide a select box in a form instead of an input box. This kind of control can force the user to choose from a predefined set of values, and can prevent the user from entering content that is not visible to a certain extent. But just as an attacker can 'spoof' a URI (i.e., create a URI that mimics a trusted but invalid URI), he can also impersonate your form and create his own version of it, and therefore create a new version of the URI in the option box. Apply illegal instead of predefined safe selections. To do this is extremely simple; he just needs to look at the source code, then cut and paste the form's source code - and then the door opens up for him.

After correcting 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 try to inject malicious code into a script.

The above is one of the contents of completely banning SQL injection attacks in PHP. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!