'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 |
|---|
