i am getting a lot of error messages when uploading data to a postgis / postgresql server. The issue seem to be unescaped characters in jsonb objects. this is an examplary log from the db:

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

i am uploading from a typescript / nodejs programm like this:

          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)
          }

i think i should somehow escape characters wich to into the '${JSON.stringify(cityjsonfeature)}') line. but how would i do that? is there a general way to get rid of non working characters? or do i have to set up the query string differently?

in the provided log it seems that the problem is: "ThoroughfareName": "Op'n Hainholt", and more precisely the “‘” in “Op’n”.

any idea how to get rid of this problem would be awesome!!

Thanks a lot!!