Summary of five methods to effectively prevent SQL injection, and 5 methods to effectively prevent SQL Injection

Summary of 5 ways to effectively prevent SQL injection, 5 ways to effectively prevent SQL injection

Getting started with SQL injection

SQL injection is a very harmful form of attack. Although the damage is great, defense is far less difficult than XSS.

The reason for the existence of the SQL injection vulnerability is to splice SQL parameters. That is, the query parameters used for input are directly spliced into the SQL statement, resulting in a SQL injection vulnerability.

Demo of classic SQL injection

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

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

Where id is a parameter input by the user, then if the user enters 2, then the above sees a piece of data found, if the user enters 2 or 1 = 1 for a SQL injection attack, then see, the above statement (select id, no from user where id = 2 or 1 = 1;) All the records in the user table are found. This is a typical SQL injection.

Look at another column:

We see that through SQL injection can delete the table sqlinject directly! Visible its hazard!

The general idea of SQL injection attacks

      1. Find the location of SQL injection

      2. Determine the server type and background database type

      3. Perform SQL injection attacks against unsuitable server and database features

SQL injection attack example

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

You can enter this way to achieve account-free login:

Username: ‘or 1 = 1 –

Password: Click to log in. If there is no special treatment, the illegal user will log in with pride. (Of course, some database APIs in some languages have already dealt with these problems)

Why is this? Let us analyze:

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

String sql = "select * from user_table where username = '" + userName + "' and password = '" + password + "'";
When the above username and password 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 = 1 username equals” or 1 = 1 then this condition will succeed;

Then add two-at the end, which means a comment, which will comment the following statement so that they will not work, so that the statement can always be executed correctly, and the user can easily cheat the system and obtain a legal identity.

This is still relatively gentle, if it is executed

SELECT * FROM user_table WHERE username = ''; DROP DATABASE (DB Name)-'and password =' '
…. The consequences can be imagined…

solution

Let me talk about how to deal with JSP:

1. (Simple and effective method) PreparedStatement

Adopt precompiled statement set, which has built-in ability to handle SQL injection, as long as it uses its setXXX method to pass value.

Benefits of use:

     (1). Code readability and maintainability.

     (2). PreparedStatement maximizes performance as much as possible.

     (3) The most important point is to greatly improve the security.

principle:

SQL injection only has a destructive effect on the preparation (compilation) process of SQL statements

And PreparedStatement is ready, the execution phase is only to process the input string as data,

Instead of parsing and preparing the SQL statement, the SQL injection problem is avoided.

2. 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 whether it matches:

Pattern.matches (CHECKSQL, targerStr);
The following are specific regular expressions:

Regular expression for detecting SQL meta-characters: / (\% 27) | (\ ') | (\-\-) | (\% 23) | (#) / ix

Fix regular expressions for detecting SQL meta-characters: / ((\% 3D) | (=)) [^ \ n] * ((\% 27) | (\ ') | (\-\-) | (\ % 3B) | (:)) / i

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

Detect SQL injection, regular expression of UNION query keywords: / ((\% 27) | (\ ')) union / ix (\% 27) | (\')

Regular expression to detect MS SQL Server SQL injection attacks: / exec (\ s | \ +) + (s | x) p \ w + / ix

and many more…..

3. String filtering

A more general method:

(The parameters between || 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 if the packet is an illegal character

Prevent SQL injection from 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 |-| + |, ";

// 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. JSP page judgment code:

Use javascript to shield insecure characters on the client

Function introduction: check whether it contains "‘ "," \\ "," / "

Parameter description: the string to be checked

Return value: 0: yes 1: no

The function name is

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;

}
In general, to prevent general SQL injection, you only need to work hard on the code specification.

Whenever there is a variable in the SQL involved in execution, use JDBC (or other data persistence layer) to provide such as: PreparedStatement, remember not to use the method of concatenating strings.

to sum up

The above is the entire content of this article. I hope that the content of this article can bring some help to everyone's study or work. If you have any questions, you can leave a message to communicate.