![New axobject-query Maintainer Faces Backlash Over Controversial Decision to Support Legacy Node.js Versions](https://cdn.sanity.io/images/cgdhsj6q/production/86e6ebdea652d20da070ebbda20134b839972db7-1024x1024.webp?w=800&fit=max&auto=format)
Security News
New axobject-query Maintainer Faces Backlash Over Controversial Decision to Support Legacy Node.js Versions
A JavaScript library maintainer is under fire after merging a controversial PR to support legacy versions of Node.js.
sql-template-strings
Advanced tools
Readme
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):
let SQL = require('sql-template-strings')
let book = 'harry potter'
// mysql (for mysql2 prepared statements, just replace query with execute):
mysql.query('SELECT author FROM books WHERE name = ?', [book])
// is equivalent to
mysql.query(SQL`SELECT author FROM books WHERE name = ${book}`)
// postgres:
pg.query('SELECT author FROM books WHERE name = $1', [book])
// is equivalent to
pg.query(SQL`SELECT author FROM books WHERE name = ${book}`)
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.
Some values cannot be replaced by placeholders in prepared statements, like table names. In these cases, you need to use SQL.raw()
so the values get inserted directly. Please note that you are then responsible for escaping these values with proper escaping functions first if they come from user input. 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. Examples:
let table = 'books'
let order = 'DESC'
let column = 'author'
db.query(SQL`SELECT * FROM ${SQL.raw(table)} WHERE author = ${author} ORDER BY ${column} ${SQL.raw(order)}`)
// you MUST escape user input manually
mysql.query(SQL`SELECT * FROM ${SQL.raw(mysql.escapeId(someUserInput))} WHERE name = ${book} ORDER BY ${column} ${SQL.raw(order)}`)
pg.query(SQL`SELECT * FROM ${SQL.raw(pg.escapeIdentifier(someUserInput))} WHERE name = ${book} ORDER BY ${column} ${SQL.raw(order)}`)
// you might need to add quotes
pg.query(SQL`SELECT * FROM "${SQL.raw(table)}"`)
mysql.query(SQL`SELECT * FROM \`${SQL.raw(table)}\``)
// DONT DO THIS - THIS WILL OVERFLOW YOUR PREPARED STATEMENT BUFFER
for (let table of largeArray) {
// for every iteration, a new query will be prepared, even though it is only executed once.
// use mysql.query() instead.
mysql2.execute(SQL`SELECT * FROM ${SQL.raw(table)}`)
}
Postgres requires prepared statements to be named, otherwise the parameters will be escaped and replaced on the client side. You can still use SQL template strings though, you just need to assign a name to the query before using it:
// old way
pg.query({name: 'my_query', text: 'SELECT author FROM books WHERE name = $1', values: [book]})
// with template strings
let query = SQL`SELECT author FROM books WHERE name = ${book}`
query.name = 'my_query'
pg.query(query)
// or using lodash
pg.query(_.assign(SQL`SELECT author FROM books WHERE name = ${book}`, {name: 'my_query'}))
FAQs
ES6 tagged template strings for prepared statements with mysql and postgres
The npm package sql-template-strings receives a total of 37,592 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 JavaScript library maintainer is under fire after merging a controversial PR to support legacy versions of Node.js.
Security News
Results from the 2023 State of JavaScript Survey highlight key trends, including Vite's dominance, rising TypeScript adoption, and the enduring popularity of React. Discover more insights on developer preferences and technology usage.
Security News
The US Justice Department has penalized two consulting firms $11.3 million for failing to meet cybersecurity requirements on federally funded projects, emphasizing strict enforcement to protect sensitive government data.