7

Here is a line from my node js code:

var qry = 'INSERT INTO "sma"."RMD"("UserId","Favourite")  VALUES (' + req.body.user + ',' + JSON.stringify(req.body.favourite) + ')'

I want to insert single quotes before JSON.stringify(req.body.favourite). But i'm unable to escape the single quotes. I can't debug the issue since I'm uploading the code to a predix cloud server Any idea how to achieve that?

iJade
  • 23,144
  • 56
  • 154
  • 243
  • 2
    For the love of.... USE PARAMETERIZED QUERIES!!!!!!!!!!!!!! Don't ever write code where you assemble the query like you do in your question. You are vulnerable to [SQL-INJECTION-ATTACS](https://en.wikipedia.org/wiki/SQL_injection). – some Jan 19 '17 at 13:59
  • 1
    You should never do it! Let the database library escape the values correctly as part of the query formatting. – vitaly-t Jan 19 '17 at 14:06
  • 1
    The question is legit there are situations where one might need it e.g getting time interval '1 DAY' – amar Oct 07 '19 at 15:26

2 Answers2

8

Why don't you use prepared statements?

var qry = 'INSERT INTO "sma"."RMD"("UserId","Favourite")  VALUES ($1, $2)';
client.query(qry, [ req.body.user, JSON.stringify(req.body.favourite])'
e4c5
  • 52,766
  • 11
  • 101
  • 134
3

You could also use template literals to build your query

Template literals which use the back-tick character Check main answer here

alert(`Use "double" and 'single' quotes in the same string`);
alert(`The escape the \` back-tick character in a string`);

Template literals offer a clean syntax for: variable interpolation, multi-line strings, and more.

Community
  • 1
  • 1
Rocha
  • 31
  • 1
  • This should be the accepted answer to the question asked. Though OP is doing it wrong by string formatting the query still there are cases when you need to escape quotes within query e.g using time interval '1 Day' – amar Nov 14 '19 at 09:12