Formatting sql %like%

Question

I am trying to do the following query:

select count(*) from video where territories like %ZW%

Here is what I currently have, but it's raising an error:

for territory_code in ALL_TERRITORIES:
    sql = "select count(*) from video where territories like %{}%".format(territory_code)
    cursor.execute(sql)

What am I doing wrong here, and how would I properly escale the %% ?

score 2 · Accepted Answer · edited May 23 '17 at 12:28

2

An even better way to do this is as follows:

sql = "select count(*) from video where territories like %s"
cursor.execute(sql, ('%' + territory + '%',))

With this approach, you will be able to parameterize your query without worrying about escapes and, more importantly, without worrying about security vulnerabilities.

edited May 23 '17 at 12:28

Community

1
1

answered Jan 27 '14 at 00:48

Justin O Barber

11,291
2
40
45

score 1 · Answer 2 · answered Jan 27 '14 at 00:44

1

They way you're doing this, you need a literal string with single quotes.

 select count(*) from video where territories like '%ZW%'

answered Jan 27 '14 at 00:44

O. Jones

103,626
17
118
172

score 0 · Answer 3 · edited Jan 27 '14 at 01:08

0

Maybe you could use the simple quotation marks after the like:

"select count(*) from video where territories like '%{}%'"

edited Jan 27 '14 at 01:08

Uli Köhler

13,012
16
70
120

answered Jan 27 '14 at 00:46

Mati36

1

score 0 · Answer 4 · edited Jan 27 '14 at 01:08

0

you are missing '' single quotes around the %%. Use this instead:

"select count(*) from video where territories like '%{}%'"

edited Jan 27 '14 at 01:08

Uli Köhler

13,012
16
70
120

answered Jan 27 '14 at 00:46

mikea80

127
1
5

Formatting sql %like%

4 Answers4