Please Don't (Manually) Parameterize Your SQL Queries

Last week, I wrote a post about a plugin (that lints your SQL queries) called SafeQL. While doing so, I wrote this line:

client.query(sql`SELECT * FROM comments WHERE id = ${id}`);

Immediately, I got responses from a few places that I should parameterize my queries since it's open for SQL injections.

While they are not entirely wrong, they are not correct.

I'll explain; writing variables inside a query string is indeed a call for SQL injection:

// ⚠️ Open to SQL injection
client.query(`SELECT * FROM comments WHERE id = ${id}`)
// id = "0 OR (other statement)"

While that is true, it is not what is happening in the code below

client.query(sql`SELECT * FROM comments WHERE id = ${id}`);
          // ^^^ tagged templated literal

What is this syntax?

It is called Tagged templates, and I am quoting MDN:

Tags allow you to parse template literals with a function. The first argument of a tag function contains an array of string values. The remaining arguments are related to the expressions.

Explain to me like I am 5

// this line
client.query(sql`SELECT * FROM comments WHERE id = ${id}`)

// turns into this:
client.query({
  text: "SELECT * FROM comments WHERE id = $1",
  values: [id]
})

sql (which is a tagged template literal) is a function that looks like this:

export function sql(
  template: TemplateStringsArray,
  ...values: unknown[]
);

// Typescript built-in type
interface TemplateStringsArray extends ReadonlyArray<string> {
    readonly raw: readonly string[];
}

Given the following code:

sql`SELECT * FROM comments WHERE id = ${id}`
// template === ["SELECT * FROM comments WHERE id = "]
// values === [id]

template would be equal to ["SELECT * FROM comments WHERE id = "] and values to [id].

But how node-postgres or Sequelize handle it?

After we demystified the term tagged template literal, how can we use it to our favor?

node-postgres allows writing SQL queries by:

// string only approach
client.query("select name FROM table_name WHERE id = $1", [name]);

// object approach
client.query({
  text: "select name FROM table_name WHERE id = $1",
  values: [name]
});

Sequelize allows us to write in an object approach as well (among others), but with query rather than a text:

sequelize.query({
  query: "select name FROM table_name WHERE id = $1",
  values: [name]
});

As we might have noticed, the object structure is similar to the same structure that the sql tag has returned. In other words, when using sql tag, it returns an object that resembles a parameterized query.

sql implementation in a nutshell

function sql(template: TemplateStringsArray, ...values: unknown[]) {
  let id = 0;
  let text = template[0];

  for (const value of values) {
    query += `$${++id}`;
    query += template[id];
  }

  // we pass both `query` and `text` so it would be compatible with both
  // node-postgres and Sequelize:
  return { text, query: text, values };
}

Why should I use it over manually parameterized queries?

It is easier, safer, less error-prone, and more straightforward. Large queries can go up to dozens of parameters. It is just a matter of time before they will be misplaced. In addition, reading the query while trying to figure out what is $5 or $14 can be challenging.

npm install

Instead of maintaining an sql implementation, there are a few packages that have already done it:

@ts-safeql/sql-tag
sql-template-strings
sql-template-tag

What's next?

If you are using TypeScript and care about type safety, check out SafeQL. It is a free and open-source ESLint plugin that ensures you never misspell a table, column, or any invalid syntax. As a plus, it automatically generates TypeScript types for each query you write.