Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account


How to effectively prevent SQL injection

2024-03-04 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >


Shulou( Report--

This article mainly shows you "how to effectively prevent SQL injection", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to effectively prevent SQL injection" this article.

Getting started with sql injection

SQL injection is a kind of attack with great harm. Although the harm is great, defense is not nearly as difficult as XSS.

The reason for the SQL injection vulnerability is to concatenate SQL parameters. That is, the query parameters used for input are directly concatenated in the SQL statement, resulting in a SQL injection vulnerability.

Demonstrate the classic SQL injection

We see: select id,no from user where id=2

If the statement is obtained by concatenating sql strings, for example: String sql = "select id,no from user where id=" + id

Where id is a parameter entered by the user, then, if the user typed 2, then a piece of data was found above. If the user entered 2 or 1 to conduct a sql injection attack, then you can see that the above statement (select id,no from user where id=2 or 1 input 1;) looks up all the records in the user table. This is typical sql injection.

Look at one more column:

We see that table sqlinject can be deleted directly through sql injection! We can see its harm!

General idea of SQL injection attack

1. Find the location of SQL injection

2. Determine the server type and background database type

3. Carry out SQL injection attack according to the characteristics of impassable server and database

SQL injection attack example

For example, in a login interface, you are required to enter a user name and password:

You can enter this to achieve account-free login:

User name:'or 1 = 1-

Password: click to log in, if there is no special treatment, then the illegal user will proudly log in. (of course, today's database API in some languages has dealt with these problems.)

Why is that? Let's analyze it:

In theory, the background authentication program will have the following SQL statement:

String sql = "select * from user_table where username='" + userName+ "'and password='" + password+ "'"

When the user name and password above are entered, the above SQL statement becomes:

SELECT * FROM user_table WHERE username=''or 1 = 1-- and password=''

Analyze the SQL statement:

After the condition, username= "or 1 user name equals" or 1 # 1, then this condition will be successful.

And then add two--, which means comments, which annotates the following statements so that they don't work, so that the statements can always be executed correctly, and the user can easily fool the system and get a legal identity.

This is relatively gentle, if it is carried out.

SELECT * FROM user_table WHERE username=''; DROP DATABASE (DB Name)-- 'and password=''

... . The consequences can be imagined...

Coping methods

Next, I would like to talk about how to deal with JSP:

1. (simple and effective method) PreparedStatement

A set of precompiled statements is used, which has the built-in 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 improves performance as much as possible.

(3)。 The most important point is that the security is greatly improved.


Sql injection only destroys the preparation (compilation) of sql statements.

While PreparedStatement is ready, the execution phase only treats the input string as data.

Instead of parsing and preparing sql statements, the problem of sql injection is avoided.

two。 Use regular expressions to filter incoming parameters

Packages to be introduced:

Import java.util.regex.*

Regular expression:

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

Determine if there is a match:

Pattern.matches (CHECKSQL,targerStr)

Here are the specific regular expressions:

Detect the regular expression of SQL meta-characters: / (\% 27) | (\') | (\ -) | (\% 23) | (#) / ix

Fixed the regular expression for detecting SQL meta-characters: / ((\% 3D) | (=)) [^\ n] * ((\% 27) | (\') | (\ -) | (\% 3B) | (:)) / I

Regular expression for a typical SQL injection attack: /\ w * ((\% 27) | (\')) ((\% 6F) | o | (\% 4F)) ((\% 72) | r | (\% 52)) / ix

Detect SQL injection. Regular expression of 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... ..

3. String filtering

A more general method:

(| | 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 | * |% | chr | mid | master | truncate | char | declare |; or |-| + |,"; String inj_stra [] = split (inj_str, "|"); for (int iTun0; I)

< inj_stra.length ; i++ ){if (str.indexOf(inj_stra[i])>

= 0) {return true;}} return false;}

Call this function in 4.jsp to check whether the package is illegal.

Prevent SQL from injecting from URL: Code:

Package sql_inj;import*;import*;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 | count | * |% | chr | mid | master | truncate | char | declare |; | or |-| + |,"; / / you can also add String [] inj_stra=inj_str.split ("\\ |"); for (int item0; I)

< inj_stra.length ; i++ ){if (str.indexOf(inj_stra[i])>

= 0) {return true;}} return false;}}

5.JSP page judgment code:

Use javascript to mask unsafe characters on the client side

Function description: check whether it contains "'", "\", "/"

Parameter description: string to check

Return value: 0: yes: 1: no

The function name is

Function check (a) {return 1 / jj = new Array ("'", "\\", "/"); for (jj=0; jj < j; jj++) {temp1=a.charAt (jj); temp2=fibdn [ii]; if (tem'; p1==temp2) {return 0;}} return 1;}

In general, it is only necessary to put a little effort into the code specification to prevent general SQL injection.

Whenever there are variables in the SQL involved in execution, use JDBC (or other data persistence layer) to provide such as: PreparedStatement, and remember not to concatenate strings.

The above is all the contents of the article "how to effectively prevent SQL injection". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To



© 2024 SLNews company. All rights reserved.