Spring Security spring security in mysql 8 new keywords "groups" table name error cause bug

hi,mainly because spring security JDBC WebSecurityConfigurerAdapter .us config

@Configuration @EnableWebSecurity public class WebSecurityConfig extends WebSecurityConfigurerAdapter {

       @Autowired
       public void configureGlobal(AuthenticationManagerBuilder auth,
                                              @Qualifier("dataSource") DataSource dataSource) throws Exception {
               auth
                   .jdbcAuthentication()
                  .dataSource(dataSource)
                  .passwordEncoder(pbkdf2PasswordEncoder())
                  .withUser(User.withUsername("admin").roles("admin").build());
       }
}

so default JdbcUserDetailsManager About the groups SQL statement.

// GroupManager SQL
public static final String DEF_FIND_GROUPS_SQL = "select group_name from groups";
public static final String DEF_FIND_USERS_IN_GROUP_SQL = "select username from 
                 group_members gm, groups g "
        + "where gm.group_id = g.id" + " and g.group_name = ?";
public static final String DEF_INSERT_GROUP_SQL = "insert into groups (group_name) values (?)";
public static final String DEF_FIND_GROUP_ID_SQL = "select id from groups where group_name = 
                               ?";
public static final String DEF_INSERT_GROUP_AUTHORITY_SQL = "insert into group_authorities 
                      (group_id, authority) values (?,?)";
public static final String DEF_DELETE_GROUP_SQL = "delete from groups where id = ?";
public static final String DEF_DELETE_GROUP_AUTHORITIES_SQL = "delete from group_authorities 
                  where group_id = ?";
public static final String DEF_DELETE_GROUP_MEMBERS_SQL = "delete from group_members where 
                     group_id = ?";
public static final String DEF_RENAME_GROUP_SQL = "update groups set group_name = ? where 
                    group_name = ?";
public static final String DEF_INSERT_GROUP_MEMBER_SQL = "insert into group_members 
                          (group_id, username) values (?,?)";
public static final String DEF_DELETE_GROUP_MEMBER_SQL = "delete from group_members where 
                    group_id = ? and username = ?";
public static final String DEF_GROUP_AUTHORITIES_QUERY_SQL = "select g.id, g.group_name, 
           ga.authority "
        + "from groups g, group_authorities ga "
        + "where g.group_name = ? "
        + "and g.id = ga.group_id ";
public static final String DEF_DELETE_GROUP_AUTHORITY_SQL = "delete from group_authorities 
                   where group_id = ? and authority = ?";

look "GROUPS" become the MYSQL 8 keyword.

If executed, it will cause errors. Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups' at line

so is me temporary solution is:

@Autowired
public void configureGlobal(AuthenticationManagerBuilder auth,
                                              @Qualifier("dataSource") DataSource dataSource) throws Exception {
    JdbcUserDetailsManager jdbcUserDetailsManager = auth
            .jdbcAuthentication()
            .dataSource(dataSource)
            .passwordEncoder(pbkdf2PasswordEncoder())
            .getUserDetailsService();
    jdbcUserDetailsManager.setEnableGroups(true);
    jdbcUserDetailsManager.setFindAllGroupsSql("select group_name from `groups`");
    jdbcUserDetailsManager.setInsertGroupSql("insert into `groups` (group_name) values (?)");
    jdbcUserDetailsManager.setFindGroupIdSql("select id from `groups` where group_name = ?");
    jdbcUserDetailsManager.setDeleteGroupSql("delete from `groups` where id = ?");
    jdbcUserDetailsManager.setRenameGroupSql("update `groups` set group_name = ? where 
               group_name = ?");
    jdbcUserDetailsManager.setFindUsersInGroupSql("select username from group_members gm, 
                    `groups` g "
            + "where gm.group_id = g.id" + " and g.group_name = ?");
    jdbcUserDetailsManager.setGroupAuthoritiesByUsernameQuery("select g.id, g.group_name, 
             ga.authority "
            + "from `groups` g, group_authorities ga "
            + "where g.group_name = ? "
            + "and g.id = ga.group_id ");
    //return jdbcUserDetailsManager;
}

look code " groups ", all groups increased"``"symbols.

I think this is a BUG,so Feedback here.

Thanks to the efforts of the spring team, this is only possible when you use MYSQL 8 and enable a permission group.

Comment From: rwinch

Thanks for the report. Unfortunately, I'm not sure there is a lot we can do. It was added in MySQL 8.0.2. However, Spring Security cannot change the existing defaults because it would break users on other databases. I think the fix is for MySQL users to change the queries. Do you have any suggestions?

Comment From: vnobo

sorry sir. I don't think I have a good plan. Because in other databases, I'm not sure they follow the standard. As versions upgrade, other keywords may be added. So we have to have a unified standard that is compatible with any database, which is more complex and requires a lot of research.

Comment From: dongockien92

I think spring security should have a setting for table prefix (default is empty string). Example, when developer set prefix is "t", table names become t_groups, t_group_members, ...

Comment From: djlogan2

This is still an issue as of November 2020. Is there any kind of fix or workaround?

Comment From: rwinch

Yes. You can inject custom SQL into it.

Comment From: spring-projects-issues

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.

Comment From: spring-projects-issues

Closing due to lack of requested feedback. If you would like us to look at this issue, please provide the requested information and we will re-open the issue.