Java/Database SQL JDBC/SQL Builder

Escape SQL

 
/*
 * 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.
   * <p>
   * 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:<br>
   * <code>"SELECT * FROM addresses WHERE name="" + name + "" AND private="N""</code><br>
   * Without this function a user could give <code>" OR 1=1 OR ""=""</code>
   * as their name causing the query to be:<br>
   * <code>"SELECT * FROM addresses WHERE name="" OR 1=1 OR ""="" AND private="N""</code><br>
   * which will give all addresses, including private ones.<br>
   * Correct usage would be:<br>
   * <code>"SELECT * FROM addresses WHERE name="" + StringHelper.escapeSQL(name) + "" AND private="N""</code><br>
   * <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();
  }
}

SQL Builder

 
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);
    }
  }
}