Escaping MySQL wild cards

user_119253 0
Viewed 30893

On an older server I'm using that I can't use prepared statements on I am currently trying to fully escape user input before sending it to MySQL. For this I am using the PHP function mysql_real_escape_string.

Since this function does not escape the MySQL wildcards % and _ I am using addcslashes to escape these as well.

When I send something like:

test_test " '

to the database and then read it back the database shows:

test\_test " '

Looking at this I can't understand why the _ has a preceding backslash but the " and ' don't. Since they are all escaped with \ surely _ ' and " should all appear the same, i.e. all have the escape character visible or all not have it visible.

Are the escaping \s automatically screened out for

Can anyone explain this?

escaping mysql php
2 Answers

Surprised no one bothered to mention it after all these years, but if you don't need to do complex wildcard matching (e.g. foo%baz), I think INSTR/LOCATE/POSITION, LEFT, RIGHT, etc. should suffice. In all of my cases, I only used LIKE to match anywhere in a string (that is, for example %foobar%), so after all the horror stories about escaping LIKE patterns, I'm now using INSTR instead.

Equivalent of value LIKE '%foobar%' (match anywhere):

INSTR(value, 'foobar') > 0

Equivalent of value LIKE 'foobar%' (match at start):

INSTR(value, 'foobar') = 1

Equivalent of value LIKE '%foobar' (match at end):

RIGHT(value, 6) = 'foobar'

It might not be as straight-forward and easy to remember, and the solution for matching at the end could perhaps be improved somehow to be more universal. But these alternatives should hopefully at least give you some peace of mind in terms of security as it bypasses the need for any self-rolled escaping, and doesn't require you to alter the actual parameter values (when using prepared statements anyway).

user_9669390
Related