001 package railo.commons.sql; 002 003 import java.sql.Blob; 004 import java.sql.Clob; 005 import java.sql.Connection; 006 import java.sql.SQLException; 007 import java.sql.Statement; 008 import java.util.regex.Pattern; 009 010 import railo.commons.lang.ParserString; 011 import railo.commons.lang.StringUtil; 012 import railo.runtime.exp.PageException; 013 import railo.runtime.op.Caster; 014 import railo.runtime.type.sql.BlobImpl; 015 import railo.runtime.type.sql.ClobImpl; 016 017 public class SQLUtil { 018 019 private static final String ESCAPE_CHARS="\\{}[]^$*.?+"; 020 021 public static Pattern pattern(String pstr,boolean ignoreCase) { 022 023 char[] carr = pstr.toCharArray(); 024 char c; 025 StringBuilder sb=new StringBuilder(); 026 for(int i=0;i<carr.length;i++) { 027 c=carr[i]; 028 if(ESCAPE_CHARS.indexOf(c)!=-1) { 029 sb.append('\\'); 030 sb.append(c); 031 } 032 else if(c=='%') { 033 sb.append(".*"); 034 } 035 else if(c=='_') { 036 sb.append("."); 037 } 038 else { 039 if(ignoreCase) { 040 sb.append('['); 041 sb.append(Character.toLowerCase(c)); 042 sb.append('|'); 043 sb.append(Character.toUpperCase(c)); 044 sb.append(']'); 045 } 046 else sb.append(c); 047 } 048 } 049 return Pattern.compile(sb.toString()); 050 } 051 052 public static boolean match(Pattern pattern, String string) { 053 return pattern.matcher(string).matches(); 054 055 } 056 057 public static String removeLiterals(String sql) { 058 if(StringUtil.isEmpty(sql)) return sql; 059 return removeLiterals(new ParserString(sql),true); 060 } 061 062 private static String removeLiterals(ParserString ps, boolean escapeMysql) { 063 StringBuilder sb=new StringBuilder(); 064 char c,p=(char)0; 065 boolean inside=false; 066 do { 067 c=ps.getCurrent(); 068 069 if(c=='\''){ 070 if(inside){ 071 if(escapeMysql && p=='\\'){} 072 else if(ps.hasNext() && ps.getNext()=='\'')ps.next(); 073 else inside=false; 074 } 075 else { 076 inside=true; 077 } 078 } 079 else { 080 if(!inside && c!='*' && c!='=' && c!='?')sb.append(c); 081 } 082 p=c; 083 ps.next(); 084 }while(!ps.isAfterLast()); 085 if(inside && escapeMysql) { 086 ps.setPos(0); 087 return removeLiterals(ps, false); 088 } 089 return sb.toString(); 090 } 091 092 /** 093 * create a blog Object 094 * @param conn 095 * @param value 096 * @return 097 * @throws PageException 098 */ 099 public static Blob toBlob(Connection conn, Object value) throws PageException { 100 if(value instanceof Blob) return (Blob) value; 101 if(isOracle(conn)){ 102 Blob blob = OracleBlob.createBlob(conn,Caster.toBinary(value),null); 103 if(blob!=null) return blob; 104 } 105 return BlobImpl.toBlob(value); 106 } 107 108 /** 109 * create a clob Object 110 * @param conn 111 * @param value 112 * @return 113 * @throws PageException 114 */ 115 public static Clob toClob(Connection conn, Object value) throws PageException { 116 if(value instanceof Clob) return (Clob) value; 117 if(isOracle(conn)){ 118 Clob clob = OracleClob.createClob(conn,Caster.toString(value),null); 119 if(clob!=null) return clob; 120 } 121 return ClobImpl.toClob(value); 122 } 123 124 public static boolean isOracle(Connection conn) { 125 return StringUtil.indexOfIgnoreCase(conn.getClass().getName(), "oracle")!=-1; 126 } 127 128 public static void closeEL(Statement stat) { 129 if(stat!=null){ 130 try { 131 stat.close(); 132 } catch (SQLException e) {} 133 } 134 } 135 }