Python3: dealing with sqlite3 SELECT using variables

Question

I trying to get an SQL selection with the LIKE parameter, but it gives my only:

false,"error_code":400,"description":"Bad Request: Message text is empty"

Here is my code:

textvar = message.text 
c.execute("SELECT * FROM run WHERE act LIKE '+textvar+'")
res = c.fetchone() 
bot.send_message(message.chat.id, res)

print textvar gives me an expected sting, taken from message.text. I've tried to get some data without LIKE and that seems everything else works pretty well.

What am I doing wrong?

Martijn Pieters · Accepted Answer · 2016-05-18 11:59:56Z

2

You are testing if the string '+textvar+' (not the value from the variable textvar!) is present in your act column. There are no such columns.

Don't use string interpolation to insert variables into SQL queries, because that opens you wide to SQL injection attacks. Use query parameters:

textvar = '%{}%'.format(message.text)
c.execute("SELECT * FROM run WHERE act LIKE ?", (textvar,))

Note that I do use string interpolation to add % wildcards to the string to make sure LIKE searches for text anywhere in the column. Without wildcards LIKE is nothing more than an equality test. % matches 0 or more arbitrary characters, _ matches exactly 1 arbitrary character. See the LIKE operator documentation.

So, for your sample text 'sting', the above code produces the string '%sting%', and then the database adapter takes care of proper quoting and executes a SELECT * FROM run WHERE act LIKE '%sting%' SQL query against your database. Any row where the act column contains the substring sting will match and be returned.

edited May 18, 2016 at 11:59

answered May 18, 2016 at 11:54

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

1 Comment

alkarba Over a year ago

thanks for an answer, it was just my inattention (i was thought that there were a matches), thank for editing my question as well.

Collectives™ on Stack Overflow

Python3: dealing with sqlite3 SELECT using variables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related