Skip to content
Advertisement

What is the correct syntax for replacements within a SQL string

I am attempting to do a basic search in my sqlite3 database with this JavaScript:

function search(query) {
  let sql = `SELECT * from tracks WHERE title LIKE '%%${query}%%'`

  db.sqlite.all(sql, [], (err, rows) => {
    console.log(rows)
  })
}

This works but is vulnerable to SQL injection. I am trying to get the sqlite replacements parameter to work but I cannot get the syntax right. Sqlite doesn’t want to replace anything within the string literal.

// throws SQLITE_RANGE error
let sql = `SELECT * from tracks WHERE title LIKE '%%$query%%'`
db.sqlite.all(sql, {$query: query}, (err, rows) => {})

What is the correct way to write this so that it’s not vulernable to SQL injection?

Advertisement

Answer

I figured it out

let sql = `SELECT * from tracks WHERE title LIKE '%%' || $query || '%%'`

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement