使用节点sqlite3的绑定变量SQL语句 [英] binding variables to SQL statements using node-sqlite3

问题描述

我想转换此：

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语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！