nodejs mssql insert with parameters

Question

I'm new to NodeJS where I'm trying to insert into a table with parameters so I can avoid SQL Injection and just escape any characters that might cause issues.

So I currently have an application that insert into the data without parameters.

Here is what I have so far:

var fs = require('fs');
var sql = require('mssql');
var LINQ = require("node-linq").LINQ;
const simpleParser = require('mailparser').simpleParser;
var Client = require('yapople').Client;
var client = new Client({
  hostname: 'xxxxxx',
  port:  995,
  tls: true,
  mailparser: true,
  username: 'xxxxx',
  password: 'xxxxx'
});

const config = {
    user: 'xxxxxxx',
    password: 'xxxxx',
    server: 'xxxxx\\',
    port: 'xxxxx'
    database: 'xxxxxx',
    options: {
        instanceName: 'xxxxx'
    }
};

(async function () {
    try {
        let pool = await sql.connect(config)
        
        //Get all current emails
        let emails = await pool.request()
            .query('select uid from email')
        
        //Get uids only
        var uids = new LINQ(emails.recordset)
        .Select(function(email) {return email.uid;})
        .ToArray();

        //Get all emails
        client.connect(function() {
            client.retrieveAll(function(err, messages) {
                messages.forEach(function(message) {
                    //Check if the message exists in our database already.
                    var messageId = message.messageId;

                    var emailExists = new LINQ(uids)
                        .Where(x=>x == messageId).ToArray();

                    //If the message do not exists then add them to the database
                    if(emailExists.length == 0){
                        var sentDate = new Date(message.date).toISOString();
                        var subject = message.subject;
                        var body = message.text;
                        var mailAddress = "";
                        var mailAddressName = "";
                        
                        if(message.from.length > 0){
                            mailAddress = message.from[0].address;
                            mailAddressName = message.from[0].name;
                        }

                       const request = pool.request();
                    request.input('uid', sql.VarChar, messageId);
                    request.input('mail_address', sql.VarChar, mailAddress);
                    request.input('mail_address_display_name', sql.VarChar, mailAddressName);
                    request.input('subject', sql.VarChar, subject);
                    request.input('body', sql.VarChar, body);
                    request.input('sent_date', sql.DateTime, sentDate);
                    request.input('created_by', sql.VarChar, 'system');

                    let result = await request.query('INSERT INTO email(uid, mail_address, mail_address_display_name, subject, body, sent_date, created_by) OUTPUT INSERTED.ID values (@uid, @mail_address, @mail_address_display_name, @subject, @body, @sent_date, @created_by)', (err, result) => {
                        console.dir(result)
                    })

                    }
                });
                client.quit();
            })
        });

    } catch (err) {
        console.log(err);
        // ... error checks
    }
})()

I was looking at prepared statements but I could not get that working.

Here is what I was attempting with prepared statements

const ps = new sql.PreparedStatement();

ps.input('uid', TYPES.VarChar);  
ps.input('mail_address', TYPES.VarChar);  
ps.input('mail_address_display_name', TYPES.VarChar);
ps.input('subject', TYPES.VarChar);
ps.input('body', TYPES.VarChar);
ps.input('sent_date', TYPES.DateTime);
ps.input('created_by', TYPES.VarChar);

ps.prepare('INSERT INTO email(uid, mail_address, mail_address_display_name, subject, body, sent_date, created_by) ' +
    ' OUTPUT INSERTED.email_id VALUES (@uid, @mail_address, @mail_address_display_name, @subject, @body, @sent_date, @created_by)', 
    err => {
        ps.execute({
            uid: messageId, 
            mail_address: mailAddress, 
            mail_address_display_name: mailAddressName,
            subject: subject,
            body: body,
            sent_date: sentDate,
            created_by: 'system'
        }, (err, result) => {
            // ... error checks
                                
            ps.unprepare(err => {
                var x =1;
            })
        })
    }
)

Terry Lennox · Accepted Answer · 2018-01-16 21:18:30Z

17

You can use the connection pool request object to add parameters, e.g.

const request = pool.request()
request.input('myval', sql.VarChar, 'value')
request.query('insert into testtable (somecolumn) values (@myval)', (err, result) => {
    console.dir(result)
})

answered Jan 16, 2018 at 21:18

Terry Lennox

30.9k5 gold badges36 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

MindGame Over a year ago

Thank you for the quick response. Where does sql.VarChar come from?

Terry Lennox Over a year ago

You can make each query return a promise, e.g. request.query, then use the await keyword to resume execution once the query is complete. const request = pool.request(); let result = await request.query("select * from mytable');

Terry Lennox Over a year ago

Oh sorry, yes, is it possible it doesn't like the await keyword? The calling function should be marked async, e.g. var callingFunction = async function() { const request = pool.request(); let result = await request.query("select * from mytable"); console.log(result); }; callingFunction();

Terry Lennox Over a year ago

I think you can indeed, you just use an update rather than an insert.

rStorms Over a year ago

Just did it. Worked like a charm.

|

Collectives™ on Stack Overflow

nodejs mssql insert with parameters

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related