SQL Injection Prevention Implementation

Implement comprehensive SQL injection prevention for [APPLICATION_NAME] built with [FRAMEWORK/LANGUAGE] and [DATABASE].

Requirements

1. Audit Existing Code

Review and identify all SQL queries in:

• API endpoints: [ENDPOINT_1], [ENDPOINT_2], [ENDPOINT_3]
• Database operations: User authentication, data retrieval, updates, deletes
• Search functionality: Full-text search, filtering, sorting
• Report generation: Dynamic queries, aggregations
• Admin panels: Bulk operations, data exports

2. Replace Vulnerable Patterns

Convert all string concatenation to parameterized queries:

For Standard Queries

Replace:

// ❌ VULNERABLE - String concatenation
const query = `SELECT * FROM users WHERE username = '${username}' AND password = '${password}'`
const query = "SELECT * FROM products WHERE id = " + productId
const query = f"SELECT * FROM orders WHERE user_id = {user_id}"

With:

// ✅ SECURE - Parameterized queries
const query = 'SELECT * FROM users WHERE username = ? AND password = ?'
db.query(query, [username, password])

// Or using ORM
const user = await prisma.user.findFirst({
  where: { username, password }
})

For Dynamic Table/Column Names

Implement whitelist validation:

// ✅ SECURE - Whitelist approach
const allowedTables = ['users', 'products', 'orders']
const allowedColumns = ['name', 'email', 'created_at']

if (!allowedTables.includes(tableName)) {
  throw new Error('Invalid table name')
}

if (!allowedColumns.includes(sortColumn)) {
  throw new Error('Invalid column name')
}

const query = `SELECT * FROM ${tableName} ORDER BY ${sortColumn}`

3. Implement ORM/Query Builder

Migrate raw SQL to ORM for automatic protection:

Prisma (TypeScript/Node.js):

// User authentication
const user = await prisma.user.findUnique({
  where: { email: userEmail }
})

// Complex queries with relations
const posts = await prisma.post.findMany({
  where: {
    published: true,
    author: { name: { contains: searchTerm } }
  },
  include: { author: true, comments: true }
})

SQLAlchemy (Python):

# User query
user = session.query(User).filter(User.email == user_email).first()

# Complex queries
posts = session.query(Post)\\
  .filter(Post.published == True)\\
  .filter(Post.author.has(name=search_term))\\
  .all()

Entity Framework (.NET):

// User query
var user = await context.Users
  .Where(u => u.Email == userEmail)
  .FirstOrDefaultAsync();

// Complex queries
var posts = await context.Posts
  .Where(p => p.Published && p.Author.Name.Contains(searchTerm))
  .Include(p => p.Author)
  .Include(p => p.Comments)
  .ToListAsync();

4. Secure Special Cases

LIKE Queries

// Escape wildcards in user input
const escapedSearch = searchTerm
  .replace(/%/g, '\\\\%')
  .replace(/_/g, '\\\\_')

const query = 'SELECT * FROM products WHERE name LIKE ?'
db.query(query, [`%${escapedSearch}%`])

IN Clauses

// Generate placeholders for array
const placeholders = ids.map(() => '?').join(',')
const query = `SELECT * FROM users WHERE id IN (${placeholders})`
db.query(query, ids)

// Or use ORM
const users = await prisma.user.findMany({
  where: { id: { in: ids } }
})

Dynamic Filters

// Build safe dynamic queries
const conditions = []
const params = []

if (filters.name) {
  conditions.push('name LIKE ?')
  params.push(`%${filters.name}%`)
}

if (filters.status) {
  conditions.push('status = ?')
  params.push(filters.status)
}

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

5. Input Validation Layer

Create validation middleware/functions:

// Validation schema
const userSchema = {
  email: { type: 'email', required: true },
  age: { type: 'number', min: 0, max: 150 },
  username: { type: 'string', pattern: /^[a-zA-Z0-9_]{3,20}$/ }
}

// Validation function
function validateInput(data, schema) {
  for (const [field, rules] of Object.entries(schema)) {
    if (rules.required && !data[field]) {
      throw new Error(`${field} is required`)
    }
    
    if (rules.type === 'email' && !isValidEmail(data[field])) {
      throw new Error(`${field} must be valid email`)
    }
    
    if (rules.pattern && !rules.pattern.test(data[field])) {
      throw new Error(`${field} format is invalid`)
    }
  }
}

6. Database Security Configuration

Least Privilege Access

-- Create read-only user for queries
CREATE USER 'app_reader'@'localhost' IDENTIFIED BY '[password]';
GRANT SELECT ON [database].* TO 'app_reader'@'localhost';

-- Create limited write user
CREATE USER 'app_writer'@'localhost' IDENTIFIED BY '[password]';
GRANT SELECT, INSERT, UPDATE ON [database].* TO 'app_writer'@'localhost';

-- Never use root/admin credentials in application

Connection String Configuration

// Use environment variables
const dbConfig = {
  host: process.env.DB_HOST,
  user: process.env.DB_USER, // Use limited privilege user
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME
}

7. Security Testing

Automated Testing

// Test for SQL injection vulnerabilities
describe('SQL Injection Prevention', () => {
  test('should reject malicious input in username', async () => {
    const maliciousInput = "admin' OR '1'='1"
    const result = await login(maliciousInput, 'password')
    expect(result).toBeNull()
  })
  
  test('should handle special characters safely', async () => {
    const specialChars = "O'Reilly"
    const user = await createUser({ name: specialChars })
    expect(user.name).toBe(specialChars)
  })
})

Manual Testing Checklist

• Test all input fields with ' OR '1'='1
• Test with '; DROP TABLE users; --
• Test with UNION SELECT attacks
• Test with time-based blind injection
• Test with special characters: ', ", \\, %, _
• Run SQLMap against endpoints
• Review all raw SQL queries
• Verify ORM usage is correct

8. Logging and Monitoring

// Log suspicious patterns
function detectSQLInjection(input) {
  const suspiciousPatterns = [
    /(')|(--)|(\\/\\*)|(\*\\/)/i,  // SQL comments
    /(union|select|insert|update|delete|drop|create)/i,  // SQL keywords
    /(or|and)\\s+['"]?\\d+['"]?\\s*=\\s*['"]?\\d+/i  // OR 1=1 patterns
  ]
  
  for (const pattern of suspiciousPatterns) {
    if (pattern.test(input)) {
      logger.warn('Potential SQL injection attempt detected', {
        input,
        pattern: pattern.toString(),
        timestamp: new Date(),
        ip: request.ip
      })
      return true
    }
  }
  return false
}

Implementation Checklist

• Audit all database queries in codebase
• Replace string concatenation with parameterized queries
• Implement ORM for new features
• Add input validation for all user inputs
• Whitelist table/column names for dynamic queries
• Configure least-privilege database users
• Add automated security tests
• Set up logging for suspicious patterns
• Run security scanning tools (SQLMap, SAST)
• Document secure coding practices for team

Expected Deliverables

1. Refactored code with all SQL injection vulnerabilities fixed
2. Validation layer for all user inputs
3. Test suite covering SQL injection attack vectors
4. Security documentation for the team
5. Monitoring setup for detecting attack attempts