'Insert point geometry into a SQL Server (mssql) table using Knex

I am trying to insert point geometry into a SQL Server (mssql) table using Knex:

const db = require('./knex')();
const { v4: uuidv4 } = require('uuid');

exports.create = async function(point, account){

  const data = {

    ptid: uuidv4(),
    ptx: point.ptx,
    pty: point.pty,
    srid: point.srid,
    geom: 'geometry::Point(' + point.ptx + ', ' + point.pty + ', ' + point.srid + ')'

  }

  await db('point').insert(data);

  return data;

}

So 'geometry::Point(' + point.ptx + ', ' + point.pty + ', ' + point.srid + ')' gets passed to the insert as a string and SQL Server throws an error. How can I get Knex to treat this as an unquoted raw geometry 'value' - ?

In the Knex SQL output, note the single quotes around 'geometry::Point(2173635.077607,259425.451693,2274)'

{
  method: 'insert',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [
    'geometry::Point(2173635.077607,259425.451693,2274)',
    'c4e40cb1-885e-4a88-81c8-d98ca1109e58',
    '2173635.077611',
    '259425.451746',
    2274,
  ],
  __knexQueryUid: 'EorwIyvv_u5Zc0iuVSx7T',
  sql: 'insert into [point] ([geom], [ptid], [ptx], [pty], [srid]) values (?, ?, ?, ?, ?)'

Manually running the query in SSMS without the quotes works without a hitch:

insert into [point] ([geom], [ptid], [ptx], [pty], [srid]]) 
values (geometry::Point(2173635.077607,259425.451693,2274), 
   'c4e40cb1-885e-4a88-81c8-d98ca1109e58',
    '2173635.077607', '259425.451693', 2274)

Update:

Was able to save the point geometry via knex.raw

await db.raw('insert into [point] ([geom], [ptid], [ptx], [pty], [srid]) \
  values (' + geometry::Point(' + point.ptx + ', ' + point.pty + ', ' + point.srid + '), \
    \'' + data.ptid + '\', ' + point.ptx + ', ' + point.pty + ', ' + point.srid + ')')

As far as I know, prepared statements / parameterized queries are not possible via knex.raw so this solution is susceptible to sql injection. Still looking for a solution that prevents or at least limits potential sql injection.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source