Unknown column c1 in WHERE clause node.js sql

Question

I am getting data like this

category :  "c1,c2"

I am doing category.split(','); to get data like this

category:  [ 'c1', 'c2' ]

I want to pass into SQL query for IN ( ) operator like this but unable to figure out way.

SELECT * FROM employee emp WHERE .........   AND emp.category in ( 'c1','c2' ) ORDER BY.. 

right now if I pass category.toString() it goes like this

SELECT * FROM employee emp WHERE .........   AND emp.category in ( c1,c2 ) ORDER BY.. 

which gives Error : 'Unknown column 'c1' in 'where clause'',

Answer 1

As you noted, you need to have your query built as emp.category in ( 'c1','c2' ).

Your method currently generates emp.category in ( c1,c2 )

So you need a proper escaping of your values so that the ingested values are indeed quoted. It depends on how you build the query exactly, but a proper escaping mechanism should not have this problem.

start by testing with category : "'c1','c2'" just to wrap things in your head.

But you will surely need to dive into how your queries are built. An invalid escaping mechanism can lead to a lot of security issues as it will inevitably lead to SQL injections attacks.

In node.js land, you can use the https://www.npmjs.com/package/pg-format module in order to properly escape hand-built queries. You could also use a highler level query builder like knex.