# sql-tagged-template-literal

> ES6 SQL-escaping tagged template literal that spits out a sanitized SQL string

Latest version **1.3.0** (published 2024-10-17) · WTFPL license · 0 weekly downloads

## Install

```sh
npm install sql-tagged-template-literal
pnpm add sql-tagged-template-literal
yarn add sql-tagged-template-literal
bun add sql-tagged-template-literal
```

## Health

**Score 33/100 (F)** — status: maintenance-mode.

Positive: has types package; no vulnerabilities; high quality score.

Warnings: low downloads; no esm support.

Negative: stale; low maintenance score.

## Facts

| | |
|---|---|
| Version | 1.3.0 |
| Published | 2024-10-17 |
| First published | 2016-09-17 |
| Weekly downloads | 0 |
| License | WTFPL |
| TypeScript types | separate (@types/sql-tagged-template-literal) |
| Module format | CommonJS |
| Node | >=16.0.0 |
| Dependencies | 1 |
| Unpacked size | 5 KB |
| Known vulnerabilities | 0 |
| Install scripts | no |
| GitHub stars | 13 |
| Author | TehShrike |
| Maintainers | tehshrike |
| Keywords | sql, mysql, es6, tag, tagged, template, literal, string |

## Links

- npm: https://www.npmjs.com/package/sql-tagged-template-literal
- Repository: https://github.com/TehShrike/sql-tagged-template-literal
- Homepage: https://github.com/TehShrike/sql-tagged-template-literal#readme
- Issues: https://github.com/TehShrike/sql-tagged-template-literal/issues
- npm.io page: https://npm.io/package/sql-tagged-template-literal

## Dependencies (1)

- [sqlstring](https://npm.io/package/sqlstring.md) 2.0.1

## Alternatives

- [@mce/gif](https://npm.io/package/@mce/gif.md) — 2.6K weekly downloads
- [cleanse](https://npm.io/package/cleanse.md) — 173 weekly downloads
- [str](https://npm.io/package/str.md) — 127 weekly downloads
- [naming](https://npm.io/package/naming.md) — 95 weekly downloads
- [tap-telco-api](https://npm.io/package/tap-telco-api.md) — 19 weekly downloads

## Recent versions

- 1.3.0 (latest) — 2024-10-17
- 1.2.0 — 2022-12-29
- 1.1.0 — 2021-04-11
- 1.0.2 — 2017-08-08
- 1.0.1 — 2016-09-17
- 1.0.0 — 2016-09-17

## README

<!-- js
const sql = require('./')
-->

# sql-tagged-template-literal

```sh
npm install sql-tagged-template-literal
```

Useful for data dumps and other "just gimme a query" tasks.

```js
const userInput = `Robert'); DROP TABLE Students;--`

const query = sql`INSERT INTO awesome_table (sweet_column) VALUES (${userInput})`

query // => `INSERT INTO awesome_table (sweet_column) VALUES ('Robert\\'); DROP TABLE Students;--')`
```

- Unlike [node-sql-template-strings](https://github.com/felixfbecker/node-sql-template-strings), this module returns a string
- Unlike [sql-concat](https://github.com/TehShrike/sql-concat), this module isn't great at building queries dynamically

Uses the [sqlstring](https://github.com/mysqljs/sqlstring) library for escaping.

Only meant for escaping *values* - you shouldn't put table or column names in expressions.

## Escape mechanisms

### `null` is an unquoted NULL

```js
sql`SELECT ${null} IS NULL` // => `SELECT NULL IS NULL`
```

### `undefined` is an unquoted NULL

```js
sql`SELECT ${undefined} IS NULL` // => `SELECT NULL IS NULL`
```

### Strings are escaped and quoted

```js
sql`SELECT ${"what's up"} AS lulz` // => `SELECT 'what\\'s up' AS lulz`
```

### Numbers are not quoted

```js
sql`SELECT ${13} AS totally_lucky` // => `SELECT 13 AS totally_lucky`
```

### Booleans are converted to text

```js
sql`SELECT ${true} = ${false}` // => `SELECT true = false`
```

### Objects are JSONed, then escaped

MySQL has a [JSON](https://dev.mysql.com/doc/refman/5.7/en/json.html) data type, after all.

```js
const legitObject = { fancy: 'yes\'m' }

const jsonInsertQuery = sql`INSERT INTO document_store (json_column) VALUES (${legitObject})`

jsonInsertQuery // => `INSERT INTO document_store (json_column) VALUES ('{\\"fancy\\":\\"yes\\'m\\"}')`
```

### Arrays and Sets become comma separated with their values escaped

```js
const arrayQuery = sql`WHERE name IN(${[ `Alice`, userInput ]})`

arrayQuery // => "WHERE name IN('Alice', 'Robert\\'); DROP TABLE Students;--')"
```

```js
const mySet = new Set([ 1, 42 ])
sql`WHERE value IN(${ mySet })` // => "WHERE value IN(1, 42)"
```

```js
const twoDimensionalArray = [[`a`, 1], [`b`, 2], [`c`, 3]]
const twoDimensionalQuery = sql`INSERT INTO tablez (letter, number) VALUES ${twoDimensionalArray}`

twoDimensionalQuery // => `INSERT INTO tablez (letter, number) VALUES ('a', 1), ('b', 2), ('c', 3)`
```

# License

[WTFPL](http://wtfpl2.com/)

---
_Source: https://npm.io/package/sql-tagged-template-literal · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._