使用节点sqlite3的绑定变量SQL语句

问题描述：

我想转换此：

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) { 
  ...

要这样：

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

但我不断收到此错误：
错误：SQLITE_ERROR：近$表：语法错误

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

但我不知道什么是语法错在这里，因为格式是因为我已经API文档中看到它。我已经试过了，'@'，和'？''：'前，每个变量，但似乎没有得到认可。
什么是错误的，我code？

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?

答

绑定的参数只对 WHERE 子句中的值的工作。表和列名（统称标识）将无法正常工作。

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 + ' = ?'

不幸的是，节点sqlite3的未出现有一个。（

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

SQLite的确实提供了一个报价功能，的％重量运营商，但节点的sqlite3未出现，以使其可用。

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

您将不得不自己编写。按照从的指示在Python 这个答案，并将其转换为JavaScript。

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

确保该字符串可以连接codeD为UTF-8。

确保字符串中不包含任何NULL字符。

全部替换与。

裹在双引号整个事情。

我不是很好的JavaScript，因此我将离开你code这一点。

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

使用节点sqlite3的绑定变量SQL语句

相关推荐