SQL injection in Prisma, Supabase and Node apps: fixing string-built queries

SQL injection happens when user input becomes part of the SQL text, so the database cannot tell the difference between your query and what the user typed. It lets people read data they should not see, change it, or delete it. It is old, well understood and still in AI-generated code, usually in the one place where the ORM was too awkward and a raw query seemed easier.

Why this happens

ORMs like Prisma and Drizzle, and the Supabase client, are safe by default. The problem appears when something doesn’t fit, such as a search with a dynamic sort order, a report, or a full-text query, and the AI writes SQL by hand. It often builds the string the way you would build any other string:

// Unsafe: Prisma
const rows = await prisma.$queryRawUnsafe(
  `SELECT * FROM "Product" WHERE name LIKE '%${search}%'`
)

// Unsafe: node-postgres
const result = await client.query(
  `SELECT * FROM users WHERE email = '${email}'`
)

// Unsafe: Drizzle
const rows = await db.execute(sql.raw(`SELECT * FROM orders WHERE id = ${id}`))

If search is %' OR '1'='1, the query now returns every row. Variations of the same idea can read other tables.

The same mistake appears inside Supabase database functions. A plpgsql function that builds a query with EXECUTE and joins strings with || has the same flaw, and anyone who can call it with rpc() can feed it input.

How to check your own app

1. Search your code for $queryRawUnsafe, $executeRawUnsafe, sql.raw, .query( and .execute(. Open each hit and see whether a template literal or + puts a variable into the SQL text.
2. In Supabase, open SQL Editor and look at the source of your functions (the Database section lists them). Search for execute and look at how the string is built.
3. Test a search or filter field on your own app by typing a single quote ('). If you get a server error or a database error message, that input is probably going into the SQL text. A safe app treats it as an ordinary character.

Only test this on your own app, and use a development or staging database if you can.

The fix

1. Pass values as parameters

Prisma: use the tagged template with $queryRaw. The values become parameters automatically:

const rows = await prisma.$queryRaw`
  SELECT * FROM "Product" WHERE name ILIKE ${'%' + search + '%'}
`

The backticks must come straight after $queryRaw. Calling $queryRaw as a normal function with a string you built yourself does not give you the same protection. If you do need $queryRawUnsafe, pass values as separate arguments and use $1, $2 in the SQL:

const rows = await prisma.$queryRawUnsafe(
  'SELECT * FROM "Product" WHERE name ILIKE $1',
  `%${search}%`
)

node-postgres (pg):

const result = await client.query(
  'SELECT * FROM users WHERE email = $1',
  [email]
)

Drizzle: use the sql template tag, which parameterises interpolated values, and avoid sql.raw for anything a user can influence:

import { sql } from 'drizzle-orm'

const rows = await db.execute(sql`SELECT * FROM orders WHERE id = ${id}`)

2. Fix Supabase functions that build SQL

If the function does not need dynamic SQL, write it as a plain query and the problem goes away. If it does, never join user values into the string:

create or replace function public.search_items(term text, sort_col text)
returns setof public.items
language plpgsql
as $$
begin
  if sort_col not in ('name', 'created_at', 'price') then
    raise exception 'Invalid sort column';
  end if;

  return query execute
    format('select * from public.items where name ilike $1 order by %I', sort_col)
    using '%' || term || '%';
end;
$$;

Values go in with USING and $1. Identifiers (table and column names) go in with format('%I', ...), and %L quotes a literal value if you ever need one in the text. The explicit check on sort_col is extra protection, and the next section explains why it is worth having. If the function is security definer, also read Supabase security definer functions.

3. Allow-list column names and sort direction

Parameters can carry values. They cannot carry column names or ASC/DESC. When users pick the sort order, map their choice to a fixed set:

const SORT_COLUMNS = { name: 'name', price: 'price', newest: 'createdAt' } as const

const column = SORT_COLUMNS[sort as keyof typeof SORT_COLUMNS] ?? 'createdAt'
const direction = order === 'asc' ? 'asc' : 'desc'

const products = await prisma.product.findMany({
  orderBy: { [column]: direction },
})

An unknown value falls back to a default. Where the ORM can do the job, use it, and keep raw SQL for the cases it cannot handle.

4. Limit what the database account can do

The database user your app connects with should not be a superuser or the owner of every table if it does not need to be. If an injection slips through, a limited account limits the damage.

Mistakes to avoid

• Escaping quotes by hand. Use parameters. Hand-written escaping has edge cases.
• Validating only in the browser. Anyone can call your API directly, so checks have to run on the server.
• Trusting numbers. id from a URL is still a string until you parse it. Number(id) or a zod schema before it reaches a query is a good habit.
• Building .or() filters from raw input in Supabase. .or('name.eq.' + input) lets the user add filter conditions of their own. Validate the input or use .eq() and friends.
• Leaving error details on. Database errors shown to the user make probing easier. Log them on the server and return a short message.