A tagged template literals utility for ease of composing parameterized SQL queries
Template Literals
Since ES6 (JavaScript 2015), the “Template Literals” feature is available and provides an easy way to interpolate variables and expressions into strings. e.g.:
const firstName = "Joe";
const lastName = "Bloggs";const message = `Welcome ${firstName}, ${lastName}!`;
console.log(message);
// will output: Welcome Joe, Bloggs!
This syntax might also be handy when comes to the time when you have to manually compose SQL queries:
const sqlQuery = `SELECT * FROM users WHERE id='${userId}'`;However, this approach can (and often does) lead to SQL injection vulnerabilities as the parameter is directly embedded in the SQL string without proper processing.
Parameterized SQL queries
To avoid SQL injection vulnerabilities, most database client libraries support “parameterized SQL queries”. Take “node-postgres” for instance, you can send a parameterized SQL query as the followings:
client.query("SELECT * FROM users WHERE id = $1", [userId]);For the parameterized SQL query above, the parameter substitution doesn’t happen on the client side. In fact, the unaltered query text SELECT * FROM users WHERE id = $1 will be sent to the PostgreSQL server, together with the parameter value userId. The parameter will then be safely substituted into the query with the battle-tested parameter substitution code on the server-side.
Despite the benefit of SQL injection vulnerabilities prevention, it could be hard to construct complex parameterized SQL queries and maintain the correct parameter order in your code, especially when constructing queries containing conditional query conditions. Here is an example:
const parameters = [];
const conditions = [];if (req.query["field1"]) {
conditions.push("field1 = $1");
parameters.push(req.query["field1"]);
}
if (req.query["field2"]) {
conditions.push("field2 = $2");
/* The query might break here. when `req.query["field1"]` doesn't have a value, this will be the first condition. Thus, thr query string should be "field2 = $1". */
parameters.push(req.query["field2"]);
}const where = conditions.length ? " WHERE " + conditions.join(" AND ") : "";client.query("SELECT * FROM users" + where, parameters);
Tagged Template Literals
When look at the problem above closely, we will realise the problem comes from the needs of concatenating query fragments, while keep the context information of involved parameters with query fragments. “Tagged template literals” are just the right tools to solve the problem here.
A tagged template literal is a more advanced form of the template literal. It allow you to parse the template literal with a function. The function receives a list of placeholder values and return a value as the string interpolation result. The string interpolation result is often a string. But the function can choose to return a more advanced data structure (e.g. an object) that carries both query text string and involved parameters as well. This allow us to always process query text string & involved parameters as a whole during the string interpolation / complex query construction.
The SQLSyntax Utility
The “SQLSyntax” is a nodejs library that is created based on the similar idea above. The original idea is actually from the popular ScalikeJDBC library from the scala world. The “SQLSyntax” library offers a sqls function that always produces an instance of SQLSyntax class as the string interpolation result. By leveraging es6’s Tagged Template Literals feature, users can enjoy the convenience of template literals syntax, while making sure the context information of involved parameters is always proper maintained and go with the query fragments where the parameters are involved.
Here is a simple example shows its basic usage:
import SQLSyntax, {sqls} from "sql-syntax";// the return value is an instance of SQLSyntax class
const query:SQLSyntax = sqls`SELECT * FROM users WHERE user_id = ${userId} AND number = ${number}`;// we can generate SQL query text string & binding parameters array for querying database.
const [sql, parameters] = query.toQuery();// sql: "SELECT * FROM users WHERE user_id = $1 AND number = $2"
// parameters: [userId, number]
const result = await client.query(sql, parameters);// Or more concisely using spread syntax for function calls
const result = await client.query(...query.toQuery());
The sqls function can also recognise any SQLSyntax class instance values that are passed as string interpolation values. When it happens, the sqls function will merge the SQL query text string of all involved query fragments (that are represented by SQLSyntax class instance values), create a new parameter list and return a new SQLSyntax class instance.
Here is an example:
import SQLSyntax, {sqls} from "sql-syntax";// create 2 query fragments that involve parameters
const condition1:SQLSyntax = sqls`user_id = ${userId}`;
const condition2:SQLSyntax = sqls`number = ${number}`;const [sql1, parameters1] = condition1.toQuery();
// sql1: "user_id = $1"
// parameters1: [userId]// Create the final query by passing the query fragments
const query:SQLSyntax = sqls`SELECT * FROM users
WHERE ${sqls`field1=${field1Val}`} AND ${condition1} AND ${condition2}`;const [sql, parameters] = query.toQuery();
// sql: "SELECT * FROM users WHERE field1=$1 AND user_id=$2 AND number=$3"
// parameters1: [field1Val, userId, number]// execute the query
const result = await client.query(...query.toQuery());
Support for Other Databases
The default toQuery method of SQLSyntax object will generate SQL text string targeting postgreSQL. If it doesn't work for you, you can replace the logic with your own implementation:
import SQLSyntax from "sql-syntax";SQLSyntax.customToQueryFunc = (s:SQLSyntax) => {
//you own implementation...
}
Try it out
To try it out, you can install the library using yarn or npm by:
// if you use npm
npm install sql-syntax// if you use yarn
yarn add sql-syntax
You can also visit its github repo: https://github.com/t83714/SQLSyntax or API documents for more information.
