My SQL insert statement, string variable has apostrophe

Question

I'm trying to insert data into a MySQL database with an INSERT statement in java. However one of the variables I am using contains an apostrophe and I'm not sure how to account for it in the statement.

public static void addAlbum(Album album) throws Exception {    
        
        try{
            Connection conn = getDBConnection();
            PreparedStatement posted = conn.prepareStatement(
                    "INSERT INTO album(title, year, singer, company) VALUES ('" + album.getName() +"', '"+ album.getYear() +"', '"+ album.getSinger() +"', '"+ album.getCompany() +"')"
            );
            posted.executeUpdate();
        }
        catch(Exception e) {
            System.out.println(e);
        }
        finally{System.out.println("Insert completed");}
    }

It's the album.getName() that retrieving a String that is "I'm Back", what could I do so that this value could properly be inserted rather than the apostrophe cutting the String and invalidating the statement?

score 2 · Accepted Answer · answered Nov 26 '20 at 21:44

Assumming, all the values are of String type, you should do it as follows:

String sql = "INSERT INTO album(title, year, singer, company) VALUES (?, ?, ?, ?)";
PreparedStatement posted = conn.prepareStatement(sql);
posted.setString(1, album.getName());
posted.setString(2, album.getYear());
posted.setString(3, album.getSinger());
posted.setString(4, album.getCompany());

This will also protect your database from SQL injection.

Note: If year is an integer, you shoukd do posted.setInt(2, album.getYear());

My SQL insert statement, string variable has apostrophe

1 Answers1