Prevent SQL injection in web projects

Source of the article ： Learn through http://www.bdgxy.com/

One 、SQL Introduction to injection

SQL Injection is one of the most common network attacks , It's not using the operating system BUG To achieve an attack , It's about the negligence of programmers , adopt SQL sentence , Login without account , Even tampering with the database .

Two 、SQL The general idea of injection attack

• 1. Look for SQL Injection location
• 2. Judge the server type and background database type
• 3. For different server and database characteristics SQL Injection attack

3、 ... and 、SQL Inject attack instance

For example, in a login interface , Require user name and password ：

This can be input to achieve account free login ：

user name ： ‘or 1 = 1 –

The secret code ：

Click to log in , If there is no special treatment , Then the illegal user is proud to log in .( Of course, there are databases in some languages API These problems have been dealt with )

Why is that ? Let's analyze ：

In theory , The background authentication program will have the following SQL sentence ：

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

When you enter the user name and password above , above SQL Statement becomes ：
SELECT * FROM user_table WHERE username=
‘‘or 1 = 1 – and password=’’

“”"
analysis SQL sentence ：
After the condition username=”or 1=1 The user name is equal to ” or 1=1 Then this condition is bound to succeed ;

And then add two -, This means annotating , It annotates the following statements , Let them not work , This statement will always execute correctly , Users easily cheat the system , Get legal status .
It's more gentle , If it's execution
SELECT * FROM user_table WHERE
username=‘’ ;DROP DATABASE (DB Name) --’ and password=‘’
The consequences can be imagined …
“”"

Four 、 How to defend SQL Inject

Be careful ： Where there is SQL Injection of vulnerability procedures , It's because the program has to accept variables or URL Parameters passed , And this variable or parameter is made up of SQL Part of the statement ,

For user input or parameters passed , We should always be vigilant , This is in the field of security 「 External data is not trusted 」 Principles , throughout Web All kinds of attacks in the field of security ,

Most of it is because developers violate this principle , So it's natural to think of , From the detection of variables 、 Filter 、 Verification starts , Make sure the variables are what the developers expect .

1、 Check variable data type and format

If your SQL The statement is similar to where id={$id} This form , Everything in the database id It's all numbers , Then it should be SQL Before being executed , Check to make sure that the variable id yes int type ; If it's an acceptance email , Then we should check and strictly ensure that the variable must be in mailbox format , Other types like date 、 Time and so on is also a truth . Sum up ： As long as it's a variable with a fixed format , stay SQL Statement before execution , Should be strictly in accordance with the fixed format to check , Make sure the variable is in the format we expect , This can be largely avoided SQL Injection attack .

such as , We are going to accept username In the parameter example , Our product design should be at the beginning of user registration , There is a user name rule , such as 5-20 Characters , It can only be written by upper and lower case letters 、 Numbers and some security symbols , Does not contain special characters . At this point we should have a check_username Function to perform a unified check . however , There are still many exceptions that cannot be applied to this criterion , For example, the article publishing system , Comment system must allow users to submit arbitrary string scenarios , This requires other solutions such as filtering .

2、 Filter special symbols

For variables whose fixed format cannot be determined , Be sure to filter or escape special symbols .

3、 Bound variable , Use precompiled statements

MySQL Of mysqli The driver provides support for precompiled statements , Different programming languages , There are different ways to use precompiled sentences

actually , Binding variables using precompiled statements is a precaution SQL The best way to inject , Use precompiled SQL Sentence semantics will not change , stay SQL In the sentence , Variables are marked with question marks ? Express , No matter how powerful a hacker is , Can't change SQL Structure of statement

Summary ：

• 1. Using precompiled bound variables SQL sentence
• 2. Strictly encrypt the user's confidential information
• 3. Do not open the production environment at will Webserver The error display
• 4. Use regular expressions to filter the incoming parameters
• 5. String filtering
• 6. Check whether the package letter has illegal characters

in general , Guard against the general SQL Injection can be prevented by working on the code specification

The above is what Xiaobian introduced to you web In the project SQL Inject , I hope that's helpful . Thank you very much for your comments on the rookie tutorial www.piaodoo.com Website support ！