Northwind TrySQL

An unrestricted, browser-based SQL playground built for classroom use — specifically for teaching SQL fundamentals in the context of an ethical hacking and offensive security curriculum.

---

Background

W3Schools' TrySQL editor is the go-to starting point for countless SQL tutorials, but its query restrictions make it unsuitable for security training. Instructors teaching SQL injection need students to be able to run UNION queries, stack statements, query system tables, and experiment freely — all things W3Schools actively blocks.

This project recreates the same Northwind dataset in a fully open sandbox. There are no query restrictions, no server, and no network dependency beyond the initial CDN load. Students can break things, try anything, and learn without guardrails.

---

How It Works

The entire application is a single index.html file.

• sql.js compiles SQLite to WebAssembly and runs it entirely in the browser. There is no backend, no database server, and no data ever leaves the student's machine.
• CodeMirror 5 provides the SQL editor with syntax highlighting, autocomplete (Ctrl+Space), and keyboard execution (Ctrl+Enter).
• The full Northwind dataset is embedded as SQL INSERT statements and loaded into an in-memory SQLite database when the page opens.
• Because the database is in-memory, it resets on every page refresh — a useful property for demos, since students can DROP tables or corrupt data and simply reload to start fresh.

Opening the File

Option A — Direct file open (works in Firefox, may fail in Chrome):

# Just double-click index.html, or:
start index.html

Option B — Local server (works everywhere, recommended):

# Python (usually already installed)
python -m http.server 8080

# Node.js
npx serve .

# Then visit http://localhost:8080

Chrome sometimes blocks WebAssembly loaded from a file:// URL. If the database status indicator stays on "Loading…", use Option B.

---

The Dataset

This playground uses the classic Northwind sample database — the same dataset originally shipped with Microsoft Access and used by W3Schools. It models a small international food distributor.

Table	Rows	Description
Customers	91	Companies that place orders
Products	77	Items sold, with price and category
Orders	196	Order headers linking customers and employees
OrderDetails	512	Line items: which product, in which quantity, on which order
Employees	9	Sales staff who handle orders
Suppliers	29	Companies that supply products
Categories	8	Product categories (Beverages, Seafood, etc.)
Shippers	3	Shipping companies

Key Relationships

Suppliers → Products → Categories
Customers → Orders  → Employees
           Orders   → Shippers
           Orders   → OrderDetails → Products

---

SQL for Ethical Hacking: Teaching Examples

The queries below are standard, valid SQL — nothing destructive. They are grouped by the technique they illustrate, because each technique has a direct counterpart in SQL injection methodology. Students who understand why these queries work are far better prepared to both execute and defend against injection attacks.

---

1. Column Count Enumeration via ORDER BY

Before a UNION attack can work, an attacker needs to know how many columns the original query returns. The classic technique is to increment the ORDER BY column number until the query errors out.

-- This works (Customers has 7 columns, so 1-7 are valid)
SELECT * FROM Customers ORDER BY 7;

-- This errors: "1st ORDER BY term out of range"
SELECT * FROM Customers ORDER BY 8;

Teaching point: In a live injection, the attacker doesn't see the schema. They inject ORDER BY 1, ORDER BY 2, etc. and watch for the error to appear. The last number that succeeds is the column count — which they then use to craft a matching UNION.

---

2. UNION — Combining Result Sets

UNION appends rows from a second SELECT onto the first. Both queries must return the same number of columns. This is the basis of UNION-based SQL injection.

-- Pull customers and suppliers into one result set
SELECT CustomerName, City, Country FROM Customers
UNION
SELECT SupplierName, City, Country FROM Suppliers
ORDER BY Country;

-- Mix unrelated data using literal values to fill column slots
SELECT CustomerName, ContactName, Country FROM Customers WHERE 1=0
UNION
SELECT 'injected value', 'attacker data', 'exfiltrated';

Teaching point: The WHERE 1=0 on the first query suppresses its real rows, so only the injected row is returned. This is exactly what a UNION injection payload does — it silences the original query and substitutes the attacker's chosen data.

---

3. UNION — Extracting the Database Schema

In SQLite, the sqlite_master table holds the schema: every table name and its CREATE statement. This is the SQLite equivalent of querying information_schema.tables in MySQL/MSSQL.

-- Discover all table names
SELECT name, type FROM sqlite_master WHERE type = 'table';

-- Get the full column definitions for a specific table
SELECT sql FROM sqlite_master WHERE name = 'Customers';

-- UNION it into a query that returns two text columns
SELECT CustomerName, Country FROM Customers WHERE 1=0
UNION
SELECT name, sql FROM sqlite_master WHERE type = 'table';

Teaching point: Once an attacker can run a UNION, the next move is almost always to dump sqlite_master (or information_schema) to enumerate the rest of the database. Students should recognize this pattern in both offensive walkthroughs and in query logs they may be defending.

---

4. Stacked Queries (Statement Chaining)

Some database interfaces allow multiple statements separated by a semicolon. This is called query stacking or piggy-backing.

-- Two completely independent queries, run one after the other
SELECT CustomerName, Country FROM Customers WHERE Country = 'Germany';
SELECT ProductName, Price FROM Products WHERE Price > 50;

-- A read followed by a write — the classic stacking concern
SELECT * FROM Customers WHERE CustomerID = 1;
INSERT INTO Customers (CustomerID, CustomerName, Country)
VALUES (999, 'Injected Corp', 'Exfiltrated');

-- Confirm it worked
SELECT * FROM Customers WHERE CustomerID = 999;

Teaching point: Not all database connectors support stacking (PDO in PHP with MySQL does not by default; SQLite often does). When it works, stacking turns a read-only injection point into a write — enabling data modification, account creation, or (in some databases) reading files via LOAD_FILE. Students should understand both the power and the variability of this technique across platforms.

---

5. Subqueries — Nested Data Extraction

Subqueries allow data from one table to be used as a condition or value in another query. In injection contexts, they are used to extract data one row at a time or to build boolean-based (blind) probes.

-- Who placed the most orders? (subquery in WHERE)
SELECT CustomerName FROM Customers
WHERE CustomerID = (
  SELECT CustomerID FROM Orders
  GROUP BY CustomerID
  ORDER BY COUNT(*) DESC
  LIMIT 1
);

-- Inline subquery as a column (scalar subquery) — exfiltrates data row by row
SELECT 
  CustomerName,
  (SELECT COUNT(*) FROM Orders WHERE Orders.CustomerID = Customers.CustomerID) AS OrderCount
FROM Customers
ORDER BY OrderCount DESC;

Teaching point: Blind SQL injection — where the application returns no data but behaves differently based on whether a condition is true — relies on subqueries. The attacker crafts a condition like WHERE 1=(SELECT 1 FROM Users WHERE username='admin' AND SUBSTRING(password,1,1)='a') and infers the answer from the application's response. Understanding subqueries is prerequisite to understanding blind injection.

---

6. Conditional Logic — Boolean-Based Probing

CASE WHEN and IIF() let a query return different values depending on a condition. In injection, this is used to encode a true/false answer into an observable output difference.

-- Encode a condition into the output
SELECT 
  ProductName,
  Price,
  CASE 
    WHEN Price > 50 THEN 'HIGH'
    WHEN Price > 20 THEN 'MID'
    ELSE 'LOW'
  END AS PriceTier
FROM Products
ORDER BY Price DESC;

-- Boolean probe: returns a row only when the condition is true
-- In an injection scenario, "returns a row" vs "returns nothing" is the signal
SELECT CustomerName FROM Customers
WHERE CustomerID = 1
  AND (SELECT CASE WHEN (COUNT(*) > 5) THEN 1 ELSE 0 END FROM Employees) = 1;

Teaching point: Boolean-based blind injection works by converting any true/false question into an observable behavior difference (a result row appears, an error is thrown, or the page loads differently). Students should be able to read and write CASE expressions because automated tools like sqlmap generate them at high volume.

---

7. String Manipulation — Filter Evasion and Data Packing

SQLite provides functions for concatenating, slicing, and transforming strings. These are used offensively to pack multiple values into a single column slot and to bypass naive input filters.

-- Concatenation: pack multiple fields into one output column
SELECT 
  EmployeeID || ':' || FirstName || ' ' || LastName || ' <' || Notes || '>' AS EmployeeRecord
FROM Employees;

-- SUBSTR: extract one character at a time (core of character-by-character blind extraction)
SELECT SUBSTR(CustomerName, 1, 1) AS FirstChar FROM Customers WHERE CustomerID = 1;
SELECT SUBSTR(CustomerName, 2, 1) AS SecondChar FROM Customers WHERE CustomerID = 1;

-- HEX encoding: bypass string-literal filters that block quotes
-- hex('Alfreds') = '416C66726564732' — can be compared without quote characters
SELECT CustomerName FROM Customers 
WHERE HEX(SUBSTR(CustomerName, 1, 1)) = HEX('A');

Teaching point: Many injection filters look for keywords or quote characters. Attackers use HEX(), CHAR(), and string concatenation to reconstruct strings without triggering those filters. Understanding these functions helps students both craft bypasses and write more robust WAF rules.

---

8. Aggregation — Bulk Data Compression

Aggregation functions collapse many rows into one. In injection contexts, this is useful for extracting an entire column's worth of data in a single request.

-- Dump all customer names into one comma-separated string
SELECT GROUP_CONCAT(CustomerName, ' | ') FROM Customers WHERE Country = 'USA';

-- Combine multiple fields across all rows into one extractable blob
SELECT GROUP_CONCAT(EmployeeID || ':' || FirstName || ':' || LastName, '; ')
FROM Employees;

Teaching point: A UNION can only return values the application will display. If the application only renders one field, an attacker uses GROUP_CONCAT (MySQL: GROUP_CONCAT, MSSQL: STRING_AGG, Oracle: LISTAGG) to pack an entire table into that one field. This is a standard technique in manual injection and a good exercise for understanding multi-row extraction.

---

9. LIKE and Wildcards — Reconnaissance by Pattern

-- Find customers whose name contains a fragment (case-insensitive in SQLite)
SELECT CustomerName, Country FROM Customers WHERE CustomerName LIKE '%market%';

-- Find all UK-based contacts whose email domain might vary
SELECT ContactName, Country FROM Customers WHERE Country LIKE 'U%';

-- Wildcard across multiple tables via UNION
SELECT 'Customer' AS Source, CustomerName AS Name FROM Customers WHERE CustomerName LIKE '%restaurant%'
UNION
SELECT 'Supplier', SupplierName FROM Suppliers WHERE SupplierName LIKE '%restaurant%';

Teaching point: LIKE queries are used in injection to probe for specific data patterns — account names, email formats, internal hostnames — without knowing exact values. They also illustrate why parameterized queries must escape % and _ even in non-injection contexts.

---

10. LIMIT and OFFSET — Row-by-Row Iteration

-- Retrieve one row at a time — used when output is restricted to a single record
SELECT CustomerName, Country FROM Customers LIMIT 1 OFFSET 0;
SELECT CustomerName, Country FROM Customers LIMIT 1 OFFSET 1;
SELECT CustomerName, Country FROM Customers LIMIT 1 OFFSET 2;

-- Inside a subquery: extract the Nth row's value
SELECT (SELECT CustomerName FROM Customers ORDER BY CustomerID LIMIT 1 OFFSET 4);

Teaching point: Many injection scenarios only return one row. LIMIT 1 OFFSET N is the standard way to iterate through a table one record at a time. Automated tools increment N in a loop; students doing manual injection should understand how this works before relying on tooling.

---

Usage Tips for Instructors

• The database resets on every page refresh. Students can freely INSERT, UPDATE, DROP, and DELETE — a reload restores everything. This makes it safe to demonstrate destructive statements live.
• Click a table name in the sidebar to expand its column list. Double-click to auto-run SELECT *.
• Ctrl+Enter executes the current query. Ctrl+Space opens the autocomplete menu with table and column names.
• Stacked queries work. Separate multiple statements with ; and both will execute (the last result set is displayed).
• For a screen-share demo, the app runs entirely offline once the CDN resources have loaded once — no internet connection required mid-class.

---

License

The Northwind dataset is a Microsoft sample database, widely reproduced for educational use. This project's application code is released under the MIT License.