From String Concatenation to Parameterized Queries: Building a Safe SQL Builder#

During a recent code review, I found this pattern scattered throughout the codebase:

const sql = `SELECT * FROM users WHERE id = ${userId}`

Convenient? Yes. Secure? Absolutely not. This is a textbook SQL injection vulnerability. If userId comes from user input and someone passes ' OR '1'='1, your entire users table is compromised.

I decided to build a SQL query builder tool that generates common queries quickly while avoiding the security pitfalls of manual SQL writing.

Core Design of a SQL Builder#

Basic Query Construction#

The simplest SELECT query builder:

function buildSelect(table: string, columns: string[] = ['*'], options: QueryOptions = {}) {
  const { where, orderBy, limit } = options
  
  let sql = `SELECT ${columns.join(', ')} FROM ${table}`
  
  if (where) {
    sql += ` WHERE ${where}`
  }
  
  if (orderBy) {
    sql += ` ORDER BY ${orderBy}`
  }
  
  if (limit) {
    sql += ` LIMIT ${limit}`
  }
  
  return sql
}

// Usage
buildSelect('users', ['id', 'name', 'email'], {
  where: 'status = "active"',
  orderBy: 'created_at DESC',
  limit: 10
})
// SELECT id, name, email FROM users WHERE status = "active" ORDER BY created_at DESC LIMIT 10

This solves the tedious string concatenation problem, but the security issue remains—the where clause is still string concatenation.

Parameterized Queries: The Core of Injection Prevention#

Why Parameterization Works#

Parameterized queries separate SQL statements from data:

// Dangerous: string concatenation
const dangerous = `SELECT * FROM users WHERE name = '${userName}'`

// Safe: parameterized query
const safe = {
  sql: 'SELECT * FROM users WHERE name = ?',
  params: [userName]
}

The database engine first compiles the SQL statement into an execution plan, then fills in parameter values. Parameter values are never executed as SQL code, eliminating injection at the source.

Builder Implementation# 

interface ParameterizedQuery {
  sql: string
  params: any[]
}

function buildSafeSelect(
  table: string, 
  columns: string[] = ['*'],
  conditions: Record<string, any> = {},
  options: Omit<QueryOptions, 'where'> = {}
): ParameterizedQuery {
  const params: any[] = []
  const whereClauses: string[] = []
  
  // Build parameterized WHERE clause
  Object.entries(conditions).forEach(([column, value]) => {
    if (value === null) {
      whereClauses.push(`${column} IS NULL`)
    } else if (Array.isArray(value)) {
      // IN query
      const placeholders = value.map(() => '?').join(', ')
      whereClauses.push(`${column} IN (${placeholders})`)
      params.push(...value)
    } else {
      whereClauses.push(`${column} = ?`)
      params.push(value)
    }
  })
  
  let sql = `SELECT ${columns.join(', ')} FROM ${table}`
  
  if (whereClauses.length > 0) {
    sql += ` WHERE ${whereClauses.join(' AND ')}`
  }
  
  if (options.orderBy) {
    sql += ` ORDER BY ${options.orderBy}`
  }
  
  if (options.limit) {
    sql += ` LIMIT ?`
    params.push(options.limit)
  }
  
  return { sql, params }
}

// Usage example
const query = buildSafeSelect('users', ['id', 'name'], {
  status: 'active',
  role: ['admin', 'editor'],  // IN query
  deleted_at: null            // IS NULL
}, {
  orderBy: 'created_at DESC',
  limit: 20
})

console.log(query)
// {
//   sql: 'SELECT id, name FROM users WHERE status = ? AND role IN (?, ?) AND deleted_at IS NULL ORDER BY created_at DESC LIMIT ?',
//   params: ['active', 'admin', 'editor', 20]
// }

The generated query can be passed directly to database drivers:

const result = await db.execute(query.sql, query.params)

Complex Query Building Strategies#

JOIN Queries#

Multi-table joins are another pain point:

interface JoinClause {
  table: string
  on: string
  type?: 'INNER' | 'LEFT' | 'RIGHT'
}

function buildJoin(
  mainTable: string,
  joins: JoinClause[],
  columns: string[] = ['*'],
  conditions: Record<string, any> = {}
): ParameterizedQuery {
  const params: any[] = []
  const whereClauses: string[] = []
  
  let sql = `SELECT ${columns.join(', ')} FROM ${mainTable}`
  
  // Build JOIN clauses
  joins.forEach(join => {
    const joinType = join.type || 'INNER'
    sql += ` ${joinType} JOIN ${join.table} ON ${join.on}`
  })
  
  // WHERE clause
  Object.entries(conditions).forEach(([column, value]) => {
    whereClauses.push(`${column} = ?`)
    params.push(value)
  })
  
  if (whereClauses.length > 0) {
    sql += ` WHERE ${whereClauses.join(' AND ')}`
  }
  
  return { sql, params }
}

// Usage: Query users and their orders
const query = buildJoin(
  'users u',
  [
    { table: 'orders o', on: 'u.id = o.user_id', type: 'LEFT' },
    { table: 'products p', on: 'o.product_id = p.id' }
  ],
  ['u.name', 'o.order_id', 'p.title'],
  { 'u.status': 'active' }
)

// SELECT u.name, o.order_id, p.title 
// FROM users u 
// LEFT JOIN orders o ON u.id = o.user_id 
// INNER JOIN products p ON o.product_id = p.id 
// WHERE u.status = ?

Dynamic Condition Building#

In real applications, query conditions often come from user filters:

function buildDynamicQuery(
  table: string,
  filters: {
    search?: string
    status?: string
    dateFrom?: string
    dateTo?: string
    tags?: string[]
  }
): ParameterizedQuery {
  const params: any[] = []
  const conditions: string[] = []
  
  // Fuzzy search
  if (filters.search) {
    conditions.push(`(name LIKE ? OR description LIKE ?)`)
    const searchTerm = `%${filters.search}%`
    params.push(searchTerm, searchTerm)
  }
  
  // Exact match
  if (filters.status) {
    conditions.push(`status = ?`)
    params.push(filters.status)
  }
  
  // Date range
  if (filters.dateFrom) {
    conditions.push(`created_at >= ?`)
    params.push(filters.dateFrom)
  }
  if (filters.dateTo) {
    conditions.push(`created_at <= ?`)
    params.push(filters.dateTo)
  }
  
  // Array condition
  if (filters.tags?.length) {
    const placeholders = filters.tags.map(() => '?').join(', ')
    conditions.push(`id IN (SELECT item_id FROM item_tags WHERE tag_id IN (${placeholders}))`)
    params.push(...filters.tags)
  }
  
  const sql = conditions.length > 0
    ? `SELECT * FROM ${table} WHERE ${conditions.join(' AND ')}`
    : `SELECT * FROM ${table}`
  
  return { sql, params }
}

Batch INSERT/UPDATE Operations#

Batch Insert Optimization#

Single-row inserts are inefficient. Batch inserts are a common requirement:

function buildBatchInsert(
  table: string,
  rows: Record<string, any>[]
): ParameterizedQuery {
  if (rows.length === 0) {
    throw new Error('No data to insert')
  }
  
  const columns = Object.keys(rows[0])
  const params: any[] = []
  
  // Each row gets its own placeholder group
  const valueGroups = rows.map(row => {
    const values = columns.map(col => {
      params.push(row[col])
      return '?'
    })
    return `(${values.join(', ')})`
  })
  
  const sql = `INSERT INTO ${table} (${columns.join(', ')}) VALUES ${valueGroups.join(', ')}`
  
  return { sql, params }
}

// Batch insert 100 records
const query = buildBatchInsert('products', products)
// INSERT INTO products (name, price, stock) VALUES (?, ?, ?), (?, ?, ?), ...

UPSERT Syntax#

MySQL’s ON DUPLICATE KEY UPDATE:

function buildUpsert(
  table: string,
  data: Record<string, any>,
  uniqueKey: string
): ParameterizedQuery {
  const columns = Object.keys(data)
  const params: any[] = []
  
  const placeholders = columns.map(col => {
    params.push(data[col])
    return '?'
  })
  
  // Update non-unique key fields on conflict
  const updateClauses = columns
    .filter(col => col !== uniqueKey)
    .map(col => {
      params.push(data[col])
      return `${col} = ?`
    })
  
  const sql = `
    INSERT INTO ${table} (${columns.join(', ')}) 
    VALUES (${placeholders.join(', ')})
    ON DUPLICATE KEY UPDATE ${updateClauses.join(', ')}
  `
  
  return { sql, params }
}

Tool Implementation: Online SQL Builder#

Based on these principles, I built an online tool: SQL Builder

Core features:

  • Supports SELECT/INSERT/UPDATE/DELETE operations
  • Auto-formats SQL (keywords on new lines)
  • One-click copy to clipboard
  • Visual form input to reduce syntax errors

While the tool generates raw SQL (for learning and debugging), the underlying philosophy prioritizes parameterized queries. For production projects, I recommend using it alongside an ORM or query builder library.

Lessons Learned#

1. Table/Column Names Cannot Be Parameterized#

Parameterization only works for values. Table and column names must be whitelist-validated:

const ALLOWED_TABLES = ['users', 'orders', 'products']
const ALLOWED_COLUMNS = {
  users: ['id', 'name', 'email', 'status'],
  // ...
}

function validateIdentifier(table: string, columns: string[]) {
  if (!ALLOWED_TABLES.includes(table)) {
    throw new Error(`Invalid table: ${table}`)
  }
  
  const invalidCols = columns.filter(col => 
    col !== '*' && !ALLOWED_COLUMNS[table].includes(col)
  )
  
  if (invalidCols.length > 0) {
    throw new Error(`Invalid columns: ${invalidCols.join(', ')}`)
  }
}

2. LIMIT Parameter Type Issues#

Some database drivers are sensitive to LIMIT parameter types:

// Wrong: passed string
params.push('10')

// Correct: passed number
params.push(10)

3. NULL Value Handling#

WHERE column = NULL always returns empty results. You must use IS NULL:

if (value === null) {
  conditions.push(`${column} IS NULL`)
} else {
  conditions.push(`${column} = ?`)
  params.push(value)
}

Conclusion#

A SQL builder may seem simple, but making it secure, flexible, and easy to use requires attention to many details:

  1. Parameterized queries are the core of injection prevention—never concatenate user input
  2. Dynamic conditions require flexible handling of various data types
  3. Batch operations need performance optimization
  4. Identifier validation cannot be skipped—table/column names cannot be parameterized

Hope this helps. If you’re building a similar tool, feel free to reach out.

Related Tools: JSON Formatter | JSON to CSV