Query Firewall in ProxySQL
ProxySQL can act as a query firewall — blocking, flagging or redirecting dangerous or unauthorized SQL queries before they reach your MySQL backends. This provides an additional layer of security and protection against SQL injection, accidental data deletion, and runaway queries.
Blocking Queries
SQL — Block Dangerous Queries
-- Block DROP TABLE completely
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, error_msg, apply, comment)
VALUES
(1000, 1, '^DROP TABLE', 'Query blocked by ProxySQL: DROP TABLE not allowed', 1, 'Block DROP TABLE');
-- Block DROP DATABASE
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, error_msg, apply, comment)
VALUES
(1001, 1, '^DROP DATABASE', 'Query blocked: DROP DATABASE not allowed', 1, 'Block DROP DATABASE');
-- Block TRUNCATE on production tables
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, error_msg, apply, comment)
VALUES
(1002, 1, '^TRUNCATE TABLE (orders|payments|users)', 'Blocked: Cannot truncate critical tables', 1, 'Block TRUNCATE critical tables');
-- Block DELETE without WHERE clause
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, error_msg, apply, comment)
VALUES
(1003, 1, '^DELETE FROM [a-zA-Z_]+ *$', 'Blocked: DELETE without WHERE not allowed', 1, 'Block DELETE without WHERE');
-- Block UPDATE without WHERE clause
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, error_msg, apply, comment)
VALUES
(1004, 1, '^UPDATE [a-zA-Z_]+ SET [^W]* *$', 'Blocked: UPDATE without WHERE', 1, 'Block UPDATE without WHERE');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; Whitelist Mode — Allow Only Specific Queries
SQL — Whitelist Mode
-- Allow only specific query types (whitelist approach)
-- Rule 1: Allow SELECT
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, destination_hostgroup, apply, comment)
VALUES (100, 1, '^SELECT', 20, 1, 'Allow SELECT');
-- Rule 2: Allow INSERT on specific tables
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, destination_hostgroup, apply, comment)
VALUES (101, 1, '^INSERT INTO (orders|logs|events)', 10, 1, 'Allow INSERT on approved tables');
-- Rule 3: Allow stored procedure calls
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, destination_hostgroup, apply, comment)
VALUES (102, 1, '^CALL ', 10, 1, 'Allow stored procedure calls');
-- Rule 4: Block everything else
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, error_msg, apply, comment)
VALUES (9999, 1, '.*', 'Query type not permitted', 1, 'Default deny all');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; Per-User Firewall Rules
SQL — Per-User Firewall
-- Read-only user — block all writes
INSERT INTO mysql_query_rules
(rule_id, active, username, match_pattern, error_msg, apply, comment)
VALUES
(200, 1, 'readonly_user', '^(INSERT|UPDATE|DELETE|DROP|TRUNCATE|ALTER|CREATE)',
'User readonly_user is not allowed to run write queries', 1,
'Block writes for readonly_user');
-- API user — allow only specific schema
INSERT INTO mysql_query_rules
(rule_id, active, username, schemaname, match_pattern, destination_hostgroup, apply, comment)
VALUES
(201, 1, 'api_user', 'api_db', '^SELECT', 20, 1, 'api_user reads from api_db only');
INSERT INTO mysql_query_rules
(rule_id, active, username, error_msg, apply, comment)
VALUES
(202, 1, 'api_user', 'api_user cannot access other schemas', 1, 'Restrict api_user');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; Rate Limiting with Delay
SQL — Rate Limiting
-- Add a delay to slow down heavy reporting queries
INSERT INTO mysql_query_rules
(rule_id, active, username, match_pattern, delay, destination_hostgroup, apply, comment)
VALUES
(300, 1, 'reports_user', '^SELECT', 500, 20, 1,
'Add 500ms delay to all report queries to reduce load');
-- Timeout runaway queries (kill after 30 seconds)
INSERT INTO mysql_query_rules
(rule_id, active, match_pattern, timeout, destination_hostgroup, apply, comment)
VALUES
(301, 1, '^SELECT', 30000, 20, 1, 'Kill SELECTs running more than 30s');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; Monitor Blocked Queries
SQL — Monitor Blocked Queries
-- See how many queries are being blocked
SELECT rule_id, hits, error_msg, match_pattern, comment
FROM stats.stats_mysql_query_rules
WHERE error_msg IS NOT NULL
ORDER BY hits DESC;
-- Log blocked queries (set log=1 on firewall rules)
UPDATE mysql_query_rules SET log=1
WHERE error_msg IS NOT NULL;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;⚠ Warning: Test all firewall rules carefully in development before applying to production. A badly written regex can block legitimate queries and cause application outages.
