R2xsql Data

When to use

Pick this skill for non-code facts about the binary:

• printable strings the binary embeds (strings)
• mapped bytes and initialization state, or overwriting one byte directly (bytes)
• defined data symbols/strings (data_items)
• byte-pattern matches (byte_search)
• encoding an assembly mnemonic to bytes (assemble), or finding where an assembled instruction occurs in the binary (search_asm)
• demangling a C++/Rust/Swift/MSVC symbol name (demangle)
• reading a null-terminated string at an ARBITRARY address, not just an address strings' own detection heuristics flagged (read_cstr)
• staging, committing, or undoing a revertible raw hex byte patch, either from raw hex or an assembly mnemonic (patches)
• functions the binary imports from other libraries (imports)
• loader entry points and functions/symbols the binary exports (entries)
• the binary's relocation table (relocs)
• the binary's linked/imported library list (libs)
• top-level binary metadata: arch, bits, bintype, OS, … (binary)
• type definitions and members loaded by r2 (types, types_members — see the connect schema-catalog reference for the full column list)

For code structure (functions/blocks/instructions) use disassembly. For cross-references between code and data, use xrefs.

Tables

Common queries

-- find every string containing "password" (case-insensitive)
SELECT addr, content FROM strings WHERE content LIKE '%password%';

-- ...and where to patch it on disk: addr is the VIRTUAL address, paddr the
-- file offset. They differ by the image base, so do not use one for the other.
SELECT printf('0x%x', addr) AS vaddr, paddr, content
FROM strings WHERE content LIKE '%password%';

-- binary type / arch / bits at a glance
SELECT key, value FROM binary
WHERE key IN ('bintype','arch','bits','os','class','endian');

-- imports grouped by library
SELECT module, COUNT(*) AS n
FROM imports
GROUP BY module ORDER BY n DESC;

-- crypto API surface
SELECT name, module FROM imports
WHERE name LIKE 'Crypt%' OR name LIKE '%AES%'
   OR name LIKE '%RSA%'  OR name LIKE 'BCrypt%';

-- network / IO surface
SELECT name, module FROM imports
WHERE module IN ('WS2_32.dll','WINHTTP.dll','WININET.dll','URLMON.dll');

-- entry points / exports (`entries`)
SELECT name, addr, type FROM entries ORDER BY name;

-- linked libraries
SELECT name FROM libs ORDER BY name;

-- relocations against a named import
SELECT printf('0x%x', addr) AS at, name, type FROM relocs WHERE name IS NOT NULL;

-- readable initialized bytes in a range
SELECT addr, value FROM bytes
WHERE addr >= 0x401000 AND addr < 0x401100 AND is_initialized = 1;

-- overwrite one byte directly and permanently (NOT staged -- see caveats)
UPDATE bytes SET value = 0x90 WHERE addr = 0x401000;

-- byte pattern (hex and `.` wildcard nibbles; optional `:mask`)
SELECT addr, matched_hex FROM byte_search
WHERE pattern = '4889' AND max_results = 20;

-- search_first(pat) recipe: first match only. max_results = 1 (not just
-- LIMIT 1) skips byte_search's own N+1 full-match re-read for every hit
-- after the first -- LIMIT 1 alone would still pay that cost for every hit,
-- since the generator collects the whole result set before any row is
-- truncated (see the caveat below).
SELECT addr, matched_hex, size FROM byte_search
WHERE pattern = '4889' AND max_results = 1;

-- assemble a mnemonic to bytes (encoded relative to addr, for pc-relative
-- instructions like short branches / rip-relative loads -- omit addr for a
-- context-free encoding, which defaults to address 0)
SELECT hex, size FROM assemble WHERE asm = 'mov eax, 1';
SELECT hex FROM assemble WHERE asm = 'jmp 0x401010' AND addr = 0x401000;

-- assemble-then-search: every site where this exact instruction occurs
SELECT printf('0x%x', addr) AS at, matched_hex FROM search_asm
WHERE asm = 'push rbp' AND max_results = 20;

-- demangle a symbol name (lang is optional -- radare2 auto-detects from the
-- mangled text's own prefix regardless of what lang says, see caveats)
SELECT demangled FROM demangle WHERE mangled = '_ZN3Foo3barEv';
SELECT demangled, error FROM demangle WHERE mangled = '?foo@@YAHXZ' AND lang = 'msvc';

-- read_cstr(addr): a null-terminated string at an ARBITRARY address, not
-- just wherever `strings`' own detection heuristics already flagged one --
-- e.g. mid-string, or an address the heuristics never picked up.
SELECT content, length FROM read_cstr WHERE addr = 0x1400173f6;
SELECT content, truncated FROM read_cstr WHERE addr = 0x401000 AND max_len = 512;

-- name_at(addr) / section_at(addr) / string_at(addr): all three resolve to
-- a plain WHERE against an existing table -- no dedicated table needed.
SELECT name, namespace FROM names WHERE addr = 0x140001d70;  -- name_at
SELECT name FROM sections                                     -- section_at
WHERE start_addr <= 0x140001d70 AND end_addr > 0x140001d70;
SELECT content FROM strings WHERE addr = 0x1400173f0;         -- string_at

-- biggest strings
SELECT addr, length, type, content
FROM strings ORDER BY length DESC LIMIT 20;

-- stage a 4-byte NOP patch, then commit it
INSERT INTO patches(addr, patched_bytes) VALUES (0x401234, '90909090');
UPDATE patches SET committed = 1 WHERE addr = 0x401234;

-- see everything still staged but not yet on disk
SELECT addr, patched_bytes FROM patches WHERE committed = 0;

-- patch_asm(addr, asm) recipe: assemble-then-write in one INSERT (asm_text
-- is assembled AT addr, so pc-relative encodings land correctly) -- exactly
-- one of patched_bytes/asm_text is required, never both
INSERT INTO patches(addr, asm_text) VALUES (0x401234, 'nop');
UPDATE patches SET committed = 1 WHERE addr = 0x401234;

Bootstrap recipe

SELECT key, value FROM binary ORDER BY key;

binary includes the raw iIj keys plus the func_count, string_count, import_count, section_count quick-counts r2xsql computes at session start, plus radare2_version (the LIVE running engine's own version from ?Vj — distinct from r2xsql_version/tool_version, which are r2xsql's own build-time identity). A db_info-style key/value metadata summary needs no dedicated table: binary plus bininfo already carry every field it would have, and every *_count is just SELECT COUNT(*) FROM <table>.

Performance

strings, imports, and entries all read straight from radare2's in-process binary state on the in-process build (r_bin_get_strings, RBinImport, RBinAddr/RBinSymbol respectively) instead of running izj/iij/iej+iEj and parsing the JSON — identical rows either way, including imports.name's demangling and entries.name staying the ORIGINAL (never demangled) name on both build flavors. None of these three needs an analysis pass: they read the loaded binary's own metadata.

relocs/libs are each already ONE full-scan command (irj/ilj) with no per-function/per-address loop to collapse, so both stay command-path-only on every build flavor — no C-API producer exists or is needed. Neither needs an analysis pass either: both read the loaded binary's own bin-info directly.

Caveats

• strings.type distinguishes ascii, utf16le, utf8, … — filter on it if you only want printable ASCII.
• imports.addr is the IAT slot, not the resolved external. Use the xrefs skill to find code that calls through that slot.
• entries combines loader entry points and exports; executables can therefore have rows even when they export no symbols.
• bytes.value is NULL for uninitialized or unreadable mapped bytes.
• UPDATE bytes SET value = X WHERE addr = Y is a direct, immediate, permanent one-byte overwrite (wx) — this is NOT the same mechanism as patches. It bypasses io.cache entirely (disabling it for the write, then restoring whatever it was before, even mid-flight around an unrelated patches row) so it can never be staged, undone, or interfere with a patches entry. There is no undo; use patches instead if you want a revertible change. value must be 0-255 (rejected otherwise before any command runs), and only a currently-initialized address can be written — a BSS/unbacked address is refused up front. No INSERT/DELETE; addr/ is_initialized stay read-only.
• relocs has no addend column: the underlying C struct carries one, but radare2's own irj never emits it as a JSON field, so there is nothing for this table to read it from.
• relocs.name/demname/sym_vaddr are each independently nullable — sym_vaddr is populated only when the relocation resolves through a genuine symbol rather than an import.
• libs is a single-column table (name) — ilj carries no ordinal, load-order index, or resolved path to surface.
• patches accepts EITHER raw hex (patched_bytes) OR an assembly mnemonic (asm_text, assembled via the same mechanism as the assemble table) on INSERT — exactly one of the two, never both, never neither. asm_text itself is write-only: it is never stored or read back, so SELECT asm_text FROM patches always reads empty, even for the row it just created.
• assemble/search_asm/patches.asm_text all charset-validate the mnemonic text (letters, digits, space, and ,.+-*/[]():%#_$) before splicing it into a command — real assembly text never needs ;/@/|/ backtick/quotes, so this is enough to make the value injection-safe without quoting. assemble and patches.asm_text are also address-scoped: the encoding depends on which address the instruction lands at (pc-relative branches, rip-relative loads), so pass addr (or the patch's own target address) rather than relying on whatever the session happens to be seeked to.
• pa (backing assemble) has no JSON output mode and prints nothing to stdout on a bad mnemonic (radare2's own diagnostic is stderr-only) — a failed assemble reads back as an empty hex with a populated error, not as a SQL error.
• /a (backing search_asm) similarly has no working JSON mode: appending j right after /a is NOT this command's JSON flag — it misroutes to the unrelated /at (search-by-instruction-type) family instead (verified live; do not try .r2cmd /aj ... expecting a JSON assemble-search). search_asm parses the plain-text hit lines itself.
• demangle's lang is always advisory: radare2 auto-detects the actual demangler from the mangled text's own prefix (_Z/__Z/?/…) regardless of what lang says, and an unrecognized lang string does not abort the call — it just falls through to auto-detection. demangled is empty (with error populated) whenever radare2 can't demangle the text — including a plain, unmangled string like main.
• read_cstr's max_len = 0 is rejected before any command is issued (radare2's pszj prints nothing at all for an explicit 0, verified live). truncated = 1 means only "no NUL found in the scanned window" — it does NOT by itself distinguish a genuinely longer string from an unmapped/meaningless address: an out-of-range read still comes back as a full max_len-byte string of radare2's own unmapped-fill byte, with section/type both "unknown". Cross-check bytes for a stronger mapped/unmapped guarantee. name_at/section_at/string_at above are recipes, not tables — name_at's names lookup can return MORE THAN ONE row per address (radare2's flags are not single-valued per address); add AND namespace = '...' to disambiguate.
• patches commit is table-wide: UPDATE patches SET committed = 1 WHERE addr = X commits every pending patch in the session, not just the row at X (radare2's write cache has no selective/ranged commit — its ranged form is unsafe). SET committed = 0 is rejected; there is no verified "uncommit" primitive.
• DELETE FROM patches WHERE addr = X undoes an uncommitted patch only when it is the most-recently-staged one — radare2's undo has no address targeting, it always undoes whatever was staged last. Delete newer pending patches first if you need to undo an older one.
• INSERT's stage (wx) is address-scoped, so on the in-process backend it also uses the same structural command-dispatch path other writable tables' address-scoped writes use — a second, independent layer beneath the hex validation above. The commit (wci) and undo (wcu) primitives are fixed commands with no user input in them at all, so there is nothing there for that dispatch to add.