如何在 Node.JS 中为 MSSQL 创建准备好的语句? [英] How do I create a prepared statement in Node.JS for MSSQL?

问题描述

我需要将 Javascript 中定义的字符串插入到 MSSQL 表中.

I need to insert a string defined in Javascript into an MSSQL table.

这是我目前所拥有的:

Javascript:

var message = "It's a great day today!";  
$.post('www.server.com/message='+message, function(response){
console.log(response);
});

Node.js 服务器:

//..... a bunch of code has been used to accept the HTTP request and get the message...
// for the purpose of this example, the message is asigned to 'NodeMsg'
var mssqldb = require("../core/mssql"); 
var NodeMsg = theMessageReceivedFromHTTPRequest;
function saveMessage(message) {
    mssqldb.executeMssql("insert into messages (message, status) VALUES('"+message+"', 'new')", function (err) {
      if (err) {
        httpMsgs.show500(req, resp, err);
      }//end if
      else {
        httpMsgs.sendJson(req, resp, 'success');
      }//end else
    });
};

mssql.js(node.js 文件):

var mssqldb = require("mssql");
var settings = require("../settings");

exports.executeMssql  = function(sql, callback) {
  var conn = new mssqldb.Connection(settings.mssqlConfig);
  conn.connect()
  .then(function(){
    var req = new mssqldb.Request(conn);
    req.query(sql)
    .then(function (recordset) {
      callback(recordset);
    })
    .catch(function(err){
      console.log(err);
      callback(null, err);
    });
  })
  .catch(function(err){
    console.log(err);
    callback(null, err);
  });
};//end executeMssql

正在发生的事情摘要:

1. 我将消息定义为字符串.(注意它包含一个单引号)
2. 我正在将该字符串发送到 Node.js 服务器
3. 我正在使用 Node.js 将字符串发送到 mssql

问题:在了解了更多关于面向对象程序的知识后，我意识到我执行插入的方式是非常不受欢迎的，因为它对 SQL 注入是开放的.此外，由于字符串中的单引号，代码将在执行 SQL 查询期间中断.

The Problem: After learning more about Object Oriented Program, I have come to the realisation that the way I am performing the insert is highly frowned upon because It is open to SQL injection. Also, the code will break during the execution of the SQL query because of the single quote in the string.

解决办法:根据我找到的大多数资源，解决方案是使用Prepared Statements".

The solution: According most resources I have found, the solution is to use "Prepared Statements".

我的问题:

我到底如何转换我已经完成的工作，以使用准备好的语句?我在网上搜索了 HOURS，但找不到一个很好的示例，说明如何使用 Node.JS 为 MSSQL(不是 MySQL)执行准备好的语句，这对于 Node.Js 的初学者来说是可以理解的

How on earth do I convert what I have already done, to utilise prepared statements? I have searched the web for HOURS and I cannot find one good example of how to do a prepared statement with Node.JS for MSSQL (not MySQL) which is comprehensible for a beginner to Node.Js

推荐答案

如果您使用的是 Tedious 跨平台 MSSQL 驱动程序实现，文档在此处:http://tediousjs.github.io/tedious/parameters.html

If you are using the Tedious cross-platform MSSQL driver implementation, the documentation is here: http://tediousjs.github.io/tedious/parameters.html

基本上，您为需要注入的值准备了一个带有@xxx"占位符的 SQL 语句，然后将这些参数的实际值绑定到您的请求，然后执行您的请求.

Basically you prepare a SQL statement with '@xxx' placeholders for values you need to inject, the you bind the actual values of those parameters to your request, then execute your request.

这篇关于如何在 Node.JS 中为 MSSQL 创建准备好的语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！