6

I want to do a raw query using Sequelize and use replacements to avoid sql injection: 

var sequelize = require('sequelize');
sequelize.query("SELECT * FROM table where name =:name ORDER BY :age:direction",
{replacements:{name:"test", age:"age", direction:"desc"}, type: sequelize.QueryTypes.SELECT })

This will be converted to following query

SELECT * 
FROM table 
WHERE name = 'test' 
ORDER BY 'age' 'desc'

Since the order by column is having single quotes and direction also with single quotes, postgres throws error

Can anyone suggest how do I solve this problem with replacements in place?

melpomene
  • 84,125
  • 8
  • 85
  • 148
Manu
  • 3,979
  • 7
  • 21
  • 25
  • IMHO, age and desc shouldn't be parameters but part of the raw query: "SELECT * FROM table where name =:name ORDER BY age desc" – Cetin Basoz Jul 23 '15 at 10:10
  • the order by column is dynamic so it will be part of replacements. we dont want it to be parameters. – Manu Jul 23 '15 at 10:20
  • 1
    @Manu I have a similar issue. Did you resolve this? – Sealer_05 Aug 26 '16 at 22:31
  • As far as I know this can't be done with replacements because they only support values, not syntactic elements of the SQL language (such as `DESC`). – melpomene Dec 30 '16 at 12:37
  • Possible duplicate of [Using replacements with a raw Sequelize query: avoiding single quotes?](http://stackoverflow.com/questions/26092913/using-replacements-with-a-raw-sequelize-query-avoiding-single-quotes) – melpomene Dec 30 '16 at 12:39
  • i am facing same problem, @Manu have you got solution? – Vinay Pandya Sep 12 '18 at 10:24

1 Answers1

1

As a workaround I created the query and the sort order by separately and then concatenate them as follow:

const query= `SELECT * FROM table where name =:name ORDER BY :age`;
let sortOrder = `DESC`
sequelize.query(`${query} ${sortOrder}`, {replacements:{name:"test", age:"age"}, type: sequelize.QueryTypes.SELECT })

being there is just about play with the sortORder

oskrgg
  • 101
  • 1
  • 9