001/**
002 *
003 * Copyright (c) 2014, the Railo Company Ltd. All rights reserved.
004 *
005 * This library is free software; you can redistribute it and/or
006 * modify it under the terms of the GNU Lesser General Public
007 * License as published by the Free Software Foundation; either 
008 * version 2.1 of the License, or (at your option) any later version.
009 * 
010 * This library is distributed in the hope that it will be useful,
011 * but WITHOUT ANY WARRANTY; without even the implied warranty of
012 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
013 * Lesser General Public License for more details.
014 * 
015 * You should have received a copy of the GNU Lesser General Public 
016 * License along with this library.  If not, see <http://www.gnu.org/licenses/>.
017 * 
018 **/
019package lucee.commons.sql;
020
021import java.sql.Blob;
022import java.sql.Clob;
023import java.sql.Connection;
024import java.sql.SQLException;
025import java.sql.Statement;
026import java.util.regex.Pattern;
027
028import lucee.commons.io.SystemUtil;
029import lucee.commons.lang.ExceptionUtil;
030import lucee.commons.lang.ParserString;
031import lucee.commons.lang.StringUtil;
032import lucee.runtime.db.driver.ConnectionProxy;
033import lucee.runtime.exp.PageException;
034import lucee.runtime.op.Caster;
035import lucee.runtime.type.sql.BlobImpl;
036import lucee.runtime.type.sql.ClobImpl;
037
038public class SQLUtil {
039
040        private static final String ESCAPE_CHARS="\\{}[]^$*.?+";
041        
042        public static Pattern pattern(String pstr,boolean ignoreCase) {
043                
044                char[] carr = pstr.toCharArray();
045                char c;
046                StringBuilder sb=new StringBuilder();
047                for(int i=0;i<carr.length;i++) {
048                        c=carr[i];
049                        if(ESCAPE_CHARS.indexOf(c)!=-1) {
050                                sb.append('\\');
051                                sb.append(c);
052                        }
053                        else if(c=='%') {
054                                sb.append(".*");
055                        }
056                        else if(c=='_') {
057                                sb.append(".");
058                        }
059                        else {
060                                if(ignoreCase) {
061                                        sb.append('[');
062                                        sb.append(Character.toLowerCase(c));
063                                        sb.append('|');
064                                        sb.append(Character.toUpperCase(c));
065                                        sb.append(']');
066                                }
067                                else sb.append(c);
068                        }
069                }
070                return Pattern.compile(sb.toString());
071        }
072        
073        public static boolean match(Pattern pattern, String string) {
074                return pattern.matcher(string).matches();
075                
076        }
077
078        public static String removeLiterals(String sql) {
079                if(StringUtil.isEmpty(sql)) return sql;
080                return removeLiterals(new ParserString(sql),true);
081        }
082
083        private static String removeLiterals(ParserString ps, boolean escapeMysql) {
084                StringBuilder sb=new StringBuilder();
085                char c,p=(char)0;
086                boolean inside=false;
087                do {
088                        c=ps.getCurrent();
089                        
090                        if(c=='\''){
091                                if(inside){
092                                        if(escapeMysql && p=='\\'){}
093                                        else if(ps.hasNext() && ps.getNext()=='\'')ps.next();
094                                        else inside=false;
095                                }
096                                else {
097                                        inside=true;
098                                }
099                        }
100                        else {
101                                if(!inside && c!='*' && c!='=' && c!='?')sb.append(c);
102                        }
103                        p=c;
104                        ps.next();
105                }while(!ps.isAfterLast());
106                if(inside && escapeMysql) {
107                        ps.setPos(0);
108                        return removeLiterals(ps, false);
109                }
110                return sb.toString();
111        }
112
113        /**
114         * create a blog Object
115         * @param conn
116         * @param value
117         * @return
118         * @throws PageException
119         * @throws SQLException 
120         */
121        public static Blob toBlob(Connection conn, Object value) throws PageException, SQLException {
122                if(value instanceof Blob) return (Blob) value;
123                
124                // Java >= 1.6
125                if(SystemUtil.JAVA_VERSION>=SystemUtil.JAVA_VERSION_1_6) {
126                        try {
127                                Blob blob = conn.createBlob();
128                                blob.setBytes(1, Caster.toBinary(value));
129                                return blob;
130                        }
131                        catch(Throwable t){
132                ExceptionUtil.rethrowIfNecessary(t);
133                                return BlobImpl.toBlob(value);
134                        }
135                }
136                
137                // Java < 1.6
138                if(isOracle(conn)){
139                        Blob blob = OracleBlob.createBlob(conn,Caster.toBinary(value),null);
140                        if(blob!=null) return blob;
141                }
142                return BlobImpl.toBlob(value);
143                
144                
145        }
146
147        /**
148         * create a clob Object
149         * @param conn
150         * @param value
151         * @return
152         * @throws PageException 
153         * @throws SQLException 
154         */
155        public static Clob toClob(Connection conn, Object value) throws PageException, SQLException {
156                if(value instanceof Clob) return (Clob) value;
157                
158                // Java >= 1.6
159                if(SystemUtil.JAVA_VERSION>=SystemUtil.JAVA_VERSION_1_6) {
160                        Clob clob = conn.createClob();
161                        clob.setString(1, Caster.toString(value));
162                        return clob;
163                }
164                
165                // Java < 1.6
166                if(isOracle(conn)){
167                        Clob clob = OracleClob.createClob(conn,Caster.toString(value),null);
168                        if(clob!=null) return clob;
169                }
170                return ClobImpl.toClob(value);
171        }
172        
173        public static boolean isOracle(Connection conn) {
174                if(conn instanceof ConnectionProxy) conn=((ConnectionProxy)conn).getConnection();
175                return StringUtil.indexOfIgnoreCase(conn.getClass().getName(), "oracle")!=-1;
176        }
177
178        public static void closeEL(Statement stat) {
179                if(stat!=null){
180                        try {
181                                stat.close();
182                        } catch (SQLException e) {}
183                }
184        }
185}