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:

# 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):

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):

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):

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

Java (JDBC):

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

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:

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.

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:

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.