SQL Injection principle and solution code example, SQL example

SQL Injection principle and solution code example, SQL example

1. What is SQL injection?

1. What is SQL injection?

SQL injection is to insert SQL commands into Web forms to submit or input query strings for domain names or page requests, and finally fool the server to execute malicious SQL commands, for example, most of the previous VIP member passwords leaked by many video websites are exposed by submitting query characters through WEB forms. Such forms are particularly vulnerable to SQL injection attacks. when an application uses the input content to construct a dynamic SQL statement to access the database, an SQL injection attack occurs. If the Code uses stored procedures, which are passed as strings containing unfiltered user input, SQL Injection also occurs. Hackers can gain access to the website database through SQL injection attacks, and then they can obtain all the data in the website database, malicious hackers can use SQL injection 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 hacker behavior. Of course, you also need to understand the principles of SQL injection and learn how to protect your website database through code.

2. Causes of SQL Injection

SQL injection attacks are attacks that use design vulnerabilities to run SQL statements on the target server and other methods, when a dynamic SQL statement is generated, user input data is not verified, which is the main cause of the successful SQL injection attacks. For JDBC connection to a java database, SQL injection attacks are only valid for Statement and invalid for PreparedStatement because PreparedStatement does not allow you to change the logical structure of the query at different insertion times.

If the SQL statement used to verify the existence of a user is:

Username 'and pswd =' Password

If you enter either 'or 1 = 1 in the username field or' or 1 = 1 in the password field

Verification will be bypassed, but this method is only valid for Statement and not for PreparedStatement. Relatively Statement has the following advantages:

1. Protection against injection attacks
2. Fast Running multiple times
3. Prevent database Buffer Overflow
4. Good code readability and maintainability

These four points make the PreparedStatement the first choice for accessing database Statement objects. The disadvantage is that the flexibility is not good enough. In some cases, the Statement must be used.

3. SQL Injection principles

Next, let's take a look at the principles of SQL injection to give readers a perceptual understanding of SQL injection attacks. The principles of other attacks are consistent.

SQL Injection allows attackers to bypass the authentication mechanism and completely control databases on remote servers. SQL is short for the structured query language. It is the de facto standard for database access. Currently, most Web applications use SQL databases to store application data. Almost all Web applications use a SQL database in the background. Like most languages, SQL syntax allows database commands to be mixed with user data. If developers are not careful, user data may be interpreted as commands. In this way, remote users can not only input data to Web applications, you can also execute arbitrary commands on the database.

There are two main types of SQL injection attacks. First, the code is directly inserted into the user input variables that are connected to the SQL command and executed. The above example uses this method. Because it is directly bundled with SQL statements, it is also called the direct injection attack method. The second is an indirect attack method, which injects malicious code into a table or serves as a string for storing the original data. The stored string is connected to a dynamic SQL command to execute malicious SQL code. The injection process works by terminating the text string in advance and then appending a new command. Take direct injection attacks as an example. When you enter a variable, use a semicolon to end the current statement. Then insert a malicious SQL statement. Because the inserted command may append other strings before execution, attackers often use annotations to mark "-" to terminate the injected strings. During execution, the system considers the subsequent statement bit comments, so the subsequent text will be ignored and compilation and execution will not be carried back.

4. A simple example of SQL injection attacks:

Here is a common example to briefly describe the principles of SQL injection. Suppose we have a users table, which contains two fields: username and password. In our java code, beginners are used to using SQL concatenation for user verification. For example: "select id from users where username = '" + username + "' and password = '" + password + "'" here, username and password are used to access data obtained from web forms.. Next let's look at a simple injection. If we enter 'or 1 = 1 -- In the username input box of the form, enter something in the password form. If we enter 123 here. in this case, the SQL statement we want to execute is changed to select id from users where username = ''or 1 = 1 -- and password = '000000'. Let's take a look at this SQL statement, because 1 = 1 is true, and password = '20160301' is commented out later. Therefore, SQL verification is skipped.

Ii. How to defend against SQL injection attacks

1. Use a pre-compiled statement set, which has built-in capabilities for processing SQL injection. You only need to use its setXXX method to pass the value.

Benefits:

(1). code readability and maintainability.
(2). PreparedStatement to maximize performance.
(3). The most important thing is to greatly improve security.

String sql= "select * from users where username=? and password=?;  
     PreparedStatement preState = conn.prepareStatement(sql);  
     preState.setString(1, userName);  
     preState.setString(2, password);  
     ResultSet rs = preState.executeQuery(); 

Principle: SQL Injection only damages the preparation (Compilation) process of SQL statements, and PreparedStatement is ready. In the execution stage, the input string is used as data processing, instead of parsing and preparing SQL statements, SQL injection is avoided.

2. Use regular expressions to filter input parameters

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

Determine whether a match exists:
Pattern. matches (CHECKSQL, targerStr );

The following is a specific regular expression:

Check the Regular Expression of SQL meta-characters:/(\ % 27) | (\ ') | (\-) | (\ % 23) | (#)/ix
Corrected the regular expression used to check SQL meta-characters:/(\ % 3D) | (=) [^ \ n] * (\ % 27) | (\') | (\-) | (\ % 3B) | (:))/I
Typical Regular Expression for SQL injection attacks:/\ w * (\ % 27) | (\ ') (\ % 6F) | o | (\ % 4F )) (\ % 72) | r | (\ % 52)/ix
Check SQL injection. Regular Expression of the UNION query Keyword:/(\ % 27) | (\ ') union/ix (\ % 27) | (\')
Regular Expression used to detect ms SQL Server SQL injection attacks:/exec (\ s | \ +) + (s | x) p \ w +/ix
And so on .....

In fact, you can simply use the replace method or implement the appeal function:

public static String TransactSQLInjection(String str)
     {
        return str.replaceAll(".*([';]+|(--)+).*", " ");
     } 

3. String Filtering

A common method: (| parameters can be added according to the needs of your 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;
}

4. Call this function in jsp to check whether the letter contains invalid characters.

Prevent SQL from URL injection:

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 |-| + |, ";
// The stuff here can also be added by yourself
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. Add the client judgment code on the JSP page:

Use javascript to mask insecure characters on the client

Function Description: Check whether "'", "\", "/" is included "'","\\","/"

Parameter description: string to be checked

Returned value: 0: 1: No

function check(a)  
{
	return 1;
	fibdn = new Array (”‘” ,”\\”,”/”);
	i=fibdn.length;
	j=a.length;
	for (ii=0; ii＜i; ii++)  
	{
		for (jj=0; jj＜j; jj++)  
		{
			temp1=a.charAt(jj);
			temp2=fibdn[ii];
			if (tem';
			p1==temp2)  
			{
				return 0;
			}
		}
	}
	return 1;
}

Summary

The above is all the content about the SQL Injection principle and solution code example in this article, and I hope to help you. For more information, see MYSQL updatexml () function error injection parsing, several important MySQL variables, and analysis of oracle SQL statement optimization technical points, you are welcome to leave a message saying that the editor will reply to you and modify it in time. Thank you for your support!