CODEWITHSUNDEEP
pythonpython-3.xsqlite
alkarba
alkarba

Reputation: 13

Python3: dealing with sqlite3 SELECT using variables

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?

Upvotes: 0

Views: 471

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1121962

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.

Upvotes: 2

Related Questions