MySQL and LIKE comparison with %

问题:

If someone passes a '%' to a field that compares in my sql with su.username LIKE CONCAT('%', email ,'%')) it returns all rows. It ends up looking like su.username LIKE CONCAT('%%%'). Can I get around this in anyway without filtering out the '%'?

回答1:

I'm assuming you mean you want to escape the % so it matches a literal % instead of anything.

In that case, you just need:

... su.username LIKE CONCAT('%',REPLACE(email,'%','\\%'),'%')

回答2:

You need to escape the %, so it literally matches '%'

select * from mytable
where mycol like '%\%%';