doc/ANOMALY_DETECTION/API.md
This document provides comprehensive API reference for the Anomaly Detection feature in ProxySQL.
All configuration variables are prefixed with ai_anomaly_ and can be set via the ProxySQL admin interface.
Type: Boolean
Default: true
Dynamic: Yes
Enable or disable the anomaly detection module.
SET ai_anomaly_enabled='true';
SET ai_anomaly_enabled='false';
Example:
-- Disable anomaly detection temporarily
UPDATE mysql_servers SET ai_anomaly_enabled='false';
LOAD MYSQL VARIABLES TO RUNTIME;
Type: Integer (0-100)
Default: 70
Dynamic: Yes
The risk score threshold for blocking queries. Queries with risk scores above this threshold will be blocked if auto-block is enabled.
SET ai_anomaly_risk_threshold='80';
Risk Score Calculation:
Type: Integer
Default: 100
Dynamic: Yes
Maximum number of queries allowed per minute per user/host combination.
Time Window: 1 hour rolling window
-- Set rate limit to 200 queries per minute
SET ai_anomaly_rate_limit='200';
-- Set rate limit to 10 for testing
SET ai_anomaly_rate_limit='10';
Rate Limiting Logic:
Type: Integer (0-100)
Default: 85
Dynamic: Yes
Similarity threshold for embedding-based threat detection (future implementation).
Higher values = more exact matching required.
SET ai_anomaly_similarity_threshold='90';
Type: Boolean
Default: true
Dynamic: Yes
Automatically block queries that exceed the risk threshold.
-- Enable auto-blocking
SET ai_anomaly_auto_block='true';
-- Disable auto-blocking (log-only mode)
SET ai_anomaly_auto_block='false';
When true:
When false:
Type: Boolean
Default: false
Dynamic: Yes
Enable log-only mode (monitoring without blocking).
-- Enable log-only mode
SET ai_anomaly_log_only='true';
Log-Only Mode:
Status variables provide runtime statistics about anomaly detection.
Type: Counter Read-Only: Yes
Total number of anomalies detected since ProxySQL started.
SHOW STATUS LIKE 'ai_detected_anomalies';
Example Output:
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| ai_detected_anomalies | 152 |
+-----------------------+-------+
Prometheus Metric: proxysql_ai_detected_anomalies_total
Type: Counter Read-Only: Yes
Total number of queries blocked by anomaly detection.
SHOW STATUS LIKE 'ai_blocked_queries';
Example Output:
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| ai_blocked_queries | 89 |
+-------------------+-------+
Prometheus Metric: proxysql_ai_blocked_queries_total
The AnomalyResult structure contains the outcome of an anomaly check.
struct AnomalyResult {
bool is_anomaly; ///< True if anomaly detected
float risk_score; ///< 0.0-1.0 risk score
std::string anomaly_type; ///< Type of anomaly
std::string explanation; ///< Human-readable explanation
std::vector<std::string> matched_rules; ///< Rule names that matched
bool should_block; ///< Whether to block query
};
Type: bool
Indicates whether an anomaly was detected.
Values:
true: Anomaly detectedfalse: No anomalyType: float
Range: 0.0 - 1.0
The calculated risk score for the query.
Interpretation:
0.0 - 0.3: Low risk0.3 - 0.6: Medium risk0.6 - 1.0: High riskNote: Compare against ai_anomaly_risk_threshold / 100.0
Type: std::string
Type of anomaly detected.
Possible Values:
"sql_injection": SQL injection pattern detected"rate_limit": Rate limit exceeded"statistical": Statistical anomaly"embedding_similarity": Similar to known threat (future)"multiple": Multiple detection methods triggeredType: std::string
Human-readable explanation of why the query was flagged.
Example:
"SQL injection pattern detected: OR 1=1 tautology"
"Rate limit exceeded: 150 queries/min for user 'app'"
Type: std::vector<std::string>
List of rule names that matched.
Example:
["pattern:or_tautology", "pattern:quote_sequence"]
Type: bool
Whether the query should be blocked based on configuration.
Determined by:
is_anomaly == truerisk_score > ai_anomaly_risk_threshold / 100.0ai_anomaly_auto_block == trueai_anomaly_log_only == falseMain class for anomaly detection operations.
class Anomaly_Detector {
public:
Anomaly_Detector();
~Anomaly_Detector();
int init();
void close();
AnomalyResult analyze(const std::string& query,
const std::string& user,
const std::string& client_host,
const std::string& schema);
int add_threat_pattern(const std::string& pattern_name,
const std::string& query_example,
const std::string& pattern_type,
int severity);
std::string list_threat_patterns();
bool remove_threat_pattern(int pattern_id);
std::string get_statistics();
void clear_user_statistics();
};
Anomaly_Detector();
~Anomaly_Detector();
Description: Creates and destroys the anomaly detector instance.
Default Configuration:
enabled = truerisk_threshold = 70similarity_threshold = 85rate_limit = 100auto_block = truelog_only = falseint init();
Description: Initializes the anomaly detector.
Return Value:
0: Successnon-zero: ErrorInitialization Steps:
Example:
Anomaly_Detector* detector = new Anomaly_Detector();
if (detector->init() != 0) {
// Handle error
}
void close();
Description: Closes the anomaly detector and releases resources.
Example:
detector->close();
delete detector;
AnomalyResult analyze(const std::string& query,
const std::string& user,
const std::string& client_host,
const std::string& schema);
Description: Main entry point for anomaly detection.
Parameters:
query: The SQL query to analyzeuser: Username executing the queryclient_host: Client IP addressschema: Database schema nameReturn Value: AnomalyResult structure
Detection Pipeline:
Example:
Anomaly_Detector* detector = GloAI->get_anomaly_detector();
AnomalyResult result = detector->analyze(
"SELECT * FROM users WHERE username='admin' OR 1=1--'",
"app_user",
"192.168.1.100",
"production"
);
if (result.should_block) {
// Block the query
std::cerr << "Blocked: " << result.explanation << std::endl;
}
int add_threat_pattern(const std::string& pattern_name,
const std::string& query_example,
const std::string& pattern_type,
int severity);
Description: Adds a custom threat pattern to the detection database.
Parameters:
pattern_name: Name for the patternquery_example: Example query representing the threatpattern_type: Type of pattern (e.g., "sql_injection", "ddos")severity: Severity level (1-10)Return Value:
> 0: Pattern ID-1: ErrorExample:
int pattern_id = detector->add_threat_pattern(
"custom_sqli",
"SELECT * FROM users WHERE id='1' UNION SELECT 1,2,3--'",
"sql_injection",
8
);
std::string list_threat_patterns();
Description: Returns JSON-formatted list of all threat patterns.
Return Value: JSON string containing pattern list
Example:
std::string patterns = detector->list_threat_patterns();
std::cout << patterns << std::endl;
// Output: {"patterns": [{"id": 1, "name": "sql_injection_or", ...}]}
bool remove_threat_pattern(int pattern_id);
Description: Removes a threat pattern by ID.
Parameters:
pattern_id: ID of pattern to removeReturn Value:
true: Successfalse: Pattern not foundstd::string get_statistics();
Description: Returns JSON-formatted statistics.
Return Value: JSON string with statistics
Example Output:
{
"total_queries_analyzed": 15000,
"anomalies_detected": 152,
"queries_blocked": 89,
"detection_methods": {
"sql_injection": 120,
"rate_limiting": 25,
"statistical": 7
},
"user_statistics": {
"app_user": {"query_count": 5000, "blocked": 5},
"admin": {"query_count": 200, "blocked": 0}
}
}
void clear_user_statistics();
Description: Clears all accumulated user statistics.
Use Case: Resetting statistics after configuration changes.
The anomaly detection is integrated into the MySQL query processing flow.
File: lib/MySQL_Session.cpp
Function: MySQL_Session::handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_detect_ai_anomaly()
Location: Line ~3626
Flow:
Client Query
↓
Query Parsing
↓
libinjection SQLi Detection
↓
AI Anomaly Detection ← Integration Point
↓
Query Execution
↓
Result Return
When a query is blocked:
Example Error:
ERROR 1313 (HY000): Query blocked by anomaly detection: SQL injection pattern detected
Anomaly detection bypass for admin users: