如何防止使用Node.JS进行SQL注入? [英] How can I prevent SQL injection with Node.JS?

问题描述

如何使用Node.JS选择一排MS SQL Server数据库以防止SQL注入?我使用express框架和包 mssql .

How could I select a row of MS SQL server database with Node.JS with preventing SQL injection? I use the express framework and the package mssql.

这是我现在使用的代码的一部分，有可能以ES 6编写SQL注入.

Here is a part of my code I use now with a possibility to SQL injection written in ES 6.

const express = require('express'),
      app = express(),
      sql = require('mssql'),
      config = require('./config');

let connect = (f, next) => {
    sql.connect(config.database.connectionstring).then(f).catch((err) => {
        next(err);
    });
};

app.get('/locations/get/:id', (req, res, next) => {
    let f = () => {
        new sql.Request().query(`select * from mytable where id = ${req.params.id}`)
                         .then((recordset) => {
            console.dir(recordset);
        }).catch((err) => {
            next(err);
        });
    };

    connect(f, next);
});

推荐答案

使用PreparedStatement.这是您从文档 https://www.npmjs.com/package进行操作的方法/mssql#prepared-statement :

Use a PreparedStatement. Here is how you do it from the docs https://www.npmjs.com/package/mssql#prepared-statement :

var ps = new sql.PreparedStatement(/* [connection] */);
ps.input('id', sql.Int);
ps.prepare('select * from mytable where id = @id', function(err) {
  ps.execute({id: req.params.id}, function(err, recordset) {
    ps.unprepare(function(err) {
        // ... error checks 
    });

    // Handle the recordset
  });
});

请记住，每个准备好的语句表示池中有一个保留的连接.不要忘记准备一份准备好的声明！

Remember that each prepared statement means one reserved connection from the pool. Don't forget to unprepare a prepared statement!

您还可以在事务中创建准备好的语句(新的sql.PreparedStatement(transaction))，但是请记住，只有调用unpreepare才能在事务中执行其他请求.

You can also create prepared statements in transactions (new sql.PreparedStatement(transaction)), but keep in mind you can't execute other requests in the transaction until you call unprepare.

文档是用ES5编写的，但是我可以确保将其Promisify:)

The docs are written in ES5 but I', sure you can Promisify it :)

这篇关于如何防止使用Node.JS进行SQL注入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！