Query not working with SQL Template Strings npm

Question

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.

What npm library you use for DB access?

Martin Ždila
– Martin Ždila

2020-02-03 14:18:10 +00:00
Commented Feb 3, 2020 at 14:18 — Martin Ždila
– Martin Ždila, Commented Feb 3, 2020 at 14:18

Leo Aso · Accepted Answer · 2020-03-26 22:55:50Z

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.

GavinBelson · Accepted Answer · 2021-04-24 21:28:47Z

0

There is a way to do this with the template strings library by using append():

let queryString = SQL`SELECT * FROM `;
queryString.append(table); //put variable names into append

connection.query(queryString, (err, result) => {
  if(err) throw err;
  return cb(result);
})

answered Apr 24, 2021 at 21:28

GavinBelson

2,83429 silver badges41 bronze badges

Collectives™ on Stack Overflow

Query not working with SQL Template Strings npm

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related