INSERT data into a table - Real's Java How-to

INSERT data into a tableTag(s): JDBC

Note:the MyConnection class was used to connect to the DB

Statement stmt;
String sql;     
int rows;       

sql = "INSERT INTO tCust " 
    + "(custId, custName, custAddr) "
    + "VALUES "
    + "('" + custId   + "',"
    + "('" + custName + "',"
    + "('" + custAddr + "')";

stmt = theConn.createStatement(); 
rows = stmt.executeUpdate(sql); 
theConn.dbConn.commit(); 
stmt.close();

You need to be aware to the snippet above contains a SQL Injection vulnerability. String concatentation of this form can only be used if you first validate the fields to include only alphanumeric characters. Even, then it is generally considered bad practice when prepared statements solve this problem more cleanly. This is especially when you in a Web application environnement.

Thanks to Lawrence Angrave for the warning.

Before inserting data containing quotes, you may need to double them (so "Real's HowTo" -> "Real''s HowTo"). You can use the following function to "prepare" your string.

public class StringUtils {
    public static void main(String args[]) {
      System.out.println(StringUtils.sqlQuote("Real's HowTo"));
      System.out.println(StringUtils.sqlQuote("HowTo"));
      System.out.println(StringUtils.sqlQuote(""));
      System.out.println(StringUtils.sqlQuote("Real's HowTo's"));
      System.out.println(StringUtils.sqlQuote("'"));
      System.out.println(StringUtils.sqlQuote("''"));
      /*
      output:
         Real''s HowTo
         HowTo
       
         Real''s HowTo''s
         ''
         ''''
      */       
    }

    public static String sqlQuote(String str) {
      if(str == null || str.length() == 0 || str.indexOf("\'") == -1){
        return str;
      }
      StringBuffer sb = new StringBuffer();
 
      for(int i = 0; i < str.length(); i++){
        sb.append(str.charAt(i));
        if(str.charAt(i)=='\'') sb.append('\'')
      }
      return sb.toString();
   }
}

Or use a PreparedStatement to insert data containing QUOTES.

PreparedStatement stmt = null;
String sql;     
int rows;       

try {
  sql = "INSERT INTO tCust" 
        + "(custName) "
        + "VALUES "
        + "(?)";
  stmt = theConn.prepareStatement(sql); 
  stmt.setString(1, "Name with ' are permitted!"); 
  rows = stmt.executeUpdate(); 
  stmt.close(); 
  }
catch (Exception e){ 
  e.printStackTrace(); 
}

The character "\" (backslash) can be difficult to use in an INSERT statement since "\" is considered as an escape character in Java (and probably by the database too).

stmt.executeUpdate("INSERT INTO mytable VALUES('\\')");

may generate a SQL Exception even if the "\" is escaped for Java because you need to escape it again for the database. At the end, you need to use "\\\\" to INSERT a simple "\".

stmt.executeUpdate("INSERT INTO mytable VALUES('\\\\')");