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

# Parameterized Queries - SQL Injection Protection

## Overview

HeliosDB Nano 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:

```rust
// 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**:

```rust
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**:
```rust
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**:
```rust
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)

```rust
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

```rust
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

```rust
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:

```rust
// 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**:
   ```rust
   db.query_params(
       "SELECT * FROM users WHERE email = $1",
       &[Value::String(user_email)]
   )?;
   ```

2. **Use parameters for all data types**:
   ```rust
   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):
   ```rust
   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**:
   ```rust
   // 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):
   ```rust
   // 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:
   ```rust
   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)**:
```rust
let sql = format!(
    "INSERT INTO users (name, email) VALUES ('{}', '{}')",
    name, email
);
db.execute(&sql)?;
```

**After (safe)**:
```rust
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:

```rust
#[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:

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