---
title: "Parameterized Queries - Quick Start Guide"
---

# Parameterized Queries - Quick Start Guide


---

## Why Use Parameterized Queries?

**UNSAFE - SQL Injection Vulnerability**:
```rust
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**:
```rust
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

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

**Returns**: Number of rows affected

### Method 2: `query_params()` - For SELECT

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

**Returns**: Query results as tuples

---

## Basic Examples

### INSERT with Parameters

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

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

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

### DELETE with Parameters

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

---

## Advanced Examples

### Multiple Parameters with AND/OR

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

### Parameter Reuse

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

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

### Complex WHERE Clauses

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

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

### Float Types

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

### String Types

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

### Other Types

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

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

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

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

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

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

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

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

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

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

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

### Type Mismatches (Automatic Coercion)

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

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

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

### Tip 3: Minimize Parameter Conversions

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

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

### After (Safe)

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

---

## Testing Your Queries

### Test SQL Injection Prevention

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

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

```rust
// 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()

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

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

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

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