What I want is execute the sql
select * from articles where author like "%steven%".
For the sake of safety, i used like this way :
cursor.execute('select * from articles where %s like %s', ('author', '%steven%')
Then the result is just empty, not get a syntax error, but just empty set. But I am pretty sure there is some thing inside, I can get result use the first sql. Is there anything run with my code ?
You can't set a column name like a parameter where you're doing where %s like %s. To dynamically set the column name you need to do actual string manipulation like:
sql = 'select * from articles where '+ sql_identifier('author') +' like %s'
cursor.execute(sql, ('%steven%',))
Where sql_identifier is your lib's function for making an identifier safe for SQL injection. Something like:
# don't actually use this!
def sql_identifier(s):
return '"%s"' % s.replace('"','')
But with actual testing and knowledge of the DB engine you're using.
The problem here is fact a minor mistake. Thanks to @Asad Saeeduddin, when I try to use print cursor._last_executed to check what has happened. I found that what is in fact executed is
SELECT * FROM articles WHERE 'title' LIKE '%steven%', look the quotation mark around the title, that's the reason why I got empty set.
So always remember the string after formatting will have a quotation around
print cursor._last_executed, see what query is actually being executed.