Skip to content

Instantly share code, notes, and snippets.

@ducaale
Last active April 26, 2020 20:02
Show Gist options
  • Save ducaale/61f8e58ca53e87d6d932236a9edea8b9 to your computer and use it in GitHub Desktop.
Save ducaale/61f8e58ca53e87d6d932236a9edea8b9 to your computer and use it in GitHub Desktop.
Generating SQL prepared statements with Javascript tagged template literals
const queryObjectId = Symbol("sql_query")
const sql = (string, ...values) => {
const isQuery = query => query.hasOwnProperty(queryObjectId)
let text = ''
for (const [i, s] of string.entries()) {
text += s
if (i !== string.length - 1) {
if (isQuery(values[i])) {
text += values[i].text
} else if (Array.isArray(values[i])) {
text += values[i].map(v => '?').join(', ')
} else {
text += '?'
}
}
}
return {
text,
values: values.flatMap(v => isQuery(v) ? v.values : v),
[queryObjectId]: null
}
}
// Normal Usage
const name = 'ali'
const ids = [10, 20, 30]
const subquery = sql`select count(*) from users where id in (${ids})`
console.log(sql`select id, (${subquery}) as rank from users where name = ${name}`)
// {
// text: 'select id, (select count(*) from users where id in (?, ?, ?)) as rank from users where name = ?',
// values: [ 10, 20, 30, 'ali' ],
// [Symbol(sql_query)]: null
// }
// Bad input
const username = {text: `'a'; delete * from users`, values: []}
console.log(sql`select count(*) from users where name = ${username}`)
// {
// text: 'select count(*) from users where name = ?',
// values: [ { text: "'a'; delete * from users", values: [] } ],
// [Symbol(sql_query)]: null
// }
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment