sql - 查询中的 Node mssql 绑定(bind)

Question

我是 SQL Server 新手。当我使用 MySQL 时，使用“?”绑定(bind)变量是非常容易的。但是，我不知道如何在 mssql 中绑定(bind)变量。

我尝试过这个:

const pool = new SQL.ConnectionPool(config, function (err) {
    console.log('Connected to SQL server successfully');
}); 

var Myquery = "INSERT INTO person (idNumber, forename, surname, age, address, maritalStatus)" +
        " VALUES( " + req.body.idNumber + ", " + req.body.forename + ", " + req.body.surname +
        ", " + req.body.age + ", " + req.body.address + ", " + req.body.maritalStatus + " )";

pool.request().query(Myquery, function (err, result) {
           res.json(result);
        })

我收到此错误:

Invalid column name 'single'.

但是，当我直接在 SQL Server 中执行此处创建的查询 (Myquery) 时，一切都很顺利。我怎样才能解决这个问题？

编辑:

const pool = new SQL.ConnectionPool(config, function (err) {
    console.log('Connected to SQL server successfully');
});

const ps = new SQL.PreparedStatement(pool);
ps.input('param', SQL.NVarChar);
ps.prepare('SELECT * FROM @param', function (err) {
     if (err) console.log('error: ' + err);
    else {
        ps.execute({param: 'person'}, function (err, result) {
            console.log(result);
        })
    }
});

error: ConnectionError: Connection not yet open.

我也用过这个:

const pool = new SQL.ConnectionPool(config, function (err) {
console.log('Connected to SQL server successfully');
});
pool.request().input('param', SQL.NVarChar, 'person')
.query("SELECT * FROM @param", function (err, result) {
    if (err) console.log('error: ' + err);
    console.log(result);
});

error: ConnectionError: Connection is closed.

Answer 1

您的文本值需要用单引号括起来:

const pool = new SQL.ConnectionPool(config, function (err) {
    console.log('Connected to SQL server successfully');
}); 

    var Myquery = "INSERT INTO person (idNumber, forename, surname, age, address, maritalStatus)" +
        " VALUES( " + req.body.idNumber + ", '" + req.body.forename + "', '" + req.body.surname +
        "', " + req.body.age + ", '" + req.body.address + "', '" + req.body.maritalStatus + "' )";

    pool.request().query(Myquery, function (err, result) {
           res.json(result);
        })

以这种方式基于输入创建查询也是一个 super 坏主意，因为它允许 SQL 注入(inject)。您应该使用 @parameters ( https://blogs.msdn.microsoft.com/sqlphp/2008/09/30/how-and-why-to-use-parameterized-queries/ )