title: nodejs mssql insert with parameters
body: <p>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.</p>
  <p>So I currently have an application that insert into the data without parameters.</p>
  <p>Here is what I have so far:</p>
  <pre><code>var fs = require('fs');
  var sql = require('mssql');
  var LINQ = require(&quot;node-linq&quot;).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=&gt;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 = &quot;&quot;;
                          var mailAddressName = &quot;&quot;;
                          
                          if(message.from.length &gt; 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) =&gt; {
                          console.dir(result)
                      })
  
                      }
                  });
                  client.quit();
              })
          });
  
      } catch (err) {
          console.log(err);
          // ... error checks
      }
  })()
  </code></pre>
  <p>I was looking at prepared statements but I could not get that working.</p>
  <p>Here is what I was attempting with prepared statements</p>
  <pre><code>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 =&gt; {
          ps.execute({
              uid: messageId, 
              mail_address: mailAddress, 
              mail_address_display_name: mailAddressName,
              subject: subject,
              body: body,
              sent_date: sentDate,
              created_by: 'system'
          }, (err, result) =&gt; {
              // ... error checks
                                  
              ps.unprepare(err =&gt; {
                  var x =1;
              })
          })
      }
  ) 
  </code></pre>