Should I manually escape % when using prepared statement and Where column LIKE '%abc%abc'

Question

There are some tags in my table that has (%) such as: %tag OR tag% I am using PDO prepared statement, my problem here is if I need to escape % or leaved it like this:

$query = "SELECT * FROM table where tags like :tags";

$tags = 'tag%';
//or
$tags = 'tags\%';

$sth->prepare($query);
$sth->bindValue(":tags", '%'.$tags.'%');

UPDATE:

The column value is test%test when I search for test%test using: LIKE %test%test its ok without escape, but I want to find all tags that contains % so when I used %%% without escape it shows all results even if the column not contain %

DEMO: http://sqlfiddle.com/#!2/ae24e/6

@YourCommonSense: if search for `%test%test%` its ok without escape, but when I want to find all tags with `%` I need to use `%\%%` why that? otherwise if using `%%%` shows all results — Jason OOO, Oct 08 '13 at 09:32
Why "why"? this is just how it works. % is a wildcard while \% is a literal % — Your Common Sense, Oct 08 '13 at 09:33
@YourCommonSense: then what is different between `LIKE \`%%%\`` and `like \`%test%test%\`` — Jason OOO, Oct 08 '13 at 09:36
first one will return every row, while second one - only rows contains two tests — Your Common Sense, Oct 08 '13 at 09:43
@JasonOOO: Straight question: _"should I manually escape the `%`'s in LIKEs?"_ Straight answer: _"Yes"_. I don't get how SO can bicker about this... Using `addclashes()` in PHP works a treat for this. — Wrikken, Oct 14 '13 at 18:18
You should look at the very nice answer from @bobince, http://stackoverflow.com/questions/3683746/escaping-mysql-wild-cards — Alain Tiemblo, Oct 15 '13 at 22:44

JackDev · Accepted Answer · 2013-10-18T01:13:44.010

From reading the comments, I take it your questions is: [what is different between LIKE %%% and like %test%test%] as well '%\%%'.

While I'm sure you know most of it, I'm going to start from scratch for clarity.

'%' in SQL like clause is a 'wildcard' and it match anything. See http://www.w3schools.com/sql/sql_wildcards.asp for more information. (If you are familiar with Expression Language, it is similar to a '.*')
'\%' on the other hand escapes that 'wildcard' function, enabling you to match '%' characters in the string. (In expression language it's the same as '\.' allowing you to match dots in the string)

So in your circumstance:

'%%%' means: [match anything][match anything][matching anything], and is the same as '%'
'%test%test%' means: [anything]test[anything]test[anything], and as long as there's two 'test' in the string, it is a match. Matches would include 'testtest', 'asfasdftestasfasftest', 'asfasftesttestasdfsd', 'test%%%%test', '%%%%%test%%%%test%%%%'
'%\%%' means: [anything]%[anything], and as long as the string has a '%' literal it will be a match. Matches include '%%%%%', 'asdfasdf%dsaf', '%dasfa', 'asdfasdf%', 'asdf%%dsaf%%dafa%daf%'

Let me know if this has answered your question.

score -2 · Answer 2 · answered Oct 08 '13 at 09:07

-2

I would personally do it like this:

$query = "SELECT * FROM tabler WHERE tags LIKE :tags";
$sth->prepare($query);
$sth->bindValue(':tags', "%{%tags}%";

answered Oct 08 '13 at 09:07

Graymic

17
4

Should I manually escape % when using prepared statement and Where column LIKE '%abc%abc'

2 Answers2