[jira] [Commented] (GUACAMOLE-525) Creating user results in "Incorrect syntax near 'LIMIT'"
[
https://issues.apache.org/jira/browse/GUACAMOLE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16397729#comment-16397729
]
Nick Couchman commented on GUACAMOLE-525:
-
{quote}
Accessing them by name only really works if you're passing in an object as a
parameter and not when you're passing primitive types.
{quote}
Fortunately I turned out to be wrong about this.
I think I have a solution that works - at least, I'm able to successfully
manage password history with it, and can see overall user history. Pull
request momentarily.
> Creating user results in "Incorrect syntax near 'LIMIT'"
>
>
> Key: GUACAMOLE-525
> URL: https://issues.apache.org/jira/browse/GUACAMOLE-525
> Project: Guacamole
> Issue Type: Bug
> Components: guacamole-auth-jdbc-sqlserver
>Affects Versions: 0.9.14
> Environment: MSSQL 2012
>Reporter: Tommy Larsen
>Assignee: Nick Couchman
>Priority: Major
>
> New installation with 0.9.14 authenticating against MSSQL server 2012.
> guacamole_db set to compatibility mode "SQL Server 2005 (90)".
> No problem logging in as guacadmin, but when trying to create a user, I get
> "Incorrect syntax near 'LIMIT'". Validation of the query shown in
> catalina.out by debugging in SQL management studio confirms the error.
> The list of keywords for MSSQL server 2008 and newer does not include LIMIT.
> Catalina.out error message:
> {code:none}
> 10:59:06.579 [http-nio-8080-exec-9] ERROR o.a.g.rest.RESTExceptionWrapper -
> Unexpected internal error:
> ### Error querying database. Cause:
> com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near
> 'LIMIT'.
> ### The error may exist in
> org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml
> ### The error may involve defaultParameterMap
> ### The error occurred while setting parameters
> ### SQL: SELECT [guacamole_user_password_history].user_id,
> [guacamole_user_password_history].password_hash,
> [guacamole_user_password_history].password_salt,
> [guacamole_user_password_history].password_date FROM
> [guacamole_user_password_history] JOIN [guacamole_user] ON
> [guacamole_user_password_history].user_id = [guacamole_user].user_id
> WHERE [guacamole_user].username = ? ORDER BY
> [guacamole_user_password_history].password_date DESC LIMIT ?
> ### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax
> near 'LIMIT'.
> Mar 12, 2018 10:59:06 AM com.sun.jersey.spi.container.ContainerResponse
> logException
> SEVERE: Mapped exception to response: 500 (Internal Server Error)
> org.apache.guacamole.rest.APIException
> at
> org.apache.guacamole.rest.RESTExceptionWrapper.invoke(RESTExceptionWrapper.java:202)
> 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
> com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
> at
> com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:185)
> at
> com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
> at
> com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
> at
> com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137)
> at
> com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
> at
> com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137)
> at
> com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
> at
> com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137)
> at
> com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
> at
> com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
> at
> com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
> at
> com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
> at
> com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1511)
> at
>
[jira] [Commented] (GUACAMOLE-525) Creating user results in "Incorrect syntax near 'LIMIT'"
[
https://issues.apache.org/jira/browse/GUACAMOLE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16397312#comment-16397312
]
Nick Couchman commented on GUACAMOLE-525:
-
...and it just keeps getting better. Using "TOP" re-arranges the order of
parameters into the SELECT statement, which messes up the query. Accessing
them by name (#{maxHistorySize}) only really works if you're passing in an
object as a parameter and not when you're passing primitive types. If you're
passing in Primitive types, only the order is evaluated.
> Creating user results in "Incorrect syntax near 'LIMIT'"
>
>
> Key: GUACAMOLE-525
> URL: https://issues.apache.org/jira/browse/GUACAMOLE-525
> Project: Guacamole
> Issue Type: Bug
> Components: guacamole-auth-jdbc-sqlserver
>Affects Versions: 0.9.14
> Environment: MSSQL 2012
>Reporter: Tommy Larsen
>Priority: Major
>
> New installation with 0.9.14 authenticating against MSSQL server 2012.
> guacamole_db set to compatibility mode "SQL Server 2005 (90)".
> No problem logging in as guacadmin, but when trying to create a user, I get
> "Incorrect syntax near 'LIMIT'". Validation of the query shown in
> catalina.out by debugging in SQL management studio confirms the error.
> The list of keywords for MSSQL server 2008 and newer does not include LIMIT.
> Catalina.out error message:
> {code:none}
> 10:59:06.579 [http-nio-8080-exec-9] ERROR o.a.g.rest.RESTExceptionWrapper -
> Unexpected internal error:
> ### Error querying database. Cause:
> com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near
> 'LIMIT'.
> ### The error may exist in
> org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml
> ### The error may involve defaultParameterMap
> ### The error occurred while setting parameters
> ### SQL: SELECT [guacamole_user_password_history].user_id,
> [guacamole_user_password_history].password_hash,
> [guacamole_user_password_history].password_salt,
> [guacamole_user_password_history].password_date FROM
> [guacamole_user_password_history] JOIN [guacamole_user] ON
> [guacamole_user_password_history].user_id = [guacamole_user].user_id
> WHERE [guacamole_user].username = ? ORDER BY
> [guacamole_user_password_history].password_date DESC LIMIT ?
> ### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax
> near 'LIMIT'.
> Mar 12, 2018 10:59:06 AM com.sun.jersey.spi.container.ContainerResponse
> logException
> SEVERE: Mapped exception to response: 500 (Internal Server Error)
> org.apache.guacamole.rest.APIException
> at
> org.apache.guacamole.rest.RESTExceptionWrapper.invoke(RESTExceptionWrapper.java:202)
> 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
> com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
> at
> com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:185)
> at
> com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
> at
> com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
> at
> com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137)
> at
> com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
> at
> com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137)
> at
> com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
> at
> com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137)
> at
> com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
> at
> com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
> at
> com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
> at
> com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
> at
> com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1511)
> at
>
[jira] [Commented] (GUACAMOLE-525) Creating user results in "Incorrect syntax near 'LIMIT'"
[
https://issues.apache.org/jira/browse/GUACAMOLE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16397087#comment-16397087
]
Nick Couchman commented on GUACAMOLE-525:
-
{quote}
The list of keywords for MSSQL server 2008 and newer does not include LIMIT.
{quote}
{quote}
This saddens me.
{quote}
It gets better:
https://stackoverflow.com/questions/971964/limit-10-20-in-sql-server
Apparently the support for various work-arounds is spotty, too - some of them
only work in 2005 or newer, some in 2012. This is going to make it so much fun
to come up with a solution that works across different TSQL-type databases and
different versions.
Looks like it impacts password history and user history. I initially wasn't
able to reproduce the issue because I didn't have password history enabled - it
wasn't until I looked closer at the error that I saw that's where it occurred
during user creation.
> Creating user results in "Incorrect syntax near 'LIMIT'"
>
>
> Key: GUACAMOLE-525
> URL: https://issues.apache.org/jira/browse/GUACAMOLE-525
> Project: Guacamole
> Issue Type: Bug
> Components: guacamole-auth-jdbc-sqlserver
>Affects Versions: 0.9.14
> Environment: MSSQL 2012
>Reporter: Tommy Larsen
>Priority: Major
>
> New installation with 0.9.14 authenticating against MSSQL server 2012.
> guacamole_db set to compatibility mode "SQL Server 2005 (90)".
> No problem logging in as guacadmin, but when trying to create a user, I get
> "Incorrect syntax near 'LIMIT'". Validation of the query shown in
> catalina.out by debugging in SQL management studio confirms the error.
> The list of keywords for MSSQL server 2008 and newer does not include LIMIT.
> Catalina.out error message:
> {code:none}
> 10:59:06.579 [http-nio-8080-exec-9] ERROR o.a.g.rest.RESTExceptionWrapper -
> Unexpected internal error:
> ### Error querying database. Cause:
> com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near
> 'LIMIT'.
> ### The error may exist in
> org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml
> ### The error may involve defaultParameterMap
> ### The error occurred while setting parameters
> ### SQL: SELECT [guacamole_user_password_history].user_id,
> [guacamole_user_password_history].password_hash,
> [guacamole_user_password_history].password_salt,
> [guacamole_user_password_history].password_date FROM
> [guacamole_user_password_history] JOIN [guacamole_user] ON
> [guacamole_user_password_history].user_id = [guacamole_user].user_id
> WHERE [guacamole_user].username = ? ORDER BY
> [guacamole_user_password_history].password_date DESC LIMIT ?
> ### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax
> near 'LIMIT'.
> Mar 12, 2018 10:59:06 AM com.sun.jersey.spi.container.ContainerResponse
> logException
> SEVERE: Mapped exception to response: 500 (Internal Server Error)
> org.apache.guacamole.rest.APIException
> at
> org.apache.guacamole.rest.RESTExceptionWrapper.invoke(RESTExceptionWrapper.java:202)
> 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
> com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
> at
> com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:185)
> at
> com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
> at
> com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
> at
> com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137)
> at
> com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
> at
> com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137)
> at
> com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
> at
> com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137)
> at
> com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
> at
> com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
> at
>
[jira] [Commented] (GUACAMOLE-525) Creating user results in "Incorrect syntax near 'LIMIT'"
[
https://issues.apache.org/jira/browse/GUACAMOLE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16395787#comment-16395787
]
Michael Jumper commented on GUACAMOLE-525:
--
{quote}
The list of keywords for MSSQL server 2008 and newer does not include LIMIT.
{quote}
This saddens me.
> Creating user results in "Incorrect syntax near 'LIMIT'"
>
>
> Key: GUACAMOLE-525
> URL: https://issues.apache.org/jira/browse/GUACAMOLE-525
> Project: Guacamole
> Issue Type: Bug
> Components: guacamole-auth-jdbc-sqlserver
>Affects Versions: 0.9.14
> Environment: Linux 4.4.0-87-generic #110-Ubuntu SMP Tue
> Jul 18 12:55:35 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
> Guacamole 0.9.14
> Database for auth: MSSQL 2012 - guacamole_db runs in 2005 compatibility mode.
>
>Reporter: Tommy Larsen
>Priority: Major
>
> New installation with 0.9.14 authenticating against MSSQL server 2012.
> guacamole_db set to compatibility mode "SQL Server 2005 (90)".
> No problem logging in as guacadmin, but when trying to create a user, I get
> "Incorrect syntax near 'LIMIT'". Validation of the query shown in
> catalina.out by debugging in SQL management studio confirms the error.
> The list of keywords for MSSQL server 2008 and newer does not include LIMIT.
> Catalina.out error message:
> {code:none}
> 10:59:06.579 [http-nio-8080-exec-9] ERROR o.a.g.rest.RESTExceptionWrapper -
> Unexpected internal error:
> ### Error querying database. Cause:
> com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near
> 'LIMIT'.
> ### The error may exist in
> org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml
> ### The error may involve defaultParameterMap
> ### The error occurred while setting parameters
> ### SQL: SELECT [guacamole_user_password_history].user_id,
> [guacamole_user_password_history].password_hash,
> [guacamole_user_password_history].password_salt,
> [guacamole_user_password_history].password_date FROM
> [guacamole_user_password_history] JOIN [guacamole_user] ON
> [guacamole_user_password_history].user_id = [guacamole_user].user_id
> WHERE [guacamole_user].username = ? ORDER BY
> [guacamole_user_password_history].password_date DESC LIMIT ?
> ### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax
> near 'LIMIT'.
> Mar 12, 2018 10:59:06 AM com.sun.jersey.spi.container.ContainerResponse
> logException
> SEVERE: Mapped exception to response: 500 (Internal Server Error)
> org.apache.guacamole.rest.APIException
> at
> org.apache.guacamole.rest.RESTExceptionWrapper.invoke(RESTExceptionWrapper.java:202)
> 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
> com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
> at
> com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:185)
> at
> com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
> at
> com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
> at
> com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137)
> at
> com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
> at
> com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137)
> at
> com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
> at
> com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137)
> at
> com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
> at
> com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
> at
> com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
> at
> com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
> at
> com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1511)
> at
> com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1442)
> at
>
