CODEWITHSUNDEEP
javasqljdbcescaping
Tony Geleynse
Tony Geleynse

Reputation: 21

How to escape apostrophes from user input

So I'm importing information from a .csv file into a database. However I'm having trouble with the user inputted description in the csv file as they contain apostrophes which is breaking the SQL import.

My Import statement is String sqlJob = 

"INSERT INTO job (ID, Job_Contact, Internal_Comment, Customer_Name," 
  + " Duration, Job_Location, Job_Completion_Date, Job_Technician," 
  + " Job_Asset, Job_Type, Job_Description) VALUES ('"
  +csvRecord.get(i)+"', '"+csvRecord.get(26)+"', '"+csvRecord.get(16)
  +"', '"+csvRecord.get(2)+"', '"+csvRecord.get(31)+"', '"+csvRecord.get(27)
  +"', '"+csvRecord.get(28)+"', '"+csvRecord.get(29)+"', '"+csvRecord.get(30)
  +"', '"+csvRecord.get(33)+"', '"+csvRecord.get(34)+"');";

A simplified form would be 

"INSERT INTO job (ID, Description) VALUES ('"
  + csvRecord.get(i) + "', '" + csvRecord.get(34) + "');";

The problem is csvRecord.get(34) will sometimes contain apostrophes. For example "My name's Bob" and that apostrophe in Bob is breaking the surrounding apostrophes that are needed to declare that the value is a string for the SQL.

Is there an easy way to parse the string and add escape characters in front of apostrophes or should I format my original SQL command a different way.

Thanks!

Upvotes: 2

Views: 506

Answers (2)

The Impaler
The Impaler

Reputation: 48750

The problem is you are assembling the SQL statement as a String, concatenating the parameter values. This is highly not advisable.

Use PreparedStatements instead that are easier to use. You can pass parameters without any modification and they won't interfere with the correct execution of the SQL statement.

Also as a valuable bonus, your code will be free of SQL Injection problems.

Example from the JDBC Tutorial:

String updateString = "update COFFEES set SALES = ? where COF_NAME = ?";
PreparedStatement updateSales = con.prepareStatement(updateString);
updateSales.setInt(1, numSales);
updateSales.setString(2, coffeeName);
updateSales.executeUpdate();

As you see, the parameters are NOT concatenated into the String, but they will take the place of the ? symbols.

Even if coffeeName has the value "Africa's Best" (apostrophe included) the query will work well.

Upvotes: 9

BeautyIT
BeautyIT

Reputation: 11

Use PreparedStatement so you can be in save.

 String name = request.getParameter( "ID" );
        String message = request.getParameter( "Job_Contact" );

        Connection c = null;
        try
        {
            String url = "jdbc:mysql://localhost/**"databasename"**;
            String username = "**<Database Login>**";
            String password = "**<Database password>**";

            **// at the ? section you can add any quantity you need to**

            String sql = "insert into **<databasename>**(name, message) values (?, ?)";

            c = DriverManager.getConnection( url, username, password );
            PreparedStatement pstmt = c.prepareStatement( sql );
            pstmt.setString( 1, name );
            pstmt.setString( 2, message );
            pstmt.executeUpdate();
        }
        catch( SQLException e )
        {
            throw new ServletException( e );
        }
        finally
        {
            try
            {
                if( c != null ) c.close();
            }
            catch( SQLException e )
            {
                throw new ServletException( e );
            }
        }

Upvotes: 1

Related Questions