Java/Database SQL JDBC/SQL Builder

Escape SQL

   <source lang="java">

/*

* Static String formatting and query routines.
* Copyright (C) 2001-2005 Stephen Ostermiller
* http://ostermiller.org/contact.pl?regarding=Java+Utilities
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 2 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
* GNU General Public License for more details.
*
* See COPYING.TXT for details.
*/

import java.util.HashMap; import java.util.regex.Pattern; /**

* Utilities for String formatting, manipulation, and queries.
* More information about this class is available from .
*
* @author Stephen Ostermiller http://ostermiller.org/contact.pl?regarding=Java+Utilities
* @since ostermillerutils 1.00.00
*/

public class StringHelper {

 /**
  * Replaces characters that may be confused by an SQL
  * parser with their equivalent escape characters.

*

* Any data that will be put in an SQL query should * be be escaped. This is especially important for data * that comes from untrusted sources such as Internet users. * <p> * For example if you had the following SQL query:
* "SELECT * FROM addresses WHERE name="" + name + "" AND private="N""
* Without this function a user could give " OR 1=1 OR ""="" * as their name causing the query to be:
* "SELECT * FROM addresses WHERE name="" OR 1=1 OR ""="" AND private="N""
* which will give all addresses, including private ones.
* Correct usage would be:
* "SELECT * FROM addresses WHERE name="" + StringHelper.escapeSQL(name) + "" AND private="N""
* <p> * Another way to avoid this problem is to use a PreparedStatement * with appropriate placeholders. * * @param s String to be escaped * @return escaped String * @throws NullPointerException if s is null. * * @since ostermillerutils 1.00.00 */ public static String escapeSQL(String s){ int length = s.length(); int newLength = length; // first check for characters that might // be dangerous and calculate a length // of the string that has escapes. for (int i=0; i<length; i++){ char c = s.charAt(i); switch(c){ case "\\": case "\"": case "\"": case "\0":{ newLength += 1; } break; } } if (length == newLength){ // nothing to escape in the string return s; } StringBuffer sb = new StringBuffer(newLength); for (int i=0; i<length; i++){ char c = s.charAt(i); switch(c){ case "\\":{ sb.append("\\\\"); } break; case "\"":{ sb.append("\\\""); } break; case "\"":{ sb.append("\\\""); } break; case "\0":{ sb.append("\\0"); } break; default: { sb.append(c); } } } return sb.toString(); } } </source>

SQL Builder

   <source lang="java">

import java.util.HashMap; import java.util.Iterator; import java.util.Map; public class BuilderMain {

 public static void main(String[] args) {
   InsertBuilder builder = new InsertBuilder();
   builder.setTable("employees");
   builder.addColumnAndData("employee_id", new Integer(221));
   builder.addColumnAndData("first_name", ""Shane"");
   builder.addColumnAndData("last_name", ""Grinnell"");
   builder.addColumnAndData("email", ""al@yahoo.ru"");
   String sql = SQLDirector.buildSQL(builder);
   System.out.println(sql);
 }

} class SQLDirector {

 public static String buildSQL(SQLBuilder builder) {
   StringBuffer buffer = new StringBuffer();
   buffer.append(builder.getCommand());
   buffer.append(builder.getTable());
   buffer.append(builder.getWhat());
   buffer.append(builder.getCriteria());
   return buffer.toString();
 }

} abstract class SQLBuilder {

 /**
  * Gets the command attribute of the SQLBuilder object
  * 
  * @return The command value or what type of Builder this is. This will return
  *         a SQL command.
  * @since
  */
 public abstract String getCommand();
 /**
  * Gets the table attribute of the SQLBuilder object
  * 
  * @return The table name value
  * @since
  */
 public abstract String getTable();
 /**
  * Gets the what value of the SQLBuilder object. This attribute will differ
  * based on what type of object we are using. This could be a list of columns
  * and data.
  * 
  * @return The what value
  * @since
  */
 public abstract String getWhat();
 /**
  * Gets the criteria attribute of the SQLBuilder object
  * 
  * @return The criteria value
  * @since
  */
 public abstract String getCriteria();

} class InsertBuilder extends SQLBuilder {

 private String table;
 private Map columnsAndData = new HashMap();
 private String criteria;
 /**
  * Sets the table attribute of the InsertBuilder object
  * 
  * @param table
  *          The new table value
  * @since
  */
 public void setTable(String table) {
   this.table = table;
 }
 /**
  * Gets the command attribute of the InsertBuilder object
  * 
  * @return The command value
  * @since
  */
 public String getCommand() {
   return "INSERT INTO ";
 }
 /**
  * Gets the table attribute of the InsertBuilder object
  * 
  * @return The table value
  * @since
  */
 public String getTable() {
   return table;
 }
 /**
  * Gets the what attribute of the InsertBuilder object
  * 
  * @return The what value
  * @since
  */
 public String getWhat() {
   StringBuffer columns = new StringBuffer();
   StringBuffer values = new StringBuffer();
   StringBuffer what = new StringBuffer();
   String columnName = null;
   Iterator iter = columnsAndData.keySet().iterator();
   while (iter.hasNext()) {
     columnName = (String) iter.next();
     columns.append(columnName);
     values.append(columnsAndData.get(columnName));
     if (iter.hasNext()) {
       columns.append(",");
       values.append(",");
     }
   }
   what.append(" (");
   what.append(columns);
   what.append(") VALUES (");
   what.append(values);
   what.append(") ");
   return what.toString();
 }
 /**
  * Gets the criteria attribute of the InsertBuilder object
  * 
  * @return The criteria value
  * @since
  */
 public String getCriteria() {
   return "";
 }
 /**
  * Adds a feature to the ColumnAndData attribute of the InsertBuilder object
  * 
  * @param columnName
  *          The feature to be added to the ColumnAndData attribute
  * @param value
  *          The feature to be added to the ColumnAndData attribute
  * @since
  */
 public void addColumnAndData(String columnName, Object value) {
   if (value != null) {
     columnsAndData.put(columnName, value);
   }
 }

}

 </source>