Lesson 29 / 35
Security: Injection, Mass Assignment and Secrets
Keep raw SQL parameterised, whitelist writable fields instead of trusting request bodies, and protect database credentials.
By Rahul Agarwal · 15 years in web development
Where ORMs stop protecting you
The high-level query APIs parameterise values, so findMany({ where: { name } }) is not open to SQL injection even if name contains quotes. The protection ends when you build SQL text: string-concatenated raw queries, Prisma's $queryRawUnsafe, TypeORM where strings with ${} interpolation, or Sequelize literal() with user input. Identifiers such as column names for sorting cannot be bound as parameters, so map them through a fixed whitelist. The second risk is mass assignment: passing req.body straight into create or update lets a user set fields you never meant to expose, such as role: 'admin' or isVerified: true. Pick allowed fields explicitly, or use Sequelize's fields option. Third, credentials: keep the connection string in environment variables or a secret manager, never in Git, and give the app a database user with only the privileges it needs. Migrations can run with a more powerful user.
Unsafe and safe versions
The first two lines can be exploited; the rest cannot, because values are bound and fields are chosen by the server.
// UNSAFE: user input becomes SQL text
await repo.createQueryBuilder('u').where(`u.name = '${req.query.name}'`).getMany();
await prisma.user.update({ where: { id }, data: req.body }); // mass assignment
// SAFE: bound parameter
await repo.createQueryBuilder('u').where('u.name = :name', { name: req.query.name }).getMany();
// SAFE: explicit fields only
const { name, bio } = req.body;
await prisma.user.update({ where: { id }, data: { name, bio } });
// SAFE: whitelist identifiers that cannot be parameters
const sortable = { newest: 'createdAt', title: 'title' } as const;
const key = sortable[req.query.sort as keyof typeof sortable] ?? 'createdAt';The admission form
A college form has boxes for name and marks; the office fills in 'scholarship approved'. Mass assignment is letting the student write in the office box too. Parameterised SQL is insisting that whatever the student writes stays inside its box and is never read as an instruction.
Do not leak raw database errors
Returning the ORM's error message to the client can reveal table names, constraint names or SQL fragments. Log the full error on the server, and send the client a generic message plus a status code. Map known cases, such as a unique-constraint violation, to friendly messages like 'email already registered'.
Quick check: A sort parameter from the URL is used as the column name in ORDER BY. What is the correct protection?
- Bind it as a query parameter like any value
- Map the input to a fixed whitelist of allowed column names
- Escape single quotes in the input
- Convert the input to upper case
Answer
Map the input to a fixed whitelist of allowed column names — Parameters bind values, not identifiers. A whitelist guarantees only known column names ever reach the SQL.