CODEWITHSUNDEEP
javasqljdbc
Alex Bakic
Alex Bakic

Reputation: 1

Java DB (JDBC) SQL - Lexical Error , encountered "@" (64) after ""

I have compiled my JavaFX application and when I try to take the data from the UI and update the database I get this error:

java.sql.SQLSyntaxErrorException: Lexical Error at line 1 , column 105. Encountered "@" (64) after : ""
at org.apache.derby.client.am.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.client.am.ClientStatement.execute(Unknown Source)
at Query.generateOperation(Query.java:45)
at AdminAccount.addAdmin(AdminAccount.java:27)
at Root.lambdaExpression(Root.java:89)

This is the code which is referenced for Query ->

public void generateOperation(String query) {
    try {
        Statement statement = connection.createStatement();
        statement.execute(query) // line raised by compiler
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

This is the code which is referenced for AdminAccount ->

public void addAdmin(String username, String password, String email) {
    int id = generateId(); // return random number for id
    String values = id + ", " + username + ", " + password + ", " + email;
    DB.generateOperation("insert into APP.ADMINDETAILS (ID , USERNAME , PASSWORD , EMAIL) values (" + values + ")"); // DB is the Query object I use to interact with the database . This line is raised by the compiler
}

This is the code which is called by the user interface

submit.setOnAction(e -> {
    if (...) {
         adminAccount.addAdmin(usernameEntry.getText(), passwordEntry.getText(), emailEntry.getText()) ; // this is the line raised by compiler
    }
});

What should I amend to this code so that I don't get the lexical error when parsing the SQL statement. I think there is trouble parsing the email entry as the compiler references the "@" symbol. Should I be using another method for constructing SQL statements?

Upvotes: 0

Views: 2120

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109015

The correct way of doing things is to NOT concatenate values into a query string. You need to use PreparedStatement, with placeholders for each value you want to set, and then explicitly set each value before you execute.

Given your code, you would need to do something like:

try (PreparedStatement insertStatement = connection.prepareStatement(
        "insert into APP.ADMINDETAILS (ID, USERNAME, PASSWORD, EMAIL) values (?, ?, ?, ?)")) {
    insertStatement.setInt(1, id);
    insertStatement.setString(2, username);
    // Do NOT do this in a real application, hash is with a password hash algorithm
    insertStatement.setString(3, password); 
    insertStatement.setString(4, email);

    insertStatement.executeUpdate();
}

The question marks in the statement are the placeholders for values, and using the setXXX methods ensures the values are correctly set before execution. This makes your query safe because it will prevent SQL injection.

Upvotes: 1

Related Questions