Line breaks not created/escaped correctly in WHERE clauses #1275

Closed
larskn opened this Issue Mar 25, 2015 · 2 comments

Projects

None yet

2 participants

@larskn
larskn commented Mar 25, 2015

As described here: https://groups.google.com/forum/#!topic/querydsl/z08cNws-WSI

Using querydsl-sql 3.6.2 with MS SQL and MySQL for literal query generation. Simple example:

private String createContainsQuery(final SQLTemplates template, final String filter) {
        final Configuration configuration = new Configuration(template);
        configuration.setUseLiterals(true);
        final SimplePath<Object> table = Expressions.path(Object.class, "Tablename");
        final StringPath column = Expressions.stringPath(table, "aColumn");
        final SQLQuery sqlQuery = new SQLQuery(configuration).from(table).where(column.contains(filter));
        return sqlQuery.getSQL(column).getSQL();
    }

Creating contains and equals statements with a line break ("foo\nbar") or a literal "\n" ("foo\nbar") leads to the following reults:

SQL Server

  • Contains with line break:
select Tablename.aColumn
from Tablename
where Tablename.aColumn like '%foo\nbar%' escape '\'

This does not work since SQL server (at least 2014) does not seem to accept a literal "\n" as a line break, only '%foo' + CHAR(13) + CHAR(10) + 'bar%' or '%foo' + CHAR(10) + 'bar%' (depending on the OS) seem to work.

  • Contains with literal \n:
select Tablename.aColumn
from Tablename
where Tablename.aColumn like '%foo\\nbar%' escape '\'

This does work although '%foo\nbar%' (without escape) would be sufficient

  • Equals with line break:
select Tablename.aColumn
from Tablename
where Tablename.aColumn = 'foo\nbar'
  • Equals with literal \n:
select Tablename.aColumn
from Tablename
where Tablename.aColumn = 'foo\nbar'

One query should be looking for a line break and the other should be looking for a literal "\n" but both produce the same output. (with the latter beeing correct)

MySQL

  • Contains with line break:
select Tablename.aColumn
from Tablename
where Tablename.aColumn like '%foo\\nbar%'

The "\n" is escaped wrongly and thus the query is looking for the literal "\n" and not for a line break.

  • Contains with literal \n:
select Tablename.aColumn
from Tablename
where Tablename.aColumn like '%foo\\\\nbar%'

This query searches for the literal "\n" (two backslashes) rather than the literal "\n"

  • Equals with line break:
select Tablename.aColumn
from Tablename
where Tablename.aColumn = 'foo\\nbar'
  • Equals with literal \n:
select Tablename.aColumn
from Tablename
where Tablename.aColumn = 'foo\\nbar'

Again both queries produce the same output although they should search for different results.

@larskn
larskn commented Mar 26, 2015

I looked a little more into the issue and spotted one thing I overlooked at first:

Looking at the first query I mentioned:

select Tablename.aColumn
from Tablename
where Tablename.aColumn like '%foo\nbar%' escape '\'

Since the "\n" is showing up in the output means it was already escaped. Otherwise the query would look like.

select Tablename.aColumn
from Tablename
where Tablename.aColumn like '%foo
bar%' escape '\'

Which means the general problem seems to be that QueryDSL internally escapes the line break at some point which leads to the query looking for a literal line break.

If i only create the base query

select Tablename.aColumn
from Tablename

and manually add in Java code

query += " where Tablename.aColumn like '%foo\nbar%' ";

The query correctly returns the row with the line break. (So CHAR(10) and CHAR(13) are not needed, this is handled by the JDBC driver)

@timowest timowest added the progress label Apr 1, 2015
@timowest
Member
timowest commented Apr 1, 2015

Thanks for issue. I added a PR that should fix it.

@timowest timowest closed this in #1297 Apr 4, 2015
@timowest timowest removed the progress label May 9, 2015
@timowest timowest added this to the 4.0.0 milestone May 9, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment