Is neon.tech SQL vulnerable to injection?

Today I am doing something different. I need to answer a problem posed by a reader. He worries that a cool javascript feature I alluded to in an article, is vulnerable to SQL injection. This is the function:

await sql`SELECT email, name, prefs FROM user WHERE id=${user.id}`

If user.id is 1;delete from user; Will the user table be empty afterwards? I don't know off the top of my head if it is. Since I wrote the article, I should test it. This is what I did to test his assertion.

I created a new directory called 'arrow-test' and in the terminal, I created an environment with bun.sh in it:

uv venv
source .venv/bin/activate
uv pip install -U pip nodeenv
nodeenv -p
npm install -g bun

What's all this:

• uv venv - uv is a super fast venv and pip replacement this creates our Python virtual environment
• source .venv/bin/activate - activate our virtual environment
• uv pip install -U pip nodeenv - upgrade pip and install nodeenv, a node virtual environment
• nodeenv -p - add node to our python virtual environment
• npm install -g bun - get npm to add bun to our python virtual environment

With bun set up, I followed the example from Use Neon's Serverless Postgres with Bun and created a 'hello.js' to see if it works:

import { neon } from "@neondatabase/serverless";

// Bun automatically loads the DATABASE_URL from .env.local
// Refer to: https://bun.sh/docs/runtime/env for more information

const sql = neon(process.env.DATABASE_URL);
const rows = await sql`SELECT version()`;
console.log(rows[0].version);

$ bun run hello.js

So that works. Now we need to create a user table, populate it and select one row by id. Let's do this in a bun test. I'll use the first example and run it:

import { expect, test } from "bun:test";

// tests...
test("2 + 2", () => {
  expect(2 + 2).toBe(4);
});

$ bun test

That works. So now we can adapt it to our sql. The test framework has some handy life cycle hooks that we can use to create our table, populate and drop:

import { expect, test, beforeAll, afterAll } from "bun:test";
import { neon } from "@neondatabase/serverless";

const sql = neon(process.env.DATABASE_URL);

const user = {
  id: null,
  email: "foo@bar.com",
  name: "foo",
  prefs: { theme: "dark" },
};

beforeAll(async () => {
  // global setup
  await sql`CREATE TABLE IF NOT EXISTS test_user (
    id serial,
    email text,
    name text,
    prefs json,
    PRIMARY KEY (id)
  )`;
  const ins_row = await sql`INSERT INTO test_user (email, name, prefs)
    VALUES (${user.email}, ${user.name}, ${user.prefs})
    RETURNING id`;
  user.id = ins_row[0].id;
});

afterAll(async () => {
  // global teardown
  await sql`DROP TABLE IF EXISTS test_user`;
});

Then we can write our success test:

test("select", async () => {
  const sel_row = await sql`SELECT email, name, prefs
    FROM test_user
    WHERE id=${user.id}`;
  expect(sel_row[0].email).toBe(user.email);
  expect(sel_row[0].name).toBe(user.name);
  expect(sel_row[0].prefs.theme).toBe(user.prefs.theme);
});

And finally, we can do the work we started out to discover:

test("injection", async () => {
  const bad = { id: "1;delete from test_user;" };
  const sel_row = await sql`SELECT email, name, prefs
    FROM test_user
    WHERE id=${bad.id}`;
  throw "should not get here";
});

Happily, it throws an error:

NeonDbError: invalid input syntax for type integer: "1;delete from test_user;"
code: "22P02"
  at new NeonDbError (./arrow-test/node_modules/@neondatabase/serverless/index.mjs:1512:127)
  at ./arrow-test/node_modules/@neondatabase/serverless/index.mjs:408:55

✗ injection [80.23ms]

This is the best outcome. The error indicates that our example is not vulnerable to this type of SQL injection. I cannot prove it is safe from all sql injection, but I have proved that it is safe from this. Thank you, Karl Popper.

This did not take much time and I now have a directory that looks like this in Zed. Guess it's time to delete it. Bun was easy to use, well documented and nice and speedy. I'm a python developer and yet bun's websockets are super fast too…

I hope this was useful. Thanks for reading.