SQL Blacklist by Digest or Keyword

Summary

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

Scenarios

Scenario 1: High-frequency SQL

A misbehaving client can generate a burst of repeated SQL 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 blacklist 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 blacklist 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_blacklist.

Data Model

Table: mysql.sql_blacklist

CREATE TABLE IF NOT EXISTS mysql.sql_blacklist (
  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

sqlblacklist.Load reads mysql.sql_blacklist 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_BLACKLIST
• DML changes to mysql.sql_blacklist 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 blacklist.
2. If a rule matches:
   • Return ErrSQLDeniedByBlacklist.

Restricted/internal SQL bypasses the blacklist.

Error Behavior

Return ErrSQLDeniedByBlacklist with a message describing the rule:

• digest_text <normalized>
• keywords <k1, k2, ...>

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_blacklist 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_BLACKLIST.
• Reload behavior is validated by clearing the auto-reload flag and forcing
  manual reload.

Examples

Digest rules

INSERT INTO mysql.sql_blacklist 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:

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

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

Denied SQL:

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