2

I have a results database I am trying to write a LineString geometry value, a total_time, and an id into my Postgres db with PostGIS.

The query I'm trying to do in Python with psycopg2 is this....

    insert_query = 'INSERT INTO public.results(the_geom, total_time, id) VALUES (ST_SetSRID('+str(hex_shortest_route_geom)+'::geometry, 4326), '+str(total_time)+', '+str(id)+');'

Yes I am aware this is some bad practices with SQL and prone to SQL Injections. So for example the query turns into...

INSERT INTO public.results(the_geom, total_time, id) VALUES (ST_SetSRID(010200000052000000F9B605F0BB6352C0961BB1BBE59044405E34BFF5BC6352C05882D60D25914440131736B9CE6352C0B8BE5994239144406A0B19D9DF6352C07EDAF2DB21914440677BF486FB6352C02C442C071F914440B616C15A106452C03912C3691C914440502ED7EC126452C0AA85ED821C914440E95F92CA146452C092CD55F31C914440183A1B97166452C0036F92301D9144405567B5C01E6452C0ACF6FAA424914440F1C1C650296452C0D01D0E5C2F9144400DECE703386452C0EC191DEB3D914440BAE2981B426452C0B1AEC04648914440BFF2203D456452C020C88B964D914440AC1F9BE4476452C016BF29AC549144404AC33A9F4B6452C0BD3A223A5F9144403102CF624E6452C08C1AA77C63914440E82FF488516452C0199B0C7D6691444068244223586452C0106AD0E16B91444061A351265A6452C0AA98EF856E91444097C5C4E6636452C03D27BD6F7C91444043BE9536666452C0E04499B2789144406B798AC1686452C0288705AD769144405DA5BBEB6C6452C09B481FA9749144405C0F15996F6452C0015C7F01739144404EB4AB90726452C05C9F83786E9144404D66BCAD746452C08DB9C6C26B914440DA2B3DE4776452C0F48AA71E69914440B4E4F1B47C6452C0CB87FB23679144405A93B8D87E6452C025636B6C6591444005758016816452C05CB0AFD062914440D5496C2D826452C00BBEC4B35F9144402DE8BD31846452C08E57207A52914440149D1B89866452C004D2B47A3D914440C10E52A6876452C085AC133C2A91444019260978886452C084C2FB05169144403C3E6BC8896452C01E335019FF904440081AD8CF876452C01EE85729F39044400F289B72856452C07A7E07F5E3904440DF8CF568856452C03345CA60D6904440676AC82E876452C039758AFAC99044408B2C3EAA866452C093B71270BE904440E01C6448846452C04D840D4FAF9044408EB4AF97816452C0CACA8A3CA49044402491C71D806452C07307FBC09F9044408591031B7C6452C04611082A9990444057163BD0796452C0F19B6791929044407EF6C88B716452C0959F54FB7490444020E05A376F6452C0B6BC72BD6D904440FE15E8C96B6452C090F980406790444048EEFA16676452C0AB81412D61904440CFD325885F6452C00E7A803859904440EE23B7265D6452C0A5A723DB54904440F152A005576452C0AA2DD0494A9044404E8E4CD15B6452C092109B7E3F9044401AEC9799636452C0D7A7774C38904440488F3A95676452C0C1CD3D7F3590444099AC9B9C6B6452C080400C193490444049B5F40D716452C05D3C17A133904440663ED6427F6452C04600811832904440C3DBDEC9826452C040B912263190444058CFA4A88E6452C0B29FC55224904440BDD17DEFA56452C02DE8BD3104904440A243E048A06452C0D82725A9F18F4440A3BC45169F6452C00F05D8ECED8F4440637A67599C6452C09CBE43F6E68F444022BF23CF9A6452C0E0F3C308E18F4440F9FDF6D0996452C096CA8058DB8F44400310D2AE9D6452C0401B1428BD8F44408076D1A19D6452C0CD920035B58F44405DA9C2FA9A6452C0A89086AE9F8F44405F14F362976452C05F950B957F8F44405CAE7E6C926452C0DEB06D51668F4440E25CC30C8D6452C003A1AB634B8F44407F64DFBA876452C09475EE2C308F4440DC12149A7F6452C093F5E516048F44409D499BAA7B6452C05E1E21B9EB8E4440DBD2B4D5766452C08C1A0236D68E4440AF88505B596452C05B8D36D8E58E444051BCCADA266452C08100BE80038F4440A39F5F4A136452C0BE440603198F44404BE1E6F9F86352C0E0B65B374A8F4440::geometry, 4326), 14.5755758286, 090219AMOctober202017);

The Geometry for the LineString is converted to a hex value to store in Postgres like such with shapely...

hex_shortest_route_geom = LineString(the_geometry).wkb_hex

Yet, I am getting syntax errors in my SQL queries...

psycopg2.ProgrammingError: syntax error at or near "F9B605F0BB6352C0961BB1BBE59044405E34BFF5BC6352C05882D60D25914440131736B9CE6352C0B8BE5994239144406A0B19D9DF6352C07EDAF2DB21914440677BF486FB6352C02C442C071F914440B616C15A106452C03912C3691C914440502ED7EC126452C0AA85ED821C914440E95F92CA146452C092CD55F31C914440183A1B97166452C0036F92301D9144405567B5C01E6452C0ACF6FAA424914440F1C1C650296452C0D01D0E5C2F9144400DECE703386452C0EC191DEB3D914440BAE2981B426452C0B1AEC04648914440BFF2203D456452C020C88B964D914440AC1F9BE4476452C016BF29AC549144404AC33A9F4B6452C0BD3A223A5F9144403102CF624E6452C08C1AA77C63914440E82FF488516452C0199B0C7D6691444068244223586452C0106AD0E16B91444061A351265A6452C0AA98EF856E91444097C5C4E6636452C03D27BD6F7C91444043BE9536666452C0E04499B2789144406B798AC1686452C0288705AD769144405DA5BBEB6C6452C09B481FA9749144405C0F15996F6452C0015C7F01739144404EB4AB90726452C05C9F83786E9144404D66BCAD746452C08DB9C6C26B914440DA2B3DE4776452C0F48AA71E69914440B4E4F1B47C6452C0CB87FB23679144405A93B8D87E6452C025636B6C6591444005758016816452C05CB0AFD062914440D5496C2D826452C00BBEC4B35F9144402DE8BD31846452C08E57207A52914440149D1B89866452C004D2B47A3D914440C10E52A6876452C085AC133C2A91444019260978886452C084C2FB05169144403C3E6BC8896452C01E335019FF904440081AD8CF876452C01EE85729F39044400F289B72856452C07A7E07F5E3904440DF8CF568856452C03345CA60D6904440676AC82E876452C039758AFAC99044408B2C3EAA866452C093B71270BE904440E01C6448846452C04D840D4FAF9044408EB4AF97816452C0CACA8A3CA49044402491C71D806452C07307FBC09F9044408591031B7C6452C04611082A9990444057163BD0796452C0F19B6791929044407EF6C88B716452C0959F54FB7490444020E05A376F6452C0B6BC72BD6D904440FE15E8C96B6452C090F980406790444048EEFA16676452C0AB81412D61904440CFD325885F6452C00E7A803859904440EE23B7265D6452C0A5A723DB54904440F152A005576452C0AA2DD0494A9044404E8E4CD15B6452C092109B7E3F9044401AEC9799636452C0D7A7774C38904440488F3A95676452C0C1CD3D7F3590444099AC9B9C6B6452C080400C193490444049B5F40D716452C05D3C17A133904440663ED6427F6452C04600811832904440C3DBDEC9826452C040B912263190444058CFA4A88E6452C0B29FC55224904440BDD17DEFA56452C02DE8BD3104904440A243E048A06452C0D82725A9F18F4440A3BC45169F6452C00F05D8ECED8F4440637A67599C6452C09CBE43F6E68F444022BF23CF9A6452C0E0F3C308E18F4440F9FDF6D0996452C096CA8058DB8F44400310D2AE9D6452C0401B1428BD8F44408076D1A19D6452C0CD920035B58F44405DA9C2FA9A6452C0A89086AE9F8F44405F14F362976452C05F950B957F8F44405CAE7E6C926452C0DEB06D51668F4440E25CC30C8D6452C003A1AB634B8F44407F64DFBA876452C09475EE2C308F4440DC12149A7F6452C093F5E516048F44409D499BAA7B6452C05E1E21B9EB8E4440DBD2B4D5766452C08C1A0236D68E4440AF88505B596452C05B8D36D8E58E444051BCCADA266452C08100BE80038F4440A39F5F4A136452C0BE440603198F44404BE1E6F9F86352C0E0B65B374A8F4440"
LINE 1: ...al_time, id) VALUES (ST_SetSRID(010200000052000000F9B605F0BB...

I'm unsure what's going on, any help?

Improve this question

2 Answers 2

Reset to default
4

Don't put thing directly in strings :

insert_query = 'INSERT INTO public.results(the_geom, total_time, id) VALUES (ST_SetSRID(:geom::geometry, 4326), :total_time, :id)'

your_db_connection.execute(insert_query, geom=hex_shortest_route_geom, total_time=total_time, id=id)

The original problem was quote. When issuing this kind of error you can can copy/paste plain SQL in psql.

In addition, using the PostGIS query ST_SetSRID is wrong. You have a hex value of the LineString, use ST_GeomFromHKB instead like this...

insert_query = """INSERT INTO public.results(the_geom, total_time, id) VALUES (ST_GeomFromWKB(%(geom)s::geometry, 4326), %(total_time)s, %(id)s)"""
Improve this answer
1
  • Ah okay I guess that's why it's a best practice, thanks. Also going to just add one thing to your answer about not using ST_SetSRID and use ST_GeomFromWKB instead as that's another problem I figured out.
    – Carson
    Commented Oct 20, 2017 at 14:56
2

As you say, passing parameters through string concatenation is prone to SQL injection. But the bigger problem, if you're just working on an internal application, is that it's easy to mess your quoting up, which is what's happened here.

A Postgres text literal needs to be surrounded with single quotes, which is annoying if your Python strings themselves use single quotes. Why not just do the right thing and pass the values as parameters?

See the docs for details.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.