How SQL Injection happens in Python database scripts and how to fix it

The Vulnerability: SQL Injection in discover_relationships.py

The MangosSuperUI/Scripts/discover_relationships.py file is responsible for analyzing database schemas and discovering relationships between tables — a core piece of infrastructure that queries live database metadata. But two of its functions, sample_distinct_values and count_distinct, contained a critical SQL injection flaw that could have allowed an attacker to execute arbitrary SQL against the underlying database.

The root cause? Python f-strings used to build SQL queries, with database names, table names, and column names dropped in raw — no escaping, no validation, no parameterization.

The Vulnerability Explained

What the code looked like before the fix

In sample_distinct_values (around line 195), the original query was constructed like this:

# VULNERABLE — before the fix
cursor.execute(f"SELECT DISTINCT `{column}` FROM `{database}`.`{table}` "
               f"WHERE `{column}` IS NOT NULL AND `{column}` != 0 LIMIT {limit}")

And in count_distinct (around line 216):

# VULNERABLE — before the fix
cursor.execute(f"SELECT COUNT(DISTINCT `{column}`) FROM `{database}`.`{table}` "
               f"WHERE `{column}` IS NOT NULL AND `{column}` != 0")

The variables database, table, and column are passed in from the caller — and if those values come from command-line arguments, environment variables, or any external configuration, an attacker who controls them can inject arbitrary SQL.

Why backtick quoting alone isn't enough

Many developers assume that wrapping identifiers in backticks (`) is sufficient protection. It isn't. Backticks delimit MySQL identifiers, but a backtick inside the identifier value will terminate the delimiter early. Consider what happens if column is set to:

id` FROM users WHERE 1=1; DROP TABLE users; -- 

The resulting query becomes:

SELECT DISTINCT `id` FROM users WHERE 1=1; DROP TABLE users; -- ` FROM `mydb`.`mytable` WHERE ...

The backtick is closed prematurely, and the injected SQL executes. The LIMIT {limit} interpolation is equally dangerous — if limit is not strictly an integer, arbitrary SQL fragments can be appended there too.

Real-world attack scenario

Imagine discover_relationships.py is invoked as part of an automated pipeline where the database and table names are read from a configuration file or passed as arguments:

python discover_relationships.py --database "mydb" --table "orders`; GRANT ALL ON *.* TO 'attacker'@'%'; --"

With the vulnerable code, this would construct and execute:

SELECT DISTINCT `col` FROM `mydb`.`orders`; GRANT ALL ON *.* TO 'attacker'@'%'; -- `.`...`

Depending on the database user's privileges, this could escalate to full database compromise, data exfiltration, or destruction of data.

The Fix

What changed

The fix applied two complementary defenses across both functions:

1. Backtick escaping for all SQL identifiers

Before using database, table, or column in a query, each value now has its backtick characters doubled — the standard MySQL escape sequence for literal backticks inside backtick-quoted identifiers:

db_esc  = database.replace('`', '``')
tbl_esc = table.replace('`', '``')
col_esc = column.replace('`', '``')

This means an attacker-supplied value like orders`; DROP TABLE -- becomes orders; DROP TABLE -- `` inside the query, which MySQL treats as a literal identifier name rather than a SQL injection vector.

2. Parameterized query for the LIMIT clause

The LIMIT value, previously interpolated raw as {limit}, is now passed as a proper query parameter:

# FIXED — after the fix
cursor.execute(f"SELECT DISTINCT `{col_esc}` FROM `{db_esc}`.`{tbl_esc}` "
               f"WHERE `{col_esc}` IS NOT NULL AND `{col_esc}` != 0 LIMIT %s",
               (int(limit),))

The int(limit) cast ensures the value is strictly numeric before it even reaches the database driver, and the %s placeholder hands off binding to the database connector — which handles escaping correctly.

Before vs. after comparison

Why this approach is correct for identifiers

Standard SQL parameterization (using %s placeholders) works for values — string literals, numbers, dates. It does not work for SQL identifiers like table names and column names, because the database driver quotes and escapes them as string literals, not as identifiers. The correct approach for identifiers in MySQL is exactly what this fix does: use backtick quoting and escape any literal backticks in the name by doubling them.

Prevention & Best Practices

1. Never use f-strings or % formatting to build SQL

This is the single most important rule. If you find yourself writing:

cursor.execute(f"SELECT * FROM {table_name}")

Stop. This is a SQL injection waiting to happen.

2. Use parameterization for all values

For any value that isn't a SQL identifier (table/column name), use your database driver's parameterization:

# MySQL Connector / PyMySQL
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))

# sqlite3
cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))

3. Use allowlists for identifier names when possible

If the set of valid database, table, or column names is known ahead of time, validate against an allowlist before constructing any query:

ALLOWED_TABLES = {"orders", "customers", "products"}
if table not in ALLOWED_TABLES:
    raise ValueError(f"Invalid table name: {table}")

This is the strongest defense for identifier injection.

4. Escape identifiers when allowlists aren't feasible

When dynamic identifier names are truly necessary (as in schema discovery tools like this one), use the backtick-doubling approach from the fix, or use a library that provides identifier quoting (e.g., mysql.connector's cmd_query_iter or SQLAlchemy's quoted_name).

5. Run static analysis in CI

Tools that can catch this pattern:
- Bandit (B608 rule: hardcoded SQL expressions)
- Semgrep with the python.lang.security.audit.formatted-sql-query ruleset
- AI-based scanners like Orbis AppSec (which detected this exact issue)

6. Reference standards

• OWASP SQL Injection Prevention Cheat Sheet: The definitive guide to parameterization and input validation
• CWE-89: Improper Neutralization of Special Elements used in an SQL Command

Key Takeaways

• F-string SQL construction in discover_relationships.py was the direct cause — even with backtick quoting, raw identifier interpolation is exploitable by terminating the backtick early.
• LIMIT {limit} was a separate injection point — numeric-looking parameters are still dangerous if not cast and parameterized.
• Backtick doubling (replace('', '`')) is the correct MySQL escape for identifier names — standard %s parameterization cannot be used for table or column names.
• Schema introspection tools are high-risk targets — they frequently accept database/table/column names as inputs, making them prime candidates for identifier injection.
• The count_distinct function at line 216 had the same pattern and was fixed in the same PR — always audit all similar call sites when fixing injection vulnerabilities.

How Orbis AppSec Detected This

• Source: User-controlled input parameters (database, table, column, limit) passed to sample_distinct_values() and count_distinct() in discover_relationships.py
• Sink: cursor.execute(f"SELECT DISTINCT{column}FROM{database}.{table}...") at lines 195, 198, 216, and 234 — raw f-string interpolation of identifiers directly into SQL
• Missing control: No backtick escaping, no identifier allowlist validation, and no parameterization for the LIMIT clause
• CWE: CWE-89 — Improper Neutralization of Special Elements used in an SQL Command
• Fix: Added replace('', '`') escaping for all three identifier variables and replaced LIMIT {limit} with a parameterized LIMIT %s binding with an explicit int() cast

Orbis AppSec automatically detected this vulnerability and opened a pull request with the fix. Try Orbis AppSec on your repositories to find and fix issues like this automatically.

Conclusion

SQL injection through identifier interpolation is a subtle but critical vulnerability — and it's easy to overlook precisely because backtick quoting looks safe. The discover_relationships.py script is a perfect example of how schema introspection tools, which by design accept dynamic table and column names, can become injection vectors if those names aren't properly escaped.

The fix here is a model for how to handle this correctly in Python MySQL code: escape backticks in identifier names by doubling them, and use %s parameterization with an explicit type cast for any numeric parameters like LIMIT. Combined with static analysis in CI, these practices can prevent entire classes of SQL injection from reaching production.

References

• CWE-89: Improper Neutralization of Special Elements used in an SQL Command
• OWASP SQL Injection Prevention Cheat Sheet
• Python MySQL Connector — Parameterized Queries Documentation
• Semgrep rules for formatted SQL queries in Python
• fix: add parameterized queries in discover_relationships.py