Preventing Full Table Updates with MySQL sql_safe_updates and SQL Interceptors

Many developers encounter accidental full‑table updates when a WHERE clause is omitted, e.g., updating rows where company_id is null.

1. Prevent full‑table updates

Always include appropriate WHERE conditions in UPDATE/DELETE statements.

2. sql_safe_updates

The MySQL variable sql_safe_updates blocks UPDATE or DELETE statements without a WHERE clause when set to ON.

Enable it per session:

SET sql_safe_updates = 1;

Or globally:

SET GLOBAL sql_safe_updates = 1;

Check the current setting:

SHOW VARIABLES LIKE '%sql_safe_updates%';

When enabled, the following statement triggers an error:

UPDATE user SET username='javaboy';

3. SQL Interceptor (MyBatis‑Plus)

MyBatis‑Plus provides IllegalSQLInnerInterceptor to block high‑risk SQL such as full‑table updates.

Configuration example:

@Configuration
public class MybatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new IllegalSQLInnerInterceptor());
        return interceptor;
    }
}

With the interceptor, an UPDATE without WHERE raises an error, unless a dummy condition like WHERE 1=1 is present.

4. IDEA plugins

Various IntelliJ IDEA plugins can also warn about risky SQL statements.

5. Code review

Regular code reviews help catch missing WHERE clauses before they reach production.

6. Solution

Besides using sql_safe_updates and interceptors, always write explicit WHERE conditions or use safe‑update patterns.