AI-generated database code is functionally correct and almost always insecure. It concatenates strings into SQL queries, connects as root, skips TLS, and returns entire rows including password hashes. The database is where your most sensitive data lives — and AI treats it like a public spreadsheet.

Database Security Hardening

AI-generated database code is functionally correct and almost always insecure. It concatenates strings into SQL queries, connects as root, skips TLS, and returns entire rows including password hashes. The database is where your most sensitive data lives — and AI treats it like a public spreadsheet.

This skill covers parameterized queries, connection security, row-level access control, and the patterns that prevent your database from becoming a breach headline.

Parameterized Queries in Every Language

SQL injection is the oldest vulnerability in the book. AI still generates it constantly.

JavaScript / Node.js

// VULNERABLE: AI-generated string concatenation
app.get('/api/users', async (req, res) => {
  const query = `SELECT * FROM users WHERE name = '${req.query.name}'`;
  const result = await db.query(query);
  // Attack: ?name=' OR '1'='1' --
  // Returns ALL users
});

// SAFE: Parameterized query
app.get('/api/users', async (req, res) => {
  const result = await db.query(
    'SELECT id, name, email FROM users WHERE name = $1',
    [req.query.name]
  );
  res.json(result.rows);
});

Python

# VULNERABLE
cursor.execute(f"SELECT * FROM users WHERE email = '{email}'")

# SAFE: psycopg2
cursor.execute("SELECT id, name, email FROM users WHERE email = %s", (email,))

# SAFE: SQLAlchemy
from sqlalchemy import text
result = session.execute(
    text("SELECT id, name, email FROM users WHERE email = :email"),
    {"email": email}
)

Go

// VULNERABLE
query := fmt.Sprintf("SELECT * FROM users WHERE id = '%s'", userID)
rows, err := db.Query(query)

// SAFE
rows, err := db.Query("SELECT id, name, email FROM users WHERE id = $1", userID)

Java

// VULNERABLE
String query = "SELECT * FROM users WHERE id = '" + userId + "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);

// SAFE
PreparedStatement stmt = conn.prepareStatement(
    "SELECT id, name, email FROM users WHERE id = ?"
);
stmt.setString(1, userId);
ResultSet rs = stmt.executeQuery();

Dynamic Column/Table Names

Parameterized queries cannot substitute column or table names. Use a whitelist.

const ALLOWED_SORT_COLUMNS = ['name', 'created_at', 'email'];
const ALLOWED_DIRECTIONS = ['ASC', 'DESC'];

function buildOrderBy(column: string, direction: string): string {
  if (!ALLOWED_SORT_COLUMNS.includes(column)) {
    throw new Error(`Invalid sort column: ${column}`);
  }
  if (!ALLOWED_DIRECTIONS.includes(direction.toUpperCase())) {
    throw new Error(`Invalid sort direction: ${direction}`);
  }
  return `ORDER BY ${column} ${direction.toUpperCase()}`;
}

// Usage
const orderClause = buildOrderBy(req.query.sort, req.query.order);
const result = await db.query(
  `SELECT id, name, email FROM users ${orderClause} LIMIT $1 OFFSET $2`,
  [limit, offset]
);

Connection Security

Encrypted Connections

// PostgreSQL with TLS
import { Pool } from 'pg';

const pool = new Pool({
  host: process.env.DB_HOST,
  port: 5432,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  ssl: {
    rejectUnauthorized: true,                        // NEVER set to false in production
    ca: fs.readFileSync('/etc/ssl/certs/rds-ca.pem'), // CA certificate
  },
  // Connection pool settings
  max: 20,                    // Max connections in pool
  idleTimeoutMillis: 30000,   // Close idle connections after 30s
  connectionTimeoutMillis: 5000, // Fail fast if can't connect
  statement_timeout: 30000,    // Kill queries running longer than 30s
});

MySQL with TLS

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  ssl: {
    ca: fs.readFileSync('/etc/ssl/certs/rds-ca.pem'),
    rejectUnauthorized: true,
  },
  connectionLimit: 20,
  connectTimeout: 5000,
});

Row-Level Security (PostgreSQL)

RLS ensures that queries automatically filter by tenant, user, or role — even if the application code forgets.

-- Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Force RLS for table owner too (important!)
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

-- Policy: users can only see their own orders
CREATE POLICY user_orders ON orders
  FOR ALL
  USING (user_id = current_setting('app.current_user_id')::uuid);

-- Policy: admins can see everything
CREATE POLICY admin_all ON orders
  FOR ALL
  TO admin_role
  USING (true);

-- Set context per request in application
async function withUserContext(userId: string, fn: () => Promise<any>) {
  const client = await pool.connect();
  try {
    await client.query("SET LOCAL app.current_user_id = $1", [userId]);
    return await fn();
  } finally {
    client.release();
  }
}

// Usage
app.get('/api/orders', requireAuth, async (req, res) => {
  const orders = await withUserContext(req.user.id, async () => {
    // RLS automatically filters — even SELECT * is safe
    return db.query('SELECT * FROM orders');
  });
  res.json(orders.rows);
});

Multi-Tenant Isolation

AI generates single-tenant code. Multi-tenant isolation requires explicit patterns.

Schema-per-Tenant (Strongest Isolation)

async function getTenantConnection(tenantId: string) {
  // Validate tenant ID to prevent schema injection
  if (!/^[a-z0-9_]+$/.test(tenantId)) {
    throw new Error('Invalid tenant ID');
  }

  const client = await pool.connect();
  await client.query(`SET search_path TO tenant_${tenantId}, public`);
  return client;
}

Column-based Isolation with Middleware

// Middleware that injects tenant filter into every query
class TenantScopedRepository {
  constructor(private tenantId: string) {}

  async findOrders(filters: OrderFilters) {
    return db.query(
      'SELECT * FROM orders WHERE tenant_id = $1 AND status = $2',
      [this.tenantId, filters.status] // tenant_id is ALWAYS included
    );
  }

  async createOrder(data: OrderInput) {
    return db.query(
      'INSERT INTO orders (tenant_id, product, amount) VALUES ($1, $2, $3)',
      [this.tenantId, data.product, data.amount] // Can never insert for wrong tenant
    );
  }
}

Query Timeouts and DoS Prevention

AI-generated queries with no timeout can lock your database.

// Query timeout at the connection level
const pool = new Pool({
  statement_timeout: 30000, // 30 second max query time
  query_timeout: 30000,
});

// Per-query timeout for expensive operations
await db.query({
  text: 'SELECT * FROM large_table WHERE complex_condition = $1',
  values: [value],
  timeout: 5000, // 5 seconds for this specific query
});

Preventing N+1 as a DoS Vector

// VULNERABLE: N+1 pattern — 1 query + N queries
// If user has 10,000 orders, this runs 10,001 queries
app.get('/api/users/:id/orders', async (req, res) => {
  const user = await db.query('SELECT * FROM users WHERE id = $1', [req.params.id]);
  const orders = await db.query('SELECT * FROM orders WHERE user_id = $1', [req.params.id]);

  // AI might then do N more queries for order items
  for (const order of orders.rows) {
    order.items = await db.query('SELECT * FROM order_items WHERE order_id = $1', [order.id]);
  }
});

// SAFE: Single query with joins, pagination enforced
app.get('/api/users/:id/orders', async (req, res) => {
  const limit = Math.min(parseInt(req.query.limit) || 20, 100); // Max 100
  const offset = parseInt(req.query.offset) || 0;

  const result = await db.query(`
    SELECT o.id, o.total, o.status, o.created_at,
           json_agg(json_build_object('id', oi.id, 'product', oi.product, 'qty', oi.qty)) AS items
    FROM orders o
    LEFT JOIN order_items oi ON oi.order_id = o.id
    WHERE o.user_id = $1
    GROUP BY o.id
    ORDER BY o.created_at DESC
    LIMIT $2 OFFSET $3
  `, [req.params.id, limit, offset]);

  res.json(result.rows);
});

Backup Encryption

# Encrypted backup with pg_dump
pg_dump -h $DB_HOST -U $DB_USER $DB_NAME | \
  gpg --symmetric --cipher-algo AES256 --batch --passphrase-file /secrets/backup-key | \
  aws s3 cp - s3://my-backups/db-$(date +%Y%m%d).sql.gpg

# Encrypted backup with mysqldump
mysqldump -h $DB_HOST -u $DB_USER -p$DB_PASSWORD $DB_NAME | \
  openssl enc -aes-256-cbc -salt -pbkdf2 -pass file:/secrets/backup-key | \
  gzip | aws s3 cp - s3://my-backups/db-$(date +%Y%m%d).sql.enc.gz

Database Security Checklist

Check	Why
All queries are parameterized	Prevents SQL injection
App connects as non-root user	Limits blast radius
Connection uses TLS	Prevents network sniffing
Query timeouts are configured	Prevents DoS via slow queries
Pagination is enforced	Prevents data dumping
Row-level security is enabled	Enforces tenant isolation
Backups are encrypted	Protects data at rest
SELECT specifies columns, never *	Prevents leaking sensitive fields
Connection pool limits are set	Prevents connection exhaustion
Database credentials rotate	Limits exposure window

Every database query AI generates needs to pass through this checklist. The query itself might be correct SQL — but correct SQL without security controls is still a vulnerability.