Posted inDatabase

Defeating SQL Injection in MySQL Production using ProxySQL Firewall

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Why Application-Level Security Isn’t Enough

No matter how careful your dev team is, SQL Injection (SQLi) vulnerabilities remain a constant threat to large systems. These flaws often creep in through legacy code that hasn’t been refactored or via third-party libraries we implicitly trust.

Relying solely on application-level data validation is like gambling your entire database on a developer’s vigilance. A single oversight in using Prepared Statements can allow an attacker to dump your entire dataset with a simple search query. I once handled a leak of 50,000 customer records caused by a single missing filter parameter. That’s why you need a second line of defense sitting right in front of your database: ProxySQL Firewall.

Comparing Database Protection Methods

Before configuring ProxySQL, let’s look at current solutions to see why it stands out:

  • WAF (Web Application Firewall): Cloudflare or ModSecurity are great at blocking HTTP requests. However, they don’t deeply understand the MySQL protocol and can be bypassed by complex SQL obfuscation techniques.
  • Database Hardening: Limiting user permissions (GRANT/REVOKE) is mandatory. However, it doesn’t stop a valid user account from being compromised to execute mass deletion commands.
  • ProxySQL Firewall: Operates as a Layer 7 proxy. It inspects every SQL statement, matches it against patterns, and decides whether to allow, block, or rewrite it instantly.

Combining all three layers is the optimal approach, but ProxySQL serves as the most reliable final line of defense.

How ProxySQL Firewall Works

ProxySQL doesn’t block by IP like traditional firewalls; it blocks based on statement content. It uses the mysql_query_rules table to define control policies.

When a query passes through, ProxySQL matches it against a match_pattern (usually a Regex). If it matches, the system performs one of the following actions:

  • OK: Allows execution.
  • BLOCK: Returns an error immediately to the App without sending the query to the DB, protecting resources.
  • REWRITE: Automatically modifies the query to be safe before sending it.

Real-world Deployment: Blocking Dangerous Queries

Assuming you already have a ProxySQL cluster acting as a load balancer, follow these two steps to turn it into a true Firewall.

Step 1: Setting up a Blacklist

This is the fastest way to block classic patterns like OR 1=1 or destructive commands like DROP TABLE from the web side.

-- Access the admin interface
mysql -u admin -padmin -h 127.0.0.1 -P6032

-- Block queries containing 'OR 1=1' (commonly used to bypass logins)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, error_msg, apply) 
VALUES (100, 1, '(?i)OR\s+\d+=\d+', 'Access Denied: Potential SQL Injection Detected', 1);

-- Block DROP TABLE commands from the application user
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, error_msg, apply) 
VALUES (101, 1, '(?i)^DROP\s+TABLE', 'Access Denied: Dangerous command not allowed', 1);

-- Apply changes immediately
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

The (?i) flag makes the Regex case-insensitive. If an attacker uses tools like sqlmap for probing, ProxySQL will immediately block it and return a custom error message.

Step 2: Whitelisting (The Absolute Security Strategy)

Blacklisting is never enough because hackers always find ways to bypass rules. The Whitelisting method only allows approved queries to pass; everything else is forbidden.

First, enable logging to collect “clean” queries currently running:

-- Log queries for analysis
UPDATE mysql_query_rules SET log=1 WHERE rule_id=...;

Once you’ve identified the safe queries, create rules to allow them with apply=1. Finally, add a rule with the highest rule_id to block everything else.

-- Final rule: Block all unknown queries
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, error_msg, apply) 
VALUES (9999, 1, '.', 'Access Denied: Query not whitelisted', 1);

LOAD MYSQL QUERY RULES TO RUNTIME;

You need a deep understanding of the queries your application generates to avoid false positives. If you need to process logs from CSV to JSON for faster whitelist analysis, I often use toolcraft.app/en/tools/data/csv-to-json. This tool runs client-side, so sensitive data is never uploaded to the server.

Practical Operational Experience

Deploying this protection layer in Production requires caution to avoid service disruptions:

  1. Observation Mode: When creating new rules, set active=0 and use log=1 to monitor first. If no issues occur after 24-48 hours, switch to active=1.
  2. Rule Priority: ProxySQL processes rules from low to high IDs. Place specific rules (Whitelist) at lower IDs and the catch-all blocking rule at the highest ID.
  3. Performance Measurement: ProxySQL processes Regex extremely fast, usually adding < 0.5ms of latency. However, if you have thousands of complex rules, optimize your Regex to prevent system slowdowns.
  4. Continuous Monitoring: Monitor the stats_mysql_query_rules table to see which rules are being triggered unexpectedly, helping you identify early attack signs.
-- See statistics on which rules are blocking the most
SELECT rule_id, hits FROM stats.stats_mysql_query_rules;

Conclusion

Setting up ProxySQL Firewall isn’t difficult, but maintaining it requires careful synchronization with your application source code. It’s a worthy investment to protect your database from silly mistakes or targeted attacks. Don’t wait until you see a DROP DATABASE command in your logs to start worrying about security.

Share:
Tags: