Security Testing
Intermediate 20 min read Security Testing

SQL Injection Testing

SQL injection lets an attacker rewrite the queries your application runs. Learn how string-concatenated SQL becomes an injection point, how in-band, blind and out-of-band variants work, and why parameterized queries are the complete, standard fix.

SQL injectionSQLiparameterized queriesprepared statementsCWE-89blind SQLiunionpayload
Prerequisites: Security Testing Fundamentals OWASP Top 10
Introduction

What is this?

SQL injection (SQLi) is a server-side injection flaw that occurs when an application builds SQL queries by concatenating untrusted input into the query text. An attacker can alter the query's structure — rewriting WHERE clauses, appending UNION selects or stacking statements — to read, modify or delete data they should never touch. It remains one of the most severe web vulnerabilities and is completely preventable.

Motivation

Why do you need it?

  • It can expose entire databases: user tables, credentials and financial records in one request.
  • It can escalate to full server compromise via features like xp_cmdshell or COPY ... TO PROGRAM.
  • It breaks confidentiality, integrity and availability at the same time.
  • The fix — parameterized queries — is simple and free, yet the flaw keeps appearing in new code.
  • It is easy to test safely in authorized sandboxes, so teams can prove the defense works.
Think about it

Mental model

Imagine

A phone operator who repeats your words directly into the internal order system

Think of

SQL built by string concatenation

If the operator literally reads your reply into the system, saying 'cancel order 42 and refund customer 9' changes the instruction. Parameterized queries treat your input as a value, not as part of the instruction being read.

Mechanics

How it works

  • The application concatenates user input directly into a SQL statement string.
  • An attacker injects metacharacters such as a quote or a comment marker to break out of the expected value.
  • The query structure changes: OR 1=1 bypasses checks, UNION appends a second result set, and stacked statements run extra commands.
  • The database executes the modified statement and returns attacker-chosen data or changes state.
  • Prevention: parameterized queries everywhere, least-privilege database accounts, generic errors, and a WAF as defense-in-depth.

How an injection payload changes the meaning of a query.

User input
String concatenation
Altered query structure
Database executes
Data exfiltration
Reference

Key commands

curl -s -o /dev/null -w "%{http_code}" "http://sandbox.local/WebGoat/login"

Confirm the login endpoint is reachable before testing.

200
curl -s "http://sandbox.local/WebGoat/search?q=1%27%20OR%20%271%27%3D%271"

Educational smoke test: URL-encoded ' OR '1'='1 payload to observe whether the app changes behavior or leaks an error.

grep -rn "query(" app/ | head -20

List every SQL call site so each one can be audited for concatenation.

grep -rn "concat(" app/

Find dynamic-SQL helpers that build statements by joining strings.

psql "postgres://app_user@localhost/appdb" -c "SELECT current_user"

Verify the application database account runs with least privilege.

current_user\n-------------\n app_user
Hands-on

Examples

Parameterized queries: the complete fix

Parameterized queries: the complete fix
// ============================================================
// EDUCATIONAL PAYLOAD — authorized sandbox only (WebGoat lesson
// "SQL Injection"). Never run SQLi payloads against systems you
// do not own or lack written permission to test.
// ============================================================
// Vulnerable login query built by concatenation:
//   SELECT * FROM users WHERE username = '<input>' AND password = '<input>'
// Payload in the username field:
//   admin' OR '1'='1' --
// Resulting query:
//   SELECT * FROM users WHERE username = 'admin' OR '1'='1' -- ' AND password = '...'
 
// ============================================================
// DEFENSE — parameterized queries (node-postgres + bcrypt)
// ============================================================
const { Pool } = require("pg")
const bcrypt = require("bcryptjs")
const pool = new Pool()
 
async function login(username, password) {
  const result = await pool.query(
    "SELECT id, username, password_hash FROM users WHERE username = $1",
    [username]
  )
  const user = result.rows[0]
  if (!user || !bcrypt.compareSync(password, user.password_hash)) return null
  return user
}

The payload closes the quote with admin', adds OR '1'='1' which is always true, and comments out the rest with -- so the password check never runs. The defense sends the username as a bound parameter ($1): the driver transmits it as data, never as SQL text, so quotes and keywords have no effect on the statement's structure. Password hashes are compared with bcrypt so even a leaked table does not reveal credentials.

Real world

In production

  • Parameterized queries or prepared statements are the mandated pattern for every data access layer.
  • Database accounts run least-privilege, with scoped read or write roles per service.
  • ORM query builders are configured to forbid raw string interpolation.
  • A WAF and a generic error handler prevent verbose SQL errors from leaking schema details.
Guidelines

Best practices

  • Never build SQL by concatenation or template interpolation with user input.
  • Use parameterized queries or the ORM's safe query builder in every layer of the stack.
  • Run each service's database connection with the least privilege it needs.
  • Return generic error messages so query and schema details never reach the client.
  • Add a source-scan rule and a DAST check to catch concatenated queries in CI.
Watch out

Common mistakes

  • Escaping quotes instead of parameterizing — blacklist escaping is bypassable.
  • Allowing stacked queries, which turns a read-only flaw into arbitrary commands.
  • Exposing raw SQL errors to the client, revealing schema and table names.
  • Forgetting second-order injection, where stored data is later used unsafely in another query.
Root cause analysis

Common issues

Error
column "x" does not exist
Root cause

A UNION payload guessed the wrong column count or type.

Fix

Probe the column count with ORDER BY in the sandbox only, then align the UNION columns.

Error
TypeError: Cannot read properties of undefined
Root cause

The parameter binding order does not match the placeholders.

Fix

Align $1, $2 placeholders with the arguments array order and re-run the audit.

Error
The scan finds nothing, but manual testing still triggers errors
Root cause

The scanner's canned payloads are blocked by input validation or a WAF.

Fix

Test with realistic payloads in the sandbox and audit the query construction path directly in code.

When things break

Debugging tips

  • Test with a benign marker that forces a clear signal — like appending a comment character — before any UNION payload.
  • Map the schema in the WebGoat sandbox first, then verify against the real database with a read-only account.
  • Check the ORM or driver logs to confirm parameters are sent separately from the query text.
Interviews

Interview checkpoint

Practice

Hands-on lab

SQL Injection in WebGoat
40 min · intermediate
In progress
Objectives
  • Identify a concatenated login query as an injection point
  • Confirm the flaw with a benign marker before any payload
  • Demonstrate a UNION-based data extraction in the sandbox
  • Replace the vulnerable pattern with a parameterized query

SQL Injection in WebGoat

40 min · intermediate

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/3
1

Which of the following is the safe way to pass a search term to a query?

2

Input validation alone is a complete defense against SQL injection.

3

An attacker's UNION payload matches the original column count. What can they now do?

Quick reference

Cheat sheet

SQLi defense
pool.query(sql, [params])Parameterized, always
SELECT ... WHERE id = $1Postgres placeholder style
SELECT ... WHERE id = ?MySQL/Node mysql2 style
knex("items").where("id", id)ORM safe builder
SELECT current_userCheck least privilege
SET SEARCH_PATH ... / REVOKELimit table exposure
Go deeper

Official documentation

PortSwigger: SQL injection
docs
OWASP SQL Injection Prevention Cheat Sheet
docs
CWE-89: SQL Injection
docs
node-postgres documentation
docs