How to fix SQL injection

What an attacker can do

The concrete impact of leaving SQL Injection unpatched.

How to fix SQL Injection

6 ordered steps. Apply them in order — each builds on the previous.

1. 1

   Use parameterized queries (prepared statements) everywhere

   The textbook fix. Send SQL template and values separately — the database treats values as data, never as code, regardless of content.

   Every modern database driver supports parameterization. Replace every string concatenation in SQL with parameter placeholders. The performance difference is negligible (often slightly faster due to query plan caching). The security difference is total — parameterized queries cannot be SQL injected, period.

   Node.js / pgStep 1

   // VULNERABLE
   const result = await db.query(
     `SELECT * FROM users WHERE email = '${req.body.email}'`
   );
   
   // SECURE — parameterized
   const result = await db.query(
     'SELECT * FROM users WHERE email = $1',
     [req.body.email]
   );

2. 2

   Use a query builder or ORM — but watch the escape hatches

   Knex, Prisma, SQLAlchemy, Sequelize, Active Record, GORM — all parameterize by default. But every ORM has 'raw' methods that opt out.

   ORMs are SQLi-safe in their default mode. Where they leak: .raw('SELECT...' + input), .where("name = '" + input + "'"), Prisma's $queryRawUnsafe, SQLAlchemy's text() with string interpolation. These are escape hatches that ORM authors warn about — but developers reach for them when something is hard to express idiomatically. Audit every usage.

   Python / SQLAlchemyStep 2

   from sqlalchemy import text
   
   # VULNERABLE — string interpolation in raw SQL
   result = db.execute(text(f"SELECT * FROM users WHERE email = '{email}'"))
   
   # SECURE — bound parameters in raw SQL
   result = db.execute(
       text("SELECT * FROM users WHERE email = :email"),
       {"email": email},
   )
   
   # SAFE — ORM idiomatic
   result = db.query(User).filter(User.email == email).all()

3. 3

   Allow-list dynamic identifiers (table, column, ORDER BY)

   Parameterization only covers VALUES. If you need dynamic table/column names or ORDER BY direction, use an allow-list — never user input directly.

   This is the #1 case where parameterization isn't enough. Common pattern: a table that supports ?sortBy=email&direction=desc. Developers concatenate sortBy and direction into the SQL ('ORDER BY ' + sortBy + ' ' + direction'). Even with parameterization for everything else, this single concatenation is full SQLi. Fix: define an allow-list of permitted column names and directions; reject anything else.

   TypeScriptStep 3

   const SORTABLE = new Set(['email', 'name', 'created_at']);
   const DIRECTIONS = new Set(['asc', 'desc']);
   
   function buildOrderBy(sortBy: string, direction: string): string {
     if (!SORTABLE.has(sortBy)) sortBy = 'created_at';
     if (!DIRECTIONS.has(direction.toLowerCase())) direction = 'desc';
     return `ORDER BY ${sortBy} ${direction}`; // safe — both checked
   }

4. 4

   Apply principle of least privilege at the database level

   The database user your app connects as should have ONLY the privileges it needs. No DROP, no superuser, no filesystem access.

   Defense in depth — even if SQLi gets through, what can the attacker do? If your app's DB user can only SELECT/INSERT/UPDATE on application tables, SQLi stays contained to those tables. They can't drop tables, can't read pg_authid, can't COPY FROM PROGRAM. Run separate users for migrations (more privileges) and runtime (minimal privileges). Disable xp_cmdshell on MSSQL. Don't run queries as root.

   PostgreSQLStep 4

   -- App runtime user — minimal privileges
   CREATE ROLE app_runtime LOGIN PASSWORD '...';
   GRANT CONNECT ON DATABASE myapp TO app_runtime;
   GRANT USAGE ON SCHEMA public TO app_runtime;
   GRANT SELECT, INSERT, UPDATE, DELETE
     ON ALL TABLES IN SCHEMA public TO app_runtime;
   -- NO superuser, NO CREATE, NO DROP, NO COPY
   
   -- Separate migration user with elevated privileges
   CREATE ROLE app_migrate LOGIN PASSWORD '...';
   GRANT ALL PRIVILEGES ON DATABASE myapp TO app_migrate;

5. 5

   Validate input shape before it reaches the query layer

   Defense in depth #2. If a field should be a UUID, reject anything that isn't a valid UUID before it reaches the database.

   Use Zod, Joi, Pydantic, validator.js — your stack's input validation library. Reject malformed input at the API boundary. This doesn't replace parameterization but adds a layer that catches the most common SQLi payloads before they reach the database. Single quotes, semicolons, SQL keywords are fine in a string description; not fine in a UUID field.

   TypeScript / ZodStep 5

   import { z } from 'zod';
   
   const OrderRequest = z.object({
     orderId: z.string().uuid(),
     status: z.enum(['pending', 'shipped', 'delivered']),
     page: z.number().int().min(1).max(1000),
   });
   
   // In your handler
   const parsed = OrderRequest.safeParse(req.body);
   if (!parsed.success) return res.status(400).json(parsed.error);
   
   // Now everything is type-checked AND shape-validated before queries

6. 6

   Add unit tests with malicious payloads

   Every input that hits the database needs a test asserting that classic SQLi payloads return 400/404, not data.

   Tests are how you prevent regression. Pattern: for each endpoint, send the standard SQLi payloads (' OR '1'='1, '; DROP TABLE users--, ' UNION SELECT). Assert the response is 400 (validation rejected) or empty (parameterization treated it as a literal value). Run on every PR. Catches the day someone refactors and accidentally introduces concatenation.

   JestStep 6

   const SQLI_PAYLOADS = [
     "' OR '1'='1",
     "'; DROP TABLE users; --",
     "' UNION SELECT username, password FROM users --",
     "1' AND SLEEP(5) --",
   ];
   
   describe.each(SQLI_PAYLOADS)('SQLi payload %s', (payload) => {
     it('rejects payload in /api/users/search', async () => {
       const start = Date.now();
       const res = await api.get(`/users/search?q=${encodeURIComponent(payload)}`);
       const elapsed = Date.now() - start;
   
       // Either rejected by validation or returns 0 rows — never the user table
       expect([200, 400]).toContain(res.status);
       if (res.status === 200) expect(res.body.users || []).toHaveLength(0);
       expect(elapsed).toBeLessThan(2000); // no time-based SQLi
     });
   });