Quotationmark problem with Knex and SQL-database

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"

1 answer

  • answered 2018-11-08 08:07 Bardi Harborow

    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.