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}