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>