3

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

  • This would protect nothing: SQL LIKE operator will still receive the "%user_%_input" 'hostile' search and process it – Xenos Apr 10 '17 at 7:59
1

Use SELECT fields FROM table WHERE LOCATE(:user_input_placeholder, field1) > 0 but I have no idea how fast/slow it would be.

See https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_locate for LOCATE(substr, str) definition

Returns 0 if substr is not found in str, or returns the position of the occurence

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.