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