[GUACAMOLE-505] Individual user query fails after two or more login history entries exist

• Type: Bug
• Status: Resolved
• Priority: Blocker
• Resolution: Fixed
• Affects Version/s: 0.9.14
• Fix Version/s: 1.0.0
• Component/s: guacamole-auth-jdbc-sqlserver
• Assignee: Nick Couchman
• Reporter: Mike Jumper
• Created: 09/Feb/18 18:04
• Updated: 13/Mar/18 19:51
• Resolved: 13/Mar/18 19:51

Description

As reported on the mailing lists, the SQL Server extension currently fails after any particular user has logged in two or more times. This is due to a stray LEFT JOIN within the "selectOne" query specific to SQL Server:

    <!-- Select single user by username -->
    <select id="selectOne" resultMap="UserResultMap">

        SELECT
            [guacamole_user].user_id,
            [guacamole_user].username,
            password_hash,
            password_salt,
            password_date,
            disabled,
            expired,
            access_window_start,
            access_window_end,
            valid_from,
            valid_until,
            timezone,
            full_name,
            email_address,
            organization,
            organizational_role,
            (
                SELECT MAX(start_date)
                FROM [guacamole_user_history]
                WHERE [guacamole_user_history].user_id = [guacamole_user].user_id
            ) AS last_active
        FROM [guacamole_user]
        LEFT JOIN [guacamole_user_history] ON [guacamole_user_history].user_id = [guacamole_user].user_id
        WHERE
            [guacamole_user].username = #{username,jdbcType=VARCHAR}

    </select>

This LEFT JOIN should have been removed as part of commit 394a289.

Issue Links

• relates to: GUACAMOLE-394 Record user login / logout history