SQL Injection Detection and Prevention Testing

1-- Input: email = ''' OR '1'='1
2-- Query: SELECT * FROM users WHERE email = '' OR '1'='1'
3-- Effect: returns all users

1-- Input: productId = 1 UNION SELECT 1,username,password,4 FROM users--
2-- Effect: returns user credentials in product response

1// SAFE: Prisma parameterized query
2const user = await prisma.user.findFirst({
3  where: { email: userEmail }, // email is a parameter, never concatenated
4});
5// Executed as: SELECT * FROM users WHERE email = $1
6
7// UNSAFE: string concatenation
8const user = await prisma.$queryRawUnsafe(
9  `SELECT * FROM users WHERE email = '${userEmail}'` // INJECTION!
10);

1// apps/api/src/modules/auth/auth.injection.spec.ts
2const SQL_INJECTION_PAYLOADS = [
3  "' OR '1'='1",
4  "' OR '1'='1'--",
5  "'; DROP TABLE users;--",
6  "1 OR 1=1",
7  "1; SELECT sleep(5)--",
8  "' UNION SELECT 1,2,3--",
9  "admin'--",
10];
11
12describe('SQL Injection Prevention', () => {
13  SQL_INJECTION_PAYLOADS.forEach((payload) => {
14    it(`rejects payload: ${payload.substring(0, 30)}`, async () => {
15      const res = await request(app.getHttpServer())
16        .post('/auth/login')
17        .send({ email: payload, password: 'anypass' });
18
19      // MUST NOT return 500 — 500 means DB processed malformed query
20      expect(res.status).not.toBe(500);
21      // Must be 400 (validation) or 401 (invalid credentials)
22      expect([400, 401]).toContain(res.status);
23      // Must not expose database error messages
24      expect(res.body.message).not.toMatch(/syntax error|SQL|query|column|table/i);
25    });
26  });
27});

1// SAFE: tagged template literal — values become parameters
2const results = await prisma.$queryRaw`
3  SELECT id, title FROM courses
4  WHERE category = ${category}
5  ORDER BY rating DESC LIMIT ${limit}
6`;
7
8// UNSAFE: string interpolation
9const results = await prisma.$queryRawUnsafe(
10  `SELECT * FROM courses WHERE category = '${category}'` // INJECTION!
11);
12
13// Test to verify parameterization:
14describe('Prisma injection resistance', () => {
15  it('injection payload in category returns empty results, not DB error', async () => {
16    const injectionPayload = "' UNION SELECT id,email,password_hash,4,5 FROM users--";
17    await expect(
18      courseService.findByCategory(injectionPayload)
19    ).resolves.toEqual([]); // empty — no courses with that category
20    // If this throws or returns user data, the query is NOT parameterized
21  });
22});

1# In postgresql.conf:
2# log_min_duration_statement = 0
3# Then check logs: WHERE category = $1  (safe) vs WHERE category = 'typescript' (unsafe)

1# Install sqlmap
2pip install sqlmap
3
4# Scan a GET parameter
5sqlmap -u "https://staging.api.example.com/courses?category=typescript" \
6  --level=3 --risk=2 --dbms=postgresql --batch \
7  --output-dir=reports/sqlmap/
8
9# Authenticated scan with Bearer token
10sqlmap -u "https://staging.api.example.com/search" \
11  --method=POST \
12  --data='{"q":"typescript"}' \
13  --headers="Content-Type: application/json" \
14  --headers="Authorization: Bearer YOUR_JWT_TOKEN" \
15  --level=3 --risk=2 --batch
16
17# Expected on a safe API:
18# [INFO] GET parameter 'category' does not appear to be injectable
19# [CRITICAL] all tested parameters do not appear to be injectable
20
21# If vulnerable:
22# [CRITICAL] parameter 'id' is injectable (SQLi type: boolean-based blind)

1// DANGEROUS: search middleware building raw query
2app.use('/api/search', (req, res, next) => {
3  const query = `SELECT * FROM courses WHERE title ILIKE '%${req.query.q}%'`; // INJECTION
4  db.query(query).then(results => res.json(results));
5});
6
7// SAFE: parameterized with pg library
8app.use('/api/search', async (req, res, next) => {
9  const q = String(req.query.q || '').slice(0, 200); // length limit
10  const results = await db.query(
11    'SELECT * FROM courses WHERE title ILIKE $1',
12    [`%${q}%`]  // % is safe in the value, not in the query structure
13  );
14  res.json(results.rows);
15});
16
17// Best practices:
18// 1. Never concatenate user data into SQL
19// 2. Validate all query parameters with Zod before constructing queries
20// 3. DB user should only have SELECT/INSERT/UPDATE/DELETE, never DROP/ALTER
21// 4. Never expose raw database errors to clients