Slide 1

Slide 1 text

Vito Modernizing SQL Injection CTF Challenges

Slide 2

Slide 2 text

The views, opinions, and thoughts in this talk are my own. They are not necessarily the views of my employer or customers. Standard Interjection

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

No content

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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"]}' “"")

Slide 9

Slide 9 text

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"]}' “"")

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

Challenges: The Problem • Have to have someone policing and monitoring and resetting shared resources • Now you need 24h ops

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

Web Challenges: The Solutions • Instance the database per-team • Use tickets/receipts to pick the team's database • Let teams reset their own database

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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()`

Slide 17

Slide 17 text

Instancing SQLite3 • A database is just a byte array

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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;

Slide 22

Slide 22 text

Web Challenges Got Automated • (Like over a decade ago)

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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.”

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

Web Challenges Got Automated sqlmap • Please sqlmap responsibly

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

No content

Slide 30

Slide 30 text

No content

Slide 31

Slide 31 text

No content

Slide 32

Slide 32 text

No content

Slide 33

Slide 33 text

No content

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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;

Slide 38

Slide 38 text

No content

Slide 39

Slide 39 text

No content

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

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.”

Slide 43

Slide 43 text

No content

Slide 44

Slide 44 text

No content

Slide 45

Slide 45 text

No content

Slide 46

Slide 46 text

No content

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

No content

Slide 49

Slide 49 text

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"] ] )

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

Meet Gilroy • 1742 teams registered • 221 teams solved the ligature challenge • 39 teams solved Gilroy • 11 of the top 12 teams solved it

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

Confessions • Maybe hand out binaries or binary-analogues? • Gilroy’s only 350k of beams • But then you can just run strings

Slide 55

Slide 55 text

Confessions • Is it the same owasp top-ten every year? • Is the di ff erence decorations?

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

Thanks • https://hackers.town/@vito • [email protected] • https://nautilus.institute