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)
  })
}

​x
 
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) => {})

 
// throws SQLITE_RANGE errorlet 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?

Answer

I figured it out

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

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

Advertisement

Answer