A detailed introduction on how to avoid sql injection in Java

This article mainly introduces avoiding sql injection. The editor thinks it is quite good. Now I will share it with you and give you a reference. Let’s follow the editor and take a look.

1. There must be a strict distinction between the permissions of ordinary users and system administrator users.

If an ordinary user embeds another Drop Table statement in a query statement, is it allowed to be executed? Since the Drop statement is related to the basic objects of the database, the user must operate this statement Must have relevant permissions. In the permission design, for end users, that is, users of application software, there is no need to give them permissions to create and delete database objects. So even if there is embedded malicious code in the SQL statements they use, these codes will not be executed due to restrictions on their user permissions. Therefore, when designing an application, it is best to distinguish system administrator users from ordinary users. This can minimize the harm caused by injection attacks to the database.

2. Force the use of parameterized statements.

If when writing a SQL statement, the variables entered by the user are not directly embedded in the SQL statement. If you pass this variable through parameters, you can effectively prevent SQL injection attacks. In other words, user input must not be directly embedded in SQL statements. In contrast, user input must be filtered, or parameterized statements must be used to pass user input variables. Parameterized statements use parameters instead of embedding user input variables into the SQL statement. Using this measure, most SQL injection attacks can be eliminated. Unfortunately, there are not many database engines that support parameterized statements. However, database engineers should try to use parameterized statements when developing products.

3. Strengthen the verification of user input.

Generally speaking, two methods can be used to prevent SQL injection attacks. One is to strengthen the inspection and verification of user input content; the other is to force the use of parameterized statements to pass user input. Content. In the SQLServer database, there are many user input content verification tools that can help administrators deal with SQL injection attacks. Test the contents of a string variable and only accept the required value. Reject input containing binary data, escape sequences, and comment characters. This helps prevent script injection and prevents certain buffer overflow attacks. Test the size and data type of user input and enforce appropriate limits and conversions. This helps prevent intentional buffer overflows and has a significant effect on preventing injection attacks.

4. Use the security parameters that come with the SQL Server database.

In order to reduce the adverse effects of injection attacks on the SQL Server database, relatively safe SQL parameters are specially designed in the SQL Server database. During the database design process, engineers should try to use these parameters to prevent malicious SQL injection attacks.

5. How to prevent SQL injection attacks in multi-tier environments?

In multi-tier application environments, all data entered by users should be verified after verification Only then can they be allowed to enter the trusted area. Data that fails the validation process should be rejected by the database and an error message returned to the next level. Implement multi-layer verification. Precautions taken against purposeless malicious users may not be effective against determined attackers. A better approach is to validate input at the user interface and at all subsequent points across trust boundaries. For example, validating data in the client application can prevent simple script injection. However, if the next layer thinks its input has been validated, any malicious user who can bypass the client can have unrestricted access to the system. Therefore, for multi-layer application environments, when preventing injection attacks, all layers need to work together, and corresponding measures must be taken on the client and database sides to prevent SQL statement injection attacks.

6. If necessary, use professional vulnerability scanning tools to find possible points of attack.

Using professional vulnerability scanning tools can help administrators find possible points of SQL injection attacks. However, vulnerability scanning tools can only discover attack points and cannot actively defend against SQL injection attacks. Of course, this tool is often used by attackers. For example, attackers can use this tool to automatically search for attack targets and carry out attacks. For this reason, if necessary, enterprises should invest in some professional vulnerability scanning tools. A complete vulnerability scanner is different from a network scanner in that it specifically looks for SQL injection vulnerabilities in the database. The latest vulnerability scanners look for the latest discovered vulnerabilities. Therefore, professional tools can help administrators discover SQL injection vulnerabilities and remind administrators to take active measures to prevent SQL injection attacks. If the database administrator discovers the SQL injection vulnerabilities that the attacker can find and takes active measures to block the vulnerabilities, then the attacker will have no way to start.

上面主要是介绍了在web应用程序中对sql注入的大体解决思路，下面我们就根据java web应用程序的特征来具体说明一下如何解决在java web应用程序中的sql注入问题。

1.采用预编译语句集，它内置了处理SQL注入的能力，只要使用它的setXXX方法传值即可。

使用好处：

(1).代码的可读性和可维护性.

(2).PreparedStatement尽最大可能提高性能.

(3).最重要的一点是极大地提高了安全性.

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();

原理：sql注入只对sql语句的准备(编译)过程有破坏作用，而PreparedStatement已经准备好了,执行阶段只是把输入串作为数据处理,而不再对sql语句进行解析,准备,因此也就避免了sql注入问题.

2.使用正则表达式过滤传入的参数

正则表达式：

private String CHECKSQL = “^(.+)\\sand\\s(.+)|(.+)\\sor(.+)\\s$”;

判断是否匹配：

Pattern.matches(CHECKSQL,targerStr);

下面是具体的正则表达式：

检测SQL meta-characters的正则表达式 ：/(\%27)|(\')|(\-\-)|(\%23)|(#)/ix

修正检测SQL meta-characters的正则表达式 ：/((\%3D)|(=))[^\n]*((\%27)|(\')|(\-\-)|(\%3B)|(:))/i

典型的SQL 注入攻击的正则表达式 ：/\w*((\%27)|(\'))((\%6F)|o|(\%4F))((\%72)|r|(\%52))/ix

检测SQL注入，UNION查询关键字的正则表达式 ：/((\%27)|(\'))union/ix(\%27)|(\')

检测MS SQL Server SQL注入攻击的正则表达式：/exec(\s|\+)+(s|x)p\w+/ix

等等…..

其实可以简单的使用replace方法也可以实现上诉功能：

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

3.字符串过滤

比较通用的一个方法：（||之间的参数可以根据自己程序的需要添加）

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_stralength ; i++ ) 
{ 
if (str.indexOf(inj_stra[i])>=0) 
{ 
return true; 
} 
} 
return false; 
}

4.jsp中调用该函数检查是否包函非法字符

防止SQL从URL注入：

sql_inj.java代码：

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|-|+|,"; 
//这里的东西还可以自己添加 
String[] inj_stra=inj_strsplit("\\|"); 
for (int i=0 ; i < inj_stra.length ; i++ ) 
{ 
if (str.indexOf(inj_stra[i])>=0) 
{ 
return true; 
} 
} 
return false; 
} 
}

 5.JSP页面添加客户端判断代码：

使用JavaScript在客户端进行不安全字符屏蔽

功能介绍：检查是否含有”‘”,”\\”,”/”

参数说明：要检查的字符串

返回值：0：是1：不是

函数名是

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; 
}

 关于安全性，本文可总结出一下几点：

1.要对用户输入的内容保持警惕。

2.只在客户端进行输入验证等于没有验证。

3.永远不要把服务器错误信息暴露给用户。

The above is the detailed content of A detailed introduction on how to avoid sql injection in Java. For more information, please follow other related articles on the PHP Chinese website!