MySQL: Inverted comma -> ( ' ) and double quotation -> ( " ) errors in insertion query

Question

I am trying to make a to-do list using NetBeans-8.0.2 and JavaFX (FXMLApplication) that stores memory into MySQL database.

I know that, SQL query takes inverted comma -> ( ' ) and double quotation -> ( " ) as the same to understand a string.

Now, what i am trying is, (today is my sister's birthday) i am trying to add a task in my list that says: It's Rahi's birthday!

but, due to sql query, it is failing.

it's because inside the code, input's inverted comma is making a complexity in the sql query as a whole.

 @FXML
private void handleAddTaskAction(ActionEvent event) {
    String date = addTaskDatePicker.getValue().toString();
    System.out.println(date);
    String hour = hourComboBox.getValue() + "";
    String minute = minuteComboBox.getValue() + "";

    String where = whereField.getText();

    String header = headerField.getText();
    String description = descriptionArea.getText();

    if(hour.length()==0)
        hour= "12 AM";
    if(minute.length()==0)
        minute= "00";
    if(header.length()==0)
        header= "(No header available)";
    if(description.length()==0)
        description= "(No description available)";
    if(header.length()==0 && description.length()==0){
        header= "(Empty task)";
        description= "(Empty description)";
    }

    String query = "insert into task values('" + date + "','" + hour + " " + minute + " minutes', '"
            + header + "', '" + description + "', 'at " + where + "');";

    if (date.length() >= 1) {
        try {
            statement.execute(query);
        } catch (SQLException ex) {
            //Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex);

            Alert alert = new Alert(Alert.AlertType.ERROR);
            alert.setHeaderText("Error occured!");
            alert.showAndWait();
        }
    } else {
        Alert alert = new Alert(Alert.AlertType.WARNING);
        alert.setHeaderText("You must select a date.");
        alert.showAndWait();
    }

}

i want to store the message as it is typed. Any solutions ?

my database table description and The GUI are attached as picture. Ask me if you need anything else. Thank you.

Picture of: GUI and Picture of: Table description

Use an escape character, see: http://stackoverflow.com/questions/881194/how-to-escape-special-character-in-mysql — JCutting8, Dec 03 '15 at 16:53
_SQL query takes inverted comma -> ( ' ) and double quotation -> ( " ) as the same to understand a string._ No, single quotes and double quotes have different meaning. Single quotes are for data strings, and double quotes are for names in the schema. — Thomas Padron-McCarthy, Dec 03 '15 at 16:57

score 0 · Answer 1 · answered Dec 03 '15 at 16:53

0

Just use a PreparedStatement to inject your values, it will escape them for you.

answered Dec 03 '15 at 16:53

Arnaud

17,229
3
31
44

1

Yes. And a `PreparedStatement` will take care of all manner of quoting / escaping issues that you never anticipated. Pasting SQL queries together manually from user input is just begging for an SQL injection attack. But a code example would be nice to round out this answer. – John Bollinger Dec 03 '15 at 16:56

MySQL: Inverted comma -> ( ' ) and double quotation -> ( " ) errors in insertion query

1 Answers1