Search code examples
sqlpostgresqljdbcclojuresql-like

Proper formatting of sql query with LIKE clause

I have an SQL query with a LIKE clause that uses the % wildcard. I have read that LIKE clauses can be problematic, like here: http://githubengineering.com/like-injection/. But I am not sure if there would be a performance difference between the two different function calls below for certain user-inputs (although the query is essentially the same), and if the difference between them is related to what is talked about in the link.

I expected that a call to the first function would only return results like "&my-query%", so literally the user's input sorounded by two percent signs, but that is not the case.

(query db ["SELECT * FROM some_table WHERE some_value LIKE ?"
       (str "%" user-input "%")])

(query db ["SELECT * FROM some_table WHERE some_value LIKE '%' ? '%']"
       user-input])
Solution

  • Your first query looks safe to me. The second looks like invalid SQL. It looks like you are trying to do this:

    (query db ["SELECT * FROM some_table WHERE some_value LIKE CONCAT('%', ?, '%')"
   user-input])

    which is also safe.

    An unsafe query would embed the user input directly into the SQL string like this:

    (query db [(str "SELECT * FROM some_table WHERE some_value LIKE '%" user-input "%'")])