---
title: "Parameterized Queries - SQL Injection Protection"
---

# Parameterized Queries - SQL Injection Protection

## Overview

HeliosDB Lite accepts PostgreSQL-style `$1`, `$2`, ... placeholders. Values sent as
parameters are bound as data, never parsed as SQL, which closes the door on SQL
injection. Parameters are available on every surface:

- **PostgreSQL wire protocol**: the extended query protocol (Parse/Bind/Execute) that
  standard drivers use for placeholders.
- **SQL**: `PREPARE`, `EXECUTE` and `DEALLOCATE`.
- **REST API**: the `params` array on the query and execute routes.

## The Problem: SQL Injection

SQL injection happens when user input is concatenated into a statement:

```python
# UNSAFE - do not do this
name = request.args["name"]          # could be: '; DROP TABLE users; --
cur.execute(f"SELECT * FROM users WHERE name = '{name}'")
```

An attacker who sends `'; DROP TABLE users; --` runs arbitrary SQL.

## PostgreSQL Drivers

Pass values separately from the statement and let the driver bind them.

**Python (psycopg2):**

```python
import psycopg2

conn = psycopg2.connect(host="127.0.0.1", port=5432, user="postgres", dbname="heliosdb")
cur = conn.cursor()

cur.execute("SELECT * FROM users WHERE name = %s", (name,))
cur.execute(
    "INSERT INTO users (id, name, email) VALUES (%s, %s, %s)",
    (1, "Alice", "alice@example.com"),
)
conn.commit()
```

**Node.js (pg):**

```javascript
const { Client } = require('pg');
const client = new Client({ host: '127.0.0.1', port: 5432, user: 'postgres', database: 'heliosdb' });
await client.connect();

const res = await client.query('SELECT * FROM users WHERE email = $1', [email]);
await client.query('UPDATE users SET email = $1 WHERE id = $2', [newEmail, id]);
```

**Rust (tokio-postgres):**

```rust
let rows = client
    .query("SELECT * FROM users WHERE name = $1", &[&name])
    .await?;
```

**Java (JDBC):**

```java
PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE name = ?");
ps.setString(1, name);
ResultSet rs = ps.executeQuery();
```

JDBC writes `?` in Java source; the driver sends `$1` placeholders on the wire.

## SQL: PREPARE and EXECUTE

```sql
PREPARE find_user (TEXT) AS
    SELECT * FROM users WHERE name = $1;

EXECUTE find_user('Alice');

PREPARE add_user (INT, TEXT, TEXT) AS
    INSERT INTO users (id, name, email) VALUES ($1, $2, $3);

EXECUTE add_user(2, 'Bob', 'bob@example.com');

DEALLOCATE find_user;
DEALLOCATE ALL;
```

A session can hold a bounded number of prepared statements
(`server.max_prepared_statements`); `PREPARE` fails with
`too many prepared statements` when the limit is reached.

## REST API

The query and execute routes take an optional `params` array. Each entry names its
type and value:

```bash
curl -X POST http://127.0.0.1:8080/v1/branches/main/query \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
        "sql": "SELECT * FROM users WHERE name = $1 AND id > $2",
        "params": [
          {"type": "string", "value": "Alice"},
          {"type": "int4",   "value": 0}
        ]
      }'

curl -X POST http://127.0.0.1:8080/v1/branches/main/execute \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
        "sql": "UPDATE users SET email = $1 WHERE id = $2",
        "params": [
          {"type": "string", "value": "alice@new.example.com"},
          {"type": "int4",   "value": 1}
        ]
      }'
```

| `type` | SQL type |
|--------|----------|
| `null` | NULL (no `value`) |
| `int4` | INT, INTEGER |
| `int8` | BIGINT |
| `float4` | REAL |
| `float8` | DOUBLE PRECISION |
| `string` | TEXT, VARCHAR |
| `boolean` | BOOLEAN |
| `json` | JSON, JSONB |

A request carries at most 65,535 parameters. Tokens are signed with
`HELIOSDB_JWT_SECRET`; see the [REST API tutorial](/docs/lite/guides/tutorials/rest_api_tutorial/).

## Placeholder Rules

- Placeholders are 1-based: `$1` is the first value. `$0` is rejected.
- The same placeholder may appear more than once:
  `WHERE low <= $1 AND high >= $1`.
- Every placeholder needs a value; a missing value is an error, not an empty string.
- `NULL` is passed as a parameter value like any other (`None` in Python, `null` in
  JavaScript, `{"type": "null"}` over REST).

## Best Practices

**Do:**

1. Send every user-supplied value as a parameter, including numbers and dates.
2. Validate input before querying as a second line of defence (for example, check an
   e-mail address has an `@`).
3. Wrap batches of parameterized inserts in one transaction (`BEGIN` ... `COMMIT`).

**Don't:**

1. Build SQL strings by concatenating or formatting user input.
2. Use placeholders for table or column names: identifiers cannot be parameters. Pick
   them from a fixed allow-list in application code instead:

```python
ALLOWED = {"users", "products", "orders"}
if table not in ALLOWED:
    raise ValueError("invalid table")
cur.execute(f"SELECT * FROM {table} WHERE id = %s", (row_id,))
```

## Limitations

- Identifiers (table, column, schema names) cannot be parameters.
- DDL statements (`CREATE TABLE`, `ALTER TABLE`, ...) do not take parameters.

## Summary

- Use `$N` placeholders and send values separately.
- Drivers do this for you over the PostgreSQL wire protocol.
- In SQL, use `PREPARE` / `EXECUTE`; over REST, use the `params` array.
- Keep identifiers out of user input; allow-list them in code.