"unsupported format character at index "

Question

I'm trying to format a PostgreSQL query in python and that query has to have '%' between the name of a survey so I can filter surveys by name.

Here is the code:

sql = """select survey_data
from survey_data.survey_data 
where codigo_do_projeto like '%s%'
ORDER BY data_de_inicio_da_coleta desc 
limit %s
offset %s"""

However it throws this error:

"unsupported format character 'P' (0x50) at index 79"

I don't know how to make python ignore the "%" character.

Can't you use a parametrized query instead? Injecting string directly like this potentially expose you for SQL Injection problems. — pepoluan, Nov 04 '22 at 16:29
Use two percent signs (%%) https://stackoverflow.com/questions/10678229/how-can-i-selectively-escape-percent-in-python-strings — Zach J., Nov 04 '22 at 16:32
That's true! There is that problem I completely overlooked, thanks for pointing that out! — Eduardo Pitta, Nov 04 '22 at 16:34
I was using parametrized search but it wasn't working, so I gave format a try. — Eduardo Pitta, Nov 04 '22 at 16:39

Adrian Klaver · Accepted Answer · 2022-11-04T18:35:22.607

0

You have to escape the %.


sql = """select survey_data
from survey_data.survey_data 
where codigo_do_projeto like '%%'||%s||'%%'
ORDER BY data_de_inicio_da_coleta desc 
limit %s
offset %s"""

Or you can do:


search_val = '%search_term%'


sql = """select survey_data
from survey_data.survey_data 
where codigo_do_projeto like %s
ORDER BY data_de_inicio_da_coleta desc 
limit %s
offset %s"""

cur.execute(sql, [search_val, val2, val3])

edited Nov 04 '22 at 18:35

answered Nov 04 '22 at 18:29

Adrian Klaver

15,886
2
17
28

Nice, it worked! Thanks Adrian! – Eduardo Pitta Nov 04 '22 at 18:40

score -1 · Answer 2 · answered Nov 04 '22 at 16:33

-1

You need to put the survey_name part inside single quotes:

sql = """SELECT survey_data
FROM survey_data.survey_data 
WHERE project_code like '%{0}%'
ORDER BY starting_date desc 
LIMIT {1} 
OFFSET {2}*{1}""".format(survey_name,items_per_page,page_number)

answered Nov 04 '22 at 16:33

jprebys

2,469
1
11
16

Same problem unfortunately. SQL would complain about that, but it seems it is a Python error. – Eduardo Pitta Nov 04 '22 at 16:37
**Do not use** string interpolation for passing in parameters. See [Parameters](https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries) for why and how to do this safetly. – Adrian Klaver Nov 04 '22 at 18:13
Changed the code, I still have the same error – Eduardo Pitta Nov 04 '22 at 18:25
See my answer for solution. – Adrian Klaver Nov 04 '22 at 18:32

"unsupported format character at index "

2 Answers2