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

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

1 Answer 1

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

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .