New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

StringExpression.contains(): No escaping done for MS-SQL-server #630

Closed
peterpenz opened this Issue Jan 16, 2014 · 6 comments

Comments

Projects
None yet
2 participants
@peterpenz

When using StringExpression.contains() like this in combination with MS-SQL-server:

String searchString = ...;
JPAQuery query = ...;
query = query.where(QBook.book.author.contains(searchString));

then searchString won't get escaped correctly for the generated SQL-query when using MS-SQL-server. E.g. setting the searchString to "[C-P]arsen", results in finding author names like "Jon Larsen", "Jeff Karsen" or "Todd Karsenson" and so on (see http://msdn.microsoft.com/en-us/library/aa933232(SQL.80).aspx ).

However to my understanding StringExpression.contains() should escape all wildcard characters (like [ in MS-SQL server) from the input string.

We could bypass this by using StringExpression.like(String str, char escape) instead of StringExpression.contains(). We specified \ as escape character and escaped the wildcards of the input string before and attached % at the begin and end of the string. E.g. we used

query = query.where(QBook.book.author.like("%\\[C-P]arsen%", '\\'));

instead of

query = query.where(QBook.book.author.contains("[C-P]arsen"));

So only authors like "Jon [C-P]arsen" or "Todd [C-P]arsenson" are returned (yes, probably not the best real-world example ;-)).

QueryDSL version: 3.3.0
Artifacts: querydsl-apt and querydsl-jpa

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jun 14, 2014

Member

This is difficult to fix for Querydsl JPA, since we don't know the underlying SQL dialect when we serialize to JPQL, but I will try to fix it for Querydsl SQL

Member

timowest commented Jun 14, 2014

This is difficult to fix for Querydsl JPA, since we don't know the underlying SQL dialect when we serialize to JPQL, but I will try to fix it for Querydsl SQL

@timowest timowest added fixed and removed progress labels Jun 14, 2014

@timowest timowest added this to the 3.4.1 milestone Jun 14, 2014

@timowest timowest modified the milestone: 3.4.1 Jun 25, 2014

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jun 29, 2014

Member

Released in 3.4.1

Member

timowest commented Jun 29, 2014

Released in 3.4.1

@timowest timowest closed this Jun 29, 2014

@peterpenz

This comment has been minimized.

Show comment
Hide comment
@peterpenz

peterpenz Jun 29, 2014

Thanks a lot!

Thanks a lot!

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jun 29, 2014

Member

Just to make sure there are no misunderstandings, the fix has been applied for Querydsl SQL, for JPAQuery instances there won't be any SQL dialect specific like-expression treatment, since Querydsl doesn't know about the underlying RDBMS.

Member

timowest commented Jun 29, 2014

Just to make sure there are no misunderstandings, the fix has been applied for Querydsl SQL, for JPAQuery instances there won't be any SQL dialect specific like-expression treatment, since Querydsl doesn't know about the underlying RDBMS.

@peterpenz

This comment has been minimized.

Show comment
Hide comment
@peterpenz

peterpenz Jun 29, 2014

Thanks for clarification, but thats fine and all that can be done I think.

Thanks for clarification, but thats fine and all that can be done I think.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jun 29, 2014

Member

Yes, I think so too, putting RDBMS specific optimizations into the Querydsl JPA layer is difficult.

Member

timowest commented Jun 29, 2014

Yes, I think so too, putting RDBMS specific optimizations into the Querydsl JPA layer is difficult.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment