CODEWITHSUNDEEP
pythonsqlpostgresql
Yura Kharpaev
Yura Kharpaev

Reputation: 51

Compare with substring without injection

The sql-query is generated in the python module.
The database is PostgreSQL.

In sql-query there is a comparison with a substring:

'''
SELECT *
FROM TableTemp
WHERE "SomeColumn" LIKE '%{0}%'
'''.format(<some_string>)

If the string is:

%' --

Then the check will always return "True".
Additionally, this is an opportunity to do sql-injection

Prompt, how correctly to process a string that it was considered at search, but did not crash request and there were sql-injections?

UPD:
The question is settled. The decision in the commentary

Upvotes: 0

Views: 181

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169434

You would pass the string as a whole to psycopg2 as the second argument to .execute(). Reference: http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

import psycopg2    

conn = psycopg2.connect("dbname=test user=postgres")
curs = conn.cursor()
search_term = 'some string'
search_tuple = ('%{0}%'.format(search_term),) # note that this has to be a container
curs.execute('''select  
                from TableTemp 
                where SomeColumn like %s''',search_tuple).fetchall()

Demo:

>>> conn.execute('select * from t').fetchall()
[(u'10:00',), (u'8:00',)]
>>> conn.execute('select * from t where c like ?',('%8%',)).fetchall()
[(u'8:00',)]
>>> conn.execute('select * from t where c like ?',('%:%',)).fetchall()
[(u'10:00',), (u'8:00',)]

Upvotes: 1

Related Questions