How to sanitize inputs in nodejs to prevent sql injection?

Question

i have this sanitize function

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

And this sanitize function is being used here

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'})
                                }
                         
            },
        },
   );

        
    }

So while inserting the address I'm identifying sql injection. I checked everywhere the possible solutions i was not able to solve it. Please do check the sql query also which i have mentioned. How to solve this? That would be a lot of help. Thanks in advance

user_14744953 · Answer

I'd just use a standard library that already provides sanitization, like 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
})

How to sanitize inputs in nodejs to prevent sql injection?

1 Answers