Five Methods to Prevent SQL Injection

Time： 2024-09-29 Column：Security views：289

SQL injection is a dangerous web vulnerability that allows attackers to manipulate databases by injecting malicious SQL commands. This article explores how SQL injection works, its common attack vectors, and the most effective prevention techniques.

I. Introduction to SQL Injection

SQL Injection refers to inserting SQL commands into web form submissions or query strings of a URL or page request, ultimately tricking the server into executing malicious SQL commands.

Specifically, it exploits existing applications by injecting (malicious) SQL commands into the backend database engine, allowing access to a database on a vulnerable website through (malicious) SQL queries input into web forms, rather than executing SQL statements as intended by the developer.

SQL injection is one of the most common forms of web attacks. Instead of exploiting bugs in the operating system, it targets the negligence of programmers when coding. Through SQL commands, attackers can bypass authentication or even modify databases without an account.

II. General Approach to SQL Injection Attacks

Never trust user input – Validate user input through methods like regular expressions or limit input length, and convert single quotes (') and double dashes (--).
Never use dynamically constructed SQL – Use parameterized SQL or stored procedures for data queries and storage.
Never use database connections with admin privileges – Use individual, limited-permission database connections for each application.
Avoid storing sensitive information in plain text – Encrypt or hash passwords and other sensitive information.
Display minimal error messages to the user – Use custom error messages to mask system details and log exceptions in a separate table.

III. SQL Injection Attack Example

Consider a login interface where a username and password are required. Here's an example of input that could allow unauthorized access:

Username: 'or 1 = 1 --
Password: (leave empty)

If no special handling is in place, the malicious user will be able to log in successfully. Here's why:

In a typical backend authentication program, the SQL query might look like this:

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

When the above input is provided, the SQL statement becomes:

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

Breaking it down:

The condition username='' or 1 = 1 will always evaluate to true.
The -- means the rest of the statement is treated as a comment, so the password check is skipped.

This way, the user can easily trick the system into granting access.

The situation can be more dangerous if an attacker runs something like:

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

The consequences of this are devastating as the database could be deleted.

IV. Countermeasures

Here are some countermeasures specifically for JSP:

PreparedStatement (Simple and Effective Method)
Use precompiled statement sets, which are inherently protected against SQL injection as they handle SQL injection at the preparation stage. Pass values using the setXXX methods.
Advantages:
Principle:SQL injection only affects the preparation (compilation) process of SQL queries. PreparedStatement has already prepared the query, and at the execution stage, the input is treated as data rather than being parsed again, thus avoiding SQL injection.

(1) Improved code readability and maintainability.
(2) Enhanced performance with PreparedStatement.
(3) Most importantly, significantly improved security.

Filtering Input Parameters with Regular Expressions
Import the necessary package:
```
import java.util.regex.*;
```
Regular expression example:
```
private String CHECKSQL = "^(.+)\\sand\\s(.+)|(.+)\\sor(.+)\\s$";
```
To check if a string matches:
```
Pattern.matches(CHECKSQL, targetStr);
```
Some specific regular expressions include:

Detecting SQL meta-characters:
```
/(\%27)|(\’)|(\-\-)|(\%23)|(#)/ix
```

Corrected detection of SQL meta-characters:

/((\%3D)|(=))[^\n]*((\%27)|(\’)|(\-\-)|(\%3B)|(:))/i

Typical SQL injection detection:

/\w*((\%27)|(\’))((\%6F)|o|(\%4F))((\%72)|r|(\%52))/ix

Detecting UNION keyword in SQL injection:
```
/((\%27)|(\’))union/ix(\%27)|(\’)
```
Detecting SQL injection targeting MS SQL Server:
```
/exec(\s|\+)+(s|x)p\w+/ix
```

String Filtering

A more common method for filtering strings:

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

Check for Malicious Characters in JSP

Prevent SQL injection from URLs:

package sql_inj;

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_str.split("\\|");
        for (int i = 0; i < inj_stra.length; i++) {
            if (str.indexOf(inj_stra[i]) >= 0) {
                return true;
            }
        }
        return false;
    }
}

Client-side Validation with JavaScript

Use JavaScript to filter out unsafe characters like "'", "\\", and "/" on the client side.

Example function:

function check(a) {
    fibdn = new Array("'", "\\", "/");
    for (let i = 0; i < fibdn.length; i++) {
        for (let j = 0; j < a.length; j++) {
            if (a.charAt(j) == fibdn[i]) {
                return 0;
            }
        }
    }
    return 1;
}

Additionally, in Druid database connection pools, you can use WallFilter to prevent SQL injection:

<bean id="wall-filter-config" class="com.alibaba.druid.wall.WallConfig" init-method="init">
    <property name="dir" value="META-INF/druid/wall/mysql" />
</bean>

<bean id="wall-filter" class="com.alibaba.druid.wall.WallFilter">
    <property name="dbType" value="mysql" />
    <property name="config" ref="wall-filter-config" />
</bean>

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
    ...
    <property name="proxyFilters">
        <list>
            <ref bean="wall-filter"/>
        </list>
    </property>
</bean>

In conclusion, preventing common SQL injections requires proper coding practices. Whenever you have variables in SQL execution, use JDBC (or another data persistence layer) with tools like PreparedStatement and avoid using string concatenation for SQL queries.

💰 Support Us