SQL exception preparing query with ORMLite

Question

I am using an ORM (ORMlite) and all my calls are going well until I get the following error.

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement " SELECT * FROM ""STORIES"" WHERE ""TITLE"" = 'Deepcut case leads 'NOT FOLLOWED[*]'' "; SQL statement: SELECT * FROM Stories WHERE title = 'Deepcut case leads 'not followed'' [42000-152] at org.h2.message.DbException.getJdbcSQLException(DbException.java:327) at org.h2.message.DbException.get(DbException.java:167) at org.h2.message.DbException.get(DbException.java:144) at org.h2.message.DbException.getSyntaxError(DbException.java:179) at org.h2.command.Parser.getSyntaxError(Parser.java:480) at org.h2.command.Parser.prepareCommand(Parser.java:229) at org.h2.engine.Session.prepareLocal(Session.java:426) at org.h2.engine.Session.prepareCommand(Session.java:374) at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1093) at org.h2.jdbc.JdbcPreparedStatement.(JdbcPreparedStatement.java:71) at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:601) at com.j256.ormlite.jdbc.JdbcDatabaseConnection.compileStatement(JdbcDatabaseConnection.java:83) at com.j256.ormlite.stmt.mapped.MappedPreparedStmt.compile(MappedPreparedStmt.java:44) at com.j256.ormlite.stmt.StatementExecutor.buildIterator(StatementExecutor.java:169) at com.j256.ormlite.stmt.StatementExecutor.query(StatementExecutor.java:119) at com.j256.ormlite.dao.BaseDaoImpl.query(BaseDaoImpl.java:189)

I'm confused as to whats going wrong. I am calling the search from these lines:

// get our query builder from the DAO
QueryBuilder<Story, Integer> queryBuilder = StoryDao.queryBuilder();
// the 'title' field must be equal to title (a variable)
queryBuilder.where().eq(Story.TITLE_FIELD_NAME, title);
// prepare our sql statement
PreparedQuery<Story> preparedQuery = queryBuilder.prepare();
// query for all stories that have that title
List<Story> accountList = StoryDao.query(preparedQuery);

Gray · Accepted Answer · 2021-01-26 23:39:54Z

11

Syntax error in SQL statement " SELECT * FROM ""STORIES"" WHERE ""TITLE""...

@bemace is correct that there seem to be quotes in the title that is screwing up the escaping of strings generated by the query.

In ORMLite, you should use the SelectArg feature which will generate a query with SQL ? arguments and then pass the string to the prepared statement directly.

See the documentation on the SelectArg. With it, you'd do something like:

QueryBuilder<Story, Integer> queryBuilder = StoryDao.queryBuilder();
SelectArg titleArg = new SelectArg();
queryBuilder.where().eq(Story.TITLE_FIELD_NAME, titleArg);
PreparedQuery<Story> preparedQuery = queryBuilder.prepare();
titleArg.setValue(title);
List<Story> accountList = StoryDao.query(preparedQuery);

edited Jan 26, 2021 at 23:39

answered Mar 12, 2011 at 17:26

Gray

117k24 gold badges305 silver badges360 bronze badges

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

Comments

Brad Mace · Accepted Answer · 2011-03-12 05:47:21Z

1

I'm kind of guessing but it looks like there's a problem with the value in the title field, maybe an unescaped quote mark?

I'm not familiar with ORMLite but title = 'Deepcut case leads 'not followed'' doesn't look right. Should probably be "Deepcut case leads 'not followed'" or 'Deepcut case leads \'not followed\'' or some such.

answered Mar 12, 2011 at 5:47

Brad Mace

28k19 gold badges110 silver badges152 bronze badges

5 Comments

user330315 Over a year ago

Using double quotes or backslash will not work on H2 as it follows the SQL standard. Which means that string literals need to be enclosed in single quotes and to embed another quote you need to use two single quotes: 'Deepcut case leads ''not followed'

Thomas Mueller Over a year ago

Yes, 'Deepcut case leads ''not followed' is correct according to the SQL standard. All SQL databases support this.

Thomas Mueller Over a year ago

More details: 'Deepcut case leads ''not followed''' is correct according to the SQL standard. All SQL databases support this. "Deepcut case leads 'not followed'" is a quoted identifier, again according to the SQL standard (but I guess there is no such column name). 'Deepcut case leads \'not followed\'' is illegal according to the SQL standard, but supported by MySQL and PostgreSQL if SQL compatibility is disabled.

Alex Over a year ago

Is there a way to make java auto escape my strings? or should I write it myself?

user330315 Over a year ago

@Alex: use a PreparedStatement and you don't have to worry about things like that

user330315 · Accepted Answer · 2011-03-12 08:24:55Z

1

The correct syntax for the statement would be:

SELECT * FROM Stories WHERE title = 'Deepcut case leads ''not followed'' ';

Note the duplicated single quotes inside the string literal.

You will need to tell your ORM layer to follow the ANSI SQL rules for literals.

answered Mar 12, 2011 at 8:24

user330315

Comments

Sanjay T. Sharma · Accepted Answer · 2011-03-12 07:47:57Z

0

The exception says that there is some syntactical problem with your generated SELECT statement. Can you print out the generated query? Doing that might help you pin down the exact issue here.

EDIT: Looking closely at your trace shows that string escaping is not handled properly here. Is this your own QueryBuilder? Also, as per this link, are you using SelectArg or directly setting the title?

edited Mar 12, 2011 at 7:47

answered Mar 12, 2011 at 5:44

Sanjay T. Sharma

23.3k4 gold badges63 silver badges72 bronze badges

Collectives™ on Stack Overflow

SQL exception preparing query with ORMLite

4 Answers 4

Comments

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related