Syntax error with psycopg SQL query over a WKB string

Question

My code executes a simple SQL query to convert some WKB geometry to WKT. I get my WKB geometry from an url request. The code looks like this:

url = 'http://..........'
response = urllib2.urlopen(url).read()
responseJson = json.loads(response)
coordWKB = responseJson['wkb_geometry']
print(coordWKB)
cur.execute("SELECT ST_AsText(" + coordWKB + ")")

coordWKB looks like this:

'0106000020E6100000010000000103000000010000001200000017A84D0F39925EC083FC69A455C342400A1451BC48925EC0422CDC116B........'

But I get the following error:

psycopg2.ProgrammingError: syntax error at or near "A84D0F39925EC083FC69A455C342400A1451BC48925EC0422CDC11................   
LINE 1: ...000020E6100000010000000103000000010000001200000017A84D0F3992...

If I execute the code below it works:

cur.execute("select ST_AsText('0106000020E6100000010000000103000000010000001200000017A84D0F39925EC08.............')")

I can't seem to figure out what's wrong with the WKB I'm passing the query.

Answer 1

First read Understanding repr( ) function in Python. The string coordWKB

'0106000020E6100000010000000103000000010000001200000017A84D0F39925EC083FC69A455C342400A1451BC48925EC0422CDC116B........'

does not include the single quotation marks, it is just how your python interpreter displays string values for your convenience. If it did include the single quotation marks, then it would be displayed as:

"'0106000020E6100000010000000103000000010000001200000017A84D0F39925EC083FC69A455C342400A1451BC48925EC0422CDC116B........'"

With this in mind given

cur.execute("SELECT ST_AsText(" + coordWKB + ")")

the resulting SQL will be:

SELECT ST_AsText(0106000020E6100000010000000103000000010000001200000017A84D0F39925EC083FC69A455C342400A1451BC48925EC0422CDC116B........)

It should be obvious that this is wrong.

Next you should read the story of little Bobby Tables and "the problem with the query parameters". You should never include values in your SQL query by concatenating strings. That opens up the doors for SQL injection. The values have to be properly escaped so that no additional SQL can be injected to your query. That is a job for your DB-API and all you have to do is instruct it with placeholders. From basic usage of psycopg2:

# Pass data to fill a query placeholders and let Psycopg perform
# the correct conversion (no more SQL injections!)
>>> cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)",
...      (100, "abc'def"))

Do note that you do not format the query string yourself, but pass the value tuple as an argument to cur.execute. This way your original query should be written as:

cur.execute("SELECT ST_AsText(%s)", (coordWKB,))