Please Don't (Manually) Parameterize Your SQL Queries
Tags: javascript, typescript, sql, postgres
Published: 2022-09-16 14:36:01

Last week, I wrote a [post](https://dev.to/newbie012/write-sql-queries-with-confidence-typescript-postgres-5b61) about a plugin (that lints your SQL queries) called [SafeQL](https://safeql.dev). While doing so, I wrote this line:

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

Immediately, I got responses from a few places that I should [parameterize](https://www.reddit.com/r/javascript/comments/xbb4ge/comment/inz5tbn) [my](https://www.reddit.com/r/javascript/comments/xbb4ge/comment/inyqdw3) [queries](https://dev.to/raibtoffoletto/comment/21hip) 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:

```ts
// ⚠️ 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
```typescript
client.query(sql`SELECT * FROM comments WHERE id = ${id}`);
          // ^^^ tagged templated literal
```



## What is this syntax?

It is called [Tagged templates](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals#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

```ts
// 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:

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

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

Given the following code:

```typescript
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:

```typescript
// 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`:

```typescript
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

```typescript
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](https://www.npmjs.com/package/@ts-safeql/sql-tag)
[sql-template-strings](https://www.npmjs.com/package/sql-template-strings)
[sql-template-tag](https://www.npmjs.com/package/sql-template-tag)


---

## What's next?

If you are using TypeScript and care about type safety, check out [SafeQL](https://safeql.dev). 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.