Manually escape a string for raw SQL

Question

I have a raw SQL statement to be executed using SqlAlchemy. The query contains this:

FROM unnest(array['sku1', 'sku2', 'sku3'])

Building the query manually, I could not find a way to escape each parameter manually.

I tried doing:

session.execute("... FROM unnest(array[:skus]) ...", {'skus': skus})

but:

ProgrammingError: (ProgrammingError) a column definition list is required for functions returning "record"
LINE 21:         FROM unnest(array[('sku1', 'sku2', '...
                      ^

Community · Accepted Answer · 2017-05-23 11:49:33Z

2

A Python tuple is adapted to a Postgresql record and a list adapted to an array. That is why in this case a tuple is not valid.

But what you say in your own answer that works does not:

select unnest(['BA007EWCBD43', "KA036'AGHF550", 'KA036ACBK873']);
ERROR:  syntax error at or near "["
LINE 1: select unnest(['BA007EWCBD43', "KA036'AGHF550", 'KA036ACBK87...

Or you mean

FROM unnest(ARRAY['BA007EWCBD43', "KA036'AGHF550", 'KA036ACBK873'])

The parameters should never be manually escaped. Let it to the driver.

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Sep 2, 2014 at 11:07

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

warvariuc Over a year ago

yes, you're right. that's because I had a custom logger that printed this. Do you know a method to see the real query that goes to the SQL server instead of

... FROM unnest(%(skus)s) ... INFO 2014-09-02 15:21:10,419 sqlalchemy.engine.base.Engine {'skus': ['IN002EWKU161', 'FA003BMAYD09', "NI464E'WFA620", 'NI464EWCI491']}

? But not using server logs?

Clodoaldo Neto Over a year ago

@warvariuc I don't know how to get the final sql query other than reading the server log. But the sqlalchemy people sure know.

warvariuc · Accepted Answer · 2014-09-02 07:03:44Z

0

Ok, if I put a list of skus (not a tuple), everything is fine:

session.execute("... FROM unnest(:skus) ...", {'skus': ['BA007EWCBD43', 'KA036\'AGHF550', 'KA036ACBK873']})

Which generates:

FROM unnest(['BA007EWCBD43', "KA036'AGHF550", 'KA036ACBK873'])

Though a function to manually quote parameters would is still needed for other cases.

answered Sep 2, 2014 at 7:03

warvariuc

60.1k45 gold badges183 silver badges234 bronze badges

Collectives™ on Stack Overflow

Manually escape a string for raw SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related