Find and Fix a SQL Injection

Steps

1. Start DVWA locally and log in

   Run the official DVWA image locally. It listens on port 80 and needs a
   one-time database setup on first boot.

   docker run --rm -d --name dvwa -p 80:80 vulnerables/web-dvwa
   

   Open http://localhost/setup.php in your browser and click
   "Create / Reset Database". Then go to http://localhost/login.php and
   log in with the default credentials:

   username: admin
   password: password
   

   Checkpoint

   After logging in, click DVWA Security in the left nav and confirm the
   security level selector is visible with options low, medium, high,
   impossible. Set it to low for now — you'll change it in Step 4.

   Deliverable for this step: DVWA running at http://localhost, logged
   in as admin, with security level set to low.

2. Find the injection point (low security)

   Navigate to the SQL Injection module in the left nav. It's a simple
   form: enter a "User ID" and it prints that user's first/last name.

   Start with the normal, expected input to see the baseline behavior:

   User ID: 1
   → Result: First name: admin, Surname: admin
   

   Now probe with a single quote — the classic first move for finding
   injection, because it often breaks the query's string literal and
   surfaces a database error or a blank page:

   User ID: 1'
   

   You should see a SQL error (something like You have an error in your SQL syntax...) — that's the injection point announcing itself. The
   application is doing something like:

   SELECT first_name, last_name FROM users WHERE user_id = '$id';
   

   and your ' closed the string early, leaving a dangling quote the
   database can't parse.

   Confirm it with a boolean payload

   User ID: 1' OR '1'='1
   

   If this returns every user instead of just one, you've confirmed the
   WHERE clause is being overridden, not just broken.

   Checkpoint

   Record the exact request/response for both payloads (1' and
   1' OR '1'='1). You should be able to point to: the error message that
   revealed the injection, and the query result that proved you controlled
   the WHERE logic.

   Deliverable for this step: two observed payload/response pairs proving
   the User ID field is unsafely concatenated into a SQL query.

3. Exploit with UNION to extract other data

   A UNION-based injection appends a second, attacker-chosen SELECT to
   the original query's result set. It works when you can match the column
   count and get the output rendered somewhere visible — exactly DVWA's case,
   since it prints two columns (first_name, last_name).

   Step 1: find the column count

   User ID: 1' ORDER BY 1-- -
   User ID: 1' ORDER BY 2-- -
   User ID: 1' ORDER BY 3-- -
   

   Increase the number until the query errors (Unknown column) — the last
   one that worked tells you the column count. On stock DVWA this confirms
   2 columns.

   Step 2: extract data with UNION SELECT

   Now substitute a UNION SELECT with 2 columns, pulling from a different
   table entirely:

   User ID: 1' UNION SELECT user, password FROM users-- -
   

   This should print every row of users.user and users.password in the
   same "First name / Surname" display fields — data the User ID lookup was
   never supposed to expose. On the DVWA sample database, the passwords are
   MD5 hashes; note that too (weak hashing is a separate problem from
   injection, but it compounds the damage here).

   Why this works structurally

   The original query became:

   SELECT first_name, last_name FROM users WHERE user_id = '1'
   UNION SELECT user, password FROM users-- -';
   

   The -- comments out the trailing quote from the original query so it
   doesn't cause a syntax error. Two SELECTs with matching column counts,
   UNIONed, are indistinguishable to the application from one — it just
   prints whatever rows come back.

   Checkpoint

   Confirm you extracted at least the user and password columns for every
   row in the users table, using only the web form — no direct database
   access. Write down the final payload string you used.

   Deliverable for this step: the working UNION SELECT payload and the
   extracted user/password data it returned, demonstrating full exposure
   of a table the endpoint was never meant to touch.

4. See why 'medium' security still fails

   Go back to DVWA Security and set the level to medium. Open the
   SQL Injection module again — note it's now a <select> dropdown of
   numeric IDs instead of a free-text field, and the underlying code switches
   from raw concatenation to mysqli_real_escape_string() on the input.

   Escaping neutralizes quote characters, but it does not change the
   query's structure, and it does nothing if you can get numeric/unquoted
   input in. Because the source is a dropdown, the direct browser UI resists
   casual typing — but the server-side check is still just escaping, and the
   request itself is a normal HTTP parameter you can forge directly.

   Bypass it with a raw HTTP request

   Use curl (or your browser's dev tools) to submit a value the dropdown
   would never offer, sending your session cookie so the request is
   authenticated:

   # Grab your PHPSESSID cookie from the browser dev tools after logging in
   curl -s "http://localhost/vulnerabilities/sqli/?id=1+UNION+SELECT+user,password+FROM+users-- -&Submit=Submit" \
     -H "Cookie: PHPSESSID=<your-session-id>; security=medium" \
     | grep -A2 "first_name"
   

   Because the query is still built as ... WHERE user_id = $id (numeric
   context, no quotes to escape) or as a string that only strips quotes
   rather than parameterizing, a numeric/unquoted UNION payload sails
   straight through the escaping.

   Checkpoint — name the real gap

   Answer in your own words: why did adding mysqli_real_escape_string()
   not fix the vulnerability? The answer should mention that escaping
   treats symptoms (dangerous characters) rather than the cause (user input
   being interpreted as part of the query grammar at all) — a value with no
   quotes to escape (like a bare UNION SELECT) sails straight through.

   Deliverable for this step: a successful UNION extraction against
   "medium" security via a forged request, plus a one-paragraph explanation
   of why escaping alone is not a structural fix.

5. Fix it: rewrite with parameterized queries (submission)

   DVWA itself is PHP and out of scope to patch here — the point of this step
   is to prove you understand the correct fix by rewriting the equivalent
   logic in your own stack, and to write a test that shows the exact payload
   from Step 3 no longer works.

   The vulnerable pattern (pseudocode, mirrors DVWA)

   def get_user_by_id(id):
       query = "SELECT first_name, last_name FROM users WHERE user_id = '" + id + "'"
       return db.execute(query)     # id is concatenated straight into the SQL string
   

   The fix: parameterized query / prepared statement

   def get_user_by_id(id):
       query = "SELECT first_name, last_name FROM users WHERE user_id = ?"
       return db.execute(query, params=[id])   # id is bound as data, never parsed as SQL
   

   Translate this to your actual stack. A few concrete examples:

   # Python + a DB-API driver (psycopg2, sqlite3, mysql-connector, ...)
   cursor.execute(
       "SELECT first_name, last_name FROM users WHERE user_id = %s",
       (user_id,),
   )
   

   // Node.js + node-postgres
   await pool.query(
     "SELECT first_name, last_name FROM users WHERE user_id = $1",
     [userId]
   );
   

   # Ruby / ActiveRecord
   User.where("user_id = ?", user_id)
   

   Whatever language you use, the shape is the same: the query string with
   placeholders is fixed and known ahead of time; user input is handed to the
   driver separately and never touches the SQL string itself.

   Prove the fix with a test

   Write a test (in your stack's test framework, or a small script) that runs
   the exact payload from Step 3 against your parameterized version and
   asserts it behaves as inert data, not as SQL:

   test "UNION payload is treated as a literal ID, not executed":
       payload = "1' UNION SELECT user, password FROM users-- -"
       result = get_user_by_id(payload)
       # A parameterized query treats the whole string as the user_id value.
       # No row has that literal string as its id, so we expect zero rows —
       # NOT a leaked password column and NOT a SQL error.
       expect(result) == []
   
   test "normal lookup still works":
       result = get_user_by_id("1")
       expect(result) == [{ first_name: "admin", last_name: "admin" }]
   

   Why this is a structural fix, not a stronger filter

   Unlike escaping, parameterization doesn't try to spot and neutralize
   dangerous characters — it removes the possibility of the query's grammar
   changing at all, because the driver sends the query template and the data
   over the wire as two separate things. There is no string for UNION,
   --, or ' to "break out" of, because the value is never re-parsed as
   SQL syntax.

   ---

   Submission criteria

   Submit when all of the following hold:

   1. Documented evidence of the exploit against DVWA "low" (Step 2) and the
      full UNION SELECT data extraction (Step 3).
   2. Documented evidence that DVWA "medium" is bypassed via a forged
      request, plus your explanation of why escaping isn't structural
      (Step 4).
   3. A rewritten get_user_by_id-equivalent function using a parameterized
      query / prepared statement in your language of choice.
   4. A passing test suite proving: the Step 3 payload returns zero rows /
      no error against the fixed version, and a normal lookup still works
      correctly.

   Include a one-line note confirming you only tested against your local
   DVWA container, never a third-party system.