HSQL database user lacks privilege or object not found error try insert data into database

Question

I try to read data from text file then insert into database but keep getting error.

Below is the code I am writing

Class.forName("org.hsqldb.jdbcDriver");
connection = DriverManager.getConnection("jdbc:hsqldb:TestDB", "sa", "123");
connection.prepareStatement("drop table people if exists;").execute();
connection.prepareStatement("create table people (id integer, name varchar(20) not null, picture varchar(20), detail varchar(40), gender varchar(5), age integer, state varchar(5), primary key(id));").execute();
String fileName = "/Users/da/Desktop/AP Assignment 2/people.txt";
BufferedReader br = new BufferedReader(new FileReader(fileName));
while (strLine != null) {
    String data[] = null;
    strLine = br.readLine();
    data = strLine.split(",");
    int id = Integer.parseInt(data[0]);
    String name = data[1];
    String picture = data[2];
    String detail = data[3];
    String gender = data[4];
    int age = Integer.parseInt(data[5]);
    System.out.println(age);
    String state = data[6];
    String sql = "insert into people(id, name, picture, detail, gender, age, state) values (" + id + "," + name + ", " + picture + ", " + detail + ", " + gender + ", " + age + ", " + state + ")";
    connection.prepareStatement(sql).execute();
    connection.commit();
}
br.close();

Answer 1

By attempting to embed the data values into your SQL string you have created an SQL Injection problem. For example, the code

int id = 1;
String name = "gord";
String sql = "INSERT INTO people (id, name) VALUES (" + id + ", " + name + ")";
System.out.println(sql);

prints

INSERT INTO people (id, name) VALUES (1, gord)

When we try to execute that statement the query parser recognizes 1 as an integer literal but it interprets gord as a column name, so we'll get a "user lacks privilege or object not found" error.

The solution is to use a parameterized query like this:

int id = 1;
String name = "gord";
String sql = "INSERT INTO people (id, name) VALUES (?, ?)";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setInt(1, id);
ps.setString(2, name);
ps.executeUpdate();