Avoid SQL injection

What is SQL injection

SQL injection attack (SQL injection), short injection attack, is the most common security vulnerability in Web development. It can be used to obtain sensitive information from the database, or to take advantage of the characteristics of the database to perform a series of malicious operations such as adding users, exporting files, or even obtaining the highest privileges of the database or system.

The cause of SQL injection is because the program does not effectively filter the user's input, allowing the attacker to successfully submit malicious SQL query code to the server, the program after receiving the error of the attacker's input as part of the query statement execution, resulting in the original query logic is changed, Additional malicious code that was carefully crafted by the attacker was executed.

SQL injection Instance

Many web developers do not realize that SQL queries can be tampered with, thereby treating SQL queries as trusted commands. As everyone knows, SQL query can bypass access control, thereby bypassing authentication and permission checking. Furthermore, it is possible to run the host system-level commands through SQL queries.

Here are some real examples of how SQL injection can be explained in detail.

Consider the following simple login forms:

<form action= "/login" method= "POST" ><p>username: <input type= "text" name= "Username"/></p> <p>password: <input type= "Password" name= "Password"/></p><p><input type= "Submit" value= " Login "/></p></form>

The SQL inside of our processing might be something like this:

String username = "Jack"; String password = "123456"; String sql = "SELECT * from user where username = '" + username + "' and password = '" + Password + "'";

If the user enters the user name as follows, the password is arbitrary

MyUser ' or ' foo ' = ' foo '--

Then our SQL becomes as follows:

SELECT * FROM user where username = ' myuser ' or ' foo ' = = ' foo '--' and password= ' xxx '

inside -- the SQL is a comment tag, so the query statement is interrupted here. This allows the attacker to successfully log in without knowing any valid user name and password.

There is a more dangerous SQL injection for MSSQL, which is the control system, and the following scary example shows how to execute system commands on some versions of the MSSQL database.

String sql = "SELECT * FROM Products WHERE name is like '%" +prod+ "% '"//execute sql

If the attack commits

a% ' EXEC master. xp_cmdshell ' net user Test Testpass/add '--

As the value of the variable prod, SQL will become

String sql = "SELECT *" from the products WHERE name is like '%a% ' exec master. xp_cmdshell ' net user Test Testpass/add '--% ' "

The MSSQL server executes this SQL statement, including the one behind it that is used to add a new user to the system. If the program is run with SA and the MSSQLServer service has sufficient permissions, the attacker can obtain a system account to access the host.

While the above examples are for a particular database system, this does not mean that similar attacks can be performed on other database systems. For this security vulnerability, a variety of databases can suffer if different methods are used.

How to prevent SQL injection

You might say that an attacker would need information about the database structure to implement SQL injection attacks. This is true, but no one can guarantee that the attackers will not get the information, and once they have it, the database is in danger of being compromised. If you are using an open source package to access a database, such as a forum program, it is easy for an attacker to get the relevant code. If the code is poorly designed, the stakes are even greater. Currently, Discuz, Phpwind, Phpcms and other popular open source programs have been the SQL injection attack precedent.

These attacks always occur on low-security code. Therefore, never trust data entered by the outside world, especially data from users, including selection boxes, form hidden fields, and cookies. As in the first example above, even a normal query can cause disaster.

SQL injection attack is so harmful, so how to prevent it? The following recommendations may help prevent SQL injection.

2. Strictly restricting the operation permissions of the database of the Web application, giving this user only the least privilege to meet their work, thus minimizing the risk of injecting attack to the database.

4. Check that the input data has the desired data format, strictly restricting the type of the variable, such as using the RegExp package for some matching processing, or using the StrConv package to convert the string to other basic types of data to judge.

6. Escape processing of special characters (' "\ angle brackets &*; etc.) into the database, or encode conversions

8. all query statements recommend using a parameterized query interface provided by the database, and parameterized statements use parameters instead of embedding user input variables into SQL statements, that is, do not directly splice SQL statements. For example, use database/sql the query function inside Prepare and Query , or Exec(query string, args ...interface{}) .

10. It is recommended that professional SQL injection detection tools be used before the app is released to patch the discovered SQL injection vulnerabilities in a timely manner. There are many open source tools on the Internet, such as Sqlmap, Sqlninja and so on.

12. Prevent the Web site from printing SQL error messages, such as type errors, field mismatches, and so on, exposing the SQL statements in the code so that attackers are prevented from using these error messages for SQL injection.

Summarize

As we can see from the above example, SQL injection is a significant security vulnerability. So for the Web application that we usually write, we should attach great importance to every small detail, the details decide the fate, the life is so, and so is the writing Web application.

Avoid SQL injection