1. What is SQL injection attack?
SQL injection attacks are when an attacker inserts SQL commands into the input domain of a web form or the query string requested by the page, and spoofs the server to execute malicious SQL commands. In some forms, the content entered by the user is directly used to construct (or affect) dynamic SQL commands, or as input parameters for stored procedures, and such forms are particularly vulnerable to SQL injection attacks. Common SQL injection attack procedures are:
⑴ A certain web application has a login page, which controls whether the user has the right to access the application, and it requires the user to enter a name and password.
⑵ The content entered in the login page will be directly used to construct dynamic SQL commands, or directly used as parameters of stored procedures. The following is an example of application construct query:
query = new (
"SELECT * from Users WHERE login = '")
.Append().Append("' AND password='")
.Append().Append("'");
⑶ The attacker enters content such as "' or '1'='1" in the user name and password input box.
⑷ After the content entered by the user is submitted to the server, the server runs the above code to construct the SQL command to query the user. However, since the content entered by the attacker is very special, the final SQL command becomes: SELECT * from Users WHERE login = '' or '1'='1' AND password = '' or '1'='1'.
⑸ The server executes a query or stored procedure to compare the identity information entered by the user with the identity information saved in the server.
⑹ Since SQL commands have actually been modified by injection attacks and cannot truly verify the user's identity, the system will incorrectly authorize the attacker.
If the attacker knows that the application will use the content entered in the form directly for queries that verify identity, he will try to enter some special SQL string to tamper with the query to change its original function and deceive the system to grant access.
Different system environments, the possible damage caused by attackers is also different, which is mainly determined by the security permissions of the application to access the database. If the user's account has administrator or other advanced permissions, the attacker may perform various operations he wants to do on the database tables, including adding, deleting or updating data, or even directly deleting the tables.
2. How to prevent it?
Fortunately, it is not particularly difficult to prevent applications from being intruded by SQL injection attacks. Just filter all input content before constructing SQL commands using the content input in the form. Filtering input can be done in a variety of ways.
⑴ For dynamically constructing SQL queries, the following techniques can be used:
First:Replace single quotes, that is, change all single quotes that appear individually into two single quotes to prevent attackers from modifying the meaning of SQL commands. Let’s look at the previous example, “SELECT * from Users WHERE login = ''' or ''1''=''1' AND password = ''' or ''1''=''1'" will obviously get a different result from “SELECT * from Users WHERE login = '' or '1'='1' AND password = '' or '1'='1'".
second:Delete all hyphens in the user input to prevent the attacker from constructing queries such as "SELECT * from Users WHERE login = 'mas' -- AND password =''", because the second half of this query has been commented out and is no longer valid. As long as the attacker knows a legitimate user login name, he does not need to know the user's password to successfully obtain access.
third:For database accounts used to perform queries, restrict their permissions. Use different user accounts to perform query, insert, update, and delete operations. Because the operations that can be performed by different accounts are isolated, the places originally used to execute SELECT commands are prevented from being used to execute INSERT, UPDATE, or DELETE commands.
⑵ Use stored procedures to execute all queries.
The way SQL parameters are passed will prevent attackers from using single quotes and hyphens to implement attacks. In addition, it also enables database permissions to be limited to only specific stored procedures to be executed, and all user input must comply with the security context of the called stored procedures, making it difficult to re-injection attacks.
⑶ Limit the length of input to form or query string.
If the user's login name is only 10 characters at most, do not recognize more than 10 characters entered in the form, which will greatly increase the difficulty of an attacker inserting harmful code into SQL commands.
⑷ Check the legality of user input and be sure that the input content only contains legal data.
Data checks should be performed on both the client and the server side - the reason for server-side verification is to make up for the fragile security of the client verification mechanism.
On the client side, it is entirely possible for an attacker to obtain the source code of the web page, modify the script that verifys the legitimacy (or directly delete the script), and then submit the illegal content to the server through the modified form. Therefore, to ensure that the verification operation has indeed been performed, the only way is to perform verification on the server side. You can use many built-in verification objects, such as RegularExpressionValidator, which can automatically generate client scripts for verification, and of course you can also insert server-side method calls. If you cannot find the ready-made verification object, you can create one by yourself through CustomValidator.
⑸ Encrypt and save user login name, password and other data.
Encrypt the data entered by the user and then compare it with the data saved in the database. This is equivalent to "disinfecting" the data entered by the user. The data entered by the user no longer has any special meaning to the database, thus preventing attackers from injecting SQL commands. The class has a HashPasswordForStoringInConfigFile, which is very suitable for disinfecting input data.
⑹ Check the number of records returned by the query that extracts data.
If the program only requires one record to be returned, but the actual record returned exceeds one line, it is treated as an error.
The above is a way to prevent SQL injection attacks, and I hope it will be helpful to everyone's learning.