Specific Practical Methods for Preventing SQL Injection in Java

1. Force PreparedStatement (parametric query)

principle: Separate the SQL statement structure from the user input data to ensure that the input content is always processed as "data" rather than executable code.

Correct example:

String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
try (Connection conn = ();
     PreparedStatement pstmt = (sql)) {
    
    (1, username); // Automatically escape special characters (such as ' -> \')    (2, password);
    
    try (ResultSet rs = ()) {
        // Processing results    }
}

Wrong practice (high risk!):

// Directly splice SQL (there is a risk of injection!)String sql = "SELECT * FROM users WHERE username = '" + username + "'";
Statement stmt = ();
ResultSet rs = (sql);

2. Use ORM frameworks (such as Hibernate/JPA)

The ORM framework will automatically generate parameterized queries to reduce the risk of injection of handwritten SQL.

Hibernate example:

// Use the Hibernate Criteria APICriteriaBuilder cb = ();
CriteriaQuery<User> query = ();
Root<User> root = ();

(
    (("username"), username),
    (("password"), password)
);

User user = (query).uniqueResult();

HQL parameterization:

String hql = "FROM User WHERE username = :username";
Query<User> query = (hql, );
("username", username);
User user = ();

3. Input verification and filtering

Whitelist verification:

// Verify whether the username complies with the rules (only letters, numbers, and underscores are allowed)if (!("^[a-zA-Z0-9_]{4,20}$")) {
    throw new IllegalArgumentException("Invalid username");
}

Escape special characters (alternative plan):

If SQL must be spliced ​​(such as dynamic table names), it needs to be strictly filtered:

// Escape with Apache Commons TextString safeInput = (input); // It is only suitable for simple scenarios, and dependencies are not recommended!

4. Avoid dynamic splicing of SQL

High-risk scenarios: Dynamic table names, column names, etc. cannot be passedPreparedStatementParameterization.
Safety practices: Use whitelist to verify the legal value:

// Dynamic table name verification (only predefined legal table names are allowed)Set<String> validTables = ("users", "products");
if (!(tableName)) {
    throw new IllegalArgumentException("Invalid table name");
}
String sql = "SELECT * FROM " + tableName; // The splicing is safe at this time

5. Database configuration and permissions

• Minimum permission principle: The database account used by the application should only haveSELECT、INSERT、UPDATEReplace necessary permissions, prohibitedDROP、GRANTetc.

• Disable sensitive functions: Such as MySQLLOAD_FILE、INTO OUTFILE。

6. Other safety measures

Hide error message:

try {
    // Perform database operations} catch (SQLException e) {
    // Production environment returns a common error to avoid leaking details    ("Database error", e);
    throw new RuntimeException("Internal server error");
}

Use security tools:

• OWASP ESAPI: Provides a secure SQL escape method.

• SQL Injection Scan Tool:integratedsqlmaporSonarQubeConduct code audits.

Summary: The core rules for Java defense SQL injection

Scene

Safety practices

High-risk practices (avoid!)

Execute SQL query

usePreparedStatementParameterization

Stitching string generation SQL

Dynamic table name/column name

Whitelist verification of legal values

Directly splicing user input

Enter verification

Regular expression whitelist filtering

Verify only on the front end or not

Error message processing

Log logs and return a general error message

Return detailed database error message

ORM framework

Priority to using Hibernate/JPA's Criteria or HQL

Manual splicing of HQL or JPQL

Key points：

• Never trust user input: All external inputs (including HTTP request parameters, cookies, headers) need to be verified and escaped.

• Code review: Regularly check whether the item existsStatementOr string splicing SQL code.

• Depend on updates: Ensure that the database driver and ORM framework remain up to date and fix known vulnerabilities.

The above is the detailed content of the specific practical methods for Java to prevent SQL injection. For more information about Java to prevent SQL injection, please pay attention to my other related articles!