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?

Community · Accepted Answer · 2017-05-23 12:04:04Z

2

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.

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Dec 16, 2015 at 10:52

Schwern

167k28 gold badges225 silver badges370 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Haitham Fallatah Over a year ago

Oh I see, Thanks again for your help :)

Collectives™ on Stack Overflow

binding variables to SQL statements using node-sqlite3

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related