Parameterized Queries - Quick Start Guide

Parameterized Queries - Quick Start Guide

HeliosDB Nano v2.0

---

Why Use Parameterized Queries?

❌ UNSAFE - SQL Injection Vulnerability:

let user_input = get_user_input(); // Could be: '; DROP TABLE users; --
let sql = format!("SELECT * FROM users WHERE name = '{}'", user_input);
db.execute(&sql)?; // DANGER! 💥

✅ SAFE - SQL Injection Impossible:

let user_input = get_user_input(); // Could be: '; DROP TABLE users; --
db.query_params(
    "SELECT * FROM users WHERE name = $1",
    &[Value::String(user_input)]
)?; // SAFE! ✅

---

API Reference

Method 1: execute_params() - For INSERT, UPDATE, DELETE

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

Returns: Number of rows affected

Method 2: query_params() - For SELECT

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

Returns: Query results as tuples

---

Basic Examples

INSERT with Parameters

use heliosdb_nano::{EmbeddedDatabase, Value};

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

db.execute("CREATE TABLE users (id INT, name TEXT, email TEXT)")?;

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

SELECT with Parameters

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

for row in results {
    println!("User: {:?}", row);
}

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())]
)?;

---

Advanced Examples

Multiple Parameters with AND/OR

let results = db.query_params(
    "SELECT * FROM products WHERE category = $1 AND price > $2",
    &[
        Value::String("Electronics".to_string()),
        Value::Int4(100),
    ]
)?;

Parameter Reuse

// $1 is used twice in the same query
let results = db.query_params(
    "SELECT * FROM ranges WHERE low <= $1 AND high >= $1",
    &[Value::Int4(50)]
)?;

NULL Parameters

db.execute_params(
    "INSERT INTO optional_data VALUES ($1, $2)",
    &[
        Value::Int4(1),
        Value::Null, // NULL value
    ]
)?;

Complex WHERE Clauses

let results = db.query_params(
    "SELECT * FROM orders WHERE
        customer_id = $1 AND
        status = $2 AND
        total > $3 AND
        created_at > $4",
    &[
        Value::Int4(123),
        Value::String("pending".to_string()),
        Value::Int4(1000),
        Value::String("2025-01-01".to_string()),
    ]
)?;

---

Value Types

Integer Types

Value::Int2(i16_value)   // 16-bit integer
Value::Int4(i32_value)   // 32-bit integer
Value::Int8(i64_value)   // 64-bit integer

Float Types

Value::Float4(f32_value) // 32-bit float
Value::Float8(f64_value) // 64-bit float

String Types

Value::String(string_value)           // TEXT/VARCHAR
Value::Json(json_string)              // JSON/JSONB

Other Types

Value::Boolean(bool_value)            // BOOLEAN
Value::Null                            // NULL
Value::Vector(vec![f32; N])           // VECTOR(N)
Value::Array(vec![Value])             // ARRAY
Value::Bytes(vec![u8])                // BYTEA

---

Security Best Practices

✅ DO: Use Parameters for ALL User Input

fn search_users(db: &EmbeddedDatabase, search_term: &str) -> Result<Vec<Tuple>> {
    db.query_params(
        "SELECT * FROM users WHERE name LIKE $1",
        &[Value::String(format!("%{}%", search_term))]
    )
}

❌ DON’T: Concatenate User Input into SQL

fn search_users(db: &EmbeddedDatabase, search_term: &str) -> Result<Vec<Tuple>> {
    // WRONG - SQL injection vulnerability!
    let sql = format!("SELECT * FROM users WHERE name LIKE '%{}%'", search_term);
    db.query(&sql, &[])
}

✅ DO: Use Parameters for Dynamic Values

fn update_price(db: &EmbeddedDatabase, product_id: i32, new_price: i32) -> Result<u64> {
    db.execute_params(
        "UPDATE products SET price = $1 WHERE id = $2",
        &[Value::Int4(new_price), Value::Int4(product_id)]
    )
}

✅ DO: Use Hardcoded SQL for Static Queries

// No user input - safe to use execute()
db.execute("CREATE TABLE IF NOT EXISTS users (id INT, name TEXT)")?;

---

Common Patterns

Pattern 1: Insert from Form Data

struct UserForm {
    name: String,
    email: String,
    age: i32,
}

fn insert_user(db: &EmbeddedDatabase, form: &UserForm) -> Result<u64> {
    db.execute_params(
        "INSERT INTO users (name, email, age) VALUES ($1, $2, $3)",
        &[
            Value::String(form.name.clone()),
            Value::String(form.email.clone()),
            Value::Int4(form.age),
        ]
    )
}

Pattern 2: Search with Filters

struct SearchFilters {
    category: Option<String>,
    min_price: Option<i32>,
    max_price: Option<i32>,
}

fn search_products(db: &EmbeddedDatabase, filters: &SearchFilters) -> Result<Vec<Tuple>> {
    let mut sql = String::from("SELECT * FROM products WHERE 1=1");
    let mut params = Vec::new();
    let mut param_idx = 1;

    if let Some(ref category) = filters.category {
        sql.push_str(&format!(" AND category = ${}", param_idx));
        params.push(Value::String(category.clone()));
        param_idx += 1;
    }

    if let Some(min) = filters.min_price {
        sql.push_str(&format!(" AND price >= ${}", param_idx));
        params.push(Value::Int4(min));
        param_idx += 1;
    }

    if let Some(max) = filters.max_price {
        sql.push_str(&format!(" AND price <= ${}", param_idx));
        params.push(Value::Int4(max));
    }

    db.query_params(&sql, &params)
}

Pattern 3: Batch Inserts

fn insert_multiple_users(db: &EmbeddedDatabase, users: &[(String, String)]) -> Result<()> {
    for (name, email) in users {
        db.execute_params(
            "INSERT INTO users (name, email) VALUES ($1, $2)",
            &[
                Value::String(name.clone()),
                Value::String(email.clone()),
            ]
        )?;
    }
    Ok(())
}

Pattern 4: Conditional Updates

fn update_status(
    db: &EmbeddedDatabase,
    order_id: i32,
    new_status: &str,
    only_if_status: &str
) -> Result<u64> {
    db.execute_params(
        "UPDATE orders SET status = $1 WHERE id = $2 AND status = $3",
        &[
            Value::String(new_status.to_string()),
            Value::Int4(order_id),
            Value::String(only_if_status.to_string()),
        ]
    )
}

---

Error Handling

Parameter Count Mismatch

// ERROR: Only 1 parameter provided, but query needs 2
let result = db.query_params(
    "SELECT * FROM users WHERE name = $1 AND age = $2",
    &[Value::String("Alice".to_string())] // Missing $2!
);

// Error message: "Parameter $2 not provided. Expected 2 parameters, got 1"

Invalid Parameter Index

// ERROR: Parameters must be 1-based ($1, $2, ...), not 0-based
// This is automatically validated by the parser

Type Mismatches (Automatic Coercion)

// Automatic type coercion handles most cases
db.execute_params(
    "INSERT INTO numbers (value) VALUES ($1)",
    &[Value::Int4(42)] // Automatically coerced if column is INT8
)?;

---

Performance Tips

Tip 1: Reuse Database Connection

// GOOD - Reuse connection
let db = EmbeddedDatabase::new("./mydb.helio")?;
for user in users {
    db.execute_params("INSERT INTO users VALUES ($1, $2)", &[...])?;
}

// BAD - Creating new connection each time
for user in users {
    let db = EmbeddedDatabase::new("./mydb.helio")?; // Slow!
    db.execute_params("INSERT INTO users VALUES ($1, $2)", &[...])?;
}

Tip 2: Use Transactions for Batch Operations

db.begin()?;
for user in users {
    db.execute_params("INSERT INTO users VALUES ($1, $2)", &[...])?;
}
db.commit()?; // Atomic commit

Tip 3: Minimize Parameter Conversions

// GOOD - Convert once
let name_value = Value::String(name.to_string());
let email_value = Value::String(email.to_string());
db.execute_params("INSERT INTO users VALUES ($1, $2)", &[name_value, email_value])?;

// OK - Convert inline (compiler optimizes)
db.execute_params(
    "INSERT INTO users VALUES ($1, $2)",
    &[Value::String(name.to_string()), Value::String(email.to_string())]
)?;

---

Migration from Old API

Before (Unsafe)

let name = user_input;
let sql = format!("SELECT * FROM users WHERE name = '{}'", name);
db.execute(&sql)?;

After (Safe)

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

---

Testing Your Queries

Test SQL Injection Prevention

#[test]
fn test_search_safe_from_injection() {
    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();

    // Malicious input
    let malicious = "'; DROP TABLE users; --";

    // Should not drop the table
    let results = db.query_params(
        "SELECT * FROM users WHERE name = $1",
        &[Value::String(malicious.to_string())]
    ).unwrap();

    // Table should still exist
    assert_eq!(results.len(), 0);

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

---

Common Mistakes

Mistake 1: Using Old API with User Input

// WRONG - Vulnerable to SQL injection
let user_name = get_user_input();
db.execute(&format!("DELETE FROM users WHERE name = '{}'", user_name))?;

// RIGHT - Safe from SQL injection
db.execute_params(
    "DELETE FROM users WHERE name = $1",
    &[Value::String(user_name)]
)?;

Mistake 2: Wrong Parameter Numbering

// WRONG - $0 is not valid (must be $1, $2, ...)
db.query_params("SELECT * FROM users WHERE id = $0", &[...])?;

// RIGHT - Use $1, $2, $3, ...
db.query_params("SELECT * FROM users WHERE id = $1", &[...])?;

Mistake 3: Forgetting .to_string()

// WRONG - &str is not Value
db.execute_params("INSERT INTO users VALUES ($1)", &["Alice"])?;

// RIGHT - Convert to Value::String
db.execute_params(
    "INSERT INTO users VALUES ($1)",
    &[Value::String("Alice".to_string())]
)?;

---

FAQ

Q: Can I use parameters for table names?

A: No. PostgreSQL (and HeliosDB) only support parameters for values, not identifiers.

// WRONG - Cannot parameterize table name
db.query_params("SELECT * FROM $1", &[Value::String("users".to_string())])?;

// RIGHT - Table name must be in SQL
let table = "users"; // Validate this comes from trusted source!
db.query(&format!("SELECT * FROM {}", table), &[])?;

Q: Can I use parameters for column names?

A: No. Same as table names, column names cannot be parameterized.

Q: What’s the maximum number of parameters?

A: Practically unlimited. The implementation uses Vec internally, so you can have thousands of parameters if needed.

Q: Do parameters work with JOINs?

A: Yes!

db.query_params(
    "SELECT u.name, o.total FROM users u
     JOIN orders o ON u.id = o.user_id
     WHERE u.id = $1 AND o.status = $2",
    &[Value::Int4(123), Value::String("pending".to_string())]
)?;

Q: Can I mix parameterized and non-parameterized values?

A: Yes, but use parameters for ALL user input.

// Static value in SQL, dynamic value as parameter
db.query_params(
    "SELECT * FROM users WHERE active = true AND name = $1",
    &[Value::String(user_input)]
)?;

---

Summary

✅ Always use execute_params() and query_params() for user input ✅ Use PostgreSQL-style placeholders: $1, $2, $3, … ✅ Convert values to Value types ✅ SQL injection is impossible with parameterized queries ✅ Parameters work with all SQL operations

---

For more information:

• Full documentation: PARAMETERIZED_QUERY_IMPLEMENTATION_COMPLETE.md
• Test examples: tests/parameterized_query_tests.rs
• API reference: cargo doc --open