Parameterized Queries - SQL Injection Protection

Overview

HeliosDB-Lite 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_lite::{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-Lite 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_lite::{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_lite::{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_lite::{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_lite::{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_lite::{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

1. Always use parameterized queries for user input:

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

2. 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),
       ]
   )?;
   

3. 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

1. Don't concatenate user input into SQL:

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

2. 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:

1. SQL Injection Protection: User input is never interpreted as SQL code
2. Type Safety: Parameters are strongly typed (no implicit string conversions)
3. Clear Error Messages: Missing or invalid parameters are caught immediately
4. 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):

1. SELECT query parameters: Basic support for WHERE clauses. Complex queries (JOINs, subqueries) may have limited parameter support.
2. Table/column names: Cannot be parameterized (use whitelisting instead)
3. 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.