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 _.