Admin: 1 версия

2010-06-01T06:34:13Z

1 версия

в 18:01, 31 мая 2010

2010-05-31T18:01:44Z

Новая страница

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

← Предыдущая	Версия 06:34, 1 июня 2010
(нет различий)

Java/Database SQL JDBC/SQL Builder - История изменений

Admin: 1 версия

в 18:01, 31 мая 2010