How does PHP prevent SQL injection?

Nowadays, the actions that can affect your scripts become more and more complex.

I. INTRODUCTION

PHP is a powerful but easy-to-learn server-side scripting language. even a few experienced programmers can use it to create complex dynamic web sites. However, it often has many difficulties in realizing the secrets and security of Internet services. In this series of articles, we will introduce you to the security background necessary for web development and the specific knowledge and code of PHP-you can protect the security and consistency of your own web applications. First, let's briefly review the server security question-show you how to access the private information in a shared host environment, so that developers can leave the production server and maintain the latest software, provides encrypted channels and controls access to your system.

Then, we will discuss the common vulnerabilities in 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 article, we will implement a secure Web application. You will learn how to authenticate user identities, authorize and track application usage, avoid data loss, securely execute high-risk system commands, and securely use web services. Whether you have sufficient PHP Security development experience or not, this series of articles will provide a wealth of information to help you build more secure online applications.

II. what is SQL injection

If you plan to never use some data, it is meaningless to store them in a database, because the database is designed to facilitate the access and operation of data in the database. However, simply doing so may lead to potential disasters. This is not mainly because you may accidentally delete everything in the database, but because when you try to complete an "innocent" task, you may be "hijacked" by some people-using their own destructive data to replace your own data. We call this replace "injection ".

In fact, every time you ask the user to input and construct a database query, you are allowing the user to participate in building a command to access the database server. A friendly user may be satisfied with the operation. However, a malicious user will try to find a way to distort the command, as a result, the distorted command deletes data and even makes more dangerous tasks. As a programmer, your task is to find a way to avoid such malicious attacks.

III. how SQL injection works

Constructing a database query is a very direct process. Typically, it follows the following steps. To illustrate the problem, we will assume that you have a wine database table "wines" with a field "variety" (that is, the wine type ):

1. provide a form that allows users to submit certain content to be searched. Let's assume that you select a wine with the search type "lagrein.

2. retrieve the user's search term and save it-by assigning it to a variable as follows:

$ Variety = $ _ POST ['variety'];

Therefore, the value of $ variety is:

Lagrein

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

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

Therefore, the value of the variable $ query is as follows:

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 variety field is "lagrein ".

So far, this should be a very easy process that you are familiar. Unfortunately, sometimes the process we are familiar with and comfortable with can easily lead to complacency. Now, let's analyze the query we just created.

1. the fixed part of the query you created ends with a single quotation mark. you will use it to describe the start of the variable value:

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

2. use the original fixed part and the value that contains the variable submitted by the user:

$ Query. = $ variety;

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

$ Query. = "'";

Therefore, the value of $ query is as follows:

SELECT * FROM wines WHERE variety = 'lagrein'

The successful construction depends on the user input. In this example, you are using a single word (or a group of words) to indicate a type of wine. Therefore, there is no problem in the construction of this query, and the result will also be what you expect-a wine list with a wine type of "lagrein. Now, let us imagine that since your user does not enter a simple type of "lagrein" wine type, but enters the following content (note that two punctuation marks are included ):

Lagrein 'or 1 = 1;

Now, you continue to use the fixed section above to construct your query (here, we only display the result value of the $ query variable ):

SELECT * FROM wines WHERE variety ='

Then, you connect to the value of the variable containing the user input (shown in bold here ):

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

Finally, add the following quotation marks:

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

Therefore, the query result is quite different from your expectation. In fact, your query now contains not one but two commands, because the last semicolon entered by the user has ended the First Command (for record selection) and thus started a new command. In this example, the second instruction has no meaning except a simple single quotation mark. However, the first instruction is not what you want to implement. When a user places a single quotation mark in the middle of his input content, he ends the expected variable value and introduces another condition. Therefore, it is no longer to retrieve records whose variety is "lagrein", but to retrieve any one of the two criteria (the first one is yours, the second is his-variety is "lagrein" or 1 is equal to 1. Since 1 is always 1, you will retrieve all records!

You may disagree: I will not use double quotation marks instead of single quotation marks to describe the variables submitted by the user? Yes, it can at least reduce attacks by malicious users. (In previous articles, we reminded you that all error notifications to users should be forbidden. If an error message is generated here, it may help the attacker-provide a detailed explanation of why the attack fails .)

In practice, it seems difficult for your users to see all the records, not just some of them. but in fact, it does take a lot of time; seeing all the records can easily provide him with an internal structure of the table, thus providing him with an important reference to make it more vicious in the future. If your database does not contain information such as apparently harmless wines, but a list containing employees' annual income, the situation described above will be especially true.

From a theoretical perspective, this attack is indeed a terrible thing. Because unexpected content is injected into your query, this user can convert your database access to its own purpose. So now your database is open to him-just as it is open to you.

IV. PHP and MySQL injection

As we described earlier, PHP, in terms of its own design, has not done anything special-except for following your instructions. Therefore, for malicious users, it only allows "specially designed attacks" as required-as described above.

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

V. user input type

Nowadays, the actions that can affect your scripts become more and more complex.

The most obvious source of user input is, of course, a text input field on the form. Using such a domain, you are simply teaching a user to input arbitrary data. Moreover, you provide users with a large input range; there is no way to limit the data type that a user can enter in advance (although you can choose to limit its length ). This is why the vast majority of injection attacks come from unprotected form fields.

However, there are other attack sources, and you will come up with a POST method that is a technology that is potential in the form background! By simply analyzing the URIs displayed in the browser's navigation toolbar, an observed user can easily see what information is passed to a script. Although such a URI is generated programmatically, there is no way to prevent a malicious user from simply inputting a URI with an inappropriate variable value into a browser-which may lead to misuse of the URI. database.

A common policy that limits user input content is to provide a selection box in a form, rather than an input box. This control can force users to select from a set of predefined values and prevent users from entering the expected content to a certain extent. However, just as an attacker may "cheat" a URI (that is, creating a URI that can mimic a trusted but invalid URI, it may also mimic creating your form and its own version, and therefore use an illegal option in the option box instead of a predefined security option. To achieve this, it is extremely simple; he only needs to observe the source code, cut and paste the source code of the form-and then open the door for him.

After the selection is modified, he can submit the form, and his invalid commands will be accepted, just as they are original commands. Therefore, this user can use many different methods to try to inject malicious code into a script.