SQL injection

SQL injection happens when untrusted input becomes part of the SQL program instead of remaining data.

Suppose we use Node.js to run a simple query like this (I’m using pseudocode):

const color = getColorFromUser()
const query = `select * from cars where color = '${color}'`

If color is a string that contains a color like red or blue, everything works as planned.

But what if you accept this string from an input field in a form, and the attacker enters the string "blue'; drop table cars;"

Do you see what happens?

The value of query now is

select * from cars where color = 'blue'; drop table cars;'

And if you run this query, unless you removed the option to drop the table from the database permission of the database user, that is going to wipe out all of your data.

Another example.

Suppose you perform a query like this:

const query = 'SELECT * FROM users where name = "' + name + '"'

If you accept the name variable from a form, for example, and don’t sanitize it, a person could enter the value

flavio"; DELETE * FROM users; SELECT * FROM users where name ="flavio

See? Now the query will become

SELECT * FROM users where name = "flavio"; DELETE * FROM users; SELECT * FROM users where name ="flavio"

This will cause the users table to be wiped out.

The primary defense is a parameterized query. With the Node.js pg library, write:

const result = await db.query(
  'SELECT * FROM cars WHERE color = $1',
  [color]
)

The SQL text and the value are sent separately. The database never interprets characters inside color as SQL syntax.

An ORM or query builder helps only when you use its parameterized APIs. Raw SQL built with string interpolation is still vulnerable, even when it is executed through an ORM.

Parameters normally represent values, not table names, column names, or sort directions. When users can choose one of those, map the input to a small allowlist:

const allowedColumns = {
  name: 'name',
  created: 'created_at'
}

const orderBy = allowedColumns[input] ?? 'created_at'

Do not try to fix SQL injection by manually escaping quotes. Use the database driver’s parameters, validate input for the application’s own rules, and give the database account only the permissions the application needs. Least privilege limits the damage if another bug gets through.