Sunday, January 21, 2018

GWT & Spring Security

Sorry this is going to be a quick post without too much detail. But hopefully this will help someone out anyways until I can get to adding detail.

I've been struggling with how to integrate spring security with GWT. I don't have very much experience with using spring security and have been going in circles. After adding spring security with two additional files to configure it, I was able to show a login page and authenticate the user. However, as soon as that happened, the application failed to load because GWT uses POST requests to access the RPC. As it turns out, this isn't a sign that the configuration failed. Spring security expects a CSRF token on post requests to prevent security problems ... GWT doesn't insert this token when accessing the RPC... and so we get a forbidden error. Disabling csrf in the config for spring security is also not working.

I next tried using GWT's XSRF protection ... again I'm not very familiar with web application security. I thought if I did this, then my application would be able to tell spring security the csrf that it expects. This turned into a frustrating chicken and egg issue. To get the XSRF token, gwt does a POST request ... which is blocked by spring security.

Spring security examples that I've found have been for standard web applications, as opposed to GWT's host page mechanism.

I think the final solution is going to be:


  1. Use spring security as a gate keeper to the application
    1. Spring security needs to be configured to override a bunch of things, along the lines of https://crazygui.wordpress.com/2014/08/29/secure-rest-services-using-spring-security/
  2. After authenticated, the application is responsible for maintaining security within itself
    1. We can use the XSRF here for additional security
    2. maintain session information for the authenticated user
So why even use spring security and not just wire up my own login screen etc? Spring security I think will be more flexible and give more options later down the road.



Monday, January 8, 2018

Hibernate exception using match/against sql

*UPDATE*
I finally found the real reason and is quite the facepalm. The keyword "MATCH" is MySql specific. It is not part of the more general Sql syntax. Oops.
----

Apologies that this is going to be a fairly brief post, maybe someday I'll have time to come back and flesh out the details a bit more. But I think this is better than me forgetting to post at all and having this lost in my notes. Hope this helps someone else out.

The problem I've been struggling with is using the "MATCH" sql keyword in hibernate. Specifically, it is failing in my unit test.Below I list the 2 configurations that did not work (A & B) and the full stacktrace. The configuration that does work is to use the mysql driver.

tl;dr: Use MySQL for the database

Code:
final String queryString = "SELECT * FROM Course WHERE MATCH (name) AGAINST ('complex' IN BOOLEAN MODE)";
final Session session = sessionFactory.openSession();
session.beginTransaction();
NativeQuery query = session.createNativeQuery(queryString, Course.class);
List results = query.getResultList();
session.getTransaction().commit();
session.close();

Configuration A:
connection.driver_class = org.h2.Driver
connection.url = dbc:h2:mem:db1;DB_CLOSE_DELAY=-1;MVCC=TRUE
connection.username = root
connection.password = "" 
dialect = org.hibernate.dialect.H2Dialect
hbm2ddl.auto = create

Exception A:
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not prepare statement
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:147)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1423)
at org.hibernate.query.Query.getResultList(Query.java:146)
at org.catalyst.courses.entities.HibernateTest.searchCoursesQuery(HibernateTest.java:207)
at org.catalyst.courses.entities.CourseTest.testNamedQueries(CourseTest.java:444)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: org.hibernate.exception.GenericJDBCException: could not prepare statement
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1940)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1909)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1887)
at org.hibernate.loader.Loader.doQuery(Loader.java:932)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
at org.hibernate.loader.Loader.doList(Loader.java:2615)
at org.hibernate.loader.Loader.doList(Loader.java:2598)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2430)
at org.hibernate.loader.Loader.list(Loader.java:2425)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:335)
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2160)
at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:992)
at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:148)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414)
... 28 more
Caused by: org.h2.jdbc.JdbcSQLException: Function "MATCH" not found; SQL statement:
SELECT * FROM Course WHERE MATCH (name) AGAINST ('complex' IN BOOLEAN MODE) [90022-194]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:179)
at org.h2.message.DbException.get(DbException.java:155)
at org.h2.command.Parser.readJavaFunction(Parser.java:2471)
at org.h2.command.Parser.readFunction(Parser.java:2523)
at org.h2.command.Parser.readTerm(Parser.java:2860)
at org.h2.command.Parser.readFactor(Parser.java:2374)
at org.h2.command.Parser.readSum(Parser.java:2361)
at org.h2.command.Parser.readConcat(Parser.java:2331)
at org.h2.command.Parser.readCondition(Parser.java:2166)
at org.h2.command.Parser.readAnd(Parser.java:2138)
at org.h2.command.Parser.readExpression(Parser.java:2130)
at org.h2.command.Parser.parseSelectSimple(Parser.java:2087)
at org.h2.command.Parser.parseSelectSub(Parser.java:1928)
at org.h2.command.Parser.parseSelectUnion(Parser.java:1746)
at org.h2.command.Parser.parseSelect(Parser.java:1734)
at org.h2.command.Parser.parsePrepared(Parser.java:447)
at org.h2.command.Parser.parse(Parser.java:319)
at org.h2.command.Parser.parse(Parser.java:291)
at org.h2.command.Parser.prepareCommand(Parser.java:256)
at org.h2.engine.Session.prepareLocal(Session.java:564)
at org.h2.engine.Session.prepareCommand(Session.java:505)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1204)
at org.h2.jdbc.JdbcPreparedStatement.(JdbcPreparedStatement.java:73)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:288)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172)

... 43 more

Root cause A:
MATCH() function is NOT supported by H2 (http://h2-database.66688.n3.nabble.com/MySQL-Compatibility-Mode-Fulltext-MATCH-function-td4033152.html)

Configuration B:

connection.driver_class = org.hsqldb.jdbcDriver
connection.url = jdbc:hsqldb:mem:testdb
connection.username = root
connection.password = "" 
dialect = org.hibernate.dialect.HSQLDialect
hbm2ddl.auto = create

Exception B:
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not prepare statement
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:147)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1423)
at org.hibernate.query.Query.getResultList(Query.java:146)
at org.catalyst.courses.entities.HibernateTest.searchCoursesQuery(HibernateTest.java:207)
at org.catalyst.courses.entities.CourseTest.testNamedQueries(CourseTest.java:444)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1940)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1909)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1887)
at org.hibernate.loader.Loader.doQuery(Loader.java:932)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
at org.hibernate.loader.Loader.doList(Loader.java:2615)
at org.hibernate.loader.Loader.doList(Loader.java:2598)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2430)
at org.hibernate.loader.Loader.list(Loader.java:2425)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:335)
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2160)
at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:992)
at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:148)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414)
... 28 more
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: MATCH in statement [SELECT * FROM Course WHERE MATCH (name) AGAINST ('complex' IN BOOLEAN MODE)]
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172)
... 43 more
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: MATCH
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.ParserDQL.readColumnOrFunctionExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadSimpleValueExpressionPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanPrimaryOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanTestOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanFactorOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanTermOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanValueExpression(Unknown Source)
at org.hsqldb.ParserDQL.readWhereGroupHaving(Unknown Source)
at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
at org.hsqldb.Session.compileStatement(Unknown Source)
at org.hsqldb.StatementManager.compile(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)

... 47 more

Root Cause B:
:: shrug :: No clue. I've gone cross-eyed trying to figure this out, this should be supported by HSQL. The documentation says it's supported, but I can't figure out what the heck. The only thing I can think is maybe I have the magic SQL incantation incorrect. I didn't want to futz with the query string too much because ultimately the underlying database will be a MySQL database.