001/** 002 * 003 * Copyright (c) 2014, the Railo Company Ltd. All rights reserved. 004 * Copyright (c) 2015, Lucee Assosication Switzerland 005 * 006 * This library is free software; you can redistribute it and/or 007 * modify it under the terms of the GNU Lesser General Public 008 * License as published by the Free Software Foundation; either 009 * version 2.1 of the License, or (at your option) any later version. 010 * 011 * This library is distributed in the hope that it will be useful, 012 * but WITHOUT ANY WARRANTY; without even the implied warranty of 013 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 014 * Lesser General Public License for more details. 015 * 016 * You should have received a copy of the GNU Lesser General Public 017 * License along with this library. If not, see <http://www.gnu.org/licenses/>. 018 * 019 **/ 020package lucee.runtime.tag.util; 021 022import java.sql.Types; 023import java.util.ArrayList; 024import java.util.Iterator; 025import java.util.List; 026import java.util.Map.Entry; 027 028import lucee.commons.lang.StringUtil; 029import lucee.runtime.db.SQL; 030import lucee.runtime.db.SQLCaster; 031import lucee.runtime.db.SQLImpl; 032import lucee.runtime.db.SQLItem; 033import lucee.runtime.db.SQLItemImpl; 034import lucee.runtime.exp.ApplicationException; 035import lucee.runtime.exp.DatabaseException; 036import lucee.runtime.exp.PageException; 037import lucee.runtime.op.Caster; 038import lucee.runtime.op.Decision; 039import lucee.runtime.type.Array; 040import lucee.runtime.type.Collection.Key; 041import lucee.runtime.type.Struct; 042import lucee.runtime.type.scope.Argument; 043import lucee.runtime.type.util.KeyConstants; 044import lucee.runtime.type.util.ListUtil; 045 046public class QueryParamConverter { 047 048 public static SQL convert(String sql, Argument params) throws PageException{ 049 // All items of arguments will be key-based or position-based so proxy appropriate arrays 050 Iterator<Entry<Key, Object>> it = params.entryIterator(); 051 if (it.hasNext()){ 052 Entry<Key, Object> e = it.next(); 053 if(e.getKey().getString() == new String("1")) { 054 // This indicates the first item has key == 1 therefore treat as array 055 return convert(sql,Caster.toArray(params)); 056 } 057 } 058 return convert(sql,Caster.toStruct(params)); 059 } 060 061 public static SQL convert(String sql, Struct params) throws PageException{ 062 Iterator<Entry<Key, Object>> it = params.entryIterator(); 063 List<SQLItems<NamedSQLItem>> namedItems=new ArrayList<SQLItems<NamedSQLItem>>(); 064 Entry<Key, Object> e; 065 while(it.hasNext()){ 066 e = it.next(); 067 namedItems.add(toNamedSQLItem(e.getKey().getString(),e.getValue())); 068 } 069 return convert(sql, new ArrayList<SQLItems<SQLItem>>(), namedItems); 070 } 071 072 public static SQL convert(String sql, Array params) throws PageException{ 073 Iterator<Object> it = params.valueIterator(); 074 List<SQLItems<NamedSQLItem>> namedItems=new ArrayList<SQLItems<NamedSQLItem>>(); 075 List<SQLItems<SQLItem>> items=new ArrayList<SQLItems<SQLItem>>(); 076 Object value,paramValue; 077 while(it.hasNext()){ 078 value = it.next(); 079 080 if(Decision.isStruct(value)) { 081 Struct sct=(Struct) value; 082 // name (optional) 083 String name=null; 084 Object oName=sct.get(KeyConstants._name,null); 085 if(oName!=null) name=Caster.toString(oName); 086 087 // value (required) 088 paramValue=sct.get(KeyConstants._value); 089 090 if(StringUtil.isEmpty(name)) { 091 items.add(new SQLItems<SQLItem>(new SQLItemImpl(paramValue, Types.VARCHAR),sct)); 092 } else { 093 namedItems.add(new SQLItems<NamedSQLItem>(new NamedSQLItem(name, paramValue, Types.VARCHAR),sct)); 094 } 095 } else { 096 items.add(new SQLItems<SQLItem>(new SQLItemImpl(value))); 097 } 098 } 099 return convert(sql, items, namedItems); 100 } 101 102 private static SQLItems<NamedSQLItem> toNamedSQLItem(String name, Object value) throws PageException { 103 if(Decision.isStruct(value)) { 104 Struct sct=(Struct) value; 105 // value (required) 106 value=sct.get(KeyConstants._value); 107 return new SQLItems<NamedSQLItem>(new NamedSQLItem(name, value, Types.VARCHAR),sct); 108 } 109 return new SQLItems<NamedSQLItem>(new NamedSQLItem(name, value, Types.VARCHAR)); 110 } 111 112 113 private static SQL convert(String sql, List<SQLItems<SQLItem>> items, List<SQLItems<NamedSQLItem>> namedItems) throws ApplicationException , PageException { 114 //if(namedParams.size()==0) return new Pair<String, List<Param>>(sql,params); 115 116 StringBuilder sb=new StringBuilder(); 117 int sqlLen=sql.length(), initialParamSize=items.size(); 118 char c,del=0; 119 boolean inside=false; 120 int qm=0,_qm=0; 121 for(int i=0;i<sqlLen;i++){ 122 c=sql.charAt(i); 123 124 if(c=='"' || c=='\'') { 125 if(inside) { 126 if(c==del) { 127 inside=false; 128 } 129 } 130 else { 131 del=c; 132 inside=true; 133 } 134 } 135 else if(!inside) { 136 137 if(c=='?') { 138 if(++_qm>initialParamSize) 139 throw new ApplicationException("there are more question marks in the SQL than params defined"); 140 } 141 else if(c==':') { 142 StringBuilder name=new StringBuilder(); 143 char cc; 144 int y=i+1; 145 for(;y<sqlLen;y++){ 146 cc=sql.charAt(y); 147 if(!isVariableName(cc, true))break; 148 name.append(cc); 149 } 150 if(name.length()>0) { 151 i=y-1; 152 c='?'; 153 items.add( qm , get( name.toString(),namedItems ) ); 154 } 155 } 156 } 157 158 if(c=='?') { 159 int len=items.get(qm).size(); 160 for(int j=1;j<=len;j++) { 161 if(j>1)sb.append(','); 162 sb.append('?'); 163 } 164 qm++; 165 } else { 166 sb.append(c); 167 } 168 } 169 170 SQLItems<SQLItem> finalItems=flattenItems( items ); 171 172 return new SQLImpl(sb.toString(),finalItems.toArray(new SQLItem[finalItems.size()])); 173 } 174 175 private static SQLItems<SQLItem> flattenItems( List<SQLItems<SQLItem>> items ) { 176 SQLItems<SQLItem> finalItems = new SQLItems<SQLItem>(); 177 Iterator<SQLItems<SQLItem>> listsToFlatten = items.iterator(); 178 while(listsToFlatten.hasNext()){ 179 finalItems.addAll(listsToFlatten.next()); 180 } 181 return finalItems; 182 } 183 184 public static boolean isVariableName(char c, boolean alsoNumber) { 185 if((c>='a' && c<='z')||(c>='A' && c<='Z')||(c=='_')) return true; 186 if(alsoNumber && (c>='0' && c<='9')) return true; 187 return false; 188 } 189 190 191 private static SQLItems<SQLItem> get(String name, List<SQLItems<NamedSQLItem>> items) throws ApplicationException { 192 Iterator<SQLItems<NamedSQLItem>> it = items.iterator(); 193 SQLItems<NamedSQLItem> item; 194 while(it.hasNext()){ 195 item=it.next(); 196 if(item.get(0).name.equalsIgnoreCase(name)) { 197 return item.convertToSQLItems(); 198 } 199 } 200 throw new ApplicationException("no param with name ["+name+"] found"); 201 } 202 203 private static class NamedSQLItem extends SQLItemImpl { 204 public final String name; 205 206 public NamedSQLItem(String name, Object value, int type){ 207 super(value,type); 208 this.name=name; 209 } 210 211 public String toString(){ 212 return "{name:"+name+";"+super.toString()+"}"; 213 } 214 215 @Override 216 public NamedSQLItem clone(Object object) { 217 NamedSQLItem item = new NamedSQLItem(name,object,getType()); 218 item.setNulls(isNulls()); 219 item.setScale(getScale()); 220 return item; 221 } 222 } 223 224 private static class SQLItems<T extends SQLItem> extends ArrayList<T> { 225 226 public SQLItems() {} 227 228 public SQLItems(T item) { 229 add(item); 230 } 231 232 public SQLItems(T item,Struct sct) throws PageException { 233 T filledItem = fillSQLItem(item,sct); 234 Object oList = sct.get(KeyConstants._list,null); 235 if(oList!=null && Caster.toBooleanValue(oList)){ 236 Object oSeparator = sct.get(KeyConstants._separator,null); 237 String separator=","; 238 T clonedItem; 239 if(oSeparator!=null){ 240 separator=Caster.toString(oSeparator); 241 } 242 String v = Caster.toString(filledItem.getValue()); 243 Array values = ListUtil.listToArrayRemoveEmpty(v,separator); 244 int len=values.size(); 245 for(int i=1;i<=len;i++) { 246 clonedItem = (T) filledItem.clone(values.getE(i)); 247 add(clonedItem); 248 } 249 } else { 250 add(filledItem); 251 } 252 } 253 254 private SQLItems<SQLItem> convertToSQLItems() { 255 Iterator<T> it = iterator(); 256 SQLItems<SQLItem> p = new SQLItems<SQLItem>(); 257 while(it.hasNext()){ 258 p.add(it.next()); 259 } 260 return p; 261 } 262 263 private T fillSQLItem(T item,Struct sct) throws PageException, DatabaseException { 264 265 // type (optional) 266 Object oType=sct.get(KeyConstants._cfsqltype,null); 267 if(oType==null)oType=sct.get(KeyConstants._sqltype,null); 268 if(oType==null)oType=sct.get(KeyConstants._type,null); 269 if(oType!=null) { 270 item.setType(SQLCaster.toSQLType(Caster.toString(oType))); 271 } 272 273 // nulls (optional) 274 Object oNulls=sct.get(KeyConstants._nulls,null); 275 276 if(oNulls!=null) { 277 item.setNulls(Caster.toBooleanValue(oNulls)); 278 } 279 280 // scale (optional) 281 Object oScale=sct.get(KeyConstants._scale,null); 282 if(oScale!=null) { 283 item.setScale(Caster.toIntValue(oScale)); 284 } 285 286 return item; 287 } 288 } 289 290 /* 291 292 public static void main(String[] args) throws PageException { 293 List<SQLItem> one=new ArrayList<SQLItem>(); 294 one.add(new SQLItemImpl("aaa",1)); 295 one.add(new SQLItemImpl("bbb",1)); 296 297 List<NamedSQLItem> two=new ArrayList<NamedSQLItem>(); 298 two.add(new NamedSQLItem("susi","sorglos",1)); 299 two.add(new NamedSQLItem("peter","Petrus",1)); 300 301 SQL sql = convert( 302 "select ? as x, 'aa:a' as x from test where a=:susi and b=:peter and c=? and d=:susi", 303 one, 304 two); 305 306 print.e(sql); 307 308 // array with simple values 309 Array arr=new ArrayImpl(); 310 arr.appendEL("aaa"); 311 arr.appendEL("bbb"); 312 sql = convert( 313 "select * from test where a=? and b=?", 314 arr); 315 print.e(sql); 316 317 // array with complex values 318 arr=new ArrayImpl(); 319 Struct val1=new StructImpl(); 320 val1.set("value", "Susi Sorglos"); 321 Struct val2=new StructImpl(); 322 val2.set("value", "123"); 323 val2.set("type", "integer"); 324 arr.append(val1); 325 arr.append(val2); 326 sql = convert( 327 "select * from test where a=? and b=?", 328 arr); 329 print.e(sql); 330 331 // array with mixed values 332 arr.appendEL("ccc"); 333 arr.appendEL("ddd"); 334 sql = convert( 335 "select * from test where a=? and b=? and c=? and d=?", 336 arr); 337 print.e(sql); 338 339 // array mixed with named values 340 Struct val3=new StructImpl(); 341 val3.set("value", "456"); 342 val3.set("type", "integer"); 343 val3.set("name", "susi"); 344 arr.append(val3); 345 sql = convert( 346 "select :susi as name from test where a=? and b=? and c=? and d=?", 347 arr); 348 print.e(sql); 349 350 351 // struct with simple values 352 Struct sct=new StructImpl(); 353 sct.set("abc", "Sorglos"); 354 sql = convert( 355 "select * from test where a=:abc", 356 sct); 357 print.e(sql); 358 359 // struct with mixed values 360 sct.set("peter", val1); 361 sct.set("susi", val3); 362 sql = convert( 363 "select :peter as p, :susi as s from test where a=:abc", 364 sct); 365 print.e(sql); 366 367 368 }*/ 369 370}