Comprehensive block SQL injection attack analysis summary in PHP
- 2020-05-10 17:53:29
- OfStack
1. The introduction
PHP is a powerful but fairly easy to learn server-side scripting language that even inexperienced programmers can use to create complex and dynamic web sites. However, it often has many difficulties in implementing the secrets 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 that you can use to protect the security and integrity of your own web applications. First, we briefly review 1 server security issues - showing how you can 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 common vulnerabilities in PHP script implementations. 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'll implement a secure Web application. You'll learn how to authenticate users, authorize and track application usage, avoid data loss, safely execute risky system commands, and be able to safely use the web service. Whether or not you have enough experience with PHP security development, this series of articles provides a wealth of information to help you build more secure online applications.
2. What is SQL injection
There is no point in storing data in a database if you are never going to use it. Because the database is designed for easy access and manipulation of the data in the database. However, simply doing so can lead to potential disaster. This is not primarily because you might accidentally delete a slice from the database yourself; It's because, when you're trying to do something "innocent," you risk being hijacked by someone who USES his own destructive data to replace your own. We call this substitution "injection."
In fact, every time you ask a user for input to construct a database query, you are allowing that user to participate in building a command to access the database server. A friendly user may be satisfied with implementing such an operation; However, a malicious user will try to find a way to distort the command, causing the distorted command to delete data or do something even more dangerous. As a programmer, your job 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 is implemented along the following lines. Just for illustration purposes, we will assume that you have a wine database table "wines" with a field of "variety"(i.e., wine type) :
1. Provide a form - allow the user to submit something to search for. Let's say the user chooses a wine of type "lagrein".
2. Retrieve the user's search term and save it - by assigning it to a variable as shown below:
Here is the code snippet:
$variety = $_POST [' variety];
Therefore, the value of the variable $variety is now:
lagrein
3. Then, use this variable to construct a database query in the WHERE clause:
Here is the code snippet:
$query = "SELECT * FROM wines WHERE variety='$variety'";
So, the value of the variable $query is now as follows:
Here is the code snippet:
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 field variety is "lagrein".
By now, this should be a familiar and very relaxing process. Unfortunately, sometimes the process we are familiar with and comfortable with can lead to complacency. Now, let's re-examine the query we just built.
1. The fixed part of the query you created 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 the value of the variables submitted by the user:
Here is the code snippet:
$query. = $variety;
3. You then use another single quote to link the result - to describe the end of the value of the variable:
Here is the code snippet:
$query. = "";
Thus, the value of $query is as follows:
Here is the code snippet:
SELECT * FROM wines WHERE variety = 'lagrein'
The success of this construct depends on user input. In this example, you are using a single word (or maybe a group of words) to indicate a wine type. Therefore, the query is built without any problems, and the result will be a list of wines of type "lagrein" as you would expect. Now, let's imagine that instead of entering a simple wine type called "lagrein", your user enters the following (note the two punctuation marks) :
lagrein 'or 1 = 1;
Now, you go ahead and construct your query using the previously fixed part (here, we only show the result value of the $query variable) :
SELECT * FROM wines WHERE variety = '
You then connect it with the value of the variable containing the user's input (shown here in bold) :
SELECT * FROM wines WHERE variety = 'lagrein' or 1=1;
Finally, add the lower quotation marks above and below:
SELECT * FROM wines WHERE variety = 'lagrein' or 1=1; '
Therefore, the result of this query is quite different from what you would expect. In fact, your query now contains not one but two instructions, because the last semicolon the user entered has ended the first instruction (for record selection) and started a new one. In this case, the second instruction is meaningless except for a simple single quote; However, the first directive is not what you want to implement either. When the user places a single quote in the middle of his input, he closes the value of the expected variable and introduces another condition. Therefore, instead of retrieving records whose variety is "lagrein", you are retrieving records that satisfy either of the two criteria (the first is yours and the second is his -variety is "lagrein" or 1 is equal to 1). Since 1 is always 1, therefore, you will retrieve all records!
You may object: won't I use double quotes instead of single quotes to describe user-submitted variables? Yes, this at least slows down attacks by malicious users. (in previous articles, we warned you that all error notifications to users should be disabled. If an error message is generated here, then it might just help the attacker - providing a concrete explanation of why his attack failed.
In practice, getting your users to see all the records and not just one part of them might not seem like much at first glance, but in reality, it's a lot of work. Seeing all the records could easily have provided him with the internal structure of the form, thus providing him with an important reference to make it more malevolent in the future. This is especially true if your database contains, for example, a list of annual employee earnings, rather than information about apparently harmless drinks.
From the theoretical point of view, this kind of attack is indeed a very terrible thing. By injecting unexpected content into your query, this user is able to convert your database access to his own purposes. So now, your database is open to him - just as it is open to you.
4.PHP and MySQL injection
As we described earlier, PHP, by design, does nothing special - other than follow your instructions. Thus, if used by a malicious user, it only "allows" specially designed attacks as required - such as the one we described earlier.
We'll assume that you don't intentionally or even accidentally construct a destructive database query - so we'll assume that the problem lies in the input from your users. Now, let's take a closer look at the various ways users might provide information to your script.
5. Type of user input
Today, the ability of users to influence the behavior of your scripts has become more and more complex.
The most obvious source of user input is, of course, one text input field on the form. With a field like this, you are literally abetting a user to enter arbitrary data. Moreover, you provide the user with a large input range; There is no way you can pre-limit 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 unguarded form fields.
However, there are other sources of attack, and one of the techniques you might think of when you think about it is the POST method! By simply analyzing the URI display in the browser's navigation toolbar, an observant user can easily see what information is being passed to a script. Although such URI is typically generated programmatically, there is no way to prevent a malicious user from simply entering an URI with an inappropriate variable value into a browser - potentially opening a database that could be abused.
A common strategy for restricting user input is to provide a selection box in a form instead of an input box. This control forces the user to choose from a set of predefined values and, to a certain extent, prevents the user from entering unexpected content. But just as an attacker may "trick" an URI(i.e., create an URI that can impersonate a trusted but invalid URI)1, he may also impersonate the creation of your form and its own version, and thus use illegal rather than predefined security selections in the options box. This is extremely simple to achieve; He just has to look at the source code, then cut and paste the form's source code - and then 1 cut open the door for him.
After modifying the selection, he will be able to submit the form, and his invalid instructions will be accepted as if they were original instructions 1. Therefore, the user can use many different methods to try to inject malicious code into a script.
PHP is a powerful but fairly easy to learn server-side scripting language that even inexperienced programmers can use to create complex and dynamic web sites. However, it often has many difficulties in implementing the secrets 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 that you can use to protect the security and integrity of your own web applications. First, we briefly review 1 server security issues - showing how you can 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 common vulnerabilities in PHP script implementations. 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'll implement a secure Web application. You'll learn how to authenticate users, authorize and track application usage, avoid data loss, safely execute risky system commands, and be able to safely use the web service. Whether or not you have enough experience with PHP security development, this series of articles provides a wealth of information to help you build more secure online applications.
2. What is SQL injection
There is no point in storing data in a database if you are never going to use it. Because the database is designed for easy access and manipulation of the data in the database. However, simply doing so can lead to potential disaster. This is not primarily because you might accidentally delete a slice from the database yourself; It's because, when you're trying to do something "innocent," you risk being hijacked by someone who USES his own destructive data to replace your own. We call this substitution "injection."
In fact, every time you ask a user for input to construct a database query, you are allowing that user to participate in building a command to access the database server. A friendly user may be satisfied with implementing such an operation; However, a malicious user will try to find a way to distort the command, causing the distorted command to delete data or do something even more dangerous. As a programmer, your job 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 is implemented along the following lines. Just for illustration purposes, we will assume that you have a wine database table "wines" with a field of "variety"(i.e., wine type) :
1. Provide a form - allow the user to submit something to search for. Let's say the user chooses a wine of type "lagrein".
2. Retrieve the user's search term and save it - by assigning it to a variable as shown below:
Here is the code snippet:
$variety = $_POST [' variety];
Therefore, the value of the variable $variety is now:
lagrein
3. Then, use this variable to construct a database query in the WHERE clause:
Here is the code snippet:
$query = "SELECT * FROM wines WHERE variety='$variety'";
So, the value of the variable $query is now as follows:
Here is the code snippet:
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 field variety is "lagrein".
By now, this should be a familiar and very relaxing process. Unfortunately, sometimes the process we are familiar with and comfortable with can lead to complacency. Now, let's re-examine the query we just built.
1. The fixed part of the query you created 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 the value of the variables submitted by the user:
Here is the code snippet:
$query. = $variety;
3. You then use another single quote to link the result - to describe the end of the value of the variable:
Here is the code snippet:
$query. = "";
Thus, the value of $query is as follows:
Here is the code snippet:
SELECT * FROM wines WHERE variety = 'lagrein'
The success of this construct depends on user input. In this example, you are using a single word (or maybe a group of words) to indicate a wine type. Therefore, the query is built without any problems, and the result will be a list of wines of type "lagrein" as you would expect. Now, let's imagine that instead of entering a simple wine type called "lagrein", your user enters the following (note the two punctuation marks) :
lagrein 'or 1 = 1;
Now, you go ahead and construct your query using the previously fixed part (here, we only show the result value of the $query variable) :
SELECT * FROM wines WHERE variety = '
You then connect it with the value of the variable containing the user's input (shown here in bold) :
SELECT * FROM wines WHERE variety = 'lagrein' or 1=1;
Finally, add the lower quotation marks above and below:
SELECT * FROM wines WHERE variety = 'lagrein' or 1=1; '
Therefore, the result of this query is quite different from what you would expect. In fact, your query now contains not one but two instructions, because the last semicolon the user entered has ended the first instruction (for record selection) and started a new one. In this case, the second instruction is meaningless except for a simple single quote; However, the first directive is not what you want to implement either. When the user places a single quote in the middle of his input, he closes the value of the expected variable and introduces another condition. Therefore, instead of retrieving records whose variety is "lagrein", you are retrieving records that satisfy either of the two criteria (the first is yours and the second is his -variety is "lagrein" or 1 is equal to 1). Since 1 is always 1, therefore, you will retrieve all records!
You may object: won't I use double quotes instead of single quotes to describe user-submitted variables? Yes, this at least slows down attacks by malicious users. (in previous articles, we warned you that all error notifications to users should be disabled. If an error message is generated here, then it might just help the attacker - providing a concrete explanation of why his attack failed.
In practice, getting your users to see all the records and not just one part of them might not seem like much at first glance, but in reality, it's a lot of work. Seeing all the records could easily have provided him with the internal structure of the form, thus providing him with an important reference to make it more malevolent in the future. This is especially true if your database contains, for example, a list of annual employee earnings, rather than information about apparently harmless drinks.
From the theoretical point of view, this kind of attack is indeed a very terrible thing. By injecting unexpected content into your query, this user is able to convert your database access to his own purposes. So now, your database is open to him - just as it is open to you.
4.PHP and MySQL injection
As we described earlier, PHP, by design, does nothing special - other than follow your instructions. Thus, if used by a malicious user, it only "allows" specially designed attacks as required - such as the one we described earlier.
We'll assume that you don't intentionally or even accidentally construct a destructive database query - so we'll assume that the problem lies in the input from your users. Now, let's take a closer look at the various ways users might provide information to your script.
5. Type of user input
Today, the ability of users to influence the behavior of your scripts has become more and more complex.
The most obvious source of user input is, of course, one text input field on the form. With a field like this, you are literally abetting a user to enter arbitrary data. Moreover, you provide the user with a large input range; There is no way you can pre-limit 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 unguarded form fields.
However, there are other sources of attack, and one of the techniques you might think of when you think about it is the POST method! By simply analyzing the URI display in the browser's navigation toolbar, an observant user can easily see what information is being passed to a script. Although such URI is typically generated programmatically, there is no way to prevent a malicious user from simply entering an URI with an inappropriate variable value into a browser - potentially opening a database that could be abused.
A common strategy for restricting user input is to provide a selection box in a form instead of an input box. This control forces the user to choose from a set of predefined values and, to a certain extent, prevents the user from entering unexpected content. But just as an attacker may "trick" an URI(i.e., create an URI that can impersonate a trusted but invalid URI)1, he may also impersonate the creation of your form and its own version, and thus use illegal rather than predefined security selections in the options box. This is extremely simple to achieve; He just has to look at the source code, then cut and paste the form's source code - and then 1 cut open the door for him.
After modifying the selection, he will be able to submit the form, and his invalid instructions will be accepted as if they were original instructions 1. Therefore, the user can use many different methods to try to inject malicious code into a script.