0

I'm trying to save a query to a database using Knex. But if this query I'm trying to save includes quotation marks I'm getting an error.

Here's an example of how the code might look like: 

db.query(`INSERT INTO test.searches VALUES ('TestUser', 'testqueryname', ''SELECT * FROM table WHERE team='rocket'')`, info, () =>{}

Of course in the real case I'm not sending in hardcoded values but variables as strings.

Trying to save this gets me the error

error: syntax error at or near "rocket"

Tân
  • 1
  • 15
  • 56
  • 102
Koofi
  • 1
  • 1

2 Answers2

1

To start with looks like your code has javascript syntax error at least closing parenthesis is missing.

Secondly knex does not have .query() method (maybe you are using database driver directly?). You should probably be using knex.raw() even that query above could be easily written with normal knex methods.

If you are using knex.raw you can do quoting with ?? replacements and value bindings wit ?.

If you are writing your code like in the question you have no advantage at all from using knex and you should be using database driver directly instead.

Mikael Lepistö
  • 18,909
  • 3
  • 68
  • 70
0

If you are running MySQL, you need to slash escape quotation marks:

db.query(`INSERT INTO test.searches VALUES ('TestUser', 'testqueryname', 'SELECT * FROM table WHERE team=\'rocket\'')`, info, () =>{}

If you are running PostgreSQL, you need to double escape quotation marks:

db.query(`INSERT INTO test.searches VALUES ('TestUser', 'testqueryname', 'SELECT * FROM table WHERE team=''rocket''')`, info, () =>{}

Please consider using parameterised SQL queries or at least appropriately escaping input to avoid SQL injection attacks.

Bardi Harborow
  • 1,803
  • 1
  • 28
  • 41