Preventing SQL injection in web development

I. Introduction to SQL injection

SQL injection is one of the most common methods of network attack, it is not to exploit the bugs of the operating system to achieve the attack, but for the programmer to write negligence, through SQL statements, to achieve no account login, and even tamper with the database.

Second, the general idea of SQL injection attack

1. Find the location to SQL Injection 2. Determine server type and background database type 3. SQL injection attacks against different server and database features 

Iii. examples of SQL injection attacks

For example, in a login interface, required to enter a user name and password: You can enter the implementation of free account login: User name: ' or 1 = 1 -Password: Point login, if not to do special treatment, then this illegal user is very proud of landing in. Of course now some of the languages of the database API have dealt with these issues) why is this? Here we analyze: Theoretically, the background authentication program will have the following SQL statement:

String sql ="SELECT * from user_table where username='"+username+"'  andpassword='"+password+"' ";When you enter the user name and password above, the SQL statement above becomes: SELECT* FROM User_table WHERE username='' or 1 = 1--and password=''"""Parse SQL statement: After the condition username= "or 1=1 user name equals" or 1=1 then this condition will be successful; then add two-, which means that the comment, it will comment the following statements, so that they do not work, so that the statement will always be executed correctly, the user easily fooled the system, Get legal status. This is still more gentle, if it is to execute select * from user_table whereusername= ';D rop DATABASE (DB Name)--' and password= ' its consequences imaginable ..."""

Iv. How to prevent SQL injection

Note: any program that has a SQL injection vulnerability is a program that accepts arguments passed from a client user input, and that variable or parameter is part of a SQL statement.
We should always be vigilant about what the user has entered or the parameters passed, which is the "untrusted" principle of "external data" in the field of security and the various attacks in web security.
Most of them are caused by a violation of this principle, so it is natural to think of the variable detection, filtering, verification, to ensure that the variables are expected by the developer.

1. Check the variable data type and format

If your SQL statement is similar to where id={$id} This form, all the IDs in the database are numbers, then you should check to make sure that the variable ID is int type before SQL is executed, and if you accept the mailbox, you should check and make sure that the variable must be the format of the mailbox. Other types, such as date and time, are also a reason. Summing up: As long as there are fixed-format variables, before the SQL statement execution, you should strictly follow the fixed format to check, to ensure that the variables are the format we expected, which can largely avoid SQL injection attacks.
For example, we accept the username parameter example, our product design should be at the beginning of user registration, there is a user name rules, such as 5-20 characters, can only be composed of uppercase and lowercase letters, numbers and some security symbols, does not contain special characters. At this point we should have a check_username function to perform a uniform check. However, there are still many exceptions that cannot be applied to this criterion, such as the article publishing system, commenting systems, etc., which must allow the user to submit arbitrary strings, which requires filtering and other scenarios.

2. Filter Special symbols

For variables that cannot be determined in a fixed format, special symbolic filtering or escaping is a must.

3. Binding variables, using precompiled statements

MySQL's mysqli driver provides support for precompiled statements, and different programming languages, each with a method that uses precompiled statements

In fact, binding variables using precompiled statements is the best way to prevent SQL injection, using precompiled SQL statement semantics will not change, in SQL statements, variables with a question mark? The hacker can not change the structure of the SQL statement even if it is big.

Summary:

1. SQL statement using precompiled binding Variable 2. Strictly encrypt the user's confidential information 3. Do not open the Webserver error display in the production environment 4. Use regular expressions to filter incoming parameters 5. String filtering 6. Check if the letter is illegal characters in general, the prevention of general SQL injection as long as the code specification up and down can be prevented

Preventing SQL injection in web development