How to template sql with python and deal with sql composition problem?

Question

I need to format an SQL query, and it looks like this:

s += " t{}.{} = '{}' and".format(t_c, filter_c, filter_value)

but when the filter_value is something like m's it will result in

psycopg2.errors.SyntaxError: syntax error

if I use the double quote, it will say there's no such column

Any way I can resolve this problem, please?

score 0 · Accepted Answer · answered Mar 15 '20 at 18:31

0

Caused by injection vulnerability. Use parameters for filter_value and let the database API handle it.

If the table/schema names are coming from user input, whitelist those too. Parameters aren't possible for table names).

answered Mar 15 '20 at 18:31

OwlsSleeping

1,487
2
11
19

How to use 'parameters'? – Wanjing Chen Mar 17 '20 at 16:41
It varies depending on database engine, so I'll defer to the detailed answers on this one: https://stackoverflow.com/questions/1466741/parameterized-queries-with-psycopg2-python-db-api-and-postgresql As for why injection is a bad thing, as well as getting unexplained errors on single quotes, there's a risk that somebody enters a username like "'; drop table users;--" Even if you double all single quotes like in the other answer, there's the risk of second hand injection, unicode smuggling etc. – OwlsSleeping Mar 17 '20 at 21:23

score 0 · Answer 2 · answered Mar 15 '20 at 19:02

try this:

def format_value( string ):
    if '\'' in string:
        k           = string.split('\'')
        string_list = list(map(lambda x: '\'' + x+ '\'', k ))
        return  ''.join(string_list )
    else:
        return string

then

filter_value = format_value(filter_value)

before you pass filter_value to your query

How to template sql with python and deal with sql composition problem?

2 Answers2