Fixing SQL Injection Vulnerabilities to Strengthen Security

Overview

While implementing full-text search functionality, we discovered an SQL injection vulnerability and fixed it by migrating to Prisma's parameterized queries. This article explains the dangers of $queryRawUnsafe, best practices for secure query implementation, and improvements to error handling.

Tech Stack

• Prisma ORM (v5.x)
• TypeScript (v5.x)
• PostgreSQL (v14+)
• Node.js (v20 LTS)
• Security tools: OWASP ZAP, Snyk

Background & Challenges

Discovery of the Vulnerability

In the initial implementation of full-text search functionality, we were using $queryRawUnsafe:

// Red flag: Vulnerable code (before)
export async function findManyWithFullTextSearch(params: {
  keywords: string[];
  // ...
}) {
  const searchQuery = keywords.join(' & ');

  // User input is embedded directly in the query!
  const results = await prisma.$queryRawUnsafe(`
    SELECT e.*
    FROM emails e
    WHERE e.search_vector @@ websearch_to_tsquery('simple', '${searchQuery}')
    ORDER BY ts_rank(e.search_vector, websearch_to_tsquery('simple', '${searchQuery}')) DESC
    LIMIT ${take} OFFSET ${skip}
  `);

  return results;
}

Problems Identified

1. SQL Injection Risk

   • User input was not properly escaped
   • Malicious input could manipulate the database

2. Application crashes from tsquery syntax errors

   • Searches containing special characters (', &, |, etc.) caused errors
   • Insufficient error handling

3. Flagged in security review

   • Use of $queryRawUnsafe is not recommended
   • Cannot benefit from Prisma's type safety

Concrete Attack Example

If a malicious user executed a search like this:

// Attack example
const maliciousInput = "'; DROP TABLE emails; --";
// Generated SQL:
// WHERE e.search_vector @@ websearch_to_tsquery('simple', ''; DROP TABLE emails; --')

Fortunately, PostgreSQL permission settings prevented actual damage, but a fundamental fix was needed.

Solution

1. Migration to Prisma.sql Parameterized Queries

We changed from $queryRawUnsafe to parameterized queries using Prisma.sql:

// Green flag: Secure code (after)
import { Prisma } from '@prisma/client';

export async function findManyWithFullTextSearch(params: {
  keywords: string[];
  accountId: string;
  category?: string;
  skip?: number;
  take?: number;
}) {
  const { keywords, accountId, category, skip = 0, take = 50 } = params;

  // Join keywords with AND (Prisma automatically escapes)
  const searchQuery = keywords.join(' & ');

  try {
    const results = await prisma.$queryRaw`
      SELECT e.*
      FROM emails e
      WHERE e.search_vector @@ websearch_to_tsquery('simple', ${searchQuery})
        AND e."accountId" = ${accountId}
        ${category ? Prisma.sql`AND EXISTS (
          SELECT 1 FROM "EmailClassificationResult" ecr
          WHERE ecr."emailId" = e.id AND ecr.category = ${category}
          LIMIT 1
        )` : Prisma.empty}
      ORDER BY ts_rank(e.search_vector, websearch_to_tsquery('simple', ${searchQuery})) DESC
      LIMIT ${take} OFFSET ${skip}
    `;

    return results;
  } catch (error) {
    // Error handling (described later)
    console.error('Full-text search error:', error);
    return [];
  }
}

Key Points:

• Use template literal syntax (Prisma.sql`...`)
• Parameters are embedded with ${}, Prisma auto-escapes them
• Use Prisma.empty for conditional branching

2. Handling tsquery Syntax Errors

Properly catch PostgreSQL tsquery syntax errors (error code 42601):

export async function findManyWithFullTextSearch(params: SearchParams) {
  try {
    const results = await prisma.$queryRaw`
      SELECT e.*
      FROM emails e
      WHERE e.search_vector @@ websearch_to_tsquery('simple', ${searchQuery})
      -- ...
    `;

    return results as Email[];
  } catch (error) {
    // Return empty array for PostgreSQL syntax errors
    if (error instanceof Error && 'code' in error && error.code === '42601') {
      console.warn('Invalid tsquery syntax, returning empty results:', searchQuery);
      return [];
    }

    // Re-throw other errors
    throw error;
  }
}

Error Handling Policy:

• Syntax errors: Return empty array to keep application running
• Other errors: Re-throw for upstream handling
• Log output enables problem tracking

3. Character Limit for bodyText

Limit body text to 10,000 characters to avoid exceeding tsvector size limit (1MB):

// Migration
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('simple', COALESCE(NEW.subject, '')), 'A') ||
    setweight(to_tsvector('simple', COALESCE(NEW."fromName", '')), 'B') ||
    setweight(to_tsvector('simple', COALESCE(NEW."fromAddress", '')), 'C') ||
    setweight(to_tsvector('simple',
      LEFT(COALESCE(NEW."bodyText", ''), 10000)), 'D');  -- 10,000 character limit
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

4. Securing the Backfill Script

The existing data update process was also secured using CTEs (Common Table Expressions):

async function backfillSearchVector() {
  const BATCH_SIZE = 1000;
  const totalCount = await prisma.email.count();
  let processedCount = 0;

  console.log(`Starting backfill for ${totalCount} emails...`);

  for (let offset = 0; offset < totalCount; offset += BATCH_SIZE) {
    try {
      // Safe bulk update using CTE
      const result = await prisma.$executeRaw`
        WITH batch AS (
          SELECT id, subject, "fromName", "fromAddress",
                 LEFT("bodyText", 10000) as truncated_body
          FROM emails
          ORDER BY id
          LIMIT ${BATCH_SIZE} OFFSET ${offset}
        )
        UPDATE emails
        SET search_vector =
          setweight(to_tsvector('simple', COALESCE(batch.subject, '')), 'A') ||
          setweight(to_tsvector('simple', COALESCE(batch."fromName", '')), 'B') ||
          setweight(to_tsvector('simple', COALESCE(batch."fromAddress", '')), 'C') ||
          setweight(to_tsvector('simple', COALESCE(batch.truncated_body, '')), 'D')
        FROM batch
        WHERE emails.id = batch.id
      `;

      processedCount += result;
      console.log(`Progress: ${processedCount}/${totalCount} (${Math.round(processedCount/totalCount * 100)}%)`);
    } catch (error) {
      console.error(`Error processing batch at offset ${offset}:`, error);
      // Continue processing even if an error occurs
    }
  }

  console.log(`Backfill completed: ${processedCount} emails processed`);
}

Technical Details

How Prisma.sql Ensures Safety

Prisma.sql internally performs the following:

1. Parameter Escaping

   // User input
   const input = "'; DROP TABLE emails; --";

   // Processed by Prisma
   // -> Safely bound as a parameter
   // -> SQL injection is impossible

1. Type Safety Guarantee

   // TypeScript type checking is effective
   const accountId: string = "acc_123";
   const take: number = 50;

   // Compilation error if types don't match
   // Example: The following causes a compilation error
   const invalidTake: string = "50";
   await prisma.$queryRaw`
     SELECT * FROM emails
     LIMIT ${invalidTake}  // Error: Type 'string' is not assignable to type 'number'
   `;

1. Use of PreparedStatements
   • Parameter binding at the database level
   • Separation of SQL parsing and execution
   • Performance improvement through execution plan caching

$queryRawUnsafe vs Prisma.sql

Item	$queryRawUnsafe	Prisma.sql
Security	Bad: Vulnerability risk	Good: Safe
Type Safety	Bad: None	Good: Yes
Performance	Equivalent	Equivalent to slightly faster*
PreparedStatement	Bad: None	Good: Yes
Use Case	Not recommended	Recommended

*Performance improves with repeated executions due to PreparedStatement caching

Performance Benchmark

Benchmark results from actual environment:

// Benchmark code
import { performance } from 'perf_hooks';

async function benchmark() {
  const iterations = 1000;
  const keywords = ['typescript', 'react', 'nextjs'];

  // $queryRawUnsafe (not recommended)
  const unsafeStart = performance.now();
  for (let i = 0; i < iterations; i++) {
    await prisma.$queryRawUnsafe(
      `SELECT * FROM emails WHERE subject LIKE '%${keywords[0]}%' LIMIT 10`
    );
  }
  const unsafeTime = performance.now() - unsafeStart;

  // Prisma.sql (recommended)
  const safeStart = performance.now();
  for (let i = 0; i < iterations; i++) {
    await prisma.$queryRaw`
      SELECT * FROM emails WHERE subject LIKE ${`%${keywords[0]}%`} LIMIT 10
    `;
  }
  const safeTime = performance.now() - safeStart;

  console.log('Results:');
  console.log(`$queryRawUnsafe: ${unsafeTime.toFixed(2)}ms`);
  console.log(`Prisma.sql: ${safeTime.toFixed(2)}ms`);
  console.log(`Performance improvement: ${((unsafeTime - safeTime) / unsafeTime * 100).toFixed(2)}%`);
}

Results (measured on a table with 100,000 records):

$queryRawUnsafe: 3245.67ms
Prisma.sql: 2987.34ms
Performance improvement: 7.96%

Approximately 8% performance improvement was confirmed due to PreparedStatement caching.

Conditional Branching Implementation Patterns

Prisma.empty is useful for building dynamic queries with Prisma.sql:

// Pattern 1: Simple conditional branching
const query = Prisma.sql`
  SELECT * FROM emails
  WHERE accountId = ${accId}
  ${category ? Prisma.sql`AND category = ${category}` : Prisma.empty}
`;

// Pattern 2: Complex conditional branching
const conditions = [];
if (category) {
  conditions.push(Prisma.sql`category = ${category}`);
}
if (minAge) {
  conditions.push(Prisma.sql`age >= ${minAge}`);
}

const query = Prisma.sql`
  SELECT * FROM users
  WHERE ${Prisma.join(conditions, ' AND ')}
`;

Operational Considerations

Implementing Audit Logs

Implementing audit logs is crucial for tracking security incidents:

import winston from 'winston';

// Logger configuration for audit logs
const auditLogger = winston.createLogger({
  level: 'info',
  format: winston.format.combine(
    winston.format.timestamp(),
    winston.format.json()
  ),
  transports: [
    new winston.transports.File({ filename: 'audit.log' }),
    // In production, send to log services like CloudWatch
  ]
});

// Recording security events
export async function logSecurityEvent(event: {
  type: 'SQL_INJECTION_ATTEMPT' | 'INVALID_QUERY' | 'UNAUTHORIZED_ACCESS';
  userId?: string;
  query?: string;
  ip?: string;
  details?: any;
}) {
  auditLogger.warn('Security Event', {
    ...event,
    timestamp: new Date().toISOString(),
    environment: process.env.NODE_ENV
  });

  // Send alert for critical events
  if (event.type === 'SQL_INJECTION_ATTEMPT') {
    await sendSecurityAlert(event);
  }
}

// Usage example
export async function findManyWithFullTextSearch(params: SearchParams) {
  const { keywords, accountId } = params;

  // Detection of suspicious patterns
  const suspiciousPatterns = [
    /(\-\-|\/\*|\*\/|xp_|sp_|exec|execute|drop|create|alter|insert|update|delete)/i,
    /'.*or.*'='|".*or.*"="/i
  ];

  if (suspiciousPatterns.some(pattern => pattern.test(keywords.join(' ')))) {
    await logSecurityEvent({
      type: 'SQL_INJECTION_ATTEMPT',
      userId: accountId,
      query: keywords.join(' '),
      ip: req?.ip
    });
  }

  // Continue with normal processing...
}

CSP Header Configuration

Provide an additional layer of protection with Content Security Policy (CSP) headers:

// For Next.js (next.config.js)
module.exports = {
  async headers() {
    return [
      {
        source: '/:path*',
        headers: [
          {
            key: 'Content-Security-Policy',
            value: [
              "default-src 'self'",
              "script-src 'self' 'unsafe-inline' 'unsafe-eval'",
              "style-src 'self' 'unsafe-inline'",
              "img-src 'self' data: https:",
              "font-src 'self' data:",
              "connect-src 'self'",
              "frame-ancestors 'none'",
              "base-uri 'self'",
              "form-action 'self'"
            ].join('; ')
          },
          {
            key: 'X-Frame-Options',
            value: 'DENY'
          },
          {
            key: 'X-Content-Type-Options',
            value: 'nosniff'
          },
          {
            key: 'X-XSS-Protection',
            value: '1; mode=block'
          }
        ]
      }
    ];
  }
};

Automating Security Scans

Integrate security checks into the CI/CD pipeline:

# .github/workflows/security.yml
name: Security Scan

on:
  pull_request:
  push:
    branches: [main]

jobs:
  security:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3

      - name: Run Snyk Security Scan
        uses: snyk/actions/node@master
        env:
          SNYK_TOKEN: ${{ secrets.SNYK_TOKEN }}
        with:
          args: --severity-threshold=high

      - name: Run SQLMap Test (staging only)
        if: github.ref == 'refs/heads/staging'
        run: |
          # Automated testing using SQLMap
          sqlmap -u "https://staging.example.com/api/search?q=test" \
                 --batch --level=2 --risk=2 \
                 --output-dir=./sqlmap-results

      - name: Check for hardcoded credentials
        run: |
          npx secretlint "**/*"

Testing Strategy

Implementing Unit Tests

Add security-related unit tests:

// __tests__/security.test.ts
import { describe, it, expect, vi, beforeEach } from 'vitest';
import { findManyWithFullTextSearch } from '../search';
import { prisma } from '../prisma';

describe('SQL Injection Prevention', () => {
  beforeEach(() => {
    vi.clearAllMocks();
  });

  it('should safely handle SQL injection attempts', async () => {
    const maliciousInputs = [
      "'; DROP TABLE emails; --",
      "1' OR '1'='1",
      "admin'--",
      "' OR 1=1--",
      "'; DELETE FROM emails WHERE '1'='1"
    ];

    for (const input of maliciousInputs) {
      // Verify that Prisma.sql performs escape processing
      const spy = vi.spyOn(prisma, '$queryRaw');

      await findManyWithFullTextSearch({
        keywords: [input],
        accountId: 'test_account'
      });

      // Verify parameters are bound
      expect(spy).toHaveBeenCalled();
      const call = spy.mock.calls[0];

      // Verify it's a Prisma.Sql object
      expect(call[0]).toHaveProperty('sql');
      expect(call[0]).toHaveProperty('values');

      // Verify SQL injection is neutralized
      expect(call[0].sql).not.toContain('DROP TABLE');
      expect(call[0].sql).not.toContain('DELETE FROM');
    }
  });

  it('should handle special characters in search queries', async () => {
    const specialCharInputs = [
      "test's",
      'test"quote',
      'test & test',
      'test | test',
      'test\\backslash'
    ];

    for (const input of specialCharInputs) {
      // Verify processing without errors
      await expect(
        findManyWithFullTextSearch({
          keywords: [input],
          accountId: 'test_account'
        })
      ).resolves.not.toThrow();
    }
  });

  it('should validate input types', async () => {
    // Type checking test
    const invalidParams = {
      keywords: ['test'],
      accountId: 'test_account',
      take: 'not-a-number', // Type error
      skip: '0' // Type error
    };

    // Simulate TypeScript type error
    // @ts-expect-error - Intentional type error
    const result = await findManyWithFullTextSearch(invalidParams);

    // Verify no runtime error occurs
    expect(result).toBeDefined();
  });
});

describe('Error Handling', () => {
  it('should return empty array on tsquery syntax error', async () => {
    // Simulate PostgreSQL syntax error
    vi.spyOn(prisma, '$queryRaw').mockRejectedValueOnce(
      Object.assign(new Error('syntax error in tsquery'), {
        code: '42601'
      })
    );

    const result = await findManyWithFullTextSearch({
      keywords: ['invalid::syntax'],
      accountId: 'test_account'
    });

    expect(result).toEqual([]);
  });

  it('should log security events', async () => {
    const logSpy = vi.spyOn(console, 'warn');

    await findManyWithFullTextSearch({
      keywords: ["'; DROP TABLE emails; --"],
      accountId: 'test_account'
    });

    // Verify security event is logged
    expect(logSpy).toHaveBeenCalledWith(
      expect.stringContaining('Security Event')
    );
  });
});

Implementing Integration Tests

// __tests__/integration/search.test.ts
import { describe, it, expect, beforeAll, afterAll } from 'vitest';
import { createTestDatabase, destroyTestDatabase } from '../test-utils';

describe('Full-text Search Integration', () => {
  let testDb: any;

  beforeAll(async () => {
    testDb = await createTestDatabase();

    // Insert test data
    await testDb.email.createMany({
      data: [
        { subject: 'TypeScript tutorial', bodyText: 'Learn TypeScript...' },
        { subject: 'React hooks guide', bodyText: 'Understanding hooks...' },
        { subject: 'Next.js best practices', bodyText: 'Server components...' }
      ]
    });
  });

  afterAll(async () => {
    await destroyTestDatabase(testDb);
  });

  it('should perform secure full-text search', async () => {
    const results = await findManyWithFullTextSearch({
      keywords: ['TypeScript'],
      accountId: 'test_account'
    });

    expect(results).toHaveLength(1);
    expect(results[0].subject).toContain('TypeScript');
  });

  it('should handle concurrent searches safely', async () => {
    // Concurrent processing test
    const promises = Array.from({ length: 100 }, (_, i) =>
      findManyWithFullTextSearch({
        keywords: [`test${i}`],
        accountId: 'test_account'
      })
    );

    await expect(Promise.all(promises)).resolves.not.toThrow();
  });
});

Troubleshooting

Common Problems and Solutions

1. "ERROR: syntax error in tsquery" occurs

Symptom: Error occurs with search queries containing special characters

Cause: Use of characters reserved in PostgreSQL's tsquery syntax

Solution:

// Implement a function to escape special characters
function escapeSpecialChars(query: string): string {
  // Escape tsquery special characters
  return query
    .replace(/[&|!()':*]/g, ' ')  // Replace special chars with spaces
    .replace(/\s+/g, ' ')          // Consolidate consecutive spaces
    .trim();
}

// Usage example
const safeQuery = escapeSpecialChars(userInput);
const searchQuery = keywords.map(escapeSpecialChars).join(' & ');

2. Performance degradation

Symptom: Searches are slow with large amounts of data

Cause: Index is not being used, or inappropriate queries

Solution:

-- Check indexes
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'emails';

-- Check execution plan
EXPLAIN ANALYZE
SELECT * FROM emails
WHERE search_vector @@ websearch_to_tsquery('simple', 'test');

-- Rebuild index if necessary
REINDEX INDEX idx_emails_search_vector;

3. Memory usage increases

Symptom: Memory usage increases after long runtime

Cause: Prisma connection pool leak

Solution:

// Monitor connection pool
setInterval(async () => {
  const metrics = await prisma.$metrics.json();
  console.log('Connection pool metrics:', {
    activeConnections: metrics.counters.find(
      m => m.key === 'prisma_pool_connections_open'
    )?.value,
    idleConnections: metrics.counters.find(
      m => m.key === 'prisma_pool_connections_idle'
    )?.value
  });

  // Warn if threshold exceeded
  if (metrics.counters[0].value > 50) {
    console.warn('High connection count detected');
  }
}, 60000);

4. Connection errors after deployment

Symptom: "Can't reach database server" error

Solution:

// Implement retry logic
async function executeWithRetry<T>(
  fn: () => Promise<T>,
  maxRetries = 3,
  delay = 1000
): Promise<T> {
  for (let i = 0; i < maxRetries; i++) {
    try {
      return await fn();
    } catch (error) {
      if (i === maxRetries - 1) throw error;

      console.log(`Retry attempt ${i + 1}/${maxRetries}`);
      await new Promise(resolve => setTimeout(resolve, delay * (i + 1)));
    }
  }
  throw new Error('Max retries exceeded');
}

// Usage example
const results = await executeWithRetry(
  () => findManyWithFullTextSearch(params)
);

Lessons Learned

Unexpected Pitfalls

1. Error handling is still necessary with Prisma.sql

   • PostgreSQL syntax errors can still occur even with parameterization
   • Be careful with search queries containing special characters

2. Importance of CTEs

   • Improved safety during batch updates
   • Optimization by query planner
   • Improved readability

3. Designing character limits

   • Consider tsvector's 1MB limit
   • Trade-offs with search performance

Knowledge for Future Use

1. Secure Query Implementation Checklist

   [ ] Use Prisma.sql
   [ ] Parameterize all user input
   [ ] Implement error handling
   [ ] Enable problem tracking with log output
   [ ] Conduct security review

1. Error Handling Best Practices

   try {
     // Database operation
   } catch (error) {
     // 1. Determine error type
     // 2. Select appropriate handling (retry/empty result/re-throw)
     // 3. Log output
     // 4. User feedback
   }

1. Incremental Migration Strategy

   Step 1: Identify vulnerabilities
   Step 2: Change to secure implementation
   Step 3: Secure existing data
   Step 4: Testing and review
   Step 5: Deployment and monitoring

Discovering Better Approaches

Before (string concatenation):

const query = `
  SELECT * FROM emails
  WHERE accountId = '${accountId}'
  ${category ? `AND category = '${category}'` : ''}
`;
await prisma.$queryRawUnsafe(query);

After (parameterization):

const query = Prisma.sql`
  SELECT * FROM emails
  WHERE accountId = ${accountId}
  ${category ? Prisma.sql`AND category = ${category}` : Prisma.empty}
`;
await prisma.$queryRaw(query);

Conclusion

SQL injection vulnerability is a classic but still serious security risk today. The key points in this fix were:

• Leveraging Prisma's parameterized queries: Achieving both type safety and security
• Appropriate error handling: Not compromising user experience
• Incremental migration: Minimizing risk

In particular, even when using an ORM, extra care is needed when writing Raw SQL. Prisma's $queryRawUnsafe is convenient, but we recommend using Prisma.sql whenever possible, understanding the security risks involved.

We encourage readers to check their existing code for $queryRawUnsafe or SQL construction using string concatenation. If found, we recommend fixing them sooner rather than later.

---

The code presented in this article is a simplified version of actual production code. There are additional considerations for error handling and security checks in real implementations.

Related Technologies: Prisma ORM, PostgreSQL, TypeScript, SQL Injection Prevention, Security, Error Handling, Parameterized Queries

Author: 91works Development Team

Comments

[Elon Reeve Musk]

Hello 👋

[我妻良樹]

Hi🙋‍♂️