Frontend EngineeringYou asked AI to build a user search endpointWhat is endpoint?A specific URL path on a server that handles a particular type of request, like GET /api/users.. It generated a clean Express route, wired up the database query, and the feature worked perfectly in testing. What you didn't notice: the AI used a template literal to build the SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. query. An attacker who types '; DROP TABLE users; -- into the search box can delete your entire user table. AI generates SQL injectionWhat is sql injection?An attack where user input is inserted directly into a database query, letting the attacker read, modify, or delete data. Parameterized queries prevent it. vulnerabilities more often than any other backend security flaw, because string interpolationWhat is interpolation?Inserting dynamic values like usernames or counts into a text template, replacing placeholders with actual data at display time. is the most "natural" way to build a query, and AI optimizes for natural-looking code.
How the attack works
When you build a SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. query by gluing user input into a string, you're trusting that the input contains only innocent data. An attacker exploits that trust by submitting input that contains SQL syntax instead:
// What AI typically generates
const query = `SELECT * FROM users WHERE username = '${username}'`;
db.query(query);For a normal user, this produces a valid query:
SELECT * FROM users WHERE username = 'alice'But an attacker submits alice' OR '1'='1 as the username. Your template literal produces:
SELECT * FROM users WHERE username = 'alice' OR '1'='1'The OR '1'='1' condition is always true, so the query returns every row in the users table.
What attackers can do
The damage goes far beyond reading a few extra records.
| Attack | Payload example | Result |
|---|---|---|
| Bypass authentication | admin'-- as username | Logs in as admin without password |
| Dump entire table | ' UNION SELECT password FROM users -- | Extracts all passwords through the UI |
| Delete data | '; DROP TABLE users; -- | Destroys your user table |
| Read other tables | ' UNION SELECT credit_card FROM payments -- | Steals data from unrelated tables |
| Write files (some DBs) | ' INTO OUTFILE '/var/www/shell.php' -- | Creates a backdoor on the server |
CALLOUT, ?) instead of ${variable} inside the SQL string.The fix: parameterized queries
Parameterized queries separate SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. code from data. You write the query template with placeholders, then pass the values separately. The database driver handles the separation, user input can never be interpreted as SQL syntax.
// PostgreSQL - CODE_BLOCK, $2, etc. are placeholders
const query = 'SELECT * FROM users WHERE username = CODE_BLOCK';
const result = await db.query(query, [username]);
// MySQL - ? is the placeholder
connection.query(
'SELECT * FROM users WHERE username = ?',
[username],
(err, results) => { /* ... */ }
);When the attacker submits alice' OR '1'='1, the database treats the entire string as the literal value to compare, not as SQL to evaluate. The query looks for a user whose username is literally alice' OR '1'='1, finds none, and returns zero rows.
ORMs: protection by default
Object-Relational Mappers (ORMs) like Prisma, Sequelize, and TypeORM use parameterized queries for all standard operations:
// Prisma - parameterized internally
const user = await prisma.user.findUnique({
where: { username: username }
});
// Sequelize - parameterized internally
const user = await User.findOne({
where: { username: username }
});The risk with ORMs comes when you drop into raw SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. for complex queries. Most ORMs provide a raw query method, use parameterization there too:
// Prisma raw query - template tag syntax IS parameterized
const users = await prisma.$queryRaw`
SELECT * FROM users WHERE username = ${username}
`;
// But $queryRawUnsafe with string concatenation is NOT safe
// AI sometimes generates this when the template tag doesn't work
const users = await prisma.$queryRawUnsafe(
`SELECT * FROM users WHERE username = '${username}'` // VULNERABLE
);$queryRawUnsafe with string interpolation. The function name literally says "unsafe," but AI uses it to avoid TypeScript complaints. If you see $queryRawUnsafe in AI-generated code, treat it as a red flag and check for interpolated user input.The column name problem
Parameterization works for values (strings, numbers, booleans). It does not work for structural SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. elements like column names, table names, or sort directions. If your sort column comes from user input, a whitelist is the correct solution:
const allowedColumns = ['name', 'email', 'created_at', 'price'];
if (!allowedColumns.includes(req.query.sortBy)) {
return res.status(400).json({ error: 'Invalid sort column' });
}
// Now safe - you've validated against a known list
const query = `SELECT * FROM products ORDER BY ${req.query.sortBy}`;Never build column or table names from user input without a whitelist, even if you think the input is validated elsewhere.
Defense in depth
Parameterized queries are the main fix, but multiple layers make your application more resilient.
Least privilege database accounts
Your application's database user should have only the permissions it needs. A typical web app needs SELECT, INSERT, UPDATE, and DELETE on specific tables. It does not need DROP, CREATE, ALTER, or GRANT.
If an attacker does find an injection point, they're limited to what your app's database user can do.
Never expose raw errors
Database error messages reveal schemaWhat is schema?A formal definition of the structure your data must follow - which fields exist, what types they have, and which are required. information, table names, column names, sometimes query structure. Catch database errors and return a generic message to the client:
try {
const result = await db.query(query, params);
res.json(result.rows);
} catch (error) {
console.error('Database error:', error); // log details server-side
res.status(500).json({ error: 'An internal error occurred' }); // generic to client
}Vulnerable vs safe patterns
| Pattern | Safe? | Why |
|---|---|---|
` SELECT FROM users WHERE id = ${userId} | No | User input in SQL string |
| 'SELECT FROM users WHERE id = TABLE' with [userId] | Yes | Parameterized |
| User.findOne({ where: { id } }) | Yes | ORM uses parameterization |
| SELECT FROM ${tableName} | Depends | Only safe with whitelist |
| prisma.$queryRawSELECT WHERE id = ${id} | Yes | Template tag is parameterized |
| prisma.$queryRawUnsafe(...${id}) ` | No | String concatenation bypasses safety |
' OR '1'='1 or admin'-- against their own systems. Never test against systems you don't own.// SQL Injection: Vulnerable vs Safe
// VULNERABLE, what AI often generates
app.post('/login', async (req, res) => {
const { username, password } = req.body;
const query = `
SELECT * FROM users
WHERE username = '${username}'
AND password = '${password}'
`;
const user = await db.query(query);
});
// SAFE, parameterized query (PostgreSQL)
app.post('/login', async (req, res) => {
const { username, password } = req.body;
const query = `
SELECT * FROM users
WHERE username = $1
AND password = $2
`;
const result = await db.query(query, [username, password]);
const user = result.rows[0];
});
// SAFE, Using ORM (Prisma)
app.post('/login', async (req, res) => {
const { username, password } = req.body;
const user = await prisma.user.findUnique({
where: { username }
});
if (user && await bcrypt.compare(password, user.passwordHash)) {
// Login success
}
});
// SAFE, Whitelist for dynamic columns
app.get('/users', async (req, res) => {
const { sortBy } = req.query;
const allowedColumns = ['name', 'email', 'created_at'];
if (!allowedColumns.includes(sortBy)) {
return res.status(400).json({ error: 'Invalid sort column' });
}
const query = `SELECT * FROM users ORDER BY ${sortBy}`;
const result = await db.query(query);
});