pingcap / pingcap/tidb

Add SQL blocklist function that blocks statements by normalized SQL text or by keyword matching

Open
#66,048 0 comments 0 reactions 1 assignee Claimed by @niubell View on GitHub
sig/sql-infra type/feature-request
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

# SQL Blocklist by Digest or Keyword
## Summary

Provide a SQL blocklist that blocks statements by normalized SQL text or by
keyword matching. Rules are stored in `mysql.sql_blocklist`, updated without
restart, and applied cluster-wide.

## Scenarios

### Scenario 1: High-frequency SQL

A misbehaving client can generate a burst of repeated SQL or certain high-frequency SQL queries encountered during e-commerce flash sales that overwhelms the cluster (CPU, memory, or storage). Operators need a fast, low-overhead way to block that specific normalized SQL across all TiDB instances immediately, and to remove the rule once the incident is mitigated.

### Scenario 2: Dangerous DDL

DDL such as `DROP DATABASE`, `DROP TABLE`, or large-scale `ALTER TABLE` can
cause irreversible data loss or extended service impact if executed by mistake.
Operators can blocklist the exact normalized DDL to prevent accidental or
malicious execution while keeping normal traffic unaffected.

### Scenario 3: DDL or DML not allowed in the current environment

Some environments (for example, staging or production) may forbid certain DDL
or DML even if they are valid in other environments. A blocklist rule provides
a simple, centralized safeguard to block these statements without changing
application code or privileges.

## Requirements

- Support two rule types:
- SQL digest (normalized SQL text)
- SQL keywords (comma-separated)
- Changes take effect immediately across all TiDB instances.
- No restart required.
- Minimal overhead on the hot path.
- Backward compatible with existing system tables.

## Non-Goals

- No pattern language beyond keyword substring matching.
- No per-user or per-database scoping.
- No on-disk format changes outside `mysql.sql_bocklist`.

## Data Model

Table: `mysql.sql_blocklist`

```sql
CREATE TABLE IF NOT EXISTS mysql.sql_blocklist (
type VARCHAR(16) NOT NULL,
value VARCHAR(1024) NOT NULL
);
```

Rule semantics:

- `type = 'digest'`
- `value` is normalized SQL text (e.g. `delete from t where id = ?`).
- `type = 'keyword'`
- `value` is a comma-separated list of keywords.

## Matching Rules

### Digest rules

Normalize the SQL text using TiDB's SQL normalizer and compare for exact match.

### Keyword rules

- Split the `value` by comma.
- Trim and lowercase each keyword.
- A SQL is denied if all keywords are present as substrings in the SQL text
(case-insensitive).

## Reload and Distribution

### Local reload

`sqlblocklist.Load` reads `mysql.sql_blocklist` and rebuilds the in-memory cache.

### Cluster-wide propagation

- Each TiDB instance runs a watch loop on an etcd key.
- On updates, it reloads its local cache.

### Triggering reload

Reload is triggered by:

- `ADMIN RELOAD SQL_BLOCKLIST`
- DML changes to `mysql.sql_blocklist` that commit successfully.

## Execution Flow

1) On statement execution:
- Compute digest and normalized SQL from `StmtCtx.SQLDigest()`.
- If not in restricted SQL mode, check the blocklist.
2) If a rule matches:
- Return `ErrSQLDeniedByBlocklist`.

Restricted/internal SQL bypasses the blocklist.

## Error Behavior

Return `ErrSQLDeniedByBlocklist` with a message describing the rule:

- `digest_text `
- `keywords `

## Performance Considerations

- Cache is read-only and stored in an `atomic.Pointer`, avoiding locks.
- When the cache is empty, checks are fast and allocate nothing.
- Keyword matching uses lowercase SQL only when keyword rules exist.

## Compatibility

- Uses a new system table `mysql.sql_blocklist` only.
- No changes to existing table formats.
- Existing clusters can be upgraded without data migration.

## Testing

- Unit tests cover:
- Digest by normalized SQL.
- Keyword matching.
- `ADMIN RELOAD SQL_BLOCKLIST`.
- Reload behavior is validated by clearing the auto-reload flag and forcing
manual reload.

## Examples

### Digest rules

```sql
INSERT INTO mysql.sql_blocklist VALUES
('digest', 'delete from t where id = ?'),
('digest', 'update accounts set balance = balance - ? where id = ?'),
('digest', 'select * from orders where status = ? and created_at > ?'),
('digest', 'drop database app');
```

Denied SQL:

```sql
DELETE FROM t WHERE id = 1;
UPDATE accounts SET balance = balance - 10 WHERE id = 42;
SELECT * FROM orders WHERE status = 'paid' AND created_at > '2026-01-01';
DROP DATABASE app;
```

### Keyword rules

```sql
INSERT INTO mysql.sql_blocklist VALUES
('keyword', 'modify column, null'),
('keyword', 'drop table, if exists'),
('keyword', 'create user, identified by');
```

Denied SQL:

```sql
ALTER TABLE t MODIFY COLUMN v BIGINT NULL DEFAULT -1;
DROP TABLE IF EXISTS t;
CREATE USER u IDENTIFIED BY 'pwd';
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.