CODEWITHSUNDEEP
javasqlite
todivefor
todivefor

Reputation: 121

Escape singled quoted string in sqlite java

I am having trouble with a sqlite query where the data contains a single quote. The first is the query from println, followed by the error.

 UPDATE  Herb_SCORE_TBL SET Course = 'Orange County Nat'l',Score = '90',Rating = '69.3',Slope = '127',Differential = '17.4' WHERE DateField = '2020-02-08'

org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "l": syntax error)

My code just replaces a single quote with 2 quotes.

scoreCourse.replaceAll("'", "''");

This seems like it should be simple enough. When I execute this with DB Browser for sqlite app it works fine.

Code for query:

for (int row = 0; row < scoresInCurrentRecord; row++)                       // Loop thru all scores
        {
            String scoreDate = DisplayScores.
                    tableDisplayScores.getModel().getValueAt(row, 
                            HandicapMain.DATE_POS).toString();                      // Date
            yyyyMD = convertTableDate(scoreDate);                                   // JTable Date (MM/dd/yy) to row date (yyyy-MM-dd)
            scoreCourse = DisplayScores.
                    tableDisplayScores.getModel().getValueAt(row, 
                            HandicapMain.COURSE_POS).toString();                    // Course
            scoreCourse.replaceAll("'", "\\'");
            scoreScore = DisplayScores.
                    tableDisplayScores.getModel().getValueAt(row, 
                            HandicapMain.SCORE_POS).toString();                     // Score              
            scoreRating = DisplayScores.
                    tableDisplayScores.getModel().getValueAt(row, 
                            HandicapMain.RATING_POS).toString();                    // Rating  
            scoreSlope = DisplayScores.
                    tableDisplayScores.getModel().getValueAt(row, 
                            HandicapMain.SLOPE_POS).toString();                     // Slope 
            differential = DisplayScores.
                    tableDisplayScores.getModel().getValueAt(row, 
                            HandicapMain.DIFFERENTIAL_POS).toString();              // Differential
            double dblDifferential = Double.parseDouble(differential);              // To double
            dblDifferential = dblDifferential - reduceBy;                           // Reduction
            differential = String.valueOf(dblDifferential);                         // String
            String query = " UPDATE  " + HandicapMain.scoreTableName + " SET "                 
                     + "Course = '" + scoreCourse + "',"
                     + "Score = '" + scoreScore + "',"
                     + "Rating = '" + scoreRating + "',"
                     + "Slope = '"  + scoreSlope + "'," 
                     + "Differential = '" + differential + "'"
                     + " WHERE DateField = '" + yyyyMD + "'";
            System.out.println(query);
            try (PreparedStatement pst = SQLiteConnection.connection.
                    prepareStatement(query))                                        // Try pst
            {
//                pst.execute();                                      // Execute query
            } 
                catch (SQLException e)                                  // Catch SQL exception
                {
                    e.printStackTrace();
                }

        }

Upvotes: 0

Views: 99

Answers (3)

Andreas
Andreas

Reputation: 159114

You're using a PreparedStatement, so use it! That way you don't have to escape anything.

Also, since you're looping, use batch execution, and do not catch and ignore exceptions.

String query = "UPDATE " + HandicapMain.scoreTableName
               + " SET Course = ?"
                  + ", Score = ?"
                  + ", Rating = ?"
                  + ", Slope = ?" 
                  + ", Differential = ?"
             + " WHERE DateField = ?";
try (PreparedStatement pst = SQLiteConnection.connection.prepareStatement(query))
{
    for (int row = 0; row < scoresInCurrentRecord; row++)                       // Loop thru all scores
    {
        String scoreDate = DisplayScores.
                tableDisplayScores.getModel().getValueAt(row, 
                        HandicapMain.DATE_POS).toString();                      // Date
        yyyyMD = convertTableDate(scoreDate);                                   // JTable Date (MM/dd/yy) to row date (yyyy-MM-dd)
        scoreCourse = DisplayScores.
                tableDisplayScores.getModel().getValueAt(row, 
                        HandicapMain.COURSE_POS).toString();                    // Course
        scoreScore = DisplayScores.
                tableDisplayScores.getModel().getValueAt(row, 
                        HandicapMain.SCORE_POS).toString();                     // Score              
        scoreRating = DisplayScores.
                tableDisplayScores.getModel().getValueAt(row, 
                        HandicapMain.RATING_POS).toString();                    // Rating  
        scoreSlope = DisplayScores.
                tableDisplayScores.getModel().getValueAt(row, 
                        HandicapMain.SLOPE_POS).toString();                     // Slope 
        differential = DisplayScores.
                tableDisplayScores.getModel().getValueAt(row, 
                        HandicapMain.DIFFERENTIAL_POS).toString();              // Differential
        double dblDifferential = Double.parseDouble(differential);              // To double
        dblDifferential = dblDifferential - reduceBy;                           // Reduction
        differential = String.valueOf(dblDifferential);                         // String
        pst.setString(1, scoreCourse);
        pst.setString(2, scoreScore);
        pst.setString(3, scoreRating);
        pst.setString(4, scoreSlope);
        pst.setString(5, differential);
        pst.setString(6, yyyyMD);
        pst.addBatch();
    }
    pst.executeBatch();
}

Upvotes: 1

forpas
forpas

Reputation: 164099

In Java strings are immutable, so this:

scoreCourse.replaceAll("'", "''");

returns a String but it is not assigned to scoreCourse to change it.
Write it as an assignment:

scoreCourse = scoreCourse.replaceAll("'", "''");

Upvotes: 1

Patrick Magee
Patrick Magee

Reputation: 362

You likely need to escape the single quote before passing it to sqlite.

scoreCourse.replaceAll("'", "\\'");

The two backslash are necessary since you need to actually escape the first backslash as well in order to get \' to show up in your string.

Upvotes: 0

Related Questions