Fully block SQL injection attacks in PHP

Attack in this series, we will explore comprehensively how to block SQL injection attacks in the PHP development environment and give a specific development example.

　　 first, the introduction

PHP is a powerful but fairly easy to learn server-side scripting language that even inexperienced programmers can use to create complex, dynamic Web sites. However, it often has many difficulties in achieving the secrecy and security of Internet services. In this series, we'll introduce the security background and PHP-specific knowledge and code that are necessary for web development-you can protect your own Web application's security and consistency. First, let's briefly review server security issues-show you how to access private information in a shared hosting environment, get developers off the production server, maintain the latest software, provide encrypted channels, and control access to your system.

We then discuss the pervasive vulnerabilities in the PHP script implementation. We will 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 installment, we will implement a secure Web application. You will learn how to authenticate users, authorize and track application use, avoid data loss, securely execute highly risky system commands, and be able to safely use Web services. Whether you have enough PHP security development experience, this series of articles will provide a wealth of information to help you build a more secure online application.

　　 second, what is SQL injection

If you're going to never use some data, it makes no sense to store them in a database because the database is designed to facilitate access and manipulation of data in the database. However, if you simply do this, it can lead to potential disaster. This is not primarily because you may accidentally delete everything in your database, but because when you try to accomplish an "innocent" task, you are likely to be "hijacked" by someone-using his own destructive data to replace your own data. We call this substitution "injection".

In fact, whenever you require user input to construct a database query, you are allowing the user to participate in building a command to access the database server. A friendly user may feel satisfied with the implementation of such an operation, however, a malicious user will try to find a way to distort the command, causing the distorted command to delete data and even more dangerous things. As a programmer, your task is to find a way to avoid such malicious attacks.

　　 three, SQL injection working principle

Constructing a database query is a very straightforward process. Typically, it will follow the following ideas. Just to illustrate the problem, we will assume that you have a wine database table "Wines", which has a field of "variety" (i.e. wine type):

1. Provide a form-allows users to submit certain content to search for. Let's assume that the user chooses to search for a wine of type "Lagrein".

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

$variety = $_post[' variety '];

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

Lagrein

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

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

Therefore, 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-where the value of the field variety is "Lagrein".
So far, this should be a familiar and very relaxing process. Unfortunately, sometimes the processes we are familiar with and feel comfortable with can easily lead to complacency. Now, let's re-examine the query we just built.

1. The fixed portion 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. Use the original invariant part and the value that contains the user-submitted variable:

$query. = $variety;

3. Then, you use another single quotation mark to connect to this result-to describe the end of the variable value:

$ query. = "'";

Thus, the value of the $query is as follows:

SELECT * FROM wines WHERE variety = ' Lagrein '

The success of this construct relies on the input of the user. In this example, you are using a single word (or maybe a group of words) to indicate a wine type. As a result, the query is built without any problems, and the result will be what you would expect-a wine list with a wine type of "Lagrein". Now, let's imagine that, since your users are not typing a simple type of wine type "Lagrein," they have entered the following (note that includes two punctuation marks):

Lagrein ' or 1=1;

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

SELECT * FROM wines WHERE variety = '

You then connect to it using the value of the variable containing the user's input (here, in bold):

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

Finally, add the following quote below:

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

The results of this query will be quite different from what you expect. In fact, your query now contains not one but two instructions, because the last semicolon entered by the user has ended the first instruction (record selection) and started a new instruction. In this case, the second instruction is meaningless except for a simple single quotation mark, but the first instruction is not what you want to achieve. When the user puts a single quote in the middle of his input, he ends the value of the desired variable and introduces another condition. So instead of retrieving records that are variety as "Lagrein", you are retrieving records that meet either of the two criteria (the first one is yours, and the second is his-variety "Lagrein" or 1 equals 1). Since 1 is always 1, therefore, you will retrieve all the records!

You might disagree: I don't use double quotes to replace single quotes to describe a user-submitted variable? Yes, this can at least slow down attacks by malicious users. (In previous articles, we warned you that all error notification information should be blocked for users.) If an error message is generated here, it may help the attacker precisely-providing a specific explanation of why his attack failed. )

In practice, enabling your users to see all of the records rather than just part of it at first glance seems less cumbersome, but in fact, it was a lot of trouble to see that all the records could easily provide him with the internal structure of the form, and thus provide him with an important reference to the later realization of a more vicious purpose. If your database does not contain information such as apparently harmless wines but contains a list of employees ' yearly income, for example, then it would be particularly true to describe the situation.

And from a theoretical point of view, this attack is indeed a very scary thing. By injecting unexpected content into your query, this user is able to convert your database access to use for his own purposes. So now, your database has been opened to him-just as open to you.

　　Four, PHP and MySQL injection

As we described earlier, PHP, from its own design, did not do anything special-except in accordance with your instructions. Therefore, if it is used by a malicious user, it will simply "allow" specially designed attacks as required, as we have described earlier.

We will assume that you will not intentionally or even accidentally construct a destructive database query-so we assume the problem is in terms of input from your users. Now, let's take a closer look at the various ways in which users might provide information to your scripts.

　　V. Types of user input

Today, the behavior of users who can influence your scripts has become increasingly complex.

The most obvious source of user input is, of course, a text entry field on the form. Using a domain like this, you are deliberately abetting a user to enter arbitrary data. Also, you provide users with a large input range; there is no way to allow you to limit the type of data that a user can enter in advance (although you can choose to limit its length). This is why most of the injection-type attacks originate from the Defenseless form field.

However, there are other sources of attack, and a little thought you will think of a hidden in the form of the background of the technical-post Method! By simply analyzing the URI displayed in the browser's navigation toolbar, an observant user can easily see what information is passed to a script. Although typically such URIs are generated programmatically, there is no way to prevent a malicious user from simply entering a URI with an improper value of a variable into a browser-and potentially opening a database that might otherwise be misused.

A common strategy for restricting user input is to provide a selection box, not an input box, in a form. Such controls can force users to select from a set of predefined values and to some extent prevent users from entering content that is not expected. But just as an attacker could "trick" a URI (that is, to create a trusted but invalid URI, he might imitate creating your form and its own version, and therefore use illegal rather than predefined security selections in the Options box. This is extremely easy to achieve; he only needs to look at the source and then cut and paste the form's source code-and then it opens the door for him.

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