Are SQL placeholders safe?


#1

Question

Are SQL placeholders safe?

Answer

Placeholders are a very efficient way to pass values to a SQL query but they can be a target to value injection which simply is when someone, not the user will use the placeholder to insert a value that can recover sensitive data. For that reason, masking placeholders with ? can be a safer approach, for example, instead of:

//using node SQLite
const selectByGenre = genre => {
  db.all('SELECT title FROM Song WHERE genre = $genre', {
    $genre: genre
  }, (err, row)=>{
    console.log(row);
  })
}

//this would slightly better

const selectByGenre = g => {
  db.all('SELECT title FROM Song WHERE genre = ?', 
    g, (err, row)=>{
    console.log(row);
  })
}

It is a slight difference but it keeps the query cleaner ('sanitized' by SQLite as it is called) which means it does not specify what kind of property it is, the only thing to watch for is that the amount and order of the ? placeholders have to match the values passed.