1

I want to do exactly what cursor.mogrify does, but in a production appropriate way.

I'm updating some legacy Python code that builds queries by concatenating strings. I need to change this to escape safely.

The queries are long and built on a different server than they are run, so the normal process of using cursor.execute to escape is unattractive for both code clarity and practical viability reasons.

I would use mogrify, but I understand that it is intended for debugging purposes only.

I've looked around and can't seem to find a good answer to this. What do you suggest?

Tim Wilder
  • 1,607
  • 1
  • 18
  • 26
  • Why _the normal process of using cursor.execute to escape is unattractive for both code clarity and practical viability reasons_? i don't know anything better than that. – Clodoaldo Neto Dec 20 '13 at 20:24
  • The db isn't exposed outside of the server it runs as a child process of, so it can't be contacted directly from where the query is built. That is the practical restriction.The clarity reason is that interpolation via a long, long tuple is hard for maintainers to read. – Tim Wilder Dec 20 '13 at 21:02
  • Where did you see that `mogrify` is not intended to be used for production? Not challenging that statement, just curious... – bonh Nov 06 '17 at 20:02
  • psycopg docs in 2013 when I asked this question ;). – Tim Wilder Nov 06 '17 at 22:46

1 Answers1

3

Don't use a tuple. Use a dictionary

d = {'p1': val1, 'p2': val2}
cur.execute("""
    select *
    from t
    where col1 = %(p1)s and col2 = %(p2)s
    """, d
)

If there are optional parameters pass then as null

d = {'p1': None, 'p2': val2}
cur.execute("""
    select *
    from t
    where
        (%(p1)s is null or col1 = %(p1)s)
        and
        (%(p2)s is null or col2 = %(p2)s)
    """, d
)

Establish an ssh connection to the server and connect through it.

ssh -L 5432:localhost:5432 remotehost.com
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • Accepted because the dictionary technique is excellent. The access problem isn't a technical impossibility problem, it's a limitation imposed by design. It sounds like mogrify may be the only mogrify, and outside of manually implementing or abusing the library, constructing an escaped string independently of running it is a no go? – Tim Wilder Dec 21 '13 at 08:22
  • I'm moving the query logic. It's a bigger change than ideal, but the problem of having to construct the query before running it stems for the mixing of abstraction inherent in making database access private to the outside world, then putting query building logic in the outside world. – Tim Wilder Dec 21 '13 at 08:27
  • 2
    There is no major problem in using mogrify. The problem for me is string building which I consider bad practice. I think you should consider explaining that design restriction in another question. – Clodoaldo Neto Dec 21 '13 at 08:36