MySQL Spaces in string causing issue

Question

react & node app, Endpoint looks like this =>

   app.get("/query", (req, res) => {
    con.query(
    `SELECT (${req.query.filter}),
     count("${req.query.filter}") AS count,
     AVG(age) AS averageAge
     from census_learn_sql
     GROUP BY (${req.query.filter})
     LIMIT ${req.query.limit}`,
    (err, result, fields) => {
      err ? err : res.send(result);
    }
  );
});

when req.query.filter is a string without spaces, like 'education'... this works. When I try and find a column name that has spaces such as 'this has spaces' this query doesn't work. How can I fix this?

What does it mean, "query doesn't work"? Does it result in error message? — Roman Hocke, Feb 20 '19 at 20:51
Hi Daniel, this is not directly related to your question, but judging from you code, may I point you that definitely risk a SQL injection... https://en.wikipedia.org/wiki/SQL_injection. — Gorille, Feb 20 '19 at 20:55

score 1 · Answer 1 · answered Feb 20 '19 at 20:52

I think back-ticks should help:

con.query(
`SELECT (\`${req.query.filter}\`),
 count(\`${req.query.filter}\`) AS count,
 AVG(age) AS averageAge
 from census_learn_sql
 GROUP BY (\`${req.query.filter}\`)
 LIMIT ${req.query.limit}`,
(err, result, fields) => {
  err ? err : res.send(result);
}

);

And imho you don't need braces all around but just when you have some aggregation function call like COUNT:

con.query(
`SELECT \`${req.query.filter}\`,
 count(\`${req.query.filter}\`) AS count,
 AVG(age) AS averageAge
 from census_learn_sql
 GROUP BY \`${req.query.filter}\`
 LIMIT ${req.query.limit}`,
(err, result, fields) => {
  err ? err : res.send(result);
}

);

And I would strongly recommend to avoid column names to have spaces.

MySQL Spaces in string causing issue

1 Answers1