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

Does your table have a column called c1 or c2? The error suggests not, and hence the error. Also, what RDBMS are you really using. SQL Server and MySQL are completely different products. — Thom A
– Thom A ♦, Commented May 19, 2021 at 15:01
I suspect the problem is that you are injecting your values (insecurely?) and when you are you aren't wrapping them in single quotes; but without the application code that's impossible to know. If you're using SQL Server, I would suggest you look into table type parameters. — Thom A
– Thom A ♦, Commented May 19, 2021 at 15:03
table does not have column with those name. column name is employee as I have mentioned in query. I am using MySQL — K P
– K P, Commented May 19, 2021 at 15:03
"emp.category in ( c1,c2 )" will, however, be referencing the columns c1 and c2 not the literal strings with those values. "I am using MySQL" Then please correct your tags to retag mysql, as I removed the conflicting tags. — Thom A
– Thom A ♦, Commented May 19, 2021 at 15:05
We have injected values like this in whole project. It works when data is in numbers like id in ( 446766,446805). causing issue with string — K P
– K P, Commented May 19, 2021 at 15:05

lemon · Accepted Answer · 2022-05-15 00:24:02Z

2

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.

edited May 15, 2022 at 0:24

lemon

15.7k6 gold badges24 silver badges42 bronze badges

answered May 19, 2021 at 17:19

Harshad Akhani

544 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jerome WAGNER · Accepted Answer · 2021-05-19 17:24:28Z

0

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.

edited May 19, 2021 at 17:24

answered May 19, 2021 at 15:07

Jerome WAGNER

22.6k10 gold badges64 silver badges79 bronze badges

3 Comments

K P Over a year ago

I got this solution. I am already thinking about it. But not sure how will I make it like category : "'c1','c2'"

Jerome WAGNER Over a year ago

You need to explain how you query string is build. Do you use a library (might be a bug) or just concatenate strings (not advised for SQL query building) ?

K P Over a year ago

I am concatenating a string to make query but open to suggestion

Collectives™ on Stack Overflow

Unknown column c1 in WHERE clause node.js sql

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related