[jira] [Commented] (GUACAMOLE-942) MySQL JDBC contains an invalid SQL Query (no RDP Start possible)

["Mike Jumper (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/GUACAMOLE-942?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17028544#comment-17028544 ] 

Mike Jumper commented on GUACAMOLE-942:
---------------------------------------

For that invalid {{IN AND}} to occur within a query, an attempt would have to be made to retrieve the connections for an empty set of connection identifiers. From the MyBatis XML defining the relevant query (the MySQL implementation of {{selectReadable()}} within {{ConnectionMapper.xml}}):

{code:none}
        ...
        WHERE guacamole_connection.connection_id IN
            <foreach collection="identifiers" item="identifier"
                     open="(" separator="," close=")">
                #{identifier,jdbcType=VARCHAR}
            </foreach>
            AND ...
{code}

{{IN AND}} will only occur if {{identifiers}} is empty, but there is an explicit check for that condition prior to {{selectReadable()}} being invoked:

https://github.com/apache/guacamole-client/blob/1.1.0/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/base/ModeledDirectoryObjectService.java#L392-L394

Can you enable debug-level logging and post the full stack trace behind the error you're seeing?

> MySQL JDBC contains an invalid SQL Query (no RDP Start possible)
> ----------------------------------------------------------------
>
>                 Key: GUACAMOLE-942
>                 URL: https://issues.apache.org/jira/browse/GUACAMOLE-942
>             Project: Guacamole
>          Issue Type: Bug
>          Components: guacamole-auth-jdbc-mysql
>    Affects Versions: 1.1.0
>         Environment: CentOS 8
> Tomcat 9.0.30
> Guacamole 1.1.0, freshly upgraded from 0.9.14. 
> LDAP backend
> JDBC MySQL connector 5.1.46 to MariaDB.
>            Reporter: Stefan Bluhm
>            Priority: Major
>
> After logging in and selecting an RDP connection, the connection fails with:
>  
> "The remote desktop server is currently not unreachable."...
>  
> The catalona.out log shows the following error:
> {code:none}
> 17:02:02.435 [http-nio-8080-exec-3] ERROR o.a.g.rest.RESTExceptionMapper - Unexpected internal error: 
> ### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND 
>  (
>  guacamole_connection_permission.entity_id = 7
>  ' at line 19
> ### The error may exist in org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml
> ### The error may involve defaultParameterMap
> ### The error occurred while setting parameters
> ### SQL: SELECT guacamole_connection.connection_id, guacamole_connection.connection_name, parent_id, protocol, max_connections, max_connections_per_user, proxy_hostname, proxy_port, proxy_encryption_method, connection_weight, failover_only, MAX(start_date) AS last_active FROM guacamole_connection JOIN guacamole_connection_permission ON guacamole_connection_permission.connection_id = guacamole_connection.connection_id LEFT JOIN guacamole_connection_history ON guacamole_connection_history.connection_id = guacamole_connection.connection_id WHERE guacamole_connection.connection_id IN AND ( guacamole_connection_permission.entity_id = ? ) AND permission = 'READ' GROUP BY guacamole_connection.connection_id; SELECT primary_connection_id, guacamole_sharing_profile.sharing_profile_id FROM guacamole_sharing_profile JOIN guacamole_sharing_profile_permission ON guacamole_sharing_profile_permission.sharing_profile_id = guacamole_sharing_profile.sharing_profile_id WHERE primary_connection_id IN AND ( entity_id = ? ) AND permission = 'READ'; SELECT guacamole_connection_attribute.connection_id, attribute_name, attribute_value FROM guacamole_connection_attribute JOIN guacamole_connection_permission ON guacamole_connection_permission.connection_id = guacamole_connection_attribute.connection_id WHERE guacamole_connection_attribute.connection_id IN AND ( entity_id = ? ) AND permission = 'READ';
> ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND 
>  (
>  guacamole_connection_permission.entity_id = 7
>  ' at line 19
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)