How can I insert into table in liquibase

Question

I'm working with liquibase and I'm trying to insert roles into roles table.

CREATE TABLE roles
(
`role_id`   int(11)     NOT NULL AUTO_INCREMENT,
`role_name` varchar(50) NOT NULL,
PRIMARY KEY (`role_id`)

AUTO_INCREMENT = 18
DEFAULT CHARSET = utf8;

insert into roles (role_id, role_name)
values (1, 'USER');

Role class:

@Entity
@Table(name = "roles")
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class RoleEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int roleId;
    @Column(nullable = false, length = 45)
    private String roleName;
public RoleEntity(int roleId) {
    this.roleId = roleId;
}

public RoleEntity(String roleName) {
    this.roleName = roleName;
}

}

Create table works fine, table is created. Problem is with insert into. The error is

 Reason: liquibase.exception.DatabaseException: 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 'insert into roles (role_id, role_name)
values (1, 'USER')' at line 10 [Failed SQL: (1064) CREATE TABLE roles
(
    `role_id`   int(11)     NOT NULL AUTO_INCREMENT,
    `role_name` varchar(50) NOT NULL,
    PRIMARY KEY (`role_id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 18
  DEFAULT CHARSET = utf8;

But syntax is OK. How in other way can I insert roles to my table. Somehow through db_changelog.xml?

score 0 · Answer 1 · answered Jun 01 '22 at 21:12

From this post: 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 '''')' at line 2

The single quote char terminates the string in MySQL and everything past that is treated as a SQL command. You REALLY don't want to write your SQL like that. At best, your application will break intermittently (as you're observing) and at worst, you have just introduced a huge security vulnerability.

Here is a great article on prepared statements and PDO.

How can I insert into table in liquibase

1 Answers1