Category Archives: MySQL

Is MySQL CONCAT with PDO enough to protect LIKEs from SQL injection?

Posted on by

I’m developing a user-facing, public-accessible website where the user can input an arbitrary, hostile search term. After that, the app must do something conceptually like this against a MySQL db

SELECT fields FROM table WHERE field1 LIKE %user_input%

I must make this happen in a sql-injection safe way. So my first thought is using PDO as I’m doing everywhere else:

SELECT fields FROM table WHERE field1 LIKE :user_input_placeholder

$arrParam = array(“:user_input_placeholder” => ‘%’ . $user_input .
‘%’)

But this string concatenation doesn’t feel safe at all to me: if the user provides ‘user_%_input’ that percent won’t be escaped and will be parsed as a SQL special char. This smells dangerous already.

Someone over at SO seems to agree and proposes to do this:

SELECT fields FROM table WHERE field1 LIKE CONCAT(‘%’, :user_input_placeholder, ‘%’)

$arrParam = array(“:user_input_placeholder” => $user_input)

This looks better to me, but, still, I’m not 100% sure that this would be sql-injection free. Could you please confirm that this is the correct way to do it? If not, please provide a failing example.

Note: I must remain portable and use PDO function, so mysql_escape_string() is not a solution. I also would prefer not to manually strip-out SQL specials like % or _.

Continue reading Is MySQL CONCAT with PDO enough to protect LIKEs from SQL injection?→