CODEWITHSUNDEEP
javascriptmysqlsqlnode.js
K P
K P

Reputation: 874

Unknown column c1 in WHERE clause node.js sql

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'',

Upvotes: 1

Views: 612

Answers (2)

Harshad Akhani
Harshad Akhani

Reputation: 54

You should try the Paramtries query it will solve your problem.

pass ( ? ) like this

SELECT * 
FROM employee emp 
WHERE .........   
  AND emp.category IN ( ? ) 
ORDER BY ..

You can pass your value in Parameter Array.

Upvotes: 2

Jerome WAGNER
Jerome WAGNER

Reputation: 22422

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.

Upvotes: 0

Related Questions