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

Question

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?

Don't concatenate user input into SQL strings. Use a PreparedStatement Apart from being much more secure it also magically solves your problem — user330315
– user330315, Commented Jan 24, 2018 at 12:49
OK first off.. DO NOT APPEND plain text to a query! This error is due to a classical case of input not being sanitized — Vj-
– Vj-, Commented Jan 24, 2018 at 12:50
(The "magic" being that you are not putting quotes around the SQL string literals ... and a PreparedStatements avoids the need to do that. As well as avoiding the gnarly SQL injection problems that arise with SQL query string bashing.) — Stephen C
– Stephen C, Commented Jan 24, 2018 at 12:53

Mark Rotteveel · Accepted Answer · 2018-01-24 15:12:19Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related