Regex for SQL Column Name

Why This Pattern Prevents SQL Injection

Parameterised queries protect values, not identifiers. If your application lets a user pick a column to sort by, filter by, or group by, that column name is almost always interpolated into the SQL text as a plain string — becauseSELECT $1 FROM users is a syntax error. The only defence is a strict allowlist. This regex is the character-class half of that defence; a hard-codedSet of actual column names is the semantic half.

// VULNERABLE — user controls column name
const sort = req.query.sort;                      // "id; DROP TABLE users; --"
db.query(`SELECT * FROM users ORDER BY ${sort}`);

// SAFE — regex + allowlist (both required)
const IDENT = /^[A-Za-z_][A-Za-z0-9_]{0,62}$/;
const ALLOWED = new Set(['id', 'name', 'email', 'created_at']);

const sort = req.query.sort;
if (!IDENT.test(sort) || !ALLOWED.has(sort)) {
  return res.status(400).send('bad sort column');
}
db.query(`SELECT * FROM users ORDER BY ${sort}`);  // now safe

Why Both Checks Are Needed

The regex alone would accept "users", "orders", "payments" — none are injection attempts but all are wrong tables for a sort query onproducts. The allowlist alone has a slower lookup if someone adds a comma or space to probe your string-building. Combined, they are complete: the regex rejects malformed input in O(n), the allowlist rejects semantically wrong but syntactically valid input in O(1).

Identifier Rules Per Database

For portable code use the strictest common subset:/^[A-Za-z_][A-Za-z0-9_]{0,62}$/. Dollar signs and @/#prefixes are supported in some engines but refusing them avoids surprises on migration.

Language-Specific Usage

JavaScript / TypeScript

const IDENT = /^[A-Za-z_][A-Za-z0-9_]{0,62}$/;
const ALLOWED_COLUMNS = new Set([
  'id', 'name', 'email', 'created_at', 'updated_at'
]);

function safeColumn(input) {
  if (typeof input !== 'string') throw new Error('column must be string');
  if (!IDENT.test(input))         throw new Error('invalid identifier');
  if (!ALLOWED_COLUMNS.has(input)) throw new Error('column not allowed');
  return input;
}

// Usage in an ORM-less query builder
const col = safeColumn(req.query.sort);
const dir = ['asc','desc'].includes(req.query.dir) ? req.query.dir : 'asc';
await db.query(`SELECT * FROM users ORDER BY ${col} ${dir}`);

Python

import re

IDENT = re.compile(r'^[A-Za-z_][A-Za-z0-9_]{0,62}$')
ALLOWED_COLUMNS = {'id', 'name', 'email', 'created_at'}

def safe_column(value: str) -> str:
    if not isinstance(value, str):
        raise ValueError('column must be str')
    if not IDENT.match(value):
        raise ValueError(f'invalid identifier: {value!r}')
    if value not in ALLOWED_COLUMNS:
        raise ValueError(f'column not allowed: {value}')
    return value

# Usage
col = safe_column(request.args.get('sort', 'id'))
cur.execute(f"SELECT * FROM users ORDER BY {col} LIMIT %s", (limit,))

Go

package main

import (
    "fmt"
    "regexp"
)

var identRe = regexp.MustCompile(`^[A-Za-z_][A-Za-z0-9_]{0,62}$`)

var allowedColumns = map[string]bool{
    "id":         true,
    "name":       true,
    "email":      true,
    "created_at": true,
}

func safeColumn(s string) (string, error) {
    if !identRe.MatchString(s) {
        return "", fmt.Errorf("invalid identifier: %q", s)
    }
    if !allowedColumns[s] {
        return "", fmt.Errorf("column not allowed: %q", s)
    }
    return s, nil
}

PHP

<?php
function safe_column(string $s, array $allowed): string {
    if (!preg_match('/^[A-Za-z_][A-Za-z0-9_]{0,62}$/', $s)) {
        throw new InvalidArgumentException("invalid identifier: $s");
    }
    if (!in_array($s, $allowed, true)) {
        throw new InvalidArgumentException("column not allowed: $s");
    }
    return $s;
}

$col = safe_column($_GET['sort'] ?? 'id', ['id','name','email','created_at']);
$stmt = $pdo->prepare("SELECT * FROM users ORDER BY $col LIMIT ?");
$stmt->execute([$limit]);

Fully-Qualified Names

Some queries need schema.table.column or table.column. Chain the identifier pattern with a dot separator, bounded to 1-2 extra parts:

/^[A-Za-z_][A-Za-z0-9_]{0,62}(?:\.[A-Za-z_][A-Za-z0-9_]{0,62}){0,2}$/

// Matches:
//   "column"                     ✓
//   "table.column"               ✓
//   "schema.table.column"        ✓
// Rejects:
//   ".column"                    ✗ (empty first part)
//   "a.b.c.d"                    ✗ (too many parts)
//   "my-table.col"               ✗ (hyphen)
//   "users;drop.table"           ✗ (semicolon)

Common Pitfalls

Case-insensitive allowlist mismatch

PostgreSQL lowercases unquoted identifiers at parse time, so SELECT ID FROM USERSactually asks for the lowercase id. If your allowlist is lowercase but the user sends ID, the regex passes but the Set.has check fails. Fix: lowercase before the allowlist check, or populate the allowlist with both cases.

Reserved words

A technically valid identifier like order, group, select,user is a reserved word in most databases. RunningSELECT user FROM users breaks on MySQL and SQL Server even though your regex happily accepted it. The allowlist catches this because you only put real column names there — but if you build the allowlist dynamically from information_schema, reserved words sneak back in. Quote them ("user") only after regex-validating the inner name.

Length overflow

The {0,62}upper bound exists because PostgreSQL silently truncates identifiers over 63 chars. A regex that accepts 100 characters produces mysterious "column does not exist" errors when the user types a long name. Enforce the exact DB-level limit in your pattern.

Trusting ORM parameter binding for identifiers

Prisma, SQLAlchemy, ActiveRecord, and most ORMs refuse to parameterise identifiers for the same reason the raw drivers do — the SQL grammar does not allow it. If you seeORDER BY ? in generated SQL, your ORM is going to interpolate the string directly or throw. Always keep the regex-plus-allowlist pattern in the application layer.

SQL Identifier Cheatsheet

Testing Your SQL Identifier Regex

Use the live Regex Tester with this canonical test block:

id
user_id
USER_ID
_private
col1
schema.users.id
my-column
1_bad
user name
col"name
users;DROP TABLE
col/*comment*/
很长的中文列名

Pattern: /^[A-Za-z_][A-Za-z0-9_]{0,62}$/
Expected matches: id, user_id, USER_ID, _private, col1
Expected failures: everything else

Pattern: /^[\p{L}_][\p{L}\p{N}_]{0,62}$/u
Expected additional match: 很长的中文列名

Performance Notes

Anchored patterns with ^ and $ on fixed character classes are O(n) in the input length and never backtrack. The quantifier {0,62} is bounded, so even on malicious input (a 10,000-character string of valid identifier chars) the regex returns false in microseconds — it just hits the length cap and bails.

Related Tools

• Regex for Alphanumeric
• Regex for Slug
• Regex for Username
• Regex for File Extension
• Regex for Password Validation
• Find & Replace Tool