doc/ANOMALY_DETECTION/README.md
The Anomaly Detection module provides real-time security threat detection for ProxySQL using a multi-stage analysis pipeline. It identifies SQL injection attacks, unusual query patterns, rate limiting violations, and statistical anomalies.
-- Via admin interface
SET genai-anomaly_enabled='true';
-- Set risk threshold (0-100)
SET genai-anomaly_risk_threshold='70';
-- Set rate limit (queries per minute)
SET genai-anomaly_rate_limit='100';
-- Enable auto-blocking
SET genai-anomaly_auto_block='true';
-- Or enable log-only mode
SET genai-anomaly_log_only='false';
-- Check statistics
SHOW STATUS LIKE 'ai_detected_anomalies';
SHOW STATUS LIKE 'ai_blocked_queries';
-- View Prometheus metrics
curl http://localhost:4200/metrics | grep proxysql_ai
| Variable | Default | Description |
|---|---|---|
genai-anomaly_enabled | true | Enable/disable anomaly detection |
genai-anomaly_risk_threshold | 70 | Risk score threshold (0-100) for blocking |
genai-anomaly_rate_limit | 100 | Max queries per minute per user/host |
genai-anomaly_similarity_threshold | 85 | Similarity threshold for embedding matching (0-100) |
genai-anomaly_auto_block | true | Automatically block suspicious queries |
genai-anomaly_log_only | false | Log anomalies without blocking |
| Variable | Description |
|---|---|
ai_detected_anomalies | Total number of anomalies detected |
ai_blocked_queries | Total number of queries blocked |
Detects common SQL injection patterns using regex and keyword matching:
Patterns Detected:
OR 1=1, AND 1=1'' OR ''=''UNION SELECTDROP TABLE--, /* */0x414243CONCAT(0x41, 0x42)INTO OUTFILE, LOAD_FILESLEEP(), BENCHMARK()Example:
-- This query will be blocked:
SELECT * FROM users WHERE username='admin' OR 1=1--' AND password='xxx'
Normalizes queries for consistent pattern matching:
Example:
-- Input:
SELECT * FROM users WHERE name='John' -- comment
-- Normalized:
select * from users where name=?
Tracks query rates per user and host:
Configuration:
SET ai_anomaly_rate_limit='100';
Uses Z-score analysis to detect outliers:
Example:
-- Unusually large result set:
SELECT * FROM huge_table -- May trigger statistical anomaly
(Framework for future implementation) Detects similarity to known threat patterns using vector embeddings.
-- Blocked: OR 1=1 tautology
mysql> SELECT * FROM users WHERE username='admin' OR 1=1--';
ERROR 1313 (HY000): Query blocked: SQL injection pattern detected
-- Blocked: UNION SELECT
mysql> SELECT name FROM products WHERE id=1 UNION SELECT password FROM users;
ERROR 1313 (HY000): Query blocked: SQL injection pattern detected
-- Blocked: Comment injection
mysql> SELECT * FROM users WHERE id=1-- AND password='xxx';
ERROR 1313 (HY000): Query blocked: SQL injection pattern detected
-- Set low rate limit for testing
SET ai_anomaly_rate_limit='10';
-- After 10 queries in 1 minute:
mysql> SELECT 1;
ERROR 1313 (HY000): Query blocked: Rate limit exceeded for user 'app_user'
-- Unusual query pattern detected
mysql> SELECT * FROM users CROSS JOIN orders CROSS JOIN products;
-- May trigger: Statistical anomaly detected (high result count)
For monitoring without blocking:
-- Enable log-only mode
SET ai_anomaly_log_only='true';
SET ai_anomaly_auto_block='false';
-- Queries will be logged but not blocked
-- Monitor via:
SHOW STATUS LIKE 'ai_detected_anomalies';
# View AI metrics
curl http://localhost:4200/metrics | grep proxysql_ai
# Output includes:
# proxysql_ai_detected_anomalies_total
# proxysql_ai_blocked_queries_total
-- Check detection statistics
SELECT * FROM stats_mysql_global WHERE variable_name LIKE 'ai_%';
-- View current configuration
SELECT * FROM runtime_mysql_servers WHERE variable_name LIKE 'ai_anomaly_%';
Check if legitimate queries match patterns:
Adjust risk threshold:
SET ai_anomaly_risk_threshold='80'; -- Higher threshold
Adjust rate limit:
SET ai_anomaly_rate_limit='200'; -- Higher limit
If legitimate queries are being flagged:
Enable log-only mode to investigate:
SET ai_anomaly_log_only='true';
SET ai_anomaly_auto_block='false';
Check logs for specific patterns:
tail -f proxysql.log | grep "Anomaly:"
Adjust configuration based on findings
If detection seems inactive:
Verify anomaly detection is enabled:
SELECT * FROM runtime_mysql_servers WHERE variable_name='ai_anomaly_enabled';
Check logs for errors:
tail -f proxysql.log | grep "Anomaly:"
Verify AI features are initialized:
grep "AI_Features" proxysql.log
See API.md for complete API documentation.
See ARCHITECTURE.md for detailed architecture information.
See TESTING.md for testing guide and examples.