Mysql, dealing with String Regex

Question

I'm developing a Java desktop application that connects with a database, and I would like to know the next. It results that as far as I know, Prepared Statements avoid SQL injections while you don't make a direct concatenation with user data, but today I figured out that it doesn't escape String regex (like '%' from the LIKE operator,) due that it just escapes characters that could break up the String itself and alter the query. So, if user does:

Search = "%Dogs"; // User input
Query = "SELECT * FROM Table WHERE Field LIKE ?";
blah.setString(1, Search);

It will return all the rows that contains 'Dogs' at the beginning by injection.

Now I ask:

1-) Is this something bad / dangerous viewing from a global point?

2-) Is there a full list of Regex that Mysql could use from inside a String? if so, can you please share it with me?

Thank you.

possible duplicate of Searching using MySQL: How to escape wildcards — Bill Karwin
– Bill Karwin, Commented May 29, 2013 at 22:55
@Bohemian the documentation for LIKE does say "Pattern matching using SQL simple regular expression comparison." — Explosion Pills
– Explosion Pills, Commented May 29, 2013 at 22:56

Explosion Pills · Accepted Answer · 2013-05-29 22:55:08Z

2

If the user uses such meta characters in their search, the results may or may not be catastrophic, but a search for %% could be bad. A valid search for %Dogs may also not return the results the user was expecting which affects their experience.

LIKE only offers two meta characters, so you can escape them both on your own when acquired from users (simply using something akin to Search = Search.replaceAll("%", "\\\\%")).

answered May 29, 2013 at 22:55

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Neo Over a year ago

Agreed, another thing, apart LIKE, is there any other Operator / Function / etc that evaluates expressions like that?

Explosion Pills Over a year ago

@Neo not like that, but there is RLIKE

Collectives™ on Stack Overflow

Mysql, dealing with String Regex

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related