SQL injection principles and Workarounds code Examples

First, what is SQL injection?

1. What is SQL injection?

The so-called SQL injection, that is, by inserting SQL commands into the Web form to submit or enter the domain name or page request query string, and finally to deceive the server to execute malicious SQL commands, such as many previous film and television sites leaked VIP membership password is mostly through the Web form to submit query character burst out, Such forms are particularly susceptible to SQL injection attacks. SQL injection attacks occur when an application constructs a dynamic SQL statement to access the database by using the input content. SQL injection can also occur if the code uses stored procedures that are passed as strings that contain unfiltered user input. Hackers through SQL injection attacks can get access to the site database, then they can get all the data in the site database, malicious hackers can use SQL injection function to tamper with the data in the database and even destroy the data in the database. As a web developer you hate this kind of hacking, and of course it's important to understand how SQL injects this functionality and learn how to protect your Web database with code.

2. Why SQL injection occurs

SQL injection attacks are the main reason why SQL injection attacks are successful because they use design vulnerabilities to run SQL statements on the target server and other attacks, without validating the data entered by the user when dynamically generating SQL statements. For Java database connection JDBC, the SQL injection attack is valid only for statement and is not valid for PreparedStatement because PreparedStatement does not allow the logical structure of the query to change at different insertion times.

If the SQL statement that verifies that the user exists is:

Username ' and pswd= ' password

If you enter in the User Name field: ' or 1=1 or enter in the password fields: ' or 1=1

Validation will be bypassed, but this method is only valid for statement and invalid for PreparedStatement. Relative statement has the following advantages:

1. Anti-injection attack
2. Multiple runs fast
3. Preventing Database buffer Overflow
4. Code readability is good

These four points make PreparedStatement the first choice for the statement object that accesses the database, the disadvantage is that the flexibility is not good enough, some occasions still must use the statement.

3. SQL Injection principle

Let's take a look at the SQL injection principle so that readers have a perceptual understanding of SQL injection attacks, and the principle is consistent for other attacks.

SQL injection allows an attacker to bypass the authentication mechanism and take full control of the database on the remote server. SQL is the short name of a structured query language, which is the de facto standard for accessing a database. Currently, most Web applications use SQL databases to hold application data. Almost all Web applications use some kind of SQL database in the background. Like most languages, SQL syntax allows database commands to be mixed with user data. If the developer is not careful, the user data may be interpreted as a command, so that the remote user can not only input data to the Web application, but also can execute arbitrary commands on the database.

There are two primary forms of SQL injection attacks. One is to insert the code directly into a user input variable that is concatenated with the SQL command and makes it executable. The above example is the use of this method. Because of its direct binding with SQL statements, it is also known as direct injection attack method. The second is an indirect attack method that injects malicious code into a string to be stored in a table or stored as the original book. The stored string is connected to a dynamic SQL command to execute some malicious SQL code. The injection process works by terminating the text string prematurely, and then appending a new command. As an example of a direct injection attack. is to use a semicolon to end the current statement when the user enters the variable. Then insert a malicious SQL statement. Because the inserted command may append additional strings before execution, the attacker often flags "-" with comments to terminate the injected string. When executed, the system will assume that the following statement is commented, so the subsequent text will be ignored, not compiled and executed.

4. A simple example of SQL injection attack:

Here we give a more common example to briefly explain the principle of SQL injection. If we have a users table, there are two fields username and password. In our Java code, we are accustomed to the use of SQL splicing user authentication method. For example: "Select ID from users where username = '" +username + "' and password = '" + Password + "'" Here username and password are we access from we b The data obtained from the form. Let's take a look at a simple injection, if we enter ' or 1=1--in the username input box in the form, enter something in Password's form, if I enter 123 here. The SQL statement we are going to execute becomes the select ID from Users where username = ' or 1=1--and password = ' 123 ', let's take a look at this SQL because 1=1 is true, and password = ' 123 ' is commented out. So here's a full skip to SQL authentication.

Ii. How to protect against SQL injection attacks

1. Using a precompiled statement set, it has the ability to handle SQL injection, as long as it uses its Setxxx method to pass values.

Benefits of Use:

(1). Readability and maintainability of the code.
(2). PreparedStatement the best possible performance improvements.
(3). The most important point is that the security is greatly improved.

String sql= "SELECT * from Users where username=? and password=?;       PreparedStatement prestate = conn.preparestatement (sql);      Prestate.setstring (1, userName);      Prestate.setstring (2, password);       = Prestate.executequery ();

Principle: SQL injection only has a destructive effect on the preparation (compilation) of SQL statements, and PreparedStatement is ready, and the execution phase simply takes the input string as data processing instead of parsing and preparing the SQL statement, thus avoiding the SQL injection problem.

2. Using regular expressions to filter incoming parameters

Regular Expressions:
Private String Checksql = "^ (. +) \\sand\\s (. +) | (. +) \\sor (. +) \\s$ ";

To determine whether to match:
Pattern.matches (CHECKSQL,TARGERSTR);

The following is a specific regular expression:

Regular expression for detecting SQL Meta-characters:/(\%27) | (\ ') | (\-\-)| (\%23) | (#)/ix
Fixed regular expression for detecting SQL Meta-characters:/((\%3d) | ( =)) [^\n]* ((\%27) | ( \ ') | (\-\-)| (\%3b) | (:))/I
Regular expressions for typical SQL injection attacks:/\w* ((\%27) | ( \ ')) ((\%6f) |o| (\%4f)) ((\%72) |r| (\%52)) /ix
Regular expression for detecting SQL injection, union query Keyword:/((\%27) | ( \ ')) Union/ix (\%27) | (\‘)
Regular expression for detecting MS SQL Server SQL injection attacks:/exec (\s|\+) + (s|x) P\w+/ix
Wait a minute.....

In fact, you can simply use the Replace method can also implement the appeal function:

1 public static string transactsqlinjection (String str) 2      {3         return Str.replaceall (". *" ([';] +| (-) +). * "," "); 4      }

3. String filtering

A common method of comparison: (| | Parameters can be added according to the needs of your own program)

public static Boolean Sql_inj (String str)   {      string inj_str = "' |and|exec|insert|select|delete|update|   count|*|%| chr|mid|master|truncate|char|declare|;| Or|-|+|, ";      String inj_stra[] = Split (Inj_str, "|");      for (int i=0; i < inj_stra.length; i++)       {          if (Str.indexof (Inj_stra[i]) >=0)           {             return true;         }     }     return false; }

Call this function in 4.jsp to check if the envelope is not an illegal character

To prevent SQL from being injected from a URL:

Sql_inj.java Code:

Package Sql_inj; Import java.net.*; Import java.io.*; Import java.sql.*; Import java.text.*; Import java.lang.String; public class sql_inj{public     static Boolean Sql_inj (String str)      {         string inj_str = "' |and|exec|insert| select|delete|update|  count|*|%| chr|mid|master|truncate|char|declare|;| Or|-|+|, ";         Here the things can also add          string[] Inj_stra=inj_str.split ("\\|");         for (int i=0; i < inj_stra.length; i++)          {             if (Str.indexof (Inj_stra[i]) >=0)             {                 return true;             }         }         return false;}}     

5.JSP page Add client judgment code:

Use JavaScript to masking the client for unsafe words

Function Description: Check if it contains "'", "\ \", "/"

Parameter description: The string to check

Return value: 0: Yes 1: No

function Check (a) {    return 1;    FIBDN = new Array ("'", "\ \", "/");    I=fibdn.length;    J=a.length;    for (ii=0; ii
   

In general, the prevention of generic SQL injection can be as long as the code specification is up and down.

SQL injection principles and Workarounds code Examples