Constructing a safe dynamic query using SQL Server

Question

I am new to using the nodejs mssql/tedious library. I have a table that uses default values for columns and I can't expect logs to always have all these values present. I am wondering if there is an easier way to safely construct a query dynamically based on which values are passed to my log function.

const sql = require('mssql')
const config = require('config')

const dconfig = config.database

const addLog = async (options) => {
  const { text, sentSuccess, isError } = options
  try {
    let pool = await sql.connect(dconfig)
    let request = await pool.request()
    request.input('message', sql.Text, text.trim())
    if (sentSuccess) {
      request.input('sent', sql.Bit, !!parseInt(sentSuccess))
      await request.query('insert into dbo.MApp_Logs (message, sent_success) values (@message, @sent)')
    } else {
      await request.query('insert into dbo.MApp_Logs (message) values (@message)')
    }
  } catch (err) {
    throw err
  } finally {
    sql.close()
  }
}

You can see that here I am forced to rewrite the query several times based on which values are present. Is there a more elegant way to handle this without concatenating the values into the string?

Answer 1

I really dislike using or relying on DEFAULT constraints in SQL other than for backward compatibility; but that is a matter of taste and preference. I would favor handling your default values in code for new work, and always send the same SQL statement:

 request.input('message', sql.Text, text.trim())
 if (sentSuccess)
     request.input('sent', sql.Bit, !!parseInt(sentSuccess))
 else
     request.input('sent', sql.Bit, theDefaultValue)
 await request.query('insert into dbo.MApp_Logs (message, sent_success) values (@message, @sent)')

However, if you really want to use SQL's default definitions, you can always contatenate the sql statement and continue to parameterize:

 request.input('message', sql.Text, text.trim())
 let sqlFields = "insert into dbo.MApp_Logs (message"
 let sqlValues = ") values (@message"

 if (sentSuccess) {
     request.input('sent', sql.Bit, !!parseInt(sentSuccess))
     sqlFields += ", sent_success"
     sqlValues += ", @sent"
 }

 await request.query(sqlFields + sqlValues + ")")