向PostGIS数据库上传数据时遇语法错误，如何正确转义字符？

在使用TypeScript/Node.js程序向PostGIS/PostgreSQL服务器上传数据时，频繁收到语法错误提示。数据库日志如下：

2024-06-03T14:42:30.781942282Z 2024-06-03 14:42:30.781 UTC [916] ERROR: syntax error at or near "n" at character 2401
2024-06-03T14:42:30.781978542Z 2024-06-03 14:42:30.781 UTC [916] STATEMENT:
2024-06-03T14:42:30.781984462Z INSERT INTO cj_geometry (metadataID, location, groundgeometry, bbox, cityjsonfeature)
2024-06-03T14:42:30.781990002Z VALUES ('d5eaa521-686a-490c-80ea-385bd0978711',
2024-06-03T14:42:30.781994662Z ST_SetSRID(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[1090750.5758874745,7091366.735992711]}'),3857),
2024-06-03T14:42:30.781999762Z ST_SetSRID(ST_GeomFromGeoJSON('{"type":"MultiPolygon","coordinates":[[[[1090756.6500556513,7091357.322415222],[1090753.2715091056,7091377.62971956],[1090744.4895144769,7091376.1446406925],[1090747.8925513108,7091355.848590724],[1090756.6500556513,7091357.322415222]]]]}'),3857),
2024-06-03T14:42:30.782004902Z ST_SetSRID(ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[1090744.4895144773,7091355.848590724],[1090756.6500556516,7091355.848590724],[1090756.6500556516,7091377.62971956],[1090744.4895144773,7091377.62971956],[1090744.4895144773,7091355.848590724]]]}'),3857),
2024-06-03T14:42:30.782011112Z '{"type":"CityJSONFeature","CityObjects":{"DEHH_10ba522c-cf3e-4a31-9e13-2461cc07760b":{"type":"Building","attributes":{"creationDate":"2022-01-25T00:00:00Z","Gemeindeschluessel":"02200226","DatenquelleDachhoehe":"5000","DatenquelleLage":"1000","DatenquelleBodenhoehe":"1300","QualitaetDacherkennung":"98","Hauskoordinate":"552855,224 5937304,646","Dachhoehe":"4.50462926016637","Firsthoehe":"10.7808235498845","Traufhoehe":"6.27619428971818","tridicon_Dachform":"Satteldachhaus","Grundrissaktualitaet":"2021-12-07","Geometrietyp2DReferenz":"Polygon","measuredHeight":10.781,"function":"31001_1010","roofType":"3100","storeysAboveGround":2},"geometry":[{"type":"Solid","lod":"2","boundaries":[[[[0,1,2,3]],[[1,4,5,2]],[[4,6,7,5]],[[6,0,3,7]],[[4,1,8]],[[0,6,9]],[[1,0,9,8]],[[6,4,8,9]],[[3,2,5,7]]]],"semantics":{"surfaces":[{"type":"WallSurface"},{"type":"WallSurface"},{"type":"WallSurface"},{"type":"WallSurface"},{"type":"WallSurface"},{"type":"WallSurface"},{"type":"RoofSurface","Flaechenneigung":"53.585978459148","Flaechenrichtung":"349.789794011771","Flaechengroesse":"40.166004137133"},{"type":"RoofSurface","Flaechenneigung":"53.5853859152926","Flaechenrichtung":"169.812724206374","Flaechengroesse":"40.1052971618594"},{"type":"GroundSurface"}],"values":[[0,1,2,3,4,5,6,7,8]]}}],"address":[{"Country":"Germany","Locality":"Hamburg","ThoroughfareNumber":"105","ThoroughfareNumberSuffix":"e","ThoroughfareName":"Op'n Hainholt","PostCode":"22589"}]}},"vertices":[[1423187,532395,12580],[1431969,533881,12580],[1431969,533881,6304],[1423187,532395,6304],[1435347,513573,12580],[1435347,513573,6304],[1426590,512099,12580],[1426590,512099,6304],[1433658,523727,17085],[1424889,522248,17085]],"id":"DEHH_10ba522c-cf3e-4a31-9e13-2461cc07760b"}')
2024-06-03T14:42:30.782048142Z

经排查，问题源于JSONB对象中的未转义单引号——具体是"ThoroughfareName": "Op'n Hainholt"中的单引号，破坏了SQL语句的字符串边界。当前上传代码如下：

const cityjsonfeature = cityjsonFeature
const location = point([centerCoordinates[0], centerCoordinates[1]]).geometry

//push!
const insertGeometryQuery = `
                            INSERT INTO cj_geometry (metadataID, location, groundgeometry, bbox, cityjsonfeature)
                            VALUES ('${metaDataID}',
                            ST_SetSRID(ST_GeomFromGeoJSON('${JSON.stringify(location)}'),3857),
                            ST_SetSRID(ST_GeomFromGeoJSON('${JSON.stringify(
                              groundGeometry.features[0].geometry
                            )}'),3857),
                            ST_SetSRID(ST_GeomFromGeoJSON('${JSON.stringify(
                              boundingbox.geometry
                            )}'),3857),
                              '${JSON.stringify(cityjsonfeature)}')
                              `

try {
  await lodsql.unsafe(insertGeometryQuery).execute()
} catch (err) {
  console.error(err.stack)
}

解决方案 #

1. 使用参数化查询（强烈推荐） #

参数化查询是解决此类问题的最优方案：它会自动处理所有特殊字符的转义，同时彻底避免SQL注入风险。修改后的代码如下：

const cityjsonfeature = cityjsonFeature;
const location = point([centerCoordinates[0], centerCoordinates[1]]).geometry;

// 使用PostgreSQL位置占位符$1-$5，避免字符串拼接
const insertGeometryQuery = `
  INSERT INTO cj_geometry (metadataID, location, groundgeometry, bbox, cityjsonfeature)
  VALUES ($1,
          ST_SetSRID(ST_GeomFromGeoJSON($2), 3857),
          ST_SetSRID(ST_GeomFromGeoJSON($3), 3857),
          ST_SetSRID(ST_GeomFromGeoJSON($4), 3857),
          $5)
`;

try {
  // 将参数作为数组传递，驱动自动处理转义
  await lodsql.query(insertGeometryQuery, [
    metaDataID,
    JSON.stringify(location),
    JSON.stringify(groundGeometry.features[0].geometry),
    JSON.stringify(boundingbox.geometry),
    JSON.stringify(cityjsonfeature)
  ]).execute();
} catch (err) {
  console.error(err.stack);
}

2. 手动转义单引号（临时应急方案，不推荐） #

如果因特殊原因必须使用字符串拼接，可以手动将JSON字符串中的单引号替换为PostgreSQL支持的转义格式（两个单引号''）。示例代码如下：

// 定义PostgreSQL字符串转义函数
function escapePostgresString(str: string): string {
  return str.replace(/'/g, "''");
}

const cityjsonfeature = cityjsonFeature;
const location = point([centerCoordinates[0], centerCoordinates[1]]).geometry;

// 对所有嵌入SQL的字符串进行转义
const insertGeometryQuery = `
  INSERT INTO cj_geometry (metadataID, location, groundgeometry, bbox, cityjsonfeature)
  VALUES ('${escapePostgresString(metaDataID)}',
          ST_SetSRID(ST_GeomFromGeoJSON('${escapePostgresString(JSON.stringify(location))}'),3857),
          ST_SetSRID(ST_GeomFromGeoJSON('${escapePostgresString(JSON.stringify(groundGeometry.features[0].geometry))}'),3857),
          ST_SetSRID(ST_GeomFromGeoJSON('${escapePostgresString(JSON.stringify(boundingbox.geometry))}'),3857),
          '${escapePostgresString(JSON.stringify(cityjsonfeature))}')
`;

try {
  await lodsql.unsafe(insertGeometryQuery).execute();
} catch (err) {
  console.error(err.stack);
}

注意：手动转义容易遗漏其他特殊字符（如反斜杠、换行符等），且存在SQL注入风险，仅作为临时应急方案使用，优先选择参数化查询。

内容的提问来源于stack exchange，提问作者pcace