Vercel recently announced the launch of Vercel Postgres — a Serverless relational data store powered by Neon.

To streamline the experience, Vercel also published the  @vercel/postgres SDK, a package you can use to connect to your database from serverless and edge environments. 

If you use node-postgres, there are typically two ways to connect to a Postgres database:

1. Creating a Client() instance, which creates an individual client.
2. Creating a Pool() instance, which allows you to have a reusable pool of clients.

However, in addition to the two options mentioned above, @vercel/postgres offers another way to connect to a database and send queries:

Many people who are unfamiliar with this syntax,  think the code above is vulnerable to SQL injection attacks. Fortunately, this is not the case. 

In this article, we will explain this syntax, how it works under the hood, and why it is not vulnerable to SQL injection attacks. 

Overview of SQL injection attacks

A SQL injection is an attack where a malicious query is passed and executed by the database. These queries can allow the attacker to access or modify data within the database.

Here is an example:

This SQL query is vulnerable to SQL injection attacks because the query builds the SQL statement by directly inserting user input into the query string without proper sanitization and validation (if you are unfamiliar with this syntax, it is using template literals, which allow for string interpolation).

An attacker can then pass a value that will execute a SQL query. For example, if params.user has a value of  "'; DROP TABLE USERS; --";, the resulting query would become:

This query would execute the SELECT statement but also execute a SQL injection attack and drop the USERS table, causing data loss.

To properly pass parameters to a query, you must use a parameterized query:

The client.query() function takes two arguments: a SQL query string and an array of values that will be used in the query. Parameters in the query string are denoted by a dollar sign followed by their order in the parameter array.

$1 will be sanitized and safely replaced by the first parameter in the parameter array, in this case, the user ID.

So, how is writing sql followed by a string wrapped in backticks not vulnerable to an injection? The reason is that this is not a regular template literal but a template literal combined with a tag function. 

Understanding template literal tag functions

In JavaScript, you can define a function that processes a string literal. This is known as a “tag function”. 

Here is an example of a function that takes a name parameter and outputs a greeting message.

It can be rewritten as a tag function:

The first argument received by the tag function is an array of strings. It will contain the template literal split by the values which will be interpolated (so anything wrapped in ${…}). The second argument is the value that will be interpolated.

Notice that you can now call the function by writing the function name followed by the string delimited by backticks. This improves readability and makes it easier to understand your code.

But what if you want to create a tag function that can accept an unknown number of arguments? Here is how you would do it:

The first argument remains unchanged. As for the second argument, rather than use a fixed set of variables to pass to the template literal, we use the rest parameter syntax to capture all the values passed into the template literal and store them in an array called “values”. 

The function loops through the strings array using the forEach method and appends each string to a variable called str. It then checks if there is a corresponding value in the values array for each string, and if one is found, appends it to the greeting message. 

Finally, the function returns the greeting message.

The @vercel/postgres package uses the same concept for defining the sql function, which uses a custom tag function:

The difference is rather than returning a string, the function returns two values: the query and the parameters that were passed to the template literal.

These two values are then passed to the query() function, following the parametrized query approach, which prevents SQL injection attacks.

This is a common pattern in modern JS SQL libraries and is not just specific to the @vercel/postgres SDK.

Conclusion

In this post, you learned about SQL injection attacks, how SQL template tags work, and why they are not vulnerable to SQL injection attacks. 

To learn more about the @vercel/postgres SDK, refer to the API Reference. 

If you are new to Neon, you can sign up today for free. No credit card required.