binding variables to SQL statements using node-sqlite3

Question

I am trying to convert this:

var query_string = 'SELECT protein_A, protein_B, PIPE_score, site1_A_start FROM ' + organism + PIPE_output_table +
        ' WHERE ' + score_type + ' > ' + cutoff['range'] + ' AND protein_A = "' + item + '" ' +
        'UNION SELECT protein_A, protein_B, PIPE_score, site1_A_start FROM ' + organism + PIPE_output_table +
        ' WHERE ' + score_type + ' > ' + cutoff['range'] + ' AND protein_B = "' + item + '";';

 db.each(query_string, function (err, row) { 
  ...

To this:

var query_string = "SELECT protein_A, protein_B, PIPE_score, site1_A_start FROM $table WHERE $score_type > $score AND protein_A = '$protein'" +
        " UNION SELECT protein_A, protein_B, PIPE_score, site1_A_start FROM $table WHERE $score_type > $score AND protein_A = '$protein'";

    var placeholders = {
        $table: organism + PIPE_output_table,
        $score_type: score_type,
        $score: cutoff['range'],
        $protein: item
    };

    var stmt = db.prepare(query_string, placeholders, function(err) {
        console.log(err);
        stmt.each(function(err,row) {
            ...
        })
    }

but I keep getting this error: Error: SQLITE_ERROR: near "$table": syntax error

But I am not sure what is syntactically wrong here since the format is as I have seen it in the API documentation. I have tried '?', '@', and ':' before each variables but none seem to be recognized. What's wrong in my code?

Answer 1

Bind parameters only work for values in the WHERE clause. Table and column names (collectively called "identifiers") won't work.

"SELECT foo FROM bar WHERE this = $that"    # OK

"SELECT foo FROM bar WHERE $this = 'that'"  # Not OK

Normally you'd work around this by escaping and quoting identifiers and inserting them into the query. A good database library has a method call for this...

var this = db.quote_literal(input_column);
'SELECT foo FROM bar WHERE ' + this + ' = ?'

Unfortunately, node-sqlite3 doesn't appear to have one. :(

SQLite does provide a quoting function, the %w operator, but node-sqlite3 doesn't appear to make it available.

You'll have to write your own. Follow the instructions from this answer in Python and convert them to Javascript.

• Ensure the string can be encoded as UTF-8.
• Ensure the string does not include any NUL characters.
• Replace all " with "".
• Wrap the entire thing in double quotes.

I'm not very good with Javascript, so I'll leave you to code that.