SQL Dialect Quick Reference#

Allowed Functions#

String Functions#

Function	Description	Example
`LENGTH(str)`	String length	`LENGTH(command_line)`
`SUBSTR(str, start, length)`	Substring extraction	`SUBSTR(executable_path, 1, 10)`
`INSTR(str, substr)`	Find substring position	`INSTR(command_line, 'malicious')`
`LIKE pattern`	Pattern matching	`name LIKE '%suspicious%'`

Encoding Functions#

Function	Description	Example
`HEX(data)`	Convert to hexadecimal	`HEX(executable_hash)`
`UNHEX(hex)`	Convert from hexadecimal	`UNHEX('deadbeef')`

Mathematical Functions#

Function	Description	Example
`COUNT(*)`	Count rows	`COUNT(*) as process_count`
`SUM(expr)`	Sum values	`SUM(memory_usage)`
`AVG(expr)`	Average values	`AVG(cpu_usage)`
`MAX(expr)`	Maximum value	`MAX(memory_usage)`
`MIN(expr)`	Minimum value	`MIN(start_time)`

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