Skip to content

Query not working with SQL Template Strings npm

I’m trying to use the sql template strings npm package to use template literals in my SQL queries securely, but I keep getting an error that states that there is a problem with the sql syntax. When I omit the “SQL” bit, everything works. I’ve installed the sql-template-strings package. What am I missing? Help appreciated!

// Import MySQL connection.
const connection = require("../config/connection.js");
let SQL = require('sql-template-strings');
//SQL queries
let orm = {    
    all: (table, cb) => {
        var queryString = SQL`SELECT * FROM ${table}`;    
        connection.query(queryString, (err, result) => {
            if(err) throw err;
            return cb(result);
        })
    }    
  };
module.exports = orm;

Error I get: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”reading” at line 1

Reading is the name of the table.

Console.logging queryString renders:

$ SQLStatement { strings: [ ‘SELECT * FROM ‘, ” ], values: [ ‘reading’ ] }

And the log from omitting the SQL keyword:

$ SELECT * FROM reading

I read through all the documentation here.

Answer

When you use sql-template-strings, the values interpolated into the query are passed as parameters to the query rather than being inserted directly into the query. But table names in a query have to be declared statically, they cannot be passed as parameters. That is why your query fails. To make it work, insert the table name directly into the query string, instead of using the SQL template string interpolation.

    var queryString = 'SELECT * FROM ' + table;    
    connection.query(queryString, (err, result) => {
        if(err) throw err;
        return cb(result);
    })

An important consequence of this is that appending things directly into your SQL query is incredibly dangerous if you are not certain of what the variable actually holds, so always make sure that table is actually a valid table name, and never do this if the variable contains user input.