Upgrade to PRO for Only $50/Year—Limited-Time Offer! 🔥

Modernizing SQL Injection CTF Challenges

vito
June 28, 2024

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.

vito

June 28, 2024
Tweet

More Decks by vito

Other Decks in Programming

Transcript

  1. The views, opinions, and thoughts in this talk are my

    own. They are not necessarily the views of my employer or customers. Standard Interjection
  2. 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
  3. 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
  4. 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
  5. 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
  6. 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"]}' “"")
  7. 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"]}' “"")
  8. 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
  9. Challenges: The Problem • Have to have someone policing and

    monitoring and resetting shared resources • Now you need 24h ops
  10. 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
  11. 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
  12. Web Challenges: The Solutions • Instance the database per-team •

    Use tickets/receipts to pick the team's database • Let teams reset their own database
  13. 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
  14. 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()`
  15. 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
  16. 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
  17. 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
  18. 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;
  19. 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
  20. 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.”
  21. 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
  22. 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
  23. 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
  24. 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
  25. 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
  26. 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
  27. 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;
  28. 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
  29. 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
  30. 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.”
  31. 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
  32. 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"] ] )
  33. 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
  34. Meet Gilroy • 1742 teams registered • 221 teams solved

    the ligature challenge • 39 teams solved Gilroy • 11 of the top 12 teams solved it
  35. 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
  36. 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
  37. Confessions • Maybe hand out binaries or binary-analogues? • Gilroy’s

    only 350k of beams • But then you can just run strings
  38. Confessions • Is it the same owasp top-ten every year?

    • Is the di ff erence decorations?
  39. 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
  40. 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