DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

Cover image for Please Don't (Manually) Parameterize Your SQL Queries
Eliya Cohen
Eliya Cohen

Posted on

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}`);
Enter fullscreen mode Exit fullscreen mode

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)"
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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]
})
Enter fullscreen mode Exit fullscreen mode

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[];
}
Enter fullscreen mode Exit fullscreen mode

Given the following code:

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

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]
});
Enter fullscreen mode Exit fullscreen mode

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]
});
Enter fullscreen mode Exit fullscreen mode

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 };
}
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)

typescript

11 Tips That Make You a Better Typescript Programmer

1 Think in {Set}

Type is an everyday concept to programmers, but it’s surprisingly difficult to define it succinctly. I find it helpful to use Set as a conceptual model instead.

#2 Understand declared type and narrowed type

One extremely powerful typescript feature is automatic type narrowing based on control flow. This means a variable has two types associated with it at any specific point of code location: a declaration type and a narrowed type.

#3 Use discriminated union instead of optional fields

...

Read the whole post now!