cursor.execute(...) for string prefix search

Question

Suppose I have want to do a string prefix search in database, say

select * from some_table where name like 'abcde%';

where abcde is actually some user input string. How do I do it in MySQLdb in python? I could think of two ways:

cursor.execute("""select * from some_table where name like '%s%%'""", (some_prefix_to_search))

OR

cursor.execute("""select * from some_table where name like %s""", (some_prefix_to_search + '%'))

Which one is the correct way? For method 1, will MySQLdb correctly figure out '%s%%' is a whole, so it will generate 'abcde%' instead of something like ''abcde'%', i.e. will %s be substituted as the string value with or without the quote mark?

For method 2, will the % in the input string be thought as some special value and escaped?

Thanks.

Air · Accepted Answer · 2014-08-21 23:35:46Z

1

Your first method will not work; mysqldb will quote your input value again and the query will throw a syntax error.

Your second method works because the execute method only needs to consider % a special character in the query template string, so that it can identify placeholders and decide where to insert parameters. It has no reason to consider % a special character in the parameter values themselves because % is not a character that can be used for SQL injection.

answered Aug 21, 2014 at 23:35

Air

8,6852 gold badges56 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

cursor.execute(...) for string prefix search

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related