Scripting a command line psql command in python

Question

Background... I have an almost black box web application appliance that has a postgres DB on the back end Although I have access to a command line, to psql and and to a fairly basic Python 2.7 install, this is fairly limited (no ability to install additional python libs for example - yes, I know I could hack this but there is a contractual as well as practical element to this)

Problem... A table in the DB stores images in bytea format. based on some parameters passed from a browser in an ajax call, I need to extract the image to /tmp

To do this from psql I can do:

\copy (SELECT encode(image, 'hex') FROM images WHERE img_id = (select bin_id from binaries where id = '12345678')) TO '/tmp/12345678.jpg'

So...

Back to Python.

I have no sql libraries but I do have os and subprocess

So normally, to query the db I'd use os to:

something = os.popen(os_str).read()

where os_str is a psql shell command with an SQL statement appended

At the moment my test script looks like:

import os, sys, cgi, cgitb

form = cgi.FieldStorage()

uid = form.getvalue('uid')
if uid is None : # missing user_id
    uid = "12345678"

imgType = form.getvalue('imgType')
if imgType is None : # missing imgType
    imgType = "png"

imgName = uid + "." + imgType

pg_str = "psql -U xxx yyy -A -t -c "
sql = "???"
os_str = pg_str + "\'" + sql + "\'" + ";"
os.popen(os_str).read()

I'm fairly certain that I'm in quote/escape hell here

I've tried seemingly endless combinations to do

sql = "\copy (SELECT encode(image, 'hex') FROM images WHERE img_id = (select bin_id from binaries where id = '+ uid + "')) TO '/tmp/" + imgName + "'"

Obviously, I know that's wrong, but it seems the simplest way to illustrate what I need

Answer 1

I have no idea why you're trying to use os.popen. The docs explicitly call it obsolete and deprecated, and tell you to use the subprocess module instead. And you obviously knew about subprocess because you said, "I have no sql libraries but I do have os and subprocess."

Anyway, if you use subprocess, you don't have to worry about getting the escaping correct to make a string that the shell can parse to the list of arguments you want it to get; just pass the list of arguments you want as a list:

And you've confused yourself with your single and double quotes; you've got a literal '+ uid + in the middle of your SQL string. This is one of the many reasons it's easier to use string formatting than concatenation.

Meanwhile, I'm pretty sure the ; is a terminator for the SQL statement you're passing to pgsql. This means it should be part of the argument.

So:

fmt = r"\copy (SELECT encode(image, 'hex') FROM images WHERE img_id = (select bin_id from binaries where id = {} TO '/tmp/{}';"
sql = fmt.format(uid, imgName)
pg = ['pgsql', '-U', 'xxx', 'yyy', '-A' ,'-t', '-c', sql]
output = subprocess.check_output(pg)

---

Anyway, as for what you got wrong in your quoting attempt, there's quite a bit, in addition to the two problems above (mixing up ' and " and putting the ; outside the quoted argument):

• "\'" is the same string as "'". if you want to put literal backslashes into your strings literals, you need to escape them—or, better, just use raw strings, like r"\'".
• But you don't want \' in the first place. You're not trying to pass single quotes through the shell to pgsql, you're just trying to quote the pgsql command for the shell. You can't do that by wrapping it in \'. You can do that by wrapping it in ", which is a lot easier.
• You have single quotes within the string, which you can't do inside a single-quoted string without escaping them. So, everything from the start of the string to the first ' is quoted, then everything up to the next ' is not, and so on. (And the rules are again different on Unix and Windows.) You need to escape the single quotes—e.g., `sql.replace("'", r"\'".
• You have backslashes within the non-raw string literal which you didn't escape. In this case, you get away with it, because "\c" happens to be the same as r"\c", but that's not true in general; unless you have the list of escape characters that escape to themselves memorized, don't ever rely on it.

So, if you really wanted to do it this way:

# same first two lines as above to create sql
escaped = sql.replace("'", r"\'")
os_str = 'psql -U xxx yyy -A -t -c "{}"'.format(escaped)