Exception com.mysql.jdbc.exceptions.MySQLSyntaxErrorException

Question

im trying to add some datas to my database , but it appears an error: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException .

Here's the code:

AddToDatabase Method in Main.java

public static void addToDatabaze(String name ,String address, String city, String phone, String email, String dateOfBirth, String age, String martialStatus, String gender, String id, String mainDepartment, String department, String training) throws ClassNotFoundException, SQLException
{
    //Databaza
    Class.forName("com.mysql.jdbc.Driver");
    String url="jdbc:mysql://***.*.*.*:****/employ";
    String uname="*****";
    String pass="***********";
    connect = DriverManager.getConnection(url,uname,pass);
    Statement statement;
    String query = "INSERT INTO employeetable (name,address,city,phone,email,dateofbirth,age,martialstatus,gender,id,maindepartment,department,training)values(" + name + "," + address + "," + city + "," + phone + "," + email + "," + dateOfBirth + "," + age + "," + martialStatus + "," + gender + "," + id + "," + mainDepartment + "," + department + "," + training + ")";
    statement = connect.createStatement();
    statement.execute(query);
}

AddNewEmployeeController.java

    private Main main;
    @FXML
    private TextField nameField;
    @FXML
    private TextField addressField;
    @FXML
    private TextField cityField;
    @FXML
    private TextField phoneField;
    @FXML
    private TextField emailField;

    @FXML
    private DatePicker dateOfBirth;
    @FXML
    private TextField ageField;
    @FXML
    private ChoiceBox martialStatusBox;

    @FXML
    private RadioButton maleButton;
    @FXML
    private RadioButton femaleButton;


    @FXML
    private TextField idField;
    @FXML
    private ComboBox mainDepartmentBox;
    @FXML
    private ComboBox departmentBox;
    @FXML
    private CheckBox yesBox;
    @FXML
    private CheckBox noBox;

@FXML
    private void addButton() throws ClassNotFoundException, SQLException
    {
        if(yesBox.isSelected())
            {
                main.addToDatabaze(nameField.getText(),addressField.getText(),cityField.getText(),phoneField.getText(),emailField.getText(),dateOfBirth.getValue().toString(),ageField.getText(),martialStatusBox.getSelectionModel().getSelectedItem().toString(),"Male",idField.getText(),mainDepartmentBox.getSelectionModel().getSelectedItem().toString(),departmentBox.getSelectionModel().getSelectedItem().toString(),"Yes");
                closeBtn();
            }
    }

OUTPUT:

Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 'Years,Single,Male,1404996,Electrical,Design,Yes)' at line 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3277)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3206)
at com.mysql.jdbc.Statement.execute(Statement.java:727)
at employee.Main.addToDatabaze(Main.java:58)
at employee.view.AddNewEmployeeController.addButton(AddNewEmployeeController.java:164)
... 118 more

p.s. Main:java:58 is this row :

statement.execute(query);

AddNewEmployeeController.java:164 is this row:

main.addToDatabaze(nameField.getText(),addressField.getText(),cityField.getText(),phoneField.getText(),emailField.getText(),dateOfBirth.getValue().toString(),ageField.getText(),martialStatusBox.getSelectionModel().getSelectedItem().toString(),"Male",idField.getText(),mainDepartmentBox.getSelectionModel().getSelectedItem().toString(),departmentBox.getSelectionModel().getSelectedItem().toString(),"Yes");

Years,Single,Male,1404996,Electrical,Design,Yes is: when i tried to add data to : TextField ageField, ChoiceBox martialStatusBox,"Male", idField, ComboBox mainDepartmentBox, ComboBox departmentBox,"Yes".

Please learn how to use prepared statements; see [this tutorial](https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html). You are currently concatenating values into a query, not only is that dangerous, as it stands you don't even try to do it correctly. However switching to prepared statements is much better (and easier). See also http://stackoverflow.com/questions/3271249/difference-between-statement-and-preparedstatement — Mark Rotteveel, Nov 03 '16 at 16:26
Try `... training) values (" + name ...` instead of `... training)values(" + name ...` — Janez Kuhar, Nov 04 '16 at 01:57
@JanezKuhar That is not going to fix this problem, and it is actually perfectly valid in a lot of SQL dialects to not have whitespace between parentheses and keywords. — Mark Rotteveel, Nov 04 '16 at 07:35
print out variable `age`. It seems like you're inserting `Years`, looking at the stack trace. @MarkRotteveel You're right. — Janez Kuhar, Nov 04 '16 at 10:26
@JanezKuhar That is all not the problem, the problem is that he is concatenating values into a query string, without even quoting the string values. And the solution is to not do that at all and use prepared statements instead. — Mark Rotteveel, Nov 04 '16 at 10:46

score 0 · Answer 1 · answered Nov 04 '16 at 10:56

You need to use prepared statements:

try (PreparedStatement statement = connection.prepareStatement(
        "INSERT INTO employeetable (name,address,city,phone,email,dateofbirth,age,martialstatus,gender,id,maindepartment,department,training)" + 
        " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    statement.setString(1, name);
    statement.setString(2, address);
    // ... etc for the other fields

    statement.executeUpdate();
}

The problem with your original code, is that you forgot to add quotes around string values. The naive solution would be to use

"...('" + name + "','" + address + "'..."

However that is still bad, because guess what happens if name has the value O'Reilly. That is called SQL injection and it is a one of the biggest security problems, even thought it is easily avoidable by using prepared statements as shown above.

Exception com.mysql.jdbc.exceptions.MySQLSyntaxErrorException

1 Answers1