How to Fix SQL Injection Properly (Not Just Partially)

Why SQL Injection Is Still Everywhere

SQL injection has been on the OWASP Top 10 continuously for nearly two decades. Most developers know it exists. Most developers believe their application is not vulnerable to it. Many are wrong — not because they do not know about parameterised queries, but because their codebase uses them inconsistently, or because they are used correctly in 95% of places and incorrectly in a few that matter.

The places where SQL injection slips through are usually not the main login form — that gets attention. They are the search functionality added in a hurry, the admin filtering feature that is "internal only," the dynamic ORDER BY clause where parameterised queries do not quite work in the usual way, or the legacy function someone wrote before the team established coding standards. One vulnerable query in a production application is enough.

The Correct Fix: Parameterised Queries

A parameterised query (also called a prepared statement) separates SQL code from data. The database receives the query structure first — compiled and fixed — and then receives the values separately. No matter what a user submits as input, it cannot alter the query structure.

**Vulnerable (string concatenation):**

```python user_id = request.args.get('id') query = "SELECT * FROM users WHERE id = " + user_id cursor.execute(query) ```

If `user_id` is `1 OR 1=1`, the query becomes `SELECT * FROM users WHERE id = 1 OR 1=1`, which returns every row.

**Correct (parameterised):**

```python user_id = request.args.get('id') cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,)) ```

The `%s` is a placeholder. The database driver handles escaping. The value `1 OR 1=1` is treated as a literal string value, not as SQL syntax.

**In Node.js with mysql2:**

```javascript const userId = req.query.id; connection.execute('SELECT * FROM users WHERE id = ?', [userId], callback); ```

**In Java with JDBC:**

```java PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE id = ?"); stmt.setInt(1, userId); ResultSet rs = stmt.executeQuery(); ```

**In PHP with PDO:**

```php $stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id"); $stmt->execute(['id' => $userId]); ```

The pattern is the same in every language: the query string is fixed, and values are passed separately via the library's parameter mechanism.

The ORM Route

Object-relational mappers (ORMs) like SQLAlchemy, Django ORM, Hibernate, Eloquent, and ActiveRecord use parameterised queries internally for all standard operations. If you use an ORM's built-in query methods, you are protected:

**Django ORM (safe):**

```python User.objects.filter(username=request.POST['username']) ```

Django constructs a parameterised query internally. The `filter()` method never concatenates user input into SQL.

**The danger with ORMs** is raw query escapes. Every major ORM provides a way to execute raw SQL for cases the ORM cannot express. These raw query mechanisms bypass parameterisation if used carelessly:

**Django raw() (vulnerable if done wrong):**

```python # WRONG — still injectable User.objects.raw(f"SELECT * FROM users WHERE name = '{name}'")

# CORRECT — parameterised raw query User.objects.raw("SELECT * FROM users WHERE name = %s", [name]) ```

The key rule: never use Python f-strings, string formatting, or concatenation to build the SQL string passed to a raw query function. Always use the parameter mechanism the ORM provides.

The ORDER BY Problem

Standard parameterised queries handle WHERE clause values. They do not handle SQL identifiers — column names, table names, or keywords like `ASC` and `DESC`. If your application allows users to choose a sort column, you cannot parameterise the column name:

**Vulnerable:**

```python sort_col = request.args.get('sort', 'name') cursor.execute(f"SELECT * FROM products ORDER BY {sort_col}") ```

If `sort_col` is `name; DROP TABLE products--`, the query executes the drop.

**Correct — whitelist approach:**

```python ALLOWED_SORT_COLUMNS = {'name', 'price', 'created_at', 'rating'} sort_col = request.args.get('sort', 'name') if sort_col not in ALLOWED_SORT_COLUMNS: sort_col = 'name' cursor.execute(f"SELECT * FROM products ORDER BY {sort_col}") ```

You cannot parameterise a column name, but you can validate it against a whitelist of known-safe values before inserting it into the query. Any value not in the whitelist defaults to a safe value.

The same applies to `ASC`/`DESC` direction:

```python direction = request.args.get('dir', 'ASC').upper() if direction not in ('ASC', 'DESC'): direction = 'ASC' ```

Second-Order SQL Injection

Second-order SQL injection is a less obvious variant. The attack involves inserting a malicious value into the database safely (correctly parameterised), then having the application retrieve that value and use it in a second query without parameterisation.

The value passes through the database and appears to be safe internal data — so the developer who writes the second query does not treat it as user input. But it originated from user input, and if it reaches the second query unparameterised, the injection executes.

The defence is the same: parameterise every query, including those that operate on data retrieved from the database. The origin of a value does not determine whether it is safe.

Verifying the Fix

After making changes, test the fix by submitting SQL metacharacters in every input field that was vulnerable: a single quote (`'`), double quote (`"`), backslash, and payloads like `' OR '1'='1`. A correctly parameterised query treats these as literal string values and returns no result (or the same result as any non-matching string).

For a broader view of injection vulnerabilities, [what is SQL injection](/blog/what-is-sql-injection) covers how the attack works from first principles, and [what is XML injection](/blog/what-is-xml-injection) covers the equivalent attack against XML-based interfaces.

Yrzo AI's automated security assessment includes SQL injection testing across your web application's input fields, search functions, and API endpoints. Findings include the specific parameter and a reproduction case. From yrzoai.dev.