# FraiseQL Security Patterns

This document describes the security patterns and practices used in FraiseQL to prevent common vulnerabilities and maintain data integrity.

## SQL Injection Prevention

### Strategy: Parameterized Queries

All user input is passed to the database as **query parameters**, never interpolated into SQL strings.

#### Examples

##### Column Names (Compile-Time Only)

Column names come from schema definitions, never from user input:

```rust
// Schema defines columns: User { id: ID, name: String }
// These column names are fixed at compile time

// Generated SQL:
// INSERT INTO users (id, name) VALUES ($1, $2)
// Parameters: (user_id, user_name)
```

**Why safe**: Column names are part of the schema definition, fixed at compile time.
User input only provides VALUES, not column names.

##### LIMIT/OFFSET Parameters

Numeric limits are passed as query parameters:

```rust
// User query: "Get 10 items, skip 20"

// Generated SQL:
// SELECT * FROM items LIMIT $1 OFFSET $2
// Parameters: (10, 20)

// Database driver validates numeric types before execution
```

**Why safe**: u32 type ensures only valid integers are accepted. Database driver
never receives a string to parse.

##### JSON Path Extraction

JSON path segments are escaped before inclusion in SQL operators.

Each database handles JSON paths differently:

**PostgreSQL**: Single quotes in path segments are escaped by doubling (`'` → `''`)
within JSONB operators (`->`, `->>`, `->`):

```rust
// Example: path = ["user'name", "email"]
// Escaped: ["user''name", "email"]
// Generated SQL: data->'user''name'->>'email'
```

**MySQL**: Path segments are escaped within `JSON_EXTRACT` and `JSON_UNQUOTE`
function parameters using backslash escaping (`'` → `\'`):

```rust
// Example: path = ["user'name", "email"]
// Escaped: "$.user\'name.email"
// Generated SQL: JSON_UNQUOTE(JSON_EXTRACT(data, '$.user\'name.email'))
```

**SQLite**: Path segments are escaped within `json_extract` function parameters
using backslash escaping (`'` → `\'`):

```rust
// Example: path = ["user'name", "email"]
// Escaped: "$.user\'name.email"
// Generated SQL: json_extract(data, '$.user\'name.email')
```

**SQL Server**: Path segments are escaped within `JSON_VALUE` function parameters
by doubling single quotes (`'` → `''`):

```rust
// Example: path = ["user'name", "email"]
// Escaped: "$.user''name.email"
// Generated SQL: JSON_VALUE(data, '$.user''name.email')
```

**Implementation**: Escaping is applied in the `path_escape` module and consistently
applied across all WHERE clause generators before SQL string interpolation.

**Tested**: Comprehensive injection test suite verifies all common SQL injection
patterns are neutralized by path escaping.

### Rust Type System Protection

The Rust compiler itself prevents entire classes of SQL injection:

1. **Type Safety**: `u32` limit values can't contain SQL strings
2. **Memory Safety**: No buffer overflows or string manipulation bugs
3. **Compiler Warnings**: Unused interpolation flagged by clippy

### Testing Strategy

Security tests verify:

- SQL queries don't contain unescaped user input
- Parameterized queries are used consistently
- Type boundaries are respected

## Thread Safety Patterns

### Pattern: Interior Mutability for Context State

The WHERE clause generator uses `Cell<usize>` for parameter tracking:

```rust
pub struct PostgresWhereGenerator {
    param_counter: std::cell::Cell<usize>,
    // ...
}
```

**Why safe**:

1. **Single-threaded context**: Each WHERE generator is created for a single
   query execution and isn't shared across async tasks.
2. **Reset per call**: The counter is reset at the start of `generate()`,
   ensuring no state leakage between calls.
3. **Performance**: Avoids mutex overhead for a simple counter.

**Pattern**: Interior mutability is appropriate when:

- State is tied to a single execution context
- Concurrent access doesn't occur (verified by architecture)
- Performance is critical (avoids mutex overhead)

**Not Safe If**:

- Generator is Arc-shared across async tasks (would require AtomicUsize)
- Multiple threads call generate() on same instance

### Database Connection Pooling

Connection pooling uses thread-safe structures:

- `Arc<Pool>`: Shared connection pool reference
- `tokio::sync::Mutex`: Async-aware mutual exclusion
- Connection checkout/return: Atomic operations

## Type System Security

### Identifier Validation

GraphQL identifiers are validated against a regex at parse time:

```rust
// Regex ensures identifiers are valid:
// ^[a-zA-Z_][a-zA-Z0-9_]*$

// Invalid identifiers rejected at compile time:
// - Type name with spaces
// - Field names with special characters
// - Enum values with quotes
```

### Type Checking

GraphQL type checking prevents logic errors:

- Field type mismatches caught at compile time
- Null/Non-null violations caught at validation time
- Circular references detected and rejected

## Denial of Service Prevention

### Query Limits (Current/Planned)

Current:

- Query timeout: Configurable timeout per query execution
- Connection pool limits: Max connections to database
- Result set streaming: Avoid loading entire results in memory

Planned:

- Max result size: Configurable byte limit on responses
- Max nesting depth: Limit deeply nested JSONB extraction
- Max query complexity: Cost-based query limits

### Testing

DOS prevention tests:

- Very deep nesting (100+ levels)
- Large result sets (1M+ rows)
- Slow queries (timeout handling)

## Best Practices

### ✅ DO

- [ ] Use parameterized queries for all values
- [ ] Validate identifiers against regex
- [ ] Use Rust's type system (u32 vs String)
- [ ] Escape SQL string literals
- [ ] Use atomic operations for counters
- [ ] Test security scenarios in unit tests

### ❌ DON'T

- [ ] Interpolate user input into SQL strings
- [ ] Skip identifier validation
- [ ] Mix string types with numeric types
- [ ] Share interior mutable state across threads
- [ ] Use Cell when AtomicUsize is needed
- [ ] Trust database drivers to catch all errors

## Code Review Checklist

When reviewing code for security:

### 1. SQL Generation

- [ ] Are all values parameterized?
- [ ] Are identifiers validated?
- [ ] Are string literals escaped?
- [ ] Are limit/offset values typed correctly?

### 2. Thread Safety

- [ ] Is Cell used only for single-threaded context?
- [ ] Are Arc-shared values thread-safe?
- [ ] Are atomic operations used for counters?
- [ ] Are we using the right synchronization primitive?

### 3. Type Safety

- [ ] Are types used correctly (u32 not String)?
- [ ] Are null checks present?
- [ ] Are error types propagated?
- [ ] Is the type system preventing injection?

### 4. Denial of Service

- [ ] Are there recursion depth limits?
- [ ] Are there result size limits?
- [ ] Are there query timeouts?
- [ ] Are connection pool limits enforced?

## Resources

- [Rust Book: Safety](https://doc.rust-lang.org/book/ch19-01-unsafe-rust.html)
- [OWASP Top 10 - SQL Injection](https://owasp.org/www-community/attacks/SQL_Injection)
- [Tokio: Concurrency Patterns](https://tokio.rs/tokio/tutorial)
- [Parameterized Queries](https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html)

## Related Documentation

- `../CLAUDE.md`: Development standards and patterns
- `../../tests/`: Security-focused test examples
- `../src/db/`: Database adapter implementations
