IDASQL: SQL Interface for IDA Pro

The Hex-Rays decompiler converts assembly into C-like pseudocode. There are two primary ways to interact with decompiled code via SQL:

1. Full Function Display (Primary Read Surface)

Use the decompile(addr) function to get the entire function as a single text block. Each line is prefixed with an address anchor (e.g., /* 401010 */) or a placeholder (/* */) if no anchor exists. This is the preferred method for explaining function logic.

2. Structured Editing (Edit Surface)

Use the pseudocode table for line-level operations. This table allows for filtering by func_addr, addr, or line_num and is used for writing comments.

3. AST-level Analysis

For deep structural analysis, use:

• ctree: The Abstract Syntax Tree of the decompiled code.
• ctree_call_args: Information about call arguments within the AST.
• ctree_lvars: Used to rename, change types, or add comments to local variables.

-- Primary way to view code
SELECT decompile('DriverEntry');

If Hex-Rays is available, you can query the AST and pseudocode via specialized tables.

CRITICAL PERFORMANCE NOTE: Always filter by func_addr. Without a constraint, the engine will attempt to decompile every function in the binary, which is extremely slow.

Key Tables

• pseudocode: Line-by-line pseudocode. Use decompile(addr) for viewing; use this table for surgical comment edits. Writable columns include comment and comment_placement (e.g., semi, block1, brace1, etc.).
• ctree: The full AST of decompiled code. Filter by func_addr.
• ctree_lvars: Local variables. Writable: name, type, comment. Filter by func_addr and update via idx.
• ctree_call_args: Flattened call arguments for easy joining.

Decompiler Views

Pre-built views for common patterns:

• ctree_v_calls: Function calls with callee info.
• ctree_v_loops: for/while/do loops.
• ctree_v_ifs: if statements.
• ctree_v_returns: Return statements with classification (e.g., return_num, return_var).

In IDASQL, addresses (effective addresses or ea_t) are represented as unsigned 64-bit integers. When querying, you can use integer EAs, numeric strings, or global symbol names.

Key behaviors:

• Symbol Resolution: Symbol names (e.g., 'DriverEntry') are resolved using get_name_ea. If a symbol cannot be resolved, the error Could not resolve name to address: <name> is returned.
• Formatting: For human-readable hex display in results, use printf('0x%X', addr).
• Predicates: Use standard SQL WHERE clauses to filter by address (e.g., WHERE addr = 0x401000).

-- Using a symbol name
SELECT decompile('DriverEntry');

-- Using a numeric string for an update
UPDATE applied_types
SET decl = 'NTSTATUS DriverEntry(PDRIVER_OBJECT, PUNICODE_STRING);'
WHERE addr = 'DriverEntry';

-- Using an integer EA
SELECT (SELECT comment FROM comments WHERE addr = 0x401000 LIMIT 1);

CTEs allow you to create readable, modular queries and perform recursive traversals like call graph analysis.

Basic CTE for Filtering

You can use CTEs to isolate specific behaviors (e.g., finding functions that call malloc and also perform a null check) before joining them.

Recursive CTEs for Call Graph Traversal

Recursive CTEs are essential for traversing call graphs. You can use them to:

• Forward Traversal: Find all functions reachable from a starting point (e.g., main) up to a specific depth.
• Reverse Traversal: Find all transitive callers of a specific target address.

CTE with Aggregation

Use CTEs to pre-calculate metrics like call counts or block counts per function, then join them to the funcs table to rank functions by complexity.

-- Find all functions reachable from main (up to depth 5)
WITH RECURSIVE call_graph AS (
    -- Base case: start from main
    SELECT addr as func_addr, name, 0 as depth
    FROM funcs WHERE name = 'main'

    UNION ALL

    -- Recursive case: follow calls
    SELECT f.addr, f.name, cg.depth + 1
    FROM call_graph cg
    JOIN disasm_calls dc ON dc.func_addr = cg.func_addr
    JOIN funcs f ON f.addr = dc.callee_addr
    WHERE cg.depth < 5
      AND dc.callee_addr != 0  -- Skip indirect calls
)
SELECT DISTINCT func_addr, name, MIN(depth) as min_depth
FROM call_graph
GROUP BY func_addr
ORDER BY min_depth, name;