SQLI2

[scorpiontornado]

UNION attack, cross-table. sqlite_master table (equivalent of MySQL's information_schema).

See Kris' SQLI prerecorded lecture for more info.

What do people care about at uni/highschool? Clothes/shopping... MARKS!

Might be strikingly close to Kris' example (he also does marks + sqlite), but I could either implement it differently or simply just distinguish with my query viewer. Also, I have no idea how that was coded so I'd still be figuring things out and learning.

TODO:

• [ ] Create database (schema + insert data - prob gonna be lots, so load from .sql file? Or I could just hardcode it I guess...)
  • [ ] Students(student_id, firstname, lastname, email, phone) and Marks(student_id, course_id, mark integer, grade text)
  • [ ] Supposed to be a student lookup (find zId & email given first/last name (use ilike? or too confusing?), but someone accidentally put in the same database as grades
  • [ ] Store the create table queries in environment variables (large part of the challenge is poking around the table schema)
• [ ] Create frontend - form with input, display query results, query viewer above/below form / on the side or something.
  • [ ] This is also related to setting up a server route, and making it handle POST
  • [ ] An extension could be fetch() and updating the dom rather than a full form submit/reload, but that'll take me a while to implement (would need to make a button onclick() extract the value from the boxes, then fetch a url, figure out if I need a new route for this?)
  • [ ] Wait... Issue. I was gonna have a post request and render with variables, but that's not gonna work cause I'm not actually posting anything - I'm GETting. Perhaps query parameters + GET?
• [ ] Test to make sure it works - sqlite_master is set up correctly, UNION is working, flag can be found etc
  • [x] How do I make sqlite master only show sqli2 stuff? Different .db file, new connection I suppose
  • [x] Should I add a blacklist? Idk that's kinda annoying and they've already done it (although, in SQLI2 the only thing they needed to do was nest OR inside itself). Perhaps challenge 4 to put everything they've learned together. -> will be done in sqli3
• [ ] Add (brief) hints
• [ ] User testing - give to Hugo etc - someone with a technical (but not security) background)

[scorpiontornado]

TODO - display results (loop template), javascript query viewer, redteaming test, add padding on right of search bar

[scorpiontornado]

Update hint. ILIKE not supported, only LIKE and LOWER()/UPPER(). Also, zid is a number not string.... can't use LIKE anyway. Do we want them to be able to enter first and last name? Or ZID? Former would allow us to use the LIKE thing to display all rows but idk if its realistic. ZID lookup people names/emails seems more realistic to me, considering you wouldn't often want to go from name -> ZID as they are arbitrary.

[scorpiontornado]

Find another way to avoid the bug "no such column: test" when entering a non-numeric value like "test". This way prevents you from injecting anything rip