SQL Injection Protection - Quick Reference Card

For Day 3 Implementation Team

❌ NEVER Do This

// ❌ CRITICAL: String formatting with user input
let query = format!("SELECT * FROM {}", user_table);

// ❌ CRITICAL: String concatenation with user input
let query = "SELECT * FROM ".to_string() + &user_table;

// ❌ CRITICAL: Direct interpolation
let query = format!("WHERE id = {}", user_id);

// ❌ CRITICAL: Unvalidated identifiers
let query = format!("SELECT {} FROM users", user_column);

ALWAYS Do This

//  SAFE: Use SafeIdentifier for all table/column names
let table = SafeIdentifier::new(&user_table)?;
let column = SafeIdentifier::new(&user_column)?;

//  SAFE: Use Parameter for all values
let value = Parameter::from(user_input);

//  SAFE: Use QueryBuilder for construction
let query = select([column])
    .from(table)
    .where_eq(SafeIdentifier::new("id")?, value)
    .build();

//  SAFE: Execute with prepared query
execute_prepared(&query).await?;

Common Patterns

Pattern 1: Simple SELECT

use heliosdb_sql_builder::{select, SafeIdentifier, Parameter};

// User wants: SELECT * FROM users WHERE id = 123
let query = select(["*"])
    .from(SafeIdentifier::new("users")?)
    .where_eq(
        SafeIdentifier::new("id")?,
        Parameter::from(123)
    )
    .build();

// Generated: SELECT * FROM users WHERE id = $1
// Parameters: [123]

Pattern 2: Multiple Conditions

// User wants: WHERE status = 'active' AND age >= 18 AND city = 'NYC'
let query = select(["*"])
    .from(SafeIdentifier::new("users")?)
    .where_eq(SafeIdentifier::new("status")?, Parameter::from("active"))
    .and_op(SafeIdentifier::new("age")?, SqlOperator::Ge, Parameter::from(18))
    .and_eq(SafeIdentifier::new("city")?, Parameter::from("NYC"))
    .build();

// Generated: SELECT * FROM users WHERE status = $1 AND age >= $2 AND city = $3
// Parameters: ["active", 18, "NYC"]

Pattern 3: ORDER BY and LIMIT

// User wants: ORDER BY created_at DESC LIMIT 100 OFFSET 50
let query = select(["id", "name", "email"])
    .from(SafeIdentifier::new("users")?)
    .where_eq(SafeIdentifier::new("status")?, Parameter::from("active"))
    .order_by(SafeIdentifier::new("created_at")?, SortDirection::Desc)
    .limit(100)
    .offset(50)
    .build();

// Generated: SELECT id, name, email FROM users
//            WHERE status = $1 ORDER BY created_at DESC LIMIT $2 OFFSET $3
// Parameters: ["active", 100, 50]

Pattern 4: Dynamic Column List

// User wants to select specific columns
let columns: Vec<&str> = vec!["id", "name", "email"];

// Validate all columns first
let safe_columns: Result<Vec<_>, _> = columns
    .into_iter()
    .map(SafeIdentifier::new)
    .collect();

let safe_columns = safe_columns?;

let query = select(safe_columns)
    .from(SafeIdentifier::new("users")?)
    .build();

Pattern 5: REST API Integration

// In REST endpoint handler
pub async fn list_records(
    Path(table): Path<String>,
    Query(params): Query<QueryParams>,
) -> Result<Json<Vec<Row>>> {
    // Step 1: Validate table name
    let table_ident = SafeIdentifier::new(&table)
        .map_err(|e| ApiError::InvalidTable(e.to_string()))?;

    // Step 2: Build base query
    let mut builder = select(["*"]).from(table_ident);

    // Step 3: Add filters (validate columns)
    for filter in params.filters {
        let column = SafeIdentifier::new(&filter.column)?;
        let value = Parameter::from(filter.value);
        builder = builder.and_eq(column, value);
    }

    // Step 4: Add sorting (validate column)
    if let Some(sort) = params.sort {
        let column = SafeIdentifier::new(&sort.column)?;
        builder = builder.order_by(column, sort.direction);
    }

    // Step 5: Add pagination
    let query = builder
        .limit(params.limit.unwrap_or(100))
        .offset(params.offset.unwrap_or(0))
        .build();

    // Step 6: Execute
    let rows = db.execute_prepared(&query).await?;
    Ok(Json(rows))
}

Error Handling

SafeIdentifier Errors

match SafeIdentifier::new(user_input) {
    Ok(ident) => {
        // Use ident
    }
    Err(IdentifierError::Empty) => {
        return Err(ApiError::BadRequest("Table name cannot be empty".into()));
    }
    Err(IdentifierError::TooLong { len, max }) => {
        return Err(ApiError::BadRequest(
            format!("Table name too long: {} (max {})", len, max)
        ));
    }
    Err(IdentifierError::InvalidCharacters(name)) => {
        return Err(ApiError::BadRequest(
            format!("Invalid table name: {}", name)
        ));
    }
}

Using `?` Operator

// Convert errors automatically
impl From<IdentifierError> for ApiError {
    fn from(e: IdentifierError) -> Self {
        ApiError::InvalidInput(e.to_string())
    }
}

// Then use ? operator
let table = SafeIdentifier::new(&user_table)?;
let column = SafeIdentifier::new(&user_column)?;

Testing Your Code

Unit Test Template

#[test]
fn test_query_building() {
    let query = select(["*"])
        .from(SafeIdentifier::new("users").unwrap())
        .where_eq(
            SafeIdentifier::new("id").unwrap(),
            Parameter::from(123)
        )
        .build();

    assert_eq!(query.sql(), "SELECT * FROM users WHERE id = $1");
    assert_eq!(query.parameters().len(), 1);
    assert_eq!(query.parameters()[0], Parameter::Int(123));
}

Security Test Template

#[test]
fn test_injection_blocked() {
    // Attempt SQL injection
    let result = SafeIdentifier::new("users; DROP TABLE admin--");

    // Should be rejected
    assert!(result.is_err());
}

#[test]
fn test_injection_in_parameter_safe() {
    // Even if malicious SQL in parameter, it's safe
    let malicious = Parameter::from("1' OR '1'='1");

    let query = select(["*"])
        .from(SafeIdentifier::new("users").unwrap())
        .where_eq(SafeIdentifier::new("id").unwrap(), malicious)
        .build();

    // Will be executed as: SELECT * FROM users WHERE id = $1
    // With parameter: "1' OR '1'='1" (as literal string, not SQL)
    assert!(query.sql().contains("$1"));
}

Performance Tips

Tip 1: Use Cached Identifiers for Static Names

// For tables/columns you use frequently
static USERS_TABLE: Lazy<SafeIdentifier> = Lazy::new(|| {
    SafeIdentifier::new("users").unwrap()
});

static ID_COLUMN: Lazy<SafeIdentifier> = Lazy::new(|| {
    SafeIdentifier::new("id").unwrap()
});

// Use in queries
let query = select(["*"])
    .from(USERS_TABLE.clone())
    .where_eq(ID_COLUMN.clone(), Parameter::from(123))
    .build();

Tip 2: Reuse Prepared Queries

// Prepare once
let prepared_query = select(["*"])
    .from(SafeIdentifier::new("users")?)
    .where_eq(SafeIdentifier::new("id")?, Parameter::from(0))
    .build();

// Execute many times with different parameters
for id in 1..1000 {
    let mut query = prepared_query.clone();
    query.parameters[0] = Parameter::from(id);
    execute_prepared(&query).await?;
}

Common Mistakes

Mistake 1: Forgetting to Validate Table Names

// ❌ WRONG: Table name from URL path not validated
pub async fn get_table(Path(table): Path<String>) -> Result<Json<Vec<Row>>> {
    let query = format!("SELECT * FROM {}", table);  // VULNERABLE!
    // ...
}

//  RIGHT: Validate first
pub async fn get_table(Path(table): Path<String>) -> Result<Json<Vec<Row>>> {
    let table_ident = SafeIdentifier::new(&table)?;  // SAFE
    let query = select(["*"]).from(table_ident).build();
    // ...
}

Mistake 2: Validating but Still Using format!

// ❌ WRONG: Validates but still uses format!
let table = SafeIdentifier::new(&user_input)?;
let query = format!("SELECT * FROM {}", table.as_str());  // Don't do this!

//  RIGHT: Use QueryBuilder
let table = SafeIdentifier::new(&user_input)?;
let query = select(["*"]).from(table).build();

Mistake 3: Mixing Safe and Unsafe Code

// ❌ WRONG: Part safe, part unsafe
let safe_table = SafeIdentifier::new("users")?;
let unsafe_column = user_input;  // Not validated!
let query = format!("SELECT {} FROM {}", unsafe_column, safe_table);

//  RIGHT: All safe
let safe_table = SafeIdentifier::new("users")?;
let safe_column = SafeIdentifier::new(&user_input)?;
let query = select([safe_column]).from(safe_table).build();

When to Use What

Use Case	Use This	Don’t Use This
Table name	`SafeIdentifier`	`String`
Column name	`SafeIdentifier`	`String`
Schema name	`SafeIdentifier`	`String`
WHERE value	`Parameter`	`format!()`
LIMIT value	`.limit(u32)`	`format!()`
OFFSET value	`.offset(u32)`	`format!()`
User input (any)	`Parameter`	Direct use
SQL keywords	`SqlOperator` enum	`String`

Quick Validation Checklist

Before committing code, verify:

No format!() with user input
No string concatenation with user input
All table names validated with SafeIdentifier
All column names validated with SafeIdentifier
All user values wrapped in Parameter
Using QueryBuilder for construction
Tests include malicious input attempts
Error messages don’t leak SQL structure

Getting Help

Documentation:

Full design: docs/SQL_INJECTION_PROTECTION_DESIGN.md
Summary: docs/SQL_INJECTION_PROTECTION_SUMMARY.md
Checklist: docs/SQL_INJECTION_DAY3_CHECKLIST.md

Examples:

Existing tests: heliosdb-protocols/tests/sql_injection_tests.rs
OWASP patterns: Search for “sql injection” in codebase

If Stuck:

Check if your use case is in this quick reference
Look at existing migrated code for patterns
Run tests to verify safety
Ask team for code review

Success Metrics

Your code is safe when:

All identifiers validated before use
All values passed as parameters
No raw SQL string construction with user input
Tests include injection attempts (all blocked)
cargo clippy passes with no warnings
Performance <3% overhead vs unsafe version

SQL Injection Protection - Quick Reference Card

SQL Injection Protection - Quick Reference Card

❌ NEVER Do This

ALWAYS Do This

Common Patterns

Pattern 1: Simple SELECT

Pattern 2: Multiple Conditions

Pattern 3: ORDER BY and LIMIT

Pattern 4: Dynamic Column List

Pattern 5: REST API Integration

Error Handling

SafeIdentifier Errors

Using ? Operator

Testing Your Code

Unit Test Template

Security Test Template

Performance Tips

Tip 1: Use Cached Identifiers for Static Names

Tip 2: Reuse Prepared Queries

Common Mistakes

Mistake 1: Forgetting to Validate Table Names

Mistake 2: Validating but Still Using format!

Mistake 3: Mixing Safe and Unsafe Code

When to Use What

Quick Validation Checklist

Getting Help

Success Metrics

Using `?` Operator