Explore our sites

Stack Exchange
tour help careers 2.0
Take the tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite

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.

share|improve this question
 
That's not regex, that's LIKE.maybe you meant RLIKE –  Bohemian May 29 '13 at 22:53
 
possible duplicate of Searching using MySQL: How to escape wildcards –  Bill Karwin May 29 '13 at 22:55
 
@Bohemian the documentation for LIKE does say "Pattern matching using SQL simple regular expression comparison." –  Explosion Pills May 29 '13 at 22:56
add comment

1 Answer

up vote 2 down vote accepted

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("%", "\\\\%")).

share|improve this answer
 
Agreed, another thing, apart LIKE, is there any other Operator / Function / etc that evaluates expressions like that? –  Neo May 29 '13 at 23:01
 
@Neo not like that, but there is RLIKE –  Explosion Pills May 29 '13 at 23:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.