Preventing SQL injection in web projects

1. Introduction to SQL Injection

SQL injection is one of the more common network attack methods. It does not use operating system bugs to implement attacks, but targets programmers' negligence when writing programs. It uses SQL statements to achieve accountless login and even tamper with the database.

2. The overall idea of ​​SQL injection attack

• 1. Find the location of SQL injection
• 2. Determine the server type and backend database type
• 3. SQL injection attacks based on different server and database characteristics

SQL injection attack examples

For example, on a login interface, you are asked to enter your username and password:

You can enter this to log in without an account:

Username: 'or 1 = 1 –

password:

Click login. If no special processing is done, the illegal user will be able to log in easily. (Of course, some database APIs in some languages ​​have already handled these problems.)

Why is this? Let's analyze it below:

Theoretically, the background authentication program will have the following SQL statements:

String sql = "select * from user_table where username=
' "+userName+" ' and password=' "+password+" '";

When the above username and password are entered, the above SQL statement becomes:
SELECT * FROM user_table WHERE username=
''or 1 = 1 -- and password=''

"""
Analyze SQL statements:
If the condition is followed by username=”or 1=1 username is equal to” or 1=1, then this condition will definitely succeed;

Then add two - at the end, which means comment. It comments the following statements and makes them ineffective. In this way, the statements can always be executed correctly, and users can easily deceive the system and obtain legal identities.
This is still relatively gentle. If you execute SELECT * FROM user_table WHERE
username='' ;DROP DATABASE (DB Name) --' and password=''
The consequences can be imagined...
"""

4. How to prevent SQL injection

Note: Any program with SQL injection vulnerabilities is because the program needs to accept variables input by the client user or parameters passed by the URL, and this variable or parameter is part of the SQL statement.

We should always be vigilant about the content entered by the user or the parameters passed. This is the principle of "external data cannot be trusted" in the security field. Looking at the various attack methods in the field of Web security,

Most of them are caused by developers violating this principle, so the natural thing to think of is to start with variable detection, filtering, and verification to ensure that the variables are what the developers expected.

1. Check variable data type and format

If your SQL statement is in the form of where id={$id}, and all ids in the database are numbers, you should check to make sure that the variable id is of int type before the SQL is executed. If you are receiving an email, you should check and strictly ensure that the variable is in the format of an email address. The same is true for other types such as date, time, etc. To sum up: as long as there are variables in a fixed format, they should be strictly checked according to the fixed format before the SQL statement is executed to ensure that the variables are in the format we expect, which can largely avoid SQL injection attacks.

For example, in our previous example of accepting username parameters, our product design should have a username rule at the beginning of user registration, such as 5-20 characters, which can only be composed of uppercase and lowercase letters, numbers and some safe symbols, and no special characters. At this point we should have a check_username function to perform unified checks. However, there are still many exceptions to this rule, such as article publishing systems, comment systems, etc., which must allow users to submit arbitrary strings. This requires the use of other solutions such as filtering.

2. Filter special symbols

For variables that cannot be determined in a fixed format, special symbols must be filtered or escaped.

3. Bind variables and use precompiled statements

MySQL's mysqli driver provides support for prepared statements. Different programming languages ​​have their own methods for using prepared statements.

In fact, using precompiled statements to bind variables is the best way to prevent SQL injection. The semantics of precompiled SQL statements will not change. In SQL statements, variables are represented by question marks?. Even if hackers are very skilled, they cannot change the structure of SQL statements.

summary:

• 1. Use precompiled SQL statements with bind variables
• 2. Strictly encrypt the user's confidential information
• 3. Do not enable error display of Webserver in production environment at will
• 4. Use regular expressions to filter incoming parameters
• 5. String filtering
• 6. Check whether it contains illegal characters

In general, to prevent general SQL injection, you only need to pay attention to the code standards.

The above is what I introduced to you about preventing SQL injection in web projects. I hope it will be helpful to you. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!