0

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

Yura Kharpaev
  • 41
  • 4
  • Do these answer your question? "[How should I prevent abuse when using web.py's web.database?](https://stackoverflow.com/q/8447920/90527)", "[Wrong sql syntax because of single or double quote in string](https://stackoverflow.com/q/30046868/90527)" – outis Jun 21 '21 at 09:28

1 Answers1

1

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',)]
mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
  • Thanks, this answer is suitable for self-reference to the database. At work I use a framework in which there is a possibility to transfer only a line with a sql-request and a set of parameters. Parameters can specify a type, but it does not suit me. There was an option to self-shield characters and so on, but the best solution was to replace the quotes with double quotes. – Yura Kharpaev May 10 '17 at 10:40
  • @YuraKharpaev (and anyone else trying what Yura suggests): the following warning from the psycopg2 docs cannot be stressed enough: "Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint." The `str.format()` function should also be included in that list. – outis Jun 21 '21 at 09:16