Skip to content

Security and Data Integrity

Jump to bottom
Temp edited this page Oct 4, 2025 · 2 revisions

Security & Data Integrity

Last Updated: October 4, 2025 7:00 AM EST

The SQLite MCP Server provides comprehensive security features and data integrity mechanisms to ensure safe, reliable database operations. All 73 tools have been tested and verified for security compliance ✅

🛡️ Parameter Binding Security

Enhanced Security Interface: All query tools (read_query, write_query, create_table) support optional parameter binding to prevent SQL injection attacks:

// ✅ SECURE: Parameter binding prevents injection
read_query({
  "query": "SELECT * FROM users WHERE username = ? AND role = ?",
  "params": ["john_doe", "admin"]
})

// ✅ SECURE: Write operations with parameters  
write_query({
  "query": "INSERT INTO products (name, price, category) VALUES (?, ?, ?)",
  "params": ["Laptop", 999.99, "electronics"]
})

// ✅ v2.6.0: Enhanced automatic JSON serialization for objects/arrays
write_query({
  "query": "INSERT INTO table_name (json_data, tags) VALUES (?, ?)",
  "params": [{"key": "value", "nested": {"data": "example"}}, ["tag1", "tag2", "tag3"]]
})

// ✅ v2.6.0: JSON Helper Tools provide even simpler syntax
json_insert({
  "table": "table_name",
  "column": "json_data",
  "data": {"key": "value", "nested": {"data": "example"}}
})

Security Benefits:

  • 🛡️ SQL Injection Prevention: Malicious input treated as literal data, not executable code
  • 🔄 Backward Compatible: Existing queries without params continue to work
  • ⚡ Performance: Query plan caching and optimization
  • 🎯 Automatic JSON Handling: Dict/list objects automatically serialized to JSON
  • 📝 Best Practice: Follows secure coding standards

🛡️ SQL Injection Protection Testing

TL;DR: Critical injection vectors blocked, parameter binding validated — overall posture: STRONG 🛡️

Comprehensive Vulnerability Testing

# Test SQL injection protection (from tests directory)
cd tests && python test_sql_injection.py

# Expected result: 🛡️ Overall security posture: STRONG

What it tests:

  • Protection against the SQL injection vulnerability found in original Anthropic SQLite MCP server
  • 11 different attack vectors including multiple statements, UNION injection, blind injection
  • Parameter binding protection with malicious payloads
  • Stacked queries and comment-based injection attempts

Attack Vectors Tested

  1. Multiple Statement Injection - SELECT 1; DROP TABLE users; ✅ BLOCKED
  2. UNION-based Information Disclosure - SELECT username UNION SELECT password_hash ⚠️ Executes (but safe, as these are legitimate SELECT queries, not injection)
  3. Boolean-based Blind Injection - Conditional queries to extract data ⚠️ Executes (but safe, as these are legitimate SELECT queries, not injection)
  4. Time-based Blind Injection - Queries that could cause delays ⚠️ Executes (but safe, as these are legitimate SELECT queries, not injection)
  5. Comment-based Injection - Using --, /* */, and # comments ✅ MOSTLY BLOCKED
  6. Stacked Queries with Various Separators - Different line endings ✅ BLOCKED
  7. Parameter Binding Protection - 6 malicious payloads with safe binding ✅ ALL SAFE
  8. String Concatenation Demo - Shows what would happen with unsafe code ⚠️ Educational

Security Assessment

  • ✅ Critical attacks blocked: Multiple statements, stacked queries
  • ✅ Parameter binding working: All malicious payloads safely neutralized
  • ✅ Type checking verified: Passes strict Pyright type checking in Cursor
  • ✅ All 73 tools tested: Comprehensive MCP validation completed
  • ⚠️ Complex SELECT queries execute: This is expected behavior for valid SQL
  • 🛡️ Overall security posture: STRONG

Note: Error messages in the output are expected - they show the security protections working correctly by rejecting malicious queries.

💾 JSONB Binary Storage

The SQLite MCP Server implements SQLite JSONB binary storage format for all JSON data, providing significant advantages:

  • Reduced Storage Size: Estimated 15% space savings across migrated tables
  • Faster Parsing: No need to re-parse JSON text for each operation
  • Type Preservation: Binary format preserves data types without text conversion
  • Elimination of Escaping Issues: No complex character escaping needed
  • Efficient Path Access: Optimized for JSON path extraction operations

Usage

For optimal JSON handling, SQLite automatically uses JSONB format internally. Simply provide JSON strings directly:

// Insert JSON record directly (automatically uses JSONB internally)
write_query({
  "query": "INSERT INTO table_name (json_column) VALUES ('{\"key\": \"value\"}')"
})

// With parameter binding (for programmatic access)
write_query({
  "query": "INSERT INTO table_name (json_column) VALUES (?)",
  "params": [JSON.stringify({"key": "value"})]
})

// Query using standard JSON functions
read_query({
  "query": "SELECT json_extract(json_column, '$.key') FROM table_name"
})

Note: The explicit jsonb() function should only be used in specific advanced cases or when required for parameter binding pattern. For direct SQL statements, standard JSON strings work efficiently.

🔒 Transaction Safety

All write operations are automatically wrapped in transactions with proper rollback on errors:

  • Automatic Transactions: Every write operation is wrapped in a transaction
  • Error Rollback: Failed operations automatically roll back changes
  • Data Integrity: Ensures database consistency even during failures
  • Zero Configuration: Works automatically without setup

Example

// This operation is automatically wrapped in a transaction
write_query({
  "query": "INSERT INTO orders (customer_id, total, items) VALUES (?, ?, ?)",
  "params": [123, 299.99, JSON.stringify([{"item": "laptop", "qty": 1}])]
})

// If the operation fails, changes are automatically rolled back
// If it succeeds, the transaction is automatically committed

🔗 Foreign Key Enforcement

Automatic enforcement of foreign key constraints across all connections:

  • Referential Integrity: Ensures data relationships remain valid
  • Cascade Operations: Supports CASCADE, RESTRICT, SET NULL operations
  • Cross-Connection Consistency: Enforced across all database connections
  • PRAGMA Support: Uses SQLite's foreign_keys pragma for enforcement

Example

// Create tables with foreign key relationships
create_table({
  "query": `CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE
  )`
})

create_table({
  "query": `CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total REAL,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
  )`
})

// Foreign key constraints are automatically enforced
// This will fail if customer_id 999 doesn't exist:
write_query({
  "query": "INSERT INTO orders (customer_id, total) VALUES (?, ?)",
  "params": [999, 299.99]
})

🔐 JSON Security Validation

The JSON Helper Tools include advanced security validation features:

json_validate_security Tool

// Comprehensive security validation
json_validate_security({
  "json_data": '{"user": "admin", "permissions": ["read", "write"]}',
  "check_injection": true,
  "check_xss": true,
  "max_depth": 10
})

Security Checks:

  • SQL Injection Detection: Scans for malicious SQL patterns
  • XSS Prevention: Identifies potential cross-site scripting vectors
  • Depth Limiting: Prevents deeply nested JSON attacks
  • Pattern Analysis: Detects suspicious data patterns
  • Content Validation: Ensures JSON structure integrity

🛡️ Best Security Practices

1. Always Use Parameter Binding

// ✅ SECURE
read_query({
  "query": "SELECT * FROM users WHERE role = ?",
  "params": ["admin"]
})

// ❌ VULNERABLE
read_query({
  "query": `SELECT * FROM users WHERE role = '${userInput}'`
})

2. Validate User Input

// Validate JSON before insertion
json_validate_security({
  "json_data": userProvidedJSON,
  "check_injection": true,
  "check_xss": true
})

3. Use JSON Helper Tools

// ✅ SECURE: Automatic validation and normalization
json_insert({
  "table": "user_data",
  "column": "profile",
  "data": userProfile
})

// vs raw SQL (more error-prone)
write_query({
  "query": "INSERT INTO user_data (profile) VALUES (?)",
  "params": [JSON.stringify(userProfile)]
})

4. Regular Security Testing

# Run security tests regularly
python test_runner.py --security

# Check for SQL injection vulnerabilities
cd tests && python test_sql_injection.py

📚 Related Pages

🔐 Security First: The SQLite MCP Server prioritizes security without compromising functionality. All security features work transparently with zero configuration required.

Clone this wiki locally