Lion Cipher Lab

Turn analyst questions into log queries with an LLM, then score the SQL by executing it: string match, one-database execution, and differential testing across planted-edge databases (deterministic STUB model, optional Ollama)

• AI Security
• Intermediate
• ~90 min
• Free · no account required

Why it matters

Every SOC that puts a language model in front of its log store gets the same pitch: let analysts ask questions in English - 'which users had five or more failed logins in the last day?' - and let the model write the SQL, KQL or SPL. That is a real productivity win, and it moves the risk somewhere quiet. A query that is subtly wrong does not crash. It returns a tidy, plausible table, missing the one account that failed exactly five times, or the admin with no traffic, or the login at 16:30 UTC that was 18:30 in the office. Nobody notices, because the answer looks like an answer. So before you…

What you'll learn

• building a 25-question text-to-SQL eval set over a synthetic auth + proxy log database, each question with a hand-written GOLD query and a written-down spec (frozen 'now', case-insensitive user names, inclusive windows, a UTC+2 office) that the gold queries obey
• scoring model-written SQL three ways and quoting the counts: normalised string match accepted 7 of 25 and rejected 8 correct queries; execution on one database accepted 22 of 25 including 7 wrong ones; execution across six databases accepted 16 of 25 with 1 wrong one left
• recognising the six classic text-to-SQL bugs in a log query - an off-by-one time window, > where >= was meant, a missing DISTINCT, an INNER JOIN that should be LEFT, case-sensitive user matching, and a UTC-versus-office-time slip - and knowing which kind of test data exposes each
• reading a per-database disagreement column to see that random re-seeding alone caught only 3 of those 6 bugs (q16, q19, q21) and that the other 3 (q17, q20, q22) were caught only by databases with rows planted exactly on the boundary
• keeping the honest negative: a LIKE 'svc_%' query survived all six databases because no generated user name could tell it apart, and was caught only after you wrote the missing data shape (an account called svcdesk) into a seventh database - the test databases ARE the spec
• executing untrusted, model-written SQL safely enough to grade it: a read-only sqlite3 URI connection (mode=ro) that refused a DELETE with 'attempt to write a readonly database', and a progress-handler step budget that interrupted a never-ending recursive query
• optionally swapping the stub for a local Ollama model and scoring its queries with the same harness, offline and with no API key

Steps in this lab

1. The words first: text-to-SQL, gold query, eval set, and three ways to grade - plus the fact that the model here is a stub
2. Open a terminal and make a scratch folder (all three operating systems)
3. Build the log databases: one typical, three wide, two with planted boundary rows
4. Write the eval set: 25 analyst questions, each with a hand-written gold query and a written-down spec
5. The stub model, disclosed in full: 7 identical, 8 rewritten-but-correct, 10 wrong
6. Score it three ways and read the counts: 7, then 22 with 7 wrong, then 16 with 1 wrong
7. Executing model-written SQL: open it read-only and give every query a budget
8. The honest negative: a wrong query that all six databases passed, and the seventh database that caught it
9. Optional: swap the stub for a local model with Ollama and score its queries the same way
10. Judgment: when execution scoring is the right tool, when it is not, and how a pro builds the test databases
11. Ship it: the three numbers to quote, and what to put in the eval's README

Open the full interactive lab →