sql-tagged-template-literal

npm install sql-tagged-template-literal

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

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, this module returns a string
• Unlike sql-concat, this module isn't great at building queries dynamically

Uses the 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

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

undefined is an unquoted NULL

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

Strings are escaped and quoted

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

Numbers are not quoted

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

Booleans are converted to text

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

Objects are JSONed, then escaped

MySQL has a JSON data type, after all.

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

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

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

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

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