Prevent SQL injection when queries have dynamically-generated/variable-length parameter lists

Question

I am sorry for bad title but I have searched hours on web but I could not find anything.

Think about a user search performing with the text "this is good". I want to split text by spaces and make an SQL query with multiple "or ... like" depends on the count of word. It may be multiple. I've tried;

sub_query ""
search = "this is good"
split_by_space = search.split(" ")
for word in split_by_space:
    sub_query = sub_query + "content like '%{}%' or".format(word) #---> THIS IS NOT ESCAPED
sub_query = sub_query[0:-3] #---> to remove last ' or'
cursor.execute("select content from posts where %s",(sub_query))

This is not the right way of preventing SQL injection or prepared statement.

So, how to make prepared statement in Python/pymysql if I want to split words by spaces and perform search for every single word?

Answer 1

Here's one way to do it safely:

search = "this is good"
split_by_space = search.split(" ")
terms = []
params = []
for word in split_by_space:
    terms.append("content like %s")
    params.append("%%{}%%".format(word)) 
query = "select content from posts where %s".format(" OR ".join(terms))
cursor.execute(query, params)

But you'll find that using LIKE with wildcards does not perform well. Using LIKE can make the query take thousands of times longer than using a fulltext index (depending on how many rows the table has). See my presentation Full Text Search Throwdown.