Post Reply 
 
Thread Rating:
  • 1 Votes - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
INSERT data into a table
12-21-2010, 08:27 AM (This post was last modified: 12-21-2010 08:31 AM by tutorials4u.)
Post: #1
INSERT data into a table
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('\\\\')");
Find all posts by this user
Quote this message in a reply
Post Reply 


Forum Jump:



Send your comments, Suggestions or Queries regarding this site at info@tutorials4u.net

Copyright © 2010 Tutorials4u.net All Rights Reserved