001/* 002 * Copyright 2002-2020 the original author or authors. 003 * 004 * Licensed under the Apache License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * https://www.apache.org/licenses/LICENSE-2.0 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 013 * See the License for the specific language governing permissions and 014 * limitations under the License. 015 */ 016 017package org.springframework.jdbc.core.namedparam; 018 019import java.util.ArrayList; 020import java.util.HashSet; 021import java.util.Iterator; 022import java.util.List; 023import java.util.Map; 024import java.util.Set; 025 026import org.springframework.dao.InvalidDataAccessApiUsageException; 027import org.springframework.jdbc.core.SqlParameter; 028import org.springframework.jdbc.core.SqlParameterValue; 029import org.springframework.lang.Nullable; 030import org.springframework.util.Assert; 031 032/** 033 * Helper methods for named parameter parsing. 034 * 035 * <p>Only intended for internal use within Spring's JDBC framework. 036 * 037 * @author Thomas Risberg 038 * @author Juergen Hoeller 039 * @since 2.0 040 */ 041public abstract class NamedParameterUtils { 042 043 /** 044 * Set of characters that qualify as comment or quotes starting characters. 045 */ 046 private static final String[] START_SKIP = new String[] {"'", "\"", "--", "/*"}; 047 048 /** 049 * Set of characters that at are the corresponding comment or quotes ending characters. 050 */ 051 private static final String[] STOP_SKIP = new String[] {"'", "\"", "\n", "*/"}; 052 053 /** 054 * Set of characters that qualify as parameter separators, 055 * indicating that a parameter name in an SQL String has ended. 056 */ 057 private static final String PARAMETER_SEPARATORS = "\"':&,;()|=+-*%/\\<>^"; 058 059 /** 060 * An index with separator flags per character code. 061 * Technically only needed between 34 and 124 at this point. 062 */ 063 private static final boolean[] separatorIndex = new boolean[128]; 064 065 static { 066 for (char c : PARAMETER_SEPARATORS.toCharArray()) { 067 separatorIndex[c] = true; 068 } 069 } 070 071 072 //------------------------------------------------------------------------- 073 // Core methods used by NamedParameterJdbcTemplate and SqlQuery/SqlUpdate 074 //------------------------------------------------------------------------- 075 076 /** 077 * Parse the SQL statement and locate any placeholders or named parameters. 078 * Named parameters are substituted for a JDBC placeholder. 079 * @param sql the SQL statement 080 * @return the parsed statement, represented as ParsedSql instance 081 */ 082 public static ParsedSql parseSqlStatement(final String sql) { 083 Assert.notNull(sql, "SQL must not be null"); 084 085 Set<String> namedParameters = new HashSet<>(); 086 String sqlToUse = sql; 087 List<ParameterHolder> parameterList = new ArrayList<>(); 088 089 char[] statement = sql.toCharArray(); 090 int namedParameterCount = 0; 091 int unnamedParameterCount = 0; 092 int totalParameterCount = 0; 093 094 int escapes = 0; 095 int i = 0; 096 while (i < statement.length) { 097 int skipToPosition = i; 098 while (i < statement.length) { 099 skipToPosition = skipCommentsAndQuotes(statement, i); 100 if (i == skipToPosition) { 101 break; 102 } 103 else { 104 i = skipToPosition; 105 } 106 } 107 if (i >= statement.length) { 108 break; 109 } 110 char c = statement[i]; 111 if (c == ':' || c == '&') { 112 int j = i + 1; 113 if (c == ':' && j < statement.length && statement[j] == ':') { 114 // Postgres-style "::" casting operator should be skipped 115 i = i + 2; 116 continue; 117 } 118 String parameter = null; 119 if (c == ':' && j < statement.length && statement[j] == '{') { 120 // :{x} style parameter 121 while (statement[j] != '}') { 122 j++; 123 if (j >= statement.length) { 124 throw new InvalidDataAccessApiUsageException("Non-terminated named parameter declaration " + 125 "at position " + i + " in statement: " + sql); 126 } 127 if (statement[j] == ':' || statement[j] == '{') { 128 throw new InvalidDataAccessApiUsageException("Parameter name contains invalid character '" + 129 statement[j] + "' at position " + i + " in statement: " + sql); 130 } 131 } 132 if (j - i > 2) { 133 parameter = sql.substring(i + 2, j); 134 namedParameterCount = addNewNamedParameter(namedParameters, namedParameterCount, parameter); 135 totalParameterCount = addNamedParameter( 136 parameterList, totalParameterCount, escapes, i, j + 1, parameter); 137 } 138 j++; 139 } 140 else { 141 while (j < statement.length && !isParameterSeparator(statement[j])) { 142 j++; 143 } 144 if (j - i > 1) { 145 parameter = sql.substring(i + 1, j); 146 namedParameterCount = addNewNamedParameter(namedParameters, namedParameterCount, parameter); 147 totalParameterCount = addNamedParameter( 148 parameterList, totalParameterCount, escapes, i, j, parameter); 149 } 150 } 151 i = j - 1; 152 } 153 else { 154 if (c == '\\') { 155 int j = i + 1; 156 if (j < statement.length && statement[j] == ':') { 157 // escaped ":" should be skipped 158 sqlToUse = sqlToUse.substring(0, i - escapes) + sqlToUse.substring(i - escapes + 1); 159 escapes++; 160 i = i + 2; 161 continue; 162 } 163 } 164 if (c == '?') { 165 int j = i + 1; 166 if (j < statement.length && (statement[j] == '?' || statement[j] == '|' || statement[j] == '&')) { 167 // Postgres-style "??", "?|", "?&" operator should be skipped 168 i = i + 2; 169 continue; 170 } 171 unnamedParameterCount++; 172 totalParameterCount++; 173 } 174 } 175 i++; 176 } 177 ParsedSql parsedSql = new ParsedSql(sqlToUse); 178 for (ParameterHolder ph : parameterList) { 179 parsedSql.addNamedParameter(ph.getParameterName(), ph.getStartIndex(), ph.getEndIndex()); 180 } 181 parsedSql.setNamedParameterCount(namedParameterCount); 182 parsedSql.setUnnamedParameterCount(unnamedParameterCount); 183 parsedSql.setTotalParameterCount(totalParameterCount); 184 return parsedSql; 185 } 186 187 private static int addNamedParameter( 188 List<ParameterHolder> parameterList, int totalParameterCount, int escapes, int i, int j, String parameter) { 189 190 parameterList.add(new ParameterHolder(parameter, i - escapes, j - escapes)); 191 totalParameterCount++; 192 return totalParameterCount; 193 } 194 195 private static int addNewNamedParameter(Set<String> namedParameters, int namedParameterCount, String parameter) { 196 if (!namedParameters.contains(parameter)) { 197 namedParameters.add(parameter); 198 namedParameterCount++; 199 } 200 return namedParameterCount; 201 } 202 203 /** 204 * Skip over comments and quoted names present in an SQL statement. 205 * @param statement character array containing SQL statement 206 * @param position current position of statement 207 * @return next position to process after any comments or quotes are skipped 208 */ 209 private static int skipCommentsAndQuotes(char[] statement, int position) { 210 for (int i = 0; i < START_SKIP.length; i++) { 211 if (statement[position] == START_SKIP[i].charAt(0)) { 212 boolean match = true; 213 for (int j = 1; j < START_SKIP[i].length(); j++) { 214 if (statement[position + j] != START_SKIP[i].charAt(j)) { 215 match = false; 216 break; 217 } 218 } 219 if (match) { 220 int offset = START_SKIP[i].length(); 221 for (int m = position + offset; m < statement.length; m++) { 222 if (statement[m] == STOP_SKIP[i].charAt(0)) { 223 boolean endMatch = true; 224 int endPos = m; 225 for (int n = 1; n < STOP_SKIP[i].length(); n++) { 226 if (m + n >= statement.length) { 227 // last comment not closed properly 228 return statement.length; 229 } 230 if (statement[m + n] != STOP_SKIP[i].charAt(n)) { 231 endMatch = false; 232 break; 233 } 234 endPos = m + n; 235 } 236 if (endMatch) { 237 // found character sequence ending comment or quote 238 return endPos + 1; 239 } 240 } 241 } 242 // character sequence ending comment or quote not found 243 return statement.length; 244 } 245 } 246 } 247 return position; 248 } 249 250 /** 251 * Parse the SQL statement and locate any placeholders or named parameters. Named 252 * parameters are substituted for a JDBC placeholder, and any select list is expanded 253 * to the required number of placeholders. Select lists may contain an array of 254 * objects, and in that case the placeholders will be grouped and enclosed with 255 * parentheses. This allows for the use of "expression lists" in the SQL statement 256 * like: <br /><br /> 257 * {@code select id, name, state from table where (name, age) in (('John', 35), ('Ann', 50))} 258 * <p>The parameter values passed in are used to determine the number of placeholders to 259 * be used for a select list. Select lists should be limited to 100 or fewer elements. 260 * A larger number of elements is not guaranteed to be supported by the database and 261 * is strictly vendor-dependent. 262 * @param parsedSql the parsed representation of the SQL statement 263 * @param paramSource the source for named parameters 264 * @return the SQL statement with substituted parameters 265 * @see #parseSqlStatement 266 */ 267 public static String substituteNamedParameters(ParsedSql parsedSql, @Nullable SqlParameterSource paramSource) { 268 String originalSql = parsedSql.getOriginalSql(); 269 List<String> paramNames = parsedSql.getParameterNames(); 270 if (paramNames.isEmpty()) { 271 return originalSql; 272 } 273 StringBuilder actualSql = new StringBuilder(originalSql.length()); 274 int lastIndex = 0; 275 for (int i = 0; i < paramNames.size(); i++) { 276 String paramName = paramNames.get(i); 277 int[] indexes = parsedSql.getParameterIndexes(i); 278 int startIndex = indexes[0]; 279 int endIndex = indexes[1]; 280 actualSql.append(originalSql, lastIndex, startIndex); 281 if mp; paramSource.hasValue(paramName)) { 282 Object value = paramSource.getValue(paramName); 283 if (value instanceof SqlParameterValue) { 284 value = ((SqlParameterValue) value).getValue(); 285 } 286 if (value instanceof Iterable) { 287 Iterator<?> entryIter = ((Iterable<?>) value).iterator(); 288 int k = 0; 289 while (entryIter.hasNext()) { 290 if (k > 0) { 291 actualSql.append(", "); 292 } 293 k++; 294 Object entryItem = entryIter.next(); 295 if (entryItem instanceof Object[]) { 296 Object[] expressionList = (Object[]) entryItem; 297 actualSql.append('('); 298 for (int m = 0; m < expressionList.length; m++) { 299 if (m > 0) { 300 actualSql.append(", "); 301 } 302 actualSql.append('?'); 303 } 304 actualSql.append(')'); 305 } 306 else { 307 actualSql.append('?'); 308 } 309 } 310 } 311 else { 312 actualSql.append('?'); 313 } 314 } 315 else { 316 actualSql.append('?'); 317 } 318 lastIndex = endIndex; 319 } 320 actualSql.append(originalSql, lastIndex, originalSql.length()); 321 return actualSql.toString(); 322 } 323 324 /** 325 * Convert a Map of named parameter values to a corresponding array. 326 * @param parsedSql the parsed SQL statement 327 * @param paramSource the source for named parameters 328 * @param declaredParams the List of declared SqlParameter objects 329 * (may be {@code null}). If specified, the parameter metadata will 330 * be built into the value array in the form of SqlParameterValue objects. 331 * @return the array of values 332 */ 333 public static Object[] buildValueArray( 334 ParsedSql parsedSql, SqlParameterSource paramSource, @Nullable List<SqlParameter> declaredParams) { 335 336 Object[] paramArray = new Object[parsedSql.getTotalParameterCount()]; 337 if (parsedSql.getNamedParameterCount() > 0 && parsedSql.getUnnamedParameterCount() > 0) { 338 throw new InvalidDataAccessApiUsageException( 339 "Not allowed to mix named and traditional ? placeholders. You have " + 340 parsedSql.getNamedParameterCount() + " named parameter(s) and " + 341 parsedSql.getUnnamedParameterCount() + " traditional placeholder(s) in statement: " + 342 parsedSql.getOriginalSql()); 343 } 344 List<String> paramNames = parsedSql.getParameterNames(); 345 for (int i = 0; i < paramNames.size(); i++) { 346 String paramName = paramNames.get(i); 347 try { 348 SqlParameter param = findParameter(declaredParams, paramName, i); 349 paramArray[i] = (param != null ? new SqlParameterValue(param, paramSource.getValue(paramName)) : 350 SqlParameterSourceUtils.getTypedValue(paramSource, paramName)); 351 } 352 catch (IllegalArgumentException ex) { 353 throw new InvalidDataAccessApiUsageException( 354 "No value supplied for the SQL parameter '" + paramName + "': " + ex.getMessage()); 355 } 356 } 357 return paramArray; 358 } 359 360 /** 361 * Find a matching parameter in the given list of declared parameters. 362 * @param declaredParams the declared SqlParameter objects 363 * @param paramName the name of the desired parameter 364 * @param paramIndex the index of the desired parameter 365 * @return the declared SqlParameter, or {@code null} if none found 366 */ 367 @Nullable 368 private static SqlParameter findParameter( 369 @Nullable List<SqlParameter> declaredParams, String paramName, int paramIndex) { 370 371 if (declaredParams != null) { 372 // First pass: Look for named parameter match. 373 for (SqlParameter declaredParam : declaredParams) { 374 if (paramName.equals(declaredParam.getName())) { 375 return declaredParam; 376 } 377 } 378 // Second pass: Look for parameter index match. 379 if (paramIndex < declaredParams.size()) { 380 SqlParameter declaredParam = declaredParams.get(paramIndex); 381 // Only accept unnamed parameters for index matches. 382 if (declaredParam.getName() == null) { 383 return declaredParam; 384 } 385 } 386 } 387 return null; 388 } 389 390 /** 391 * Determine whether a parameter name ends at the current position, 392 * that is, whether the given character qualifies as a separator. 393 */ 394 private static boolean isParameterSeparator(char c) { 395 return (c < 128 && separatorIndex[c]) || Character.isWhitespace(c); 396 } 397 398 /** 399 * Convert parameter types from an SqlParameterSource into a corresponding int array. 400 * This is necessary in order to reuse existing methods on JdbcTemplate. 401 * Any named parameter types are placed in the correct position in the 402 * Object array based on the parsed SQL statement info. 403 * @param parsedSql the parsed SQL statement 404 * @param paramSource the source for named parameters 405 */ 406 public static int[] buildSqlTypeArray(ParsedSql parsedSql, SqlParameterSource paramSource) { 407 int[] sqlTypes = new int[parsedSql.getTotalParameterCount()]; 408 List<String> paramNames = parsedSql.getParameterNames(); 409 for (int i = 0; i < paramNames.size(); i++) { 410 String paramName = paramNames.get(i); 411 sqlTypes[i] = paramSource.getSqlType(paramName); 412 } 413 return sqlTypes; 414 } 415 416 /** 417 * Convert parameter declarations from an SqlParameterSource to a corresponding List of SqlParameters. 418 * This is necessary in order to reuse existing methods on JdbcTemplate. 419 * The SqlParameter for a named parameter is placed in the correct position in the 420 * resulting list based on the parsed SQL statement info. 421 * @param parsedSql the parsed SQL statement 422 * @param paramSource the source for named parameters 423 */ 424 public static List<SqlParameter> buildSqlParameterList(ParsedSql parsedSql, SqlParameterSource paramSource) { 425 List<String> paramNames = parsedSql.getParameterNames(); 426 List<SqlParameter> params = new ArrayList<>(paramNames.size()); 427 for (String paramName : paramNames) { 428 params.add(new SqlParameter( 429 paramName, paramSource.getSqlType(paramName), paramSource.getTypeName(paramName))); 430 } 431 return params; 432 } 433 434 435 //------------------------------------------------------------------------- 436 // Convenience methods operating on a plain SQL String 437 //------------------------------------------------------------------------- 438 439 /** 440 * Parse the SQL statement and locate any placeholders or named parameters. 441 * Named parameters are substituted for a JDBC placeholder. 442 * <p>This is a shortcut version of 443 * {@link #parseSqlStatement(String)} in combination with 444 * {@link #substituteNamedParameters(ParsedSql, SqlParameterSource)}. 445 * @param sql the SQL statement 446 * @return the actual (parsed) SQL statement 447 */ 448 public static String parseSqlStatementIntoString(String sql) { 449 ParsedSql parsedSql = parseSqlStatement(sql); 450 return substituteNamedParameters(parsedSql, null); 451 } 452 453 /** 454 * Parse the SQL statement and locate any placeholders or named parameters. 455 * Named parameters are substituted for a JDBC placeholder and any select list 456 * is expanded to the required number of placeholders. 457 * <p>This is a shortcut version of 458 * {@link #substituteNamedParameters(ParsedSql, SqlParameterSource)}. 459 * @param sql the SQL statement 460 * @param paramSource the source for named parameters 461 * @return the SQL statement with substituted parameters 462 */ 463 public static String substituteNamedParameters(String sql, SqlParameterSource paramSource) { 464 ParsedSql parsedSql = parseSqlStatement(sql); 465 return substituteNamedParameters(parsedSql, paramSource); 466 } 467 468 /** 469 * Convert a Map of named parameter values to a corresponding array. 470 * <p>This is a shortcut version of 471 * {@link #buildValueArray(ParsedSql, SqlParameterSource, java.util.List)}. 472 * @param sql the SQL statement 473 * @param paramMap the Map of parameters 474 * @return the array of values 475 */ 476 public static Object[] buildValueArray(String sql, Map<String, ?> paramMap) { 477 ParsedSql parsedSql = parseSqlStatement(sql); 478 return buildValueArray(parsedSql, new MapSqlParameterSource(paramMap), null); 479 } 480 481 482 private static class ParameterHolder { 483 484 private final String parameterName; 485 486 private final int startIndex; 487 488 private final int endIndex; 489 490 public ParameterHolder(String parameterName, int startIndex, int endIndex) { 491 this.parameterName = parameterName; 492 this.startIndex = startIndex; 493 this.endIndex = endIndex; 494 } 495 496 public String getParameterName() { 497 return this.parameterName; 498 } 499 500 public int getStartIndex() { 501 return this.startIndex; 502 } 503 504 public int getEndIndex() { 505 return this.endIndex; 506 } 507 } 508 509}