Modernizing SQL Injection CTF Challenges

At Nautilus Institute, we built a system for running "Raw Water," a web-based SQL injection challenge for DEF CON Capture The Flag qualifiers in 2023. This challenge allowed teams to attack a private, isolated, and persistent SQL instance through a web application that wasn't solvable with the very generic "sqlmap" tool.

This talk touches on Vito's experience with web-based challenges as both a player and challenge author, SQL sandboxing techniques and how they affect game operations, mitigations for sqlmap, and potential future work.

Transcript

1. Vito Modernizing SQL Injection CTF Challenges
2. The views, opinions, and thoughts in this talk are my own. They are not necessarily the views of my employer or customers. Standard Interjection
3. What is CTF? • Capture The Flag • In this context, it's a computer hacking contest • Get somewhere you shouldn't be • Recover a piece of valuable data • Redeem it for points
5. Who is Vito? • Helped run some CTFs • Legitimate Business Syndicate, DEF CON CTF 2013-2017 • Cyber Grand Challenge 2014-2016 • Hack-A-Sat 2020-2023 • Nautilus Institute, DEF CON CTF 2022-present • https://hackers.town/@vito
6. What is Quals? • Quali fi ers for the fi nals • Typically played by thousands of teams • Need to support them all • With very few organizers • One or, if you're lucky, two organizers understand any given chall
7. Web Challenges • XSS, CSRF is hard • have to run a victim browser that hits everyone's sites • SSRF is hard • have to run a victim network that the server hits • SQL injection is hard
8. SQL Injection # pretend the user POSTed this in params = %{"id" = > "x' union select receipient_id, text from direct_messages where sender_id='admin; -- "} exec(db, """ SELECT title, body FROM posts WHERE id = '#{params["id"]}' “"")
9. SQL Injection # pretend the user POSTed this in params = %{"id" = > "x' union select receipient_id, text from direct_messages where sender_id='admin; -- "} exec(db, """ SELECT title, body FROM posts WHERE id = '#{params["id"]}' “"")
10. Challenges: The Problem • Everything at DEF CON gets vandalized with stickers, googly eyes, etc. because it's funny • Everything in online CTFs gets vandalized because it’s funny • And also because there’s an incentive to disrupt other teams
11. Challenges: The Problem • Have to have someone policing and monitoring and resetting shared resources • Now you need 24h ops
12. Binary Challenges: The Solutions • Instance the challenges per-connection • Tickets/Receipts add some neat extras • Consistent RNG seed for some customization • Per-team fl ags and traceability
13. Not Super-Applicable to Web • Hack-A-Sat had some web challs • Very thirsty docker-compose setups • Multiple containers per connection • Full apps and databases • The main problem is web apps need to hold state between connections
14. Web Challenges: The Solutions • Instance the database per-team • Use tickets/receipts to pick the team's database • Let teams reset their own database
15. Instancing a Database • Probably don’t want to instance Postgres • I’m not running `createdb` 2000 times • I'm not running 2000 servers • I trust Postgres permissions but I don’t trust them against 2000 teams of motivated hackers
16. Instancing SQLite3 • “SQLite does not compete with client/server databases. SQLite competes with fopen()." https://sqlite.org/whentouse.html • You don’t even need to `fopen()`! • Use the fi lename `:memory:` • `sqlite3_deserialize()` if you have existing state • SQLite3 normally • `sqlite3_serialize()`
17. Instancing SQLite3 • A database is just a byte array
18. Storing Per-Team Databases • Normal Filesystem • We want to go horizontal with multiple app servers • Bothers my 12-factor brain • Distributed/Shared Filesystem • Don’t know how this will work when two players on the same team try and touch the same fi le
19. Storing Per-Team Databases • Object Storage • S3, Azure's S3 knocko ff • What're the multiple write semantics like • Pay-per-read and pay-per-write bugs me a lot
20. Storing Per-Team Databases • PostgreSQL • Use a well-supported abstraction layer to interact safely • Just put the bytes in a really wide column • It's not cursed! • It's only a little bit cursed • trust me
21. Storing Per-Team Databases BEGIN TRANSACTION; SELECT database FROM tickets WHERE id = ? FOR UPDATE SKIP LOCKED LIMIT 1; -- operate on databass UPDATE tickets SET database = ? WHERE id = ?; COMMIT;
22. Web Challenges Got Automated • (Like over a decade ago)
23. Web Challenges Got Automated DirBuster • “DirBuster is a multi threaded java application designed to brute force directories and fi les names on web/application servers.” • Ran up a hell of a bill on a challenge that was a static website • Object storage and paying per 404
24. Web Challenges Got Automated sqlmap • “sqlmap is an open source penetration testing tool that automates the process of detecting and exploiting SQL injection fl aws and taking over of database servers.”
25. Web Challenges Got Automated sqlmap • Point it at a URL with sqli and it will • Load the page • Parse HTML • Find forms • Send requests • Dump the database
26. Web Challenges Got Automated sqlmap • Please sqlmap responsibly
27. Thinking About a Solution • Normal Elixir Phoenix app • One Postgres table for ticket data & instanced databases • SQL-injectable interface presented over WebSockets • Sqlmap can’t interact with these
28. Working With WebSockets in Phoenix • Started fi guring out how to work with a raw WebSocket • Keeping ongoing form state in a process on the server, sending new fi elds to the client • Realized this is exactly what Phoenix LiveView does • thankfully before implementing all of it
34. Refining Raw Water • All these interactions are over the WebSocket so you can't frontload it • You can still puppet a web browser • Presumably you could also write a non-browser client • Sadly, it’s after 2020 and you should probably just puppet a web browser
35. Refining Raw Water defp fields_to_values(form) do form.field_seqs |> Enum.map(fn t -> field_to_value(form.fields[t]) end) |> List.flatten() |> Enum.join(", ") end defp field_to_value(%Hellform.Field{value: nil} = _field), do: [] defp field_to_value(%Hellform.Field{value: value, party: true} = _field) do "\'#{value}\'" end defp field_to_value(%Hellform.Field{} = _field) do "?" end
36. defp fields_to_bindlist(form) do form.field_seqs |> Enum.map(fn t -> field_to_bindlist(form.fields[t]) end) |> List.flatten() |> tap(fn x -> Logger.info(inspect(x)) end) end defp field_to_bindlist(%Hellform.Field{value: nil}), do: [] defp field_to_bindlist(%Hellform.Field{party: true}), do: [] defp field_to_bindlist(%Hellform.Field{value: v}), do: v
37. Drinking the Raw Water INSERT INTO orders ( product_id, "uZJJXi5pGME", "pLlCxRHto2I", -- bunch of auto-generated field names ) VALUES ( ?, -- product_id ?, ?, -- lots more interpolation sites ?, '' | | (select flag from flags) | | '', ?, ?, -- … ) RETURNING id;
40. Solving Raw Water • 1828 teams registered • 517 teams solved the baby challenge • 26 teams solved Raw Water • 11 of the top 12 teams solved it • Not Shellphish
41. Can We Do Other Vulnerabilities? • Parameter injection • When you add extra form fi elds that get put into columns the developer didn't expect you to clobber • Egor Homakov did this to GitHub and it was very funny
42. Parameter Injection • Fish said this isn't really “injection" • I think it's “CWE-74 Improper Neutralization of Special Elements in Output Used by a Downstream Component (‘Injection’)” • “The product constructs all or part of a command, data structure, or record using externally-in fl uenced input from an upstream component, but it does not neutralize or incorrectly neutralizes special elements that could modify how it is parsed or interpreted when it is sent to a downstream component.”
47. Meet Gilroy • Skipped the LiveView since it's not intended to be SQL injectable • Simpler questions about when the database needs to be locked for update • Forum software style means you can make each request either update and redirect or select and render HTML
49. Meet Gilroy {:ok, [[got]]} = Db.ins( db, """ insert into posters (id, name, password_digest, "group") values ($1, $2, $3, $4) returning id; """, [ merged_params["id"], merged_params["name"], merged_params["password_digest"], merged_params["group"] ] )
50. Meet Gilroy • there's a constraint to only allow one admin • post a banme with the admin's poster_id • make a new user in the admin group
51. Meet Gilroy • 1742 teams registered • 221 teams solved the ligature challenge • 39 teams solved Gilroy • 11 of the top 12 teams solved it
52. Confessions • I still don't feel like I know web vulns • With binaries, you just grab the API docs from your ISA vendor and your OS vendor
53. Confession • With web, you either • Hand out source and users will use really good tools to audit it • Give out nothing and it becomes guessing
54. Confessions • Maybe hand out binaries or binary-analogues? • Gilroy’s only 350k of beams • But then you can just run strings
55. Confessions • Is it the same owasp top-ten every year? • Is the di ff erence decorations?
56. Confessions • More to learn and explore! • If you want to mess with this stu ff , it’s open-source • https://github.com/Nautilus-Institute/quals-2023/tree/main/rawwater • https://github.com/Nautilus-Institute/quals-2024/tree/main/gilroy
57. Closing • Find me around, I'm doing Hacker Headshots • Come say hi at DEF CON • Later on Saturday is probably the best bet logistically • Play quals next year! • We try to announce dates on or before April 1
58. Thanks • https://hackers.town/@vito • vito@nautilus.institute • https://nautilus.institute