如何在nodejs消毒输入，以防止sql注入?

Question

我有这个清理函数

sanitizeXSS: string => {
const map = {
'&': '&',
'<': '&lt;',
'>': '&gt;',
'"': '&quot;',
"'": '&#x27;',
"/": '&#x2F;',
};
const reg = /[&<>"'/]/ig;
return string.replace(reg, (match)=>(map[match]));
}

这里使用了这个消毒函数

addOrUpdateAddress : function (request, resolve) 
{

const customerKey = sanitizeXSS(decrypt_key(request.customer_key));
const lat = sanitizeXSS(decrypt_key(request.lat));
const lng = sanitizeXSS(decrypt_key(request.lng));
const line1 = sanitizeXSS(decrypt_key(request.line1));
const line2 = sanitizeXSS(decrypt_key(request.line2));
const city = sanitizeXSS(decrypt_key(request.city));
const pincode = sanitizeXSS(decrypt_key(request.pincode));
const state = sanitizeXSS(decrypt_key(request.state));

const contact = sanitizeXSS(decrypt_key(request.contact));
const landmark = request.landmark?sanitizeXSS(decrypt_key(request.landmark)):null;
let req=request;
if(req.name && req.email)
{

updateUser(req,function(err,result)
{
console.log(err);
console.log(result);
});
}
let addressId = (!req.address_id || req.address_id == null || req.address_id == '')
? -1 : req.address_id;
console.log(addressId);
async.auto({
serviceability : function (cb)
{
searchServiceArea(req,function(err,result)
{
if(err)
{
resolve(null, {'errorMessage':'Address selected not serviceble'}, 203, 'error');
}
else
{
if(!result.hub_id)
{
resolve(null, {'errorMessage':'Address selected not serviceble'}, 203, 'error');
}
if(addressId == -1)
{
let s=mysql.write('customer_address').insert({customer_key: customerKey,line1:line1,line2:line2,lat: lat,lng: lng,city: city,state: state,pincode: pincode,contact: contact,landmark : landmark,updated_at:moment().format("YYYY-MM-DD HH:mm:ss"),created_at:moment().format("YYYY-MM-DD HH:mm:ss")}).then(function(res) 

{
if(res.length > 0)
{
cb(null,{address_id:res[0],customer_key: customerKey,line1:line1,line2:line2,lat: lat,lng: lng,city: city,state: state,pincode: pincode,contact: contact,landmark : landmark,updated_at:moment().format("YYYY-MM-DD HH:mm:ss"),created_at:moment().format("YYYY-MM-DD HH:mm:ss"),'hub_id':result.hub_id})
}
else
{
cb(true,{'errorMessage':'Unable to add address try again'})
}
});
}
else
{
let s=mysql.write('customer_address').update({line1:line1,line2:line2,lat: lat,lng: lng,city: city,state: state,pincode: pincode,contact: contact,landmark : landmark,updated_at:moment().format("YYYY-MM-DD HH:mm:ss")}).where({customer_key:customerKey, address_id:addressId}).then(function(res)
{
console.log(res)
if(res == 1)
{
cb(null,{address_id:addressId,customer_key: customerKey,line1:line1,line2:line2,lat: lat,lng: lng,city: city,state: state,pincode: pincode,contact: contact,landmark : landmark,updated_at:moment().format("YYYY-MM-DD HH:mm:ss"),created_at:moment().format("YYYY-MM-DD HH:mm:ss"),'hub_id':result.hub_id})
}
else
{
cb(true,{'errorMessage':'Unable to update address try again'})
}

},
},
);

}

因此，在插入地址时，我正在识别sql注入。我检查了所有可能的解决方案，但还是没能解决它。请检查sql查询，我也提到过。如何解决这个问题?那可帮了大忙了。提前感谢

Answer 1

我会使用已经提供清理功能的标准库，比如node-mysql。

https://github.com/mysqljs/mysql escaping-query-values

const mysql      = require('mysql');
const connection = mysql.createConnection({
host     : 'example.org',
user     : 'bob',
password : 'secret'
});
const post  = {title: 'Hello MySQL', 'content': '...'};
connection.query(
'insert into posts (title, content, updated_at, created_at) values (?, ?, ?, ?);',
[
connection.escape(post.title),
connection.escape(post.content),
connection.escape(new Date()),
connection.escape(new Date())
]
).then((err, res, fields) => {
//do stuff
})