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    }