SQL injection in depth
You find unsafe query patterns across languages and ORMs, confirm blind and second-order injection in your own app, and secure the lasting fix with a coding standard, database settings and CI rules.
Level 301 · 18 lessons · 16 practice labs
Lessons
SQL injection in code
- String concatenation in every language: PHP, Python, Java and Node
- Unsafe ORM patterns: raw queries and dynamic ordering
- What cannot be parameterised: table, column and sort names
- Second-order injection: stored input that runs later
- Short task: find four unsafe queries in the codebase
Recognising and confirming
- Recognising boolean-based blind injection
- Time-based blind injection
- Database differences: MySQL, PostgreSQL, MSSQL and SQLite
- The out-of-band injection concept and restricting outbound traffic
- Using sqlmap on your own app in a limited, safe way
- Short task: confirm a blind injection with Burp and measure its impact
The lasting fix
- Spreading prepared statements to every layer and a coding standard
- Allow-lists for dynamic sorting and filtering
- The database side: least privilege, stored procedures and hiding errors
- Short task: fix the four queries you found and write their tests
Detection
- Injection patterns in web and database logs
- Catching unsafe queries in CI with a Semgrep rule
- Capstone: find, close and retest every SQL injection in an application
Practice labs
- String concatenation in four languages: Find the unsafe queries in PHP, Python, Java and Node samples and fix each with that language's prepared statement.
- The raw query trap in an ORM: Find the injection in a report filter built with a raw query in an ORM project and move it to the ORM's parameterised path.
- Second-order injection: a stored username: Find a username stored safely at sign-up that later mixes into a query in an admin report and fix the report query.
- Dynamic table names and sorting: Make an export feature that builds table and column names from user choices safe with an allow list and a mapping table.
- Measure the true-false response difference: Confirm with Burp the response that changes with the condition on the stock check endpoint and point to the affected query in code.
- Time-based blind injection: Confirm injection through a time delay on a tracking endpoint that shows no difference and show the delay disappears after the fix.
- Database differences: one flaw, three back ends: Recognise the same flaw on MySQL, PostgreSQL and SQLite back ends and note which syntax differences change the result.
- An out-of-band request and egress restriction: Show in the lab that the database server can make outbound DNS requests and close egress for the database segment.
- Run sqlmap limited and safe: Run sqlmap on your own lab app with low risk, one parameter and a rate limit and measure its impact with the database log.
- Dynamic SQL inside a stored procedure: Find the flaw in a stored procedure that takes parameters but builds dynamic SQL inside and fix it with parameterised execution.
- An allow list for a search filter: Rewrite a multi-field search filter with an allow list and add tests that try bad inputs.
- Hide database error messages: Find every place where database errors reach users and close them with a central error handler.
- Fix four queries and ship them with tests: Fix four unsafe queries in the fictional Zelvio HR app, write a regression test for each and retest.
- An injection pattern in the database log: Find query shapes in the database query log that the app never normally produces and match them to the source web request.
- Catch unsafe queries in CI with a Semgrep rule: Write a Semgrep rule that catches concatenated queries in the codebase, add it to CI and weed out false positives.
- End-to-end detection for injection: Build an injection alert that combines web, app and database logs and show it fires on replayed attack traffic.
About this course
You find unsafe query patterns across languages and ORMs, confirm blind and second-order injection in your own app, and secure the lasting fix with a coding standard, database settings and CI rules.
What you will learn
- SQL injection in code
- Recognising and confirming
- The lasting fix
- Detection
Prerequisites
You should be comfortable with the level 101 and 201 topics of the same path.
Lab environment
Lessons run on your own Kali Linux desktop in the browser, with the tools ready in their windowed interfaces. Nothing to install; a computer screen is recommended.
Frequently asked questions
- Is this course right for my level?
- The course is at level 301; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.