How this site works

This site is built with ASP.NET Core Razor Pages. My CV lives in a single cv.json file, and every time the app starts it turns that file into a SQLite database. The .cs and .swift files on the left and the SQL console below all show the same data.

Letting visitors run SQL

Running SQL from a public page sounds risky. So the protection does not rely on "cleaning" the query text; it relies on the database engine itself:

raw.sqlite3_limit(handle, raw.SQLITE_LIMIT_ATTACHED, 0);   // no ATTACH
raw.sqlite3_set_authorizer(handle, Authorizer, null);      // reads only
raw.sqlite3_progress_handler(handle, 10_000, Progress, w); // 1.5 s limit

• The file is opened read-only.
• The authorizer only allows SELECT, reads and function calls. PRAGMA, ATTACH and writes are refused by the engine.
• The progress handler stops any query that runs longer than 1.5 seconds, so even an endless WITH RECURSIVE can't lock up the server.
• Results are capped at 200 rows, and each IP can run 30 queries a minute.

Try it

Type this into the console below:

SELECT s.name, count(*) AS projects
FROM skills s
JOIN project_skill ps ON ps.skill_id = s.id
GROUP BY s.id
ORDER BY projects DESC;