Security News
Highlights from the 2024 Rails Community Survey
A record 2,709 developers participated in the 2024 Ruby on Rails Community Survey, revealing key tools, practices, and trends shaping the Rails ecosystem.
sql-template-strings
Advanced tools
ES6 tagged template strings for prepared statements with mysql and postgres
A simple yet powerful module to allow you to use ES6 tagged template strings for prepared/escaped statements in mysql / mysql2 and postgres.
Example for escaping queries (callbacks omitted):
const SQL = require('sql-template-strings')
const book = 'harry potter'
const author = 'J. K. Rowling'
// mysql:
mysql.query('SELECT author FROM books WHERE name = ? AND author = ?', [book, author])
// is equivalent to
mysql.query(SQL`SELECT author FROM books WHERE name = ${book} AND author = ${author}`)
// postgres:
pg.query('SELECT author FROM books WHERE name = $1 AND author = $2', [book, author])
// is equivalent to
pg.query(SQL`SELECT author FROM books WHERE name = ${book} AND author = ${author}`)
This might not seem like a big deal, but when you do an INSERT with a lot columns writing all the placeholders becomes a nightmare:
db.query(
'INSERT INTO books (name, author, isbn, category, recommended_age, pages, price) VALUES (?, ?, ?, ?, ?, ?, ?)',
[name, author, isbn, category, recommendedAge, pages, price]
)
// is better written as
db.query(SQL`
INSERT
INTO books
(name, author, isbn, category, recommended_age, pages, price)
VALUES (${name}, ${author}, ${isbn}, ${category}, ${recommendedAge}, ${pages}, ${price})
`)
Also template strings support line breaks, while normal strings do not.
The SQL template string tag transforms the template string and returns an object that is understood by both mysql and postgres:
const query = SQL`SELECT author FROM books WHERE name = ${book} AND author = ${author}`
typeof query // => 'object'
query.text // => 'SELECT author FROM books WHERE name = $1 AND author = $2'
query.sql // => 'SELECT author FROM books WHERE name = ? AND author = ?'
query.values // => ['harry potter', 'J. K. Rowling']
append()
It is also possible to build queries by appending another query or a string with the append()
method (returns this
for chaining):
query.append(SQL`AND genre = ${genre}`).append(' ORDER BY rating')
query.text // => 'SELECT author FROM books WHERE name = $1 AND author = $2 AND genre = $3 ORDER BY rating'
query.sql // => 'SELECT author FROM books WHERE name = ? AND author = ? AND genre = ? ORDER BY rating'
query.values // => ['harry potter', 'J. K. Rowling', 'Fantasy'] ORDER BY rating
This allows you to build complex queries without having to care about the placeholder index or the values array:
const query = SQL`SELECT * FROM books`
if (params.name) {
query.append(SQL` WHERE name = ${params.name}`)
}
query.append(SQL` LIMIT 10 OFFSET ${params.offset || 0}`)
Some values cannot be replaced by placeholders in prepared statements, like table names.
append()
replaces the SQL.raw()
syntax from version 1, just pass a string and it will get appended raw.
Please note that when inserting raw values, you are responsible for quoting and escaping these values with proper escaping functions first if they come from user input (E.g.
mysql.escapeId()
andpg.escapeIdentifier()
). Also, executing many prepared statements with changing raw values in a loop will quickly overflow the prepared statement buffer (and destroy their performance benefit), so be careful.
const table = 'books'
const order = 'DESC'
const column = 'author'
db.query(SQL`SELECT * FROM "`.append(table).append(SQL`" WHERE author = ${author} ORDER BY ${column} `).append(order))
// escape user input manually
mysql.query(SQL`SELECT * FROM `.append(mysql.escapeId(someUserInput)).append(SQL` WHERE name = ${book} ORDER BY ${column} `).append(order))
pg.query(SQL`SELECT * FROM `.append(pg.escapeIdentifier(someUserInput)).append(SQL` WHERE name = ${book} ORDER BY ${column} `).append(order)))
Postgres requires prepared statements to be named, otherwise the parameters will be escaped and replaced on the client side.
You can set the name with the setName()
method:
// old way
pg.query({name: 'my_query', text: 'SELECT author FROM books WHERE name = $1', values: [book]})
// with template strings
pg.query(SQL`SELECT author FROM books WHERE name = ${book}`.setName('my_query'))
You can also set the name property on the statement object directly or use Object.assign()
.
FAQs
ES6 tagged template strings for prepared statements with mysql and postgres
The npm package sql-template-strings receives a total of 39,320 weekly downloads. As such, sql-template-strings popularity was classified as popular.
We found that sql-template-strings demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
A record 2,709 developers participated in the 2024 Ruby on Rails Community Survey, revealing key tools, practices, and trends shaping the Rails ecosystem.
Security News
In 2023, data breaches surged 78% from zero-day and supply chain attacks, but developers are still buried under alerts that are unable to prevent these threats.
Security News
Solo open source maintainers face burnout and security challenges, with 60% unpaid and 60% considering quitting.