Parameterized Queries - SQL Injection Protection

Overview

HeliosDB Nano v2.0+ includes built-in protection against SQL injection attacks through parameterized queries. This feature allows you to safely execute SQL statements with user-provided input without risk of SQL injection.

The Problem: SQL Injection

SQL injection occurs when user input is concatenated directly into SQL queries:

// UNSAFE - DO NOT DO THIS!
let user_input = get_user_input(); // Could be: '; DROP TABLE users; --
let sql = format!("SELECT * FROM users WHERE name = '{}'", user_input);
db.execute(&sql)?; // SQL INJECTION VULNERABILITY!

An attacker could input '; DROP TABLE users; -- and execute arbitrary SQL commands.

The Solution: Parameterized Queries

Parameterized queries treat user input as data, not code:

use heliosdb_nano::{EmbeddedDatabase, Value};

let user_input = get_user_input(); // Even malicious input is safe!

// SAFE - Uses parameterized query
db.execute_params(
    "SELECT * FROM users WHERE name = $1",
    &[Value::String(user_input)]
)?;

API Reference

Parameter Placeholder Syntax

HeliosDB Nano uses PostgreSQL-style parameter placeholders:

$1 - First parameter
$2 - Second parameter
$3 - Third parameter
etc.

Parameters are 1-indexed (start at 1, not 0).

Methods

`execute_params(&self, sql: &str, params: &[Value]) -> Result<u64>`

Execute INSERT, UPDATE, or DELETE statements with parameters.

Returns: Number of rows affected

Example:

use heliosdb_nano::{EmbeddedDatabase, Value};

let db = EmbeddedDatabase::new("./data")?;

// INSERT with parameters
db.execute_params(
    "INSERT INTO users (id, name, email) VALUES ($1, $2, $3)",
    &[
        Value::Int4(1),
        Value::String("Alice".to_string()),
        Value::String("alice@example.com".to_string()),
    ]
)?;

// UPDATE with parameters
db.execute_params(
    "UPDATE users SET email = $1 WHERE id = $2",
    &[
        Value::String("newemail@example.com".to_string()),
        Value::Int4(1),
    ]
)?;

// DELETE with parameters
db.execute_params(
    "DELETE FROM users WHERE name = $1",
    &[Value::String("Alice".to_string())]
)?;

`query_params(&self, sql: &str, params: &[Value]) -> Result<Vec<Tuple>>`

Execute SELECT queries with parameters.

Returns: Vector of tuples (rows)

Example:

use heliosdb_nano::{EmbeddedDatabase, Value};

let db = EmbeddedDatabase::new("./data")?;

// SELECT with single parameter
let results = db.query_params(
    "SELECT * FROM users WHERE name = $1",
    &[Value::String("Alice".to_string())]
)?;

// SELECT with multiple parameters
let results = db.query_params(
    "SELECT * FROM users WHERE age > $1 AND city = $2",
    &[
        Value::Int4(18),
        Value::String("New York".to_string()),
    ]
)?;

Complete Examples

Example 1: User Authentication (Safe)

use heliosdb_nano::{EmbeddedDatabase, Value};

fn authenticate_user(db: &EmbeddedDatabase, username: &str, password: &str) -> Result<bool> {
    // SAFE - even if username contains SQL injection attempts
    let results = db.query_params(
        "SELECT * FROM users WHERE username = $1 AND password_hash = $2",
        &[
            Value::String(username.to_string()),
            Value::String(hash_password(password)),
        ]
    )?;

    Ok(!results.is_empty())
}

// SQL injection attempt is safely handled:
let is_authenticated = authenticate_user(
    &db,
    "admin' OR '1'='1", // Malicious input
    "wrong_password"
)?;
// Returns false - no SQL injection!

Example 2: Search with User Input

use heliosdb_nano::{EmbeddedDatabase, Value};

fn search_products(db: &EmbeddedDatabase, search_term: &str, min_price: i32) -> Result<Vec<Tuple>> {
    // SAFE - search_term is treated as literal data
    db.query_params(
        "SELECT * FROM products WHERE name LIKE $1 AND price >= $2",
        &[
            Value::String(format!("%{}%", search_term)), // LIKE pattern
            Value::Int4(min_price),
        ]
    )
}

Example 3: Batch INSERT with Parameters

use heliosdb_nano::{EmbeddedDatabase, Value};

fn import_users(db: &EmbeddedDatabase, users: Vec<(i32, String, String)>) -> Result<u64> {
    let mut total_inserted = 0;

    for (id, name, email) in users {
        // Each insert is safe from SQL injection
        let inserted = db.execute_params(
            "INSERT INTO users (id, name, email) VALUES ($1, $2, $3)",
            &[
                Value::Int4(id),
                Value::String(name),
                Value::String(email),
            ]
        )?;
        total_inserted += inserted;
    }

    Ok(total_inserted)
}

Type Mapping

Parameters are strongly typed using the Value enum:

SQL Type	Value Variant	Example
INT2	`Value::Int2(i16)`	`Value::Int2(123)`
INT4, INT	`Value::Int4(i32)`	`Value::Int4(42)`
INT8, BIGINT	`Value::Int8(i64)`	`Value::Int8(9999999)`
FLOAT4, REAL	`Value::Float4(f32)`	`Value::Float4(3.14)`
FLOAT8, DOUBLE	`Value::Float8(f64)`	`Value::Float8(2.71828)`
TEXT, VARCHAR	`Value::String(String)`	`Value::String("hello".to_string())`
BOOLEAN	`Value::Boolean(bool)`	`Value::Boolean(true)`
NULL	`Value::Null`	`Value::Null`
BYTEA	`Value::Bytes(Vec<u8>)`	`Value::Bytes(vec![1, 2, 3])`
UUID	`Value::Uuid(uuid::Uuid)`	`Value::Uuid(my_uuid)`
JSON, JSONB	`Value::Json(String)`	`Value::Json("{}".to_string())`
VECTOR(n)	`Value::Vector(Vec<f32>)`	`Value::Vector(vec![1.0, 2.0])`

Error Handling

The API provides clear error messages for parameter-related issues:

// Error: Missing parameter
db.query_params(
    "SELECT * FROM users WHERE id = $1 AND name = $2",
    &[Value::Int4(1)] // Only 1 parameter provided, 2 required
)?;
// Error: "Parameter $2 not provided. Expected 2 parameters, got 1"

// Error: Invalid parameter index
db.query_params(
    "SELECT * FROM users WHERE id = $0", // $0 is invalid
    &[Value::Int4(1)]
)?;
// Error: "Parameter indices must be 1-based (e.g., $1, $2)"

// Error: Invalid placeholder format
db.query_params(
    "SELECT * FROM users WHERE id = ?", // ? is not supported
    &[Value::Int4(1)]
)?;
// Error: "Unsupported placeholder format: ?. Use PostgreSQL-style $N placeholders"

Best Practices

✅ DO

Always use parameterized queries for user input:

db.query_params(
    "SELECT * FROM users WHERE email = $1",
    &[Value::String(user_email)]
)?;

Use parameters for all data types:

db.execute_params(
    "UPDATE products SET price = $1, stock = $2 WHERE id = $3",
    &[
        Value::Float4(29.99),
        Value::Int4(100),
        Value::Int4(product_id),
    ]
)?;

Validate input before querying (defense in depth):

if !email.contains('@') {
    return Err(Error::validation("Invalid email format"));
}
db.query_params(
    "SELECT * FROM users WHERE email = $1",
    &[Value::String(email)]
)?;

❌ DON’T

Don’t concatenate user input into SQL:

// BAD - SQL injection risk!
let sql = format!("SELECT * FROM users WHERE email = '{}'", user_email);
db.execute(&sql)?;

Don’t use parameters for table/column names (not supported):

// This won't work - table/column names can't be parameters
db.query_params(
    "SELECT * FROM $1 WHERE $2 = $3",
    &[
        Value::String("users".to_string()),
        Value::String("id".to_string()),
        Value::Int4(1),
    ]
)?;

Instead, validate and whitelist table/column names:

let table = match user_table {
    "users" | "products" | "orders" => user_table,
    _ => return Err(Error::validation("Invalid table name")),
};
db.query_params(
    &format!("SELECT * FROM {} WHERE id = $1", table),
    &[Value::Int4(id)]
)?;

Migration from Unsafe Code

If you have existing code using string concatenation:

Before (unsafe):

let sql = format!(
    "INSERT INTO users (name, email) VALUES ('{}', '{}')",
    name, email
);
db.execute(&sql)?;

After (safe):

db.execute_params(
    "INSERT INTO users (name, email) VALUES ($1, $2)",
    &[
        Value::String(name),
        Value::String(email),
    ]
)?;

Performance

Parameterized queries have negligible performance overhead compared to string concatenation:

Parser recognizes $N placeholders (fast)
No string escaping needed
Direct value substitution during evaluation

In fact, parameterized queries can be faster in some cases because:

No string concatenation overhead
No need to escape special characters
Values are passed directly, not converted to/from strings

Security Guarantees

Using execute_params() and query_params() provides:

SQL Injection Protection: User input is never interpreted as SQL code
Type Safety: Parameters are strongly typed (no implicit string conversions)
Clear Error Messages: Missing or invalid parameters are caught immediately
PostgreSQL Compatibility: Uses standard $N placeholder syntax

Testing

Example test for SQL injection protection:

#[test]
fn test_sql_injection_protection() {
    let db = EmbeddedDatabase::new_in_memory().unwrap();
    db.execute("CREATE TABLE users (id INT, name TEXT)").unwrap();
    db.execute("INSERT INTO users VALUES (1, 'Alice')").unwrap();

    // Attempt SQL injection
    let malicious_input = "'; DROP TABLE users; --";

    // Execute query with malicious input as parameter
    let results = db.query_params(
        "SELECT * FROM users WHERE name = $1",
        &[Value::String(malicious_input.to_string())]
    ).unwrap();

    // No rows match (because it's treated as literal string)
    assert_eq!(results.len(), 0);

    // Table still exists!
    let all_users = db.query("SELECT * FROM users", &[]).unwrap();
    assert_eq!(all_users.len(), 1); // Alice is still there
}

Limitations

Current limitations (as of v2.0):

SELECT query parameters: Basic support for WHERE clauses. Complex queries (JOINs, subqueries) may have limited parameter support.
Table/column names: Cannot be parameterized (use whitelisting instead)
DDL statements: CREATE TABLE, ALTER TABLE, etc. don’t support parameters

These limitations will be addressed in future releases.

Summary

Always use execute_params() and query_params() for user input
Use PostgreSQL-style $1, $2, $3... placeholders
Parameters are strongly typed using the Value enum
SQL injection protection is automatic and transparent
Minimal performance overhead
Clear error messages for parameter issues

For more examples, see the test suite in tests/parameterized_query_tests.rs.