SQL Dialect Quick Reference

Allowed Functions

String Functions

Encoding Functions

Mathematical Functions

Banned Functions

Security-Critical (Always Banned)

• load_extension() - SQLite extension loading
• eval() - Code evaluation
• exec() - Command execution
• system() - System calls
• shell() - Shell execution

File System Operations (Not Applicable)

• readfile() - File reading
• writefile() - File writing
• edit() - File editing

Complex Pattern Matching (Performance Concerns)

• glob() - Glob patterns
• regexp() - Regular expressions
• match() - Pattern matching

Mathematical Functions (Not Applicable)

• abs() - Absolute value
• random() - Random numbers
• randomblob() - Random binary data

Formatting Functions (Not Applicable)

• quote() - SQL quoting
• printf() - String formatting
• format() - String formatting
• char() - Character conversion
• unicode() - Unicode functions
• soundex() - Soundex algorithm
• difference() - String difference

Process Data Schema

-- Core process information
CREATE TABLE processes (
    id INTEGER PRIMARY KEY,
    scan_id INTEGER NOT NULL,
    collection_time INTEGER NOT NULL,
    pid INTEGER NOT NULL,
    ppid INTEGER,
    name TEXT NOT NULL,
    executable_path TEXT,
    command_line TEXT,
    start_time INTEGER,
    cpu_usage REAL,
    memory_usage INTEGER,
    status TEXT,
    executable_hash TEXT,        -- SHA-256 hash in hex format
    hash_algorithm TEXT,         -- Usually 'sha256'
    user_id INTEGER,
    group_id INTEGER,
    accessible BOOLEAN,
    file_exists BOOLEAN,
    environment_vars TEXT,        -- JSON string of environment variables
    metadata TEXT,               -- JSON string of additional metadata
    platform_data TEXT          -- JSON string of platform-specific data
);

Common Query Patterns

Basic Detection

-- Find processes by name
SELECT * FROM processes WHERE name = 'suspicious-process';

-- Find processes with pattern matching
SELECT * FROM processes WHERE name LIKE '%malware%';

Resource Analysis

-- High CPU usage
SELECT * FROM processes WHERE cpu_usage > 80.0;

-- High memory usage
SELECT * FROM processes WHERE memory_usage > 2147483648; -- 2GB

Hash-Based Detection

-- Known malicious hashes
SELECT * FROM processes
WHERE executable_hash = 'a1b2c3d4e5f6789012345678901234567890abcdef1234567890abcdef';

Command Line Analysis

-- Suspicious command patterns
SELECT * FROM processes
WHERE command_line LIKE '%nc -l%'     -- Netcat listener
   OR command_line LIKE '%wget%'      -- Download tools
   OR LENGTH(command_line) > 1000;   -- Unusually long commands

Path-Based Detection

-- Suspicious executable locations
SELECT * FROM processes
WHERE executable_path LIKE '/tmp/%'
   OR executable_path LIKE '/var/tmp/%'
   OR executable_path IS NULL;

Performance Tips

Use Indexes

• Time-based queries: WHERE collection_time > ?
• Process ID queries: WHERE pid = ?
• Name queries: WHERE name = ?

Limit Result Sets

-- Use LIMIT for large queries
SELECT * FROM processes WHERE name LIKE '%test%' LIMIT 100;

Avoid Complex Operations

-- Good: Simple conditions
WHERE name = 'process' AND pid > 1000;

-- Avoid: Complex nested operations
WHERE LENGTH(SUBSTR(command_line, 1, 100)) > 50;

Security Best Practices

Use Parameterized Queries

-- Good: Parameterized
SELECT * FROM processes WHERE name = ?;

-- Bad: String concatenation
SELECT * FROM processes WHERE name = '" + user_input + "';

Validate Input

• Always validate user-provided SQL fragments
• Use only approved functions
• Check for banned function usage

Monitor Performance

• Watch for queries that consume excessive resources
• Use query timeouts
• Monitor memory usage