TypeScript High

SQL Injection in User Input Handler

Unsanitized user input directly interpolated into raw SQL query — classic injection vector.

Repository payments-service

PR Title Add user search by email

Author @mtran

Files changed 1

Diff size +18 / -6 lines

Changed file

src/handlers/user-search.ts

import { Pool } from 'pg';
import { getUserPool } from '../db/pool';
export async function searchUsersByEmail(email: string) {
  const pool: Pool = getUserPool();
  const query = `SELECT id, email, name FROM users WHERE email = '${email}'`;
  const result = await pool.query(query);
  return result.rows;
  // TODO: search by name too
}
import { DatabaseError, ValidationError } from '../errors';
export async function searchUsersByEmail(email: string) {
  if (!email || typeof email !== 'string') {
    throw new ValidationError('email is required');
  }
  const pool: Pool = getUserPool();
  const query = 'SELECT id, email, name FROM users WHERE email = $1';
  const result = await pool.query(query, [email]);
  return result.rows;
}

Codio Review

Summary

This PR introduces a user search function for the payments dashboard. The core logic is correct — it's fetching users by email address. However, the original implementation contains a SQL injection vulnerability that allows an attacker to execute arbitrary SQL against the database. This is a high-severity finding that must not reach production.

Findings

Finding #1 — SQL Injection (Critical) High

The deleted code constructs a SQL query using string interpolation:

const query = `SELECT id, email, name FROM users WHERE email = '${email}'`;

The email parameter flows directly from the function argument into the query string without any sanitization or parameterization. If a caller passes ' OR 1=1 -- as the email, the query becomes:

SELECT id, email, name FROM users WHERE email = '' OR 1=1 --'

This returns all rows. In a real attack, an adversary can enumerate tables, dump credentials, modify records, or in some configurations execute shell commands depending on the Postgres configuration and the application's connection privileges.

The fix uses parameterized queries — $1 placeholders — which separate the query structure from the user data. PostgreSQL handles the escaping internally; no user input ever touches the query structure.

Finding #2 — Missing Input Validation Medium

The original function accepts any value for email — including null, undefined, an empty string, or a non-email-format value. A defensive function should validate the input type and format before passing it to the database layer. The added guard checks for null/undefined and throws a ValidationError, which callers can catch and turn into an HTTP 400 response.

Note: the validation alone does not prevent injection — parameterization does. Validation is defense-in-depth, not the primary fix.

Finding #3 — TODO Left in Code Medium

The comment // TODO: search by name too should be tracked in the project management system (Linear, Jira, GitHub Issues), not left as an inline comment. Inline TODOs are frequently ignored and never closed. This is a minor finding, but the comment is now orphaned since the function was refactored.

Recommendation

Approve the PR as written — the parameterized query fix is correct and the validation guard is a meaningful improvement. However, before merging, verify that:

No other functions in the codebase use raw SQL string interpolation with user-supplied values. Run a search for template literals with `SELECT / `INSERT / `UPDATE / `DELETE and ${ in the same file. If found, apply the same parameterization fix.
The ValidationError class is defined in src/errors.ts and exported. If it doesn't exist yet, create it before merging.
Consider adding an integration test that passes a known injection payload and asserts the function returns an empty result set (not all users).
Remove the orphaned // TODO comment after creating a GitHub Issue to track the name-search feature.

Suggested Fix

src/handlers/user-search.ts

import { Pool } from 'pg';
import { getUserPool } from '../db/pool';
import { ValidationError } from '../errors';

export async function searchUsersByEmail(email: string): Promise<User[]> {
  if (!email || typeof email !== 'string') {
    throw new ValidationError('email is required');
  }

  const pool: Pool = getUserPool();
  // Parameterized query — email is never interpolated into the SQL structure
  const query = 'SELECT id, email, name FROM users WHERE email = $1';
  const result = await pool.query(query, [email]);

  return result.rows;
}

Why Codio flagged this

Codio statically analyzes every changed file for known vulnerability patterns before it even runs your tests. SQL injection via string interpolation is one of the most well-documented and exploitable classes of web vulnerabilities — OWASP has ranked it in the top 10 for over a decade. Codio catches it at PR time, before it reaches production, before an attacker finds it. The parameterized query fix is the industry-standard solution and Codio provides the exact replacement code.

Get Codio reviewing your PRs

Ship reviews like this on every pull request. No reminders. No missed PRs.

Next → Missing Test Coverage for Payment Edge Case