SQL injection remains one of the most dangerous vulnerabilities. Here's how to prevent it.
The Vulnerability#
1// ❌ VULNERABLE - String concatenation
2const query = `SELECT * FROM users WHERE email = '${email}'`;
3
4// Attack input: ' OR '1'='1
5// Resulting query: SELECT * FROM users WHERE email = '' OR '1'='1'
6// Returns ALL users!
7
8// Attack input: '; DROP TABLE users; --
9// Resulting query: SELECT * FROM users WHERE email = ''; DROP TABLE users; --'
10// Deletes entire table!Parameterized Queries#
1// ✅ SAFE - Parameterized query (pg)
2const result = await pool.query(
3 'SELECT * FROM users WHERE email = $1',
4 [email]
5);
6
7// ✅ SAFE - Named parameters (mysql2)
8const [rows] = await connection.execute(
9 'SELECT * FROM users WHERE email = :email',
10 { email }
11);
12
13// ✅ SAFE - Prepared statements
14const stmt = db.prepare('SELECT * FROM users WHERE email = ?');
15const user = stmt.get(email);ORM Protection#
1// Prisma - safe by default
2const user = await prisma.user.findUnique({
3 where: { email: userInput },
4});
5
6// Drizzle - safe by default
7const users = await db
8 .select()
9 .from(usersTable)
10 .where(eq(usersTable.email, userInput));
11
12// TypeORM - safe with parameters
13const user = await userRepository.findOne({
14 where: { email: userInput },
15});Dangerous ORM Patterns#
1// ❌ VULNERABLE - Raw queries without parameters
2const users = await prisma.$queryRawUnsafe(
3 `SELECT * FROM users WHERE name LIKE '%${search}%'`
4);
5
6// ✅ SAFE - Raw queries with parameters
7const users = await prisma.$queryRaw`
8 SELECT * FROM users WHERE name LIKE ${`%${search}%`}
9`;
10
11// ❌ VULNERABLE - Dynamic column names
12const column = req.query.sortBy;
13const query = `SELECT * FROM users ORDER BY ${column}`;
14
15// ✅ SAFE - Whitelist allowed columns
16const allowedColumns = ['name', 'email', 'created_at'];
17const column = allowedColumns.includes(req.query.sortBy)
18 ? req.query.sortBy
19 : 'created_at';Input Validation#
1import { z } from 'zod';
2
3// Validate input types
4const searchSchema = z.object({
5 email: z.string().email(),
6 id: z.string().uuid(),
7 limit: z.number().int().min(1).max(100),
8});
9
10// Sanitize for LIKE queries
11function sanitizeLikeInput(input: string): string {
12 return input.replace(/[%_]/g, '\\$&');
13}
14
15const search = sanitizeLikeInput(userInput);
16const query = `SELECT * FROM users WHERE name LIKE $1`;
17const result = await pool.query(query, [`%${search}%`]);Dynamic Queries Safely#
1// Building queries with conditions
2interface SearchParams {
3 email?: string;
4 name?: string;
5 role?: string;
6}
7
8function buildUserQuery(params: SearchParams) {
9 const conditions: string[] = [];
10 const values: unknown[] = [];
11 let paramIndex = 1;
12
13 if (params.email) {
14 conditions.push(`email = $${paramIndex++}`);
15 values.push(params.email);
16 }
17
18 if (params.name) {
19 conditions.push(`name ILIKE $${paramIndex++}`);
20 values.push(`%${params.name}%`);
21 }
22
23 if (params.role) {
24 // Whitelist allowed values
25 const allowedRoles = ['admin', 'user', 'guest'];
26 if (allowedRoles.includes(params.role)) {
27 conditions.push(`role = $${paramIndex++}`);
28 values.push(params.role);
29 }
30 }
31
32 const whereClause = conditions.length > 0
33 ? `WHERE ${conditions.join(' AND ')}`
34 : '';
35
36 return {
37 query: `SELECT * FROM users ${whereClause}`,
38 values,
39 };
40}Stored Procedures#
1-- Create a safe stored procedure
2CREATE OR REPLACE FUNCTION get_user_by_email(user_email TEXT)
3RETURNS TABLE(id INT, email TEXT, name TEXT) AS $$
4BEGIN
5 RETURN QUERY SELECT u.id, u.email, u.name
6 FROM users u
7 WHERE u.email = user_email;
8END;
9$$ LANGUAGE plpgsql;// Call stored procedure
const result = await pool.query(
'SELECT * FROM get_user_by_email($1)',
[email]
);Database Permissions#
1-- Principle of least privilege
2CREATE USER app_user WITH PASSWORD 'secure_password';
3
4-- Only grant necessary permissions
5GRANT SELECT, INSERT, UPDATE ON users TO app_user;
6GRANT SELECT ON products TO app_user;
7
8-- Never grant DROP, DELETE ALL, or admin rights
9-- REVOKE DELETE ON users FROM app_user;Testing for SQL Injection#
1// Test common injection patterns
2const injectionPatterns = [
3 "' OR '1'='1",
4 "'; DROP TABLE users; --",
5 "1; SELECT * FROM users",
6 "' UNION SELECT * FROM users --",
7 "admin'--",
8];
9
10describe('SQL Injection Prevention', () => {
11 for (const pattern of injectionPatterns) {
12 it(`should safely handle: ${pattern}`, async () => {
13 // Should not throw or return unexpected results
14 const result = await searchUsers(pattern);
15 expect(result).toEqual([]); // No matches, not all records
16 });
17 }
18});Always use parameterized queries, validate input, and follow the principle of least privilege.