Prepared statements with Node-MSSQL

Question

I want to store API data in a SQL Server database with Node.js. It works already with a normal INSERT query as you see in the first code below.

   server.route({

        method: 'POST',
        path: '/',
        handler: async(request, h) => {

            try {
                await pool.query("INSERT INTO mytable(device,data,stationId,rssi,unix_timestamp)                              VALUES('"+request.payload.device+"','"+request.payload.data+"','"+request.payload.station+"',
'"+request.payload.rssi+"','"+request.payload.time+"')");

                return h.response('Callback received').code(200);
            }
            catch (err) {
                console.log("SQL Err", err.stack);
                return 'Error';
            }

        }

    });

Now my I want to improve the code a little bit to avoid SQL injection and to have a better overview.

Therefore I want to use prepared statements like described in this documentation: https://www.npmjs.com/package/mssql#prepared-statement

So far I've managed to do this here:

server.route({
    method: 'POST',
    path: '/',
    handler: async(request, h) => {


        const ps = new sql.PreparedStatement(pool)

        try {

        ps.input('device', sql.VarChar(10))
        ps.input('data', sql.VarChar(24))
        ps.input('station', sql.NChar(10))
        ps.input('rssi', sql.Float)
        ps.input('time', sql.Int)

            await ps.prepare('INSERT INTO mytable(device,data,stationId,rssi,unix_timestamp) VALUES(@device,@data,@station,@rssi,@time)');

            try {
              await ps.execute(
                { device: request.payload.device },
                { data: request.payload.data },
                { station: request.payload.station },
                { rssi: request.payload.rssi },
                { time: request.payload.time }
                )
            } finally {
              await ps.unprepare();
            }
            return h.response('Callback received').code(200);
        }
         catch (err) {
            console.log("SQL Err", err.stack);
            return 'Error';
        }
    }
});

And the following error occurs:

at Parser.emit (events.js:223:5)
at Parser.<anonymous> (C:\Users\AW\sqltest\node_modules\tedious\lib\token\token-stream-parser.js:37:14)
at Parser.emit (events.js:223:5)
at addChunk (C:\Users\AW\sqltest\node_modules\readable-stream\lib\_stream_readable.js:297:12)
at readableAddChunk (C:\Users\AW\sqltest\node_modules\readable-stream\lib\_stream_readable.js:279:11)
at Parser.Readable.push (C:\Users\AW\sqltest\node_modules\readable-stream\lib\_stream_readable.js:240:10)
at Parser.Transform.push (C:\Users\AW\sqltest\node_modules\readable-stream\lib\_stream_transform.js:139:32)

This is an example of the JSON data I get from the API:

{
  "device":"887B53",
  "data":"4660000000000062b4a8",
  "station":"1B2C" 
  "rssi":"-123",
  "time":"1585258718"
}

I hope someone can help me with that.

score 0 · Answer 1 · answered Mar 26 '20 at 21:04

0

It appears that the error is occurring at the following line:

ps.input('station', sql.NChar(10))

I would debug the value that is being assigned here and see if it fits the schema, or if it's undefined.

Additionally on a side note if you are worried about readability and SQL injections, consider using an ORM such as Sequelize.

answered Mar 26 '20 at 21:04

fire

45
1
6

What makes you think it is because of this line? Both the data type and the string are suitable. If I take out the input the error stays. – Karol-Kahn Mar 26 '20 at 21:32
Actually I take it back. It seems that the error is happening while you're transforming parameters for your statement, something is incompatible I see you're using the type float but you're feeding straight from the JSON into the float/int types (RSSI and TIME), have you tried converting them to float and int respectively? – fire Mar 26 '20 at 21:50
if I take out these two parameters, I get the same error. As I said before, if I execute a normal INSERT query, it works. And I do the same with the JSON data. – Karol-Kahn Mar 26 '20 at 22:05

Prepared statements with Node-MSSQL

1 Answers1