pg.ddx.io  aws/aws-advanced-jdbc-wrapper GitHub issues and pull requests  
help / color / mirror / Atom feed
From: aaron-congo (@aaron-congo) <noreply+aaron-congo@github.com>
To: aws/aws-advanced-jdbc-wrapper <noreply+aws-aws-advanced-jdbc-wrapper@github.com>
Subject: [aws/aws-advanced-jdbc-wrapper] PR #2253: fix: map KMS parameters per table in multi-table statements, and refuse MySQL executable comments that change placeholders
Date: Wed, 18 Nov 2026 00:17:04 +0000
Message-ID: <gh-aws-aws-advanced-jdbc-wrapper-2253@github.com> (raw)

### Summary

KMS encryption plugin: map parameters to the right table in multi-table statements, only map columns whose table is certain, and refuse MySQL executable comments that change placeholder numbering

### Description

**Multi-table statements and columns without a table**

- Each parameter of a MySQL multi-table UPDATE (`UPDATE a JOIN b ... SET b.ssn = ?`, `UPDATE a, b SET ...`, parenthesized joins, with or without aliases) is mapped to the table its column's qualifier names. An unqualified SET column of a multi-table UPDATE is reported as unmapped, as before.
- Statement-wide mapping problems (numbered placeholders, placeholder count mismatch, mapping failure) are reported for every table a multi-table UPDATE writes, not only the first one.
- A parameter is mapped to a column only when the column's table is certain: the qualifier names one of the statement's tables or aliases, or the statement reads a single table and nothing else (no derived tables, table functions or common table expressions).
  - A table given an alias is only matched by its alias.
  - Aliases that differ only in case are not resolved.
  - Table names that differ only in case are different tables.
  - Common table expression names are not treated as tables.
- Columns whose table is not certain used to be resolved against the first parsed table. Now their values are not encrypted, and in a write they are reported as unmapped, so `encryptionFailOnUnmappedParameter` decides between refusing and warning.
- `/*@encrypt:...*/` annotations:
  - `alias.column` resolves the alias to its table.
  - An annotation without a table takes the table of the automatic mapping for the same parameter, or the statement's only table.
  - An annotation that agrees with the automatic mapping (for example `public.users.ssn` and `users.ssn`) keeps the mapping's table name.
  - In a write, an annotation that takes a parameter away from the column the statement assigns it to is reported against that column.

**MySQL and MariaDB executable comments**

MySQL Connector/J reads the contents of `/*! ... */` as code and counts a `?` inside it as a placeholder. MariaDB Connector/J and JSqlParser treat it as an ordinary comment, and the server only runs a versioned `/*!NNNNN ... */` comment if its version is at least NNNNN. Parameter indexes of such a statement depend on the driver and the server.

- `PlaceholderScanner` reports `/*!`, `/*!NNNNN` and `/*M!` comments that hold a `?`, a quote, a line comment or a nested block comment. In a statement with placeholders, it also reports those that hold a comma, an equals sign, a parenthesis or a semicolon. This only applies in MySQL mode and when the driver is unknown; PostgreSQL mode treats them as ordinary comments.
- For such statements the mapper maps no parameters by position and reports a new `EXECUTABLE_COMMENT` problem. The problem covers every table the statement writes (or reads, for a SELECT), and every word of the statement, including those inside the comments.
- The plugin refuses to prepare such a statement when any of those tables, or a table named by one of its annotations, has encrypted columns, whatever `encryptionFailOnUnmappedParameter` is set to. Executable comments holding only keywords (for example `/*!50000 LOW_PRIORITY */`) are unaffected.

**Test fix**

- `KmsWriteShapesIntegrationTest` now uses a plaintext past the 61-byte minimum in the PostgreSQL HMAC rejection tests (`test13`, `test15`). Before, the length check rejected the row before the HMAC check. This is the same change as in #2244, so the two apply cleanly in either order.

**Tests**

- Unit tests:
  - `ParameterColumnMapperTest`:
    - mappings for JOIN, comma joins, parenthesized joins, aliases, `STRAIGHT_JOIN` / `LEFT JOIN`, schema-qualified tables, `WITH ... UPDATE` and PostgreSQL `UPDATE ... FROM`;
    - table aliases, including aliases and table names that differ only in case;
    - SELECT columns with and without a certain table (derived tables, table functions, CTEs, subqueries);
    - executable comment detection and reporting, including tables named only inside the comment or in a subquery.
  - `PlaceholderScannerTest`: which comments are flagged in each mode, and the words collected from them.
  - `SqlParserConnectionPluginTest`: annotation resolution (bare, alias, schema-qualified), reporting of annotations without a certain table or that override the automatic mapping, and table order.
  - `KmsEncryptionConnectionPluginTest`: the SQL parser and KMS plugins chained, covering which parameters are encrypted, strict and non-strict handling of columns without a certain table, and executable comment refusals.
- New `KmsWriteShapesIntegrationTest` cases:
  - MySQL multi-table UPDATE writing an encrypted column in the second table, in both JOIN and comma forms;
  - MySQL multi-table UPDATE where both tables encrypt the same column under different keys, and an unqualified SET of that column is refused;
  - PostgreSQL `UPDATE ... FROM`;
  - MySQL versioned comment holding a `?` before an encrypted column's parameter, refused with `encryptionFailOnUnmappedParameter` both on and off;
  - MySQL versioned comment holding only a keyword, which still encrypts.

### Additional Reviewers

### By submitting this pull request, I confirm that my contribution is made under the terms of the Apache 2.0 license.


inline patch 1: wrapper/src/main/java/software/amazon/jdbc/parser/ParameterColumnMapper.java, wrapper/src/main/java/software/amazon/jdbc/parser/ParameterMappingProblem.java, wrapper/src/main/java/software/amazon/jdbc/parser/PlaceholderScanner.java and 8 more (+1570/-109 lines) download inline-1.patchdiff --git a/wrapper/src/main/java/software/amazon/jdbc/parser/ParameterColumnMapper.java b/wrapper/src/main/java/software/amazon/jdbc/parser/ParameterColumnMapper.java index cab7751a5..f946e9a44 100644 --- a/wrapper/src/main/java/software/amazon/jdbc/parser/ParameterColumnMapper.java +++ b/wrapper/src/main/java/software/amazon/jdbc/parser/ParameterColumnMapper.java @@ -21,6 +21,7 @@ import java.util.Collections; import java.util.HashMap; import java.util.HashSet; +import java.util.LinkedHashMap; import java.util.LinkedHashSet; import java.util.List; import java.util.Locale; @@ -65,12 +66,14 @@ import net.sf.jsqlparser.statement.select.AllColumns; import net.sf.jsqlparser.statement.select.AllTableColumns; import net.sf.jsqlparser.statement.select.Join; +import net.sf.jsqlparser.statement.select.ParenthesedFromItem; import net.sf.jsqlparser.statement.select.ParenthesedSelect; import net.sf.jsqlparser.statement.select.PlainSelect; import net.sf.jsqlparser.statement.select.Select; import net.sf.jsqlparser.statement.select.SelectItem; import net.sf.jsqlparser.statement.select.SetOperationList; import net.sf.jsqlparser.statement.select.Values; +import net.sf.jsqlparser.statement.select.WithItem; import net.sf.jsqlparser.statement.update.Update; import net.sf.jsqlparser.statement.update.UpdateSet; import net.sf.jsqlparser.statement.upsert.Upsert; @@ -121,28 +124,151 @@ public static ParameterMapping map( final boolean write = isWriteStatement(statement); @Nullable String table = null; + List<String> targetTables = Collections.emptyList(); try { table = targetTable(statement); - return new MappingBuilder(statement, table, write, scan).build(); + targetTables = targetTables(statement, table); + return new MappingBuilder(statement, table, targetTables, write, scan).build(); } catch (RuntimeException e) { LOGGER.fine(() -> Messages.get( "ParameterColumnMapper.mappingFailed", new Object[] {e.getClass().getName()})); if (!write) { - return new ParameterMapping( - Collections.emptyMap(), 0, Collections.emptyList(), Collections.emptyList(), false, table); - } - // Fail closed: every parameter of a write statement is reported as unmapped. - return new ParameterMapping( - Collections.emptyMap(), - 0, - Collections.singletonList(ParameterMappingProblem.unknownTargetParams( - table, Collections.emptyList(), range(scan.getPlaceholderCount()))), - Collections.emptyList(), - true, - table); + return new ParameterMapping(Collections.emptyMap(), 0, Collections.emptyList(), + Collections.emptyList(), false, table, targetTables, Collections.emptyMap()); + } + // Fail closed: every parameter of a write statement is reported as unmapped, for every table + // it writes. + final List<ParameterMappingProblem> problems = new ArrayList<>(); + for (String target : tablesOrUnknown(targetTables)) { + problems.add(ParameterMappingProblem.unknownTargetParams( + target, Collections.emptyList(), range(scan.getPlaceholderCount()))); + } + return new ParameterMapping(Collections.emptyMap(), 0, problems, Collections.emptyList(), true, + table, targetTables, Collections.emptyMap()); + } + } + + /** + * The tables a write statement writes, in statement order: the target table, and for a MySQL + * multi-table UPDATE ({@code UPDATE a JOIN b ...}, {@code UPDATE a, b ...}) every joined table. + */ + private static List<String> targetTables(final Statement statement, final @Nullable String table) { + final Set<String> tables = new LinkedHashSet<>(); + if (table != null) { + tables.add(table); + } + if (statement instanceof Update) { + for (Table updated : updatedTables((Update) statement, cteNames(statement))) { + tables.add(stripQuotes(updated.getName())); + } + } + return new ArrayList<>(tables); + } + + /** + * The tables an UPDATE names before SET: its table, and for a MySQL multi-table UPDATE every + * joined table, including those inside parentheses ({@code UPDATE a JOIN (b JOIN c ON ...) ...}). + * Subqueries and common table expressions joined to it are not written, and are left out. + */ + private static List<Table> updatedTables(final Update update, final Set<String> cteNames) { + final List<Table> tables = new ArrayList<>(); + collectTables(update.getTable(), cteNames, tables); + final List<Join> startJoins = update.getStartJoins(); + if (startJoins != null) { + for (Join join : startJoins) { + collectTables(join.getRightItem(), cteNames, tables); + } + } + return tables; + } + + /** + * Adds the tables of a FROM or JOIN item to {@code sink}, looking inside parentheses. + * + * @return false when the item, or part of it, is not a table: a subquery, a table function, a + * LATERAL item or a common table expression + */ + private static boolean collectTables( + final @Nullable Object item, final Set<String> cteNames, final List<Table> sink) { + if (item instanceof Table) { + final Table table = (Table) item; + if (table.getName() == null) { + return false; + } + if (table.getSchemaName() == null + && cteNames.contains(stripQuotes(table.getName()).toLowerCase(Locale.ROOT))) { + return false; + } + sink.add(table); + return true; + } + if (item instanceof ParenthesedFromItem) { + final ParenthesedFromItem parenthesed = (ParenthesedFromItem) item; + boolean onlyTables = collectTables(parenthesed.getFromItem(), cteNames, sink); + if (parenthesed.getJoins() != null) { + for (Join join : parenthesed.getJoins()) { + onlyTables &= collectTables(join.getRightItem(), cteNames, sink); + } + } + return onlyTables; + } + return false; + } + + /** Lower-case names of the statement's common table expressions. */ + private static Set<String> cteNames(final Statement statement) { + @Nullable List<WithItem> withItems = null; + if (statement instanceof Select) { + withItems = ((Select) statement).getWithItemsList(); + } else if (statement instanceof Update) { + withItems = ((Update) statement).getWithItemsList(); + } else if (statement instanceof Insert) { + withItems = ((Insert) statement).getWithItemsList(); + } + final Set<String> names = new HashSet<>(); + if (withItems != null) { + for (WithItem withItem : withItems) { + if (withItem.getAlias() != null && withItem.getAlias().getName() != null) { + names.add(stripQuotes(withItem.getAlias().getName()).toLowerCase(Locale.ROOT)); + } + } + } + return names; + } + + /** + * Records {@code table} in {@code byAlias} under its lower-case alias, or its name when it has no + * alias: a table given an alias can only be referred to by that alias. A key that two different + * tables share once lower-cased (MySQL aliases and PostgreSQL quoted names are case-sensitive) + * is added to {@code ambiguous}. + */ + private static void addAliased( + final Map<String, String> byAlias, final Set<String> ambiguous, final Table table) { + final String name = stripQuotes(table.getName()); + final String key = table.getAlias() != null && table.getAlias().getName() != null + ? stripQuotes(table.getAlias().getName()).toLowerCase(Locale.ROOT) + : name.toLowerCase(Locale.ROOT); + final @Nullable String previous = byAlias.put(key, name); + if (previous != null && !previous.equals(name)) { + ambiguous.add(key); } } + /** Every distinct word of a statement, those inside its executable comments included. */ + private static List<String> statementWords(final PlaceholderScanner.Result scan) { + final Set<String> words = new LinkedHashSet<>(scan.getWords()); + words.addAll(scan.getExecutableCommentWords()); + return new ArrayList<>(words); + } + + /** The tables to report a statement-wide problem for; a single null entry when none is known. */ + private static List<@Nullable String> tablesOrUnknown(final List<String> tables) { + if (tables.isEmpty()) { + return Collections.singletonList(null); + } + return new ArrayList<@Nullable String>(tables); + } + /** * A statement the parser could not read is judged by its words. Only a write with placeholders is * reported, since none of its parameters can be mapped. Without placeholders there is nothing to @@ -156,18 +282,22 @@ private static ParameterMapping mapUnparsed(final PlaceholderScanner.Result scan break; } } + final List<String> candidates = new ArrayList<>(new LinkedHashSet<>(scan.getWords())); + final List<Integer> indexes = range(scan.getPlaceholderCount()); + final List<ParameterMappingProblem> problems = new ArrayList<>(); if (write && scan.getPlaceholderCount() > 0) { - return new ParameterMapping( - Collections.emptyMap(), - 0, - Collections.singletonList(ParameterMappingProblem.unparsedStatement( - new ArrayList<>(new LinkedHashSet<>(scan.getWords())), range(scan.getPlaceholderCount()))), - Collections.emptyList(), - true, - null); - } - return new ParameterMapping( - Collections.emptyMap(), 0, Collections.emptyList(), Collections.emptyList(), write, null); + problems.add(ParameterMappingProblem.unparsedStatement(candidates, indexes)); + } + if (scan.hasPlaceholderSensitiveExecutableComment()) { + // Reported even without placeholders outside the comment: a driver may count one inside it. + problems.add(ParameterMappingProblem.executableComment(null, statementWords(scan), indexes)); + } + if (!problems.isEmpty()) { + return new ParameterMapping(Collections.emptyMap(), 0, problems, Collections.emptyList(), write, + null, Collections.emptyList(), Collections.emptyMap()); + } + return new ParameterMapping(Collections.emptyMap(), 0, Collections.emptyList(), + Collections.emptyList(), write, null, Collections.emptyList(), Collections.emptyMap()); } private static boolean isUnparsedWrite(final List<String> words) { @@ -313,21 +443,85 @@ private static boolean isComparison(final Expression expression) { private static final class MappingBuilder { private final Statement statement; private final @Nullable String table; + private final List<String> targetTables; private final boolean write; private final PlaceholderScanner.Result scan; private final Map<Integer, String> columns = new TreeMap<>(); private final List<ParameterMappingProblem> problems = new ArrayList<>(); private final List<UnboundColumn> unboundColumns = new ArrayList<>(); + private final Map<String, String> aliases = new HashMap<>(); + /** The tables the statement reads directly, by name; names differing in case are kept apart. */ + private final Map<String, String> readTables = new LinkedHashMap<>(); + /** Whether a FROM or JOIN item is something other than a table, such as a subquery. */ + private boolean otherSources; + /** Alias keys two different tables share; never resolved. */ + private final Set<String> ambiguousAliases = new HashSet<>(); + private final Set<String> cteNames; MappingBuilder( final Statement statement, final @Nullable String table, + final List<String> targetTables, final boolean write, final PlaceholderScanner.Result scan) { this.statement = statement; this.table = table; + this.targetTables = targetTables; this.write = write; this.scan = scan; + this.cteNames = cteNames(statement); + collectAliases(); + } + + /** + * Records every table the statement names directly (not those of subqueries), keyed by + * lower-case alias, or name when not aliased. Keys that two different tables share are dropped. + */ + private void collectAliases() { + if (statement instanceof Insert) { + addFromItem(((Insert) statement).getTable()); + } else if (statement instanceof Update) { + final Update update = (Update) statement; + addFromItem(update.getTable()); + addJoinedTables(update.getStartJoins()); + addFromItem(update.getFromItem()); + addJoinedTables(update.getJoins()); + } else if (statement instanceof Upsert) { + addFromItem(((Upsert) statement).getTable()); + } else if (statement instanceof Merge) { + addFromItem(((Merge) statement).getTable()); + } else if (statement instanceof PlainSelect) { + final PlainSelect select = (PlainSelect) statement; + addFromItem(select.getFromItem()); + addJoinedTables(select.getJoins()); + } + } + + private void addJoinedTables(final @Nullable List<Join> joins) { + if (joins == null) { + return; + } + for (Join join : joins) { + addFromItem(join.getRightItem()); + } + } + + private void addFromItem(final @Nullable Object fromItem) { + if (fromItem == null) { + return; + } + final List<Table> tables = new ArrayList<>(); + if (!collectTables(fromItem, cteNames, tables)) { + otherSources = true; + } + final Set<String> ambiguous = new HashSet<>(); + for (Table found : tables) { + final String name = stripQuotes(found.getName()); + readTables.put(name, name); + addAliased(aliases, ambiguous, found); + } + ambiguousAliases.addAll(ambiguous); + aliases.keySet().removeAll(ambiguousAliases); } ParameterMapping build() { @@ -349,17 +543,37 @@ ParameterMapping build() { if (write) { final List<Integer> fixedIndexes = new ArrayList<>(collector.fixedIndexes()); - if (!fixedIndexes.isEmpty()) { - problems.add(ParameterMappingProblem.fixedIndexParameter(table, fixedIndexes)); - } final int expected = scan.getPlaceholderCount(); - if (expected != astIndexes.size() || !isOneToN(astIndexes)) { - problems.add(ParameterMappingProblem.placeholderCountMismatch( - table, expected, astIndexes.size(), range(expected))); + for (String target : tablesOrUnknown(targetTables)) { + if (!fixedIndexes.isEmpty()) { + problems.add(ParameterMappingProblem.fixedIndexParameter(target, fixedIndexes)); + } + if (expected != astIndexes.size() || !isOneToN(astIndexes)) { + problems.add(ParameterMappingProblem.placeholderCountMismatch( + target, expected, astIndexes.size(), range(expected))); + } + } + } + + if (scan.hasPlaceholderSensitiveExecutableComment()) { + // The parameter indexes depend on how the comment is read, so none of them is mapped, and + // the statement is reported whatever it is and however many placeholders it shows outside + // the comment: a driver may count one inside it. + columns.clear(); + final List<String> concerned = write + ? targetTables : new ArrayList<>(readTables.values()); + final List<Integer> indexes = range(scan.getPlaceholderCount()); + for (String target : concerned) { + problems.add(ParameterMappingProblem.executableComment( + target, Collections.emptyList(), indexes)); } + // Also every word of the statement, its comments' included, since a table named in a + // subquery or only inside an executable comment may be touched as well. + problems.add(ParameterMappingProblem.executableComment(null, statementWords(scan), indexes)); } - return new ParameterMapping(columns, astIndexes.size(), problems, unboundColumns, write, table); + return new ParameterMapping(columns, astIndexes.size(), problems, unboundColumns, write, table, + targetTables, aliases); } private boolean isOneToN(final TreeSet<Integer> indexes) { @@ -527,11 +741,12 @@ private void mapUpdateSets(final @Nullable List<UpdateSet> updateSets) { */ private void mapUpdate(final Update update) { final Map<String, String> targets = updateTargets(update); - if (targets.isEmpty() || update.getUpdateSets() == null) { + final boolean multiTable = update.getStartJoins() != null && !update.getStartJoins().isEmpty(); + if (!multiTable || update.getUpdateSets() == null) { mapUpdateSets(update.getUpdateSets()); return; } - final Set<String> tables = new LinkedHashSet<>(targets.values()); + final List<String> tables = targetTables; for (UpdateSet updateSet : update.getUpdateSets()) { final @Nullable ExpressionList<Column> setColumns = updateSet.getColumns(); final ExpressionList<?> values = updateSet.getValues(); @@ -560,7 +775,8 @@ private void mapUpdate(final Update update) { } /** - * The tables a multi-table UPDATE writes, keyed by lower-case alias and name. Empty for an + * The tables a multi-table UPDATE writes, keyed by lower-case alias, or by name when not aliased; + * keys that two different tables share are left out. Empty for an * UPDATE of a single table (a PostgreSQL {@code FROM} list only supplies values). */ private Map<String, String> updateTargets(final Update update) { @@ -569,26 +785,14 @@ private Map<String, String> updateTargets(final Update update) { if (startJoins == null || startJoins.isEmpty()) { return targets; } - addTarget(targets, update.getTable()); - for (Join join : startJoins) { - if (join.getRightItem() instanceof Table) { - addTarget(targets, (Table) join.getRightItem()); - } + final Set<String> ambiguous = new HashSet<>(); + for (Table updated : updatedTables(update, cteNames)) { + addAliased(targets, ambiguous, updated); } + targets.keySet().removeAll(ambiguous); return targets; } - private void addTarget(final Map<String, String> targets, final @Nullable Table target) { - if (target == null || target.getName() == null) { - return; - } - final String name = stripQuotes(target.getName()); - targets.put(name.toLowerCase(Locale.ROOT), name); - if (target.getAlias() != null && target.getAlias().getName() != null) { - targets.put(stripQuotes(target.getAlias().getName()).toLowerCase(Locale.ROOT), name); - } - } - private @Nullable String resolveQualifier(final Column column, final Map<String, String> targets) { final @Nullable Table qualifier = column.getTable(); if (qualifier == null || qualifier.getName() == null) { @@ -670,10 +874,29 @@ private void mapWhere(final @Nullable Expression where) { } } + /** + * Maps a parameter compared with a column, when the column's table is certain: its qualifier + * names one of the statement's tables or aliases, or it is unqualified and the statement reads + * a single table and nothing else. Otherwise, such as for an unqualified column of several + * tables, or next to a derived table, a table function or a common table expression, the + * parameter is not mapped. + */ private void putBare(final JdbcParameter parameter, final Column column) { final Integer index = parameter.getIndex(); - if (index != null && column.getColumnName() != null) { - columns.put(index, stripQuotes(column.getColumnName())); + if (index == null || column.getColumnName() == null) { + return; + } + final String name = stripQuotes(column.getColumnName()); + final @Nullable Table qualifier = column.getTable(); + if (qualifier != null && qualifier.getName() != null) { + final @Nullable String resolved = resolveQualifier(column, aliases); + if (resolved != null) { + columns.put(index, resolved + "." + name); + } + return; + } + if (readTables.size() == 1 && !otherSources) { + columns.put(index, readTables.values().iterator().next() + "." + name); } } @@ -767,6 +990,8 @@ public static final class ParameterMapping { private final List<UnboundColumn> unboundColumns; private final boolean writeStatement; private final @Nullable String targetTable; + private final List<String> targetTables; + private final Map<String, String> tableAliases; ParameterMapping( final Map<Integer, String> columns, @@ -774,18 +999,22 @@ public static final class ParameterMapping { final List<ParameterMappingProblem> problems, final List<UnboundColumn> unboundColumns, final boolean writeStatement, - final @Nullable String targetTable) { + final @Nullable String targetTable, + final List<String> targetTables, + final Map<String, String> tableAliases) { this.columns = Collections.unmodifiableMap(new TreeMap<>(columns)); this.astParameterCount = astParameterCount; this.problems = Collections.unmodifiableList(new ArrayList<>(problems)); this.unboundColumns = Collections.unmodifiableList(new ArrayList<>(unboundColumns)); this.writeStatement = writeStatement; this.targetTable = targetTable; + this.targetTables = Collections.unmodifiableList(new ArrayList<>(targetTables)); + this.tableAliases = Collections.unmodifiableMap(new HashMap<>(tableAliases)); } /** - * Parameter index (1-based) to column: {@code table.column} for INSERT/UPDATE (bare table name), - * bare column name for SELECT. + * Parameter index (1-based) to column as {@code table.column} (bare table name). Only + * parameters whose column's table is certain are mapped. * * @return an immutable map of parameter indexes to columns, in ascending index order */ @@ -854,6 +1083,27 @@ public boolean isWriteStatement() { public @Nullable String getTargetTable() { return targetTable; } + + /** + * Bare tables a write statement writes, in statement order: the target table, plus the joined + * tables of a multi-table UPDATE. Empty when not applicable or unknown. + * + * @return an immutable list of the written tables + */ + public List<String> getTargetTables() { + return targetTables; + } + + /** + * Bare table names keyed by the lower-case alias the statement gives them, or their lower-case + * name when not aliased, for the tables it writes and reads directly (not those of subqueries + * or common table expressions). + * + * @return an immutable map of lower-case table names and aliases to bare table names + */ + public Map<String, String> getTableAliases() { + return tableAliases; + } } /** The kind of value an {@link UnboundColumn} is written with. */ diff --git a/wrapper/src/main/java/software/amazon/jdbc/parser/ParameterMappingProblem.java b/wrapper/src/main/java/software/amazon/jdbc/parser/ParameterMappingProblem.java index 63c766b93..7b63b01e1 100644 --- a/wrapper/src/main/java/software/amazon/jdbc/parser/ParameterMappingProblem.java +++ b/wrapper/src/main/java/software/amazon/jdbc/parser/ParameterMappingProblem.java @@ -44,7 +44,12 @@ public enum Type { /** A write statement with parameters that the SQL parser could not read. */ UNPARSED_STATEMENT, /** A write statement kind the mapper does not support (MERGE, REPLACE). */ - UNSUPPORTED_STATEMENT + UNSUPPORTED_STATEMENT, + /** + * A statement with a MySQL or MariaDB executable comment that changes placeholder + * numbering (see {@link PlaceholderScanner.Result#hasPlaceholderSensitiveExecutableComment()}). + */ + EXECUTABLE_COMMENT } private final Type type; @@ -174,6 +179,24 @@ public static ParameterMappingProblem unsupportedStatement( Collections.emptyList(), parameterIndexes, Collections.emptyList(), statementKind, 0, 0); } + /** + * Statement whose parameter indexes depend on how an executable comment is read. Either + * {@code table} or {@code candidateTables} names a table it writes, or reads when it does not + * write. + * + * @param table the bare target table name, or {@code null} when the statement was not parsed + * @param candidateTables identifiers that may name target tables when the statement was not parsed + * @param parameterIndexes the 1-based indexes of the statement's parameters + * @return an executable-comment mapping problem + */ + public static ParameterMappingProblem executableComment( + final @Nullable String table, + final List<String> candidateTables, + final List<Integer> parameterIndexes) { + return new ParameterMappingProblem(Type.EXECUTABLE_COMMENT, table, + Collections.emptyList(), parameterIndexes, candidateTables, null, 0, 0); + } + public Type getType() { return type; } @@ -206,7 +229,8 @@ public List<Integer> getParameterIndexes() { } /** - * Identifiers of an unparsed statement that may name tables ({@link Type#UNPARSED_STATEMENT}). + * Identifiers of an unparsed statement that may name tables ({@link Type#UNPARSED_STATEMENT}, and + * {@link Type#EXECUTABLE_COMMENT} without a table). * * @return an immutable list of candidate table identifiers */ diff --git a/wrapper/src/main/java/software/amazon/jdbc/parser/PlaceholderScanner.java b/wrapper/src/main/java/software/amazon/jdbc/parser/PlaceholderScanner.java index 750dfa5d8..de297b359 100644 --- a/wrapper/src/main/java/software/amazon/jdbc/parser/PlaceholderScanner.java +++ b/wrapper/src/main/java/software/amazon/jdbc/parser/PlaceholderScanner.java @@ -28,6 +28,13 @@ * <p>A {@code ?} inside a string literal, a quoted identifier, a comment or a dollar-quoted string is * not a placeholder. The quoting and comment rules depend on the target driver, see {@link Mode}. * The rules mirror {@code org.postgresql.core.Parser} (PgJDBC) and MySQL Connector/J. + * + * <p>MySQL and MariaDB run the contents of an executable comment ({@code /*! ... *}{@code /}, with + * an optional version after the {@code !}, and MariaDB's {@code /*M! ... *}{@code /}). Drivers do not + * agree on whether a {@code ?} inside one is a placeholder, and the server runs a versioned one only + * when it is at least that version. Such comments are skipped like other comments, and the result + * reports when one holds text that could change which placeholder is which (see + * {@link Result#hasPlaceholderSensitiveExecutableComment()}). */ public final class PlaceholderScanner { @@ -49,7 +56,7 @@ public enum Mode { private static final Result EMPTY_RESULT = new Result( Collections.<Integer>emptyList(), Collections.<int[]>emptyList(), Collections.<String>emptyList(), - Collections.<Integer>emptyList()); + Collections.<Integer>emptyList(), false, Collections.<String>emptyList()); private PlaceholderScanner() { // Utility class @@ -72,6 +79,9 @@ public static Result scan(final @Nullable String sql, final Mode mode) { final List<String> words = new ArrayList<>(); final List<Integer> statementBreaks = new ArrayList<>(); final int len = sql.length(); + boolean placeholderTextInExecutableComment = false; + boolean structureInExecutableComment = false; + final List<String> executableCommentWords = new ArrayList<>(); int i = 0; while (i < len) { @@ -96,6 +106,14 @@ public static Result scan(final @Nullable String sql, final Mode mode) { } else if (c == '/' && next == '*') { final int end = skipBlockComment(sql, i, mode == Mode.POSTGRESQL); blockComments.add(new int[] {i, end}); + final int @Nullable [] content = + mode == Mode.POSTGRESQL ? null : executableCommentContent(sql, i, end); + if (content != null) { + final int kind = classifyExecutableComment(sql, content[0], content[1]); + placeholderTextInExecutableComment |= kind == EXECUTABLE_PLACEHOLDER_TEXT; + structureInExecutableComment |= kind == EXECUTABLE_STRUCTURE; + collectWords(sql, content[0], content[1], executableCommentWords); + } i = end; } else if (c == '$' && mode != Mode.MYSQL) { i = skipDollarQuote(sql, i); @@ -130,7 +148,82 @@ public static Result scan(final @Nullable String sql, final Mode mode) { } } - return new Result(placeholders, blockComments, words, statementBreaks); + // An executable comment that only holds structure (such as "= nickname, ssn") changes which + // column a placeholder outside it is assigned to; without any placeholder that cannot matter. + final boolean sensitive = placeholderTextInExecutableComment + || (structureInExecutableComment && !placeholders.isEmpty()); + return new Result(placeholders, blockComments, words, statementBreaks, sensitive, + sensitive ? executableCommentWords : Collections.<String>emptyList()); + } + + private static final int EXECUTABLE_HARMLESS = 0; + private static final int EXECUTABLE_STRUCTURE = 1; + private static final int EXECUTABLE_PLACEHOLDER_TEXT = 2; + + /** + * The contents of the block comment {@code [start, end)} when it is a MySQL or MariaDB executable + * comment ({@code /*!}, {@code /*M!}), as {@code {contentStart, contentEnd}} after the {@code !}; + * null for any other comment. + */ + private static int @Nullable [] executableCommentContent( + final String sql, final int start, final int end) { + int contentStart = start + 2; + if (contentStart < end && (sql.charAt(contentStart) == 'M' || sql.charAt(contentStart) == 'm')) { + contentStart++; + } + if (contentStart >= end || sql.charAt(contentStart) != '!') { + return null; + } + final int contentEnd = sql.startsWith("*/", end - 2) && end - 2 > contentStart ? end - 2 : end; + return new int[] {contentStart + 1, contentEnd}; + } + + /** + * What the contents of an executable comment can change when they are run as code. A {@code ?}, + * a quote, a line comment or a nested block comment changes which placeholders there are or how + * many. A comma, an equals sign, a parenthesis or a semicolon changes which column a placeholder + * is assigned to. Keywords and names alone (such as {@code LOW_PRIORITY}) change neither. + */ + private static int classifyExecutableComment(final String sql, final int from, final int to) { + int kind = EXECUTABLE_HARMLESS; + for (int j = from; j < to; j++) { + final char ch = sql.charAt(j); + if (ch == '?' || ch == '\'' || ch == '"' || ch == '`' || ch == '#') { + return EXECUTABLE_PLACEHOLDER_TEXT; + } + final char after = j + 1 < to ? sql.charAt(j + 1) : '\0'; + if ((ch == '-' && after == '-') || (ch == '/' && after == '*')) { + return EXECUTABLE_PLACEHOLDER_TEXT; + } + if (ch == ',' || ch == '=' || ch == '(' || ch == ')' || ch == ';') { + kind = EXECUTABLE_STRUCTURE; + } + } + return kind; + } + + /** Adds the identifier-like words of {@code sql[from, to)} to {@code sink}. */ + private static void collectWords(final String sql, final int from, final int to, final List<String> sink) { + int j = from; + while (j < to) { + final char ch = sql.charAt(j); + if (Character.isLetter(ch) || ch == '_') { + int end = j + 1; + while (end < to && isIdentifierChar(sql.charAt(end))) { + end++; + } + sink.add(sql.substring(j, end)); + j = end; + } else if (Character.isDigit(ch)) { + int end = j + 1; + while (end < to && isIdentifierChar(sql.charAt(end))) { + end++; + } + j = end; + } else { + j++; + } + } } private static boolean isIdentifierChar(final char c) { @@ -275,12 +368,18 @@ public static final class Result { private final List<int[]> blockComments; private final List<String> words; private final List<List<String>> statements; + private final boolean sensitiveExecutableComment; + private final List<String> executableCommentWords; private Result( final List<Integer> placeholderOffsets, final List<int[]> blockComments, final List<String> words, - final List<Integer> statementBreaks) { + final List<Integer> statementBreaks, + final boolean sensitiveExecutableComment, + final List<String> executableCommentWords) { + this.sensitiveExecutableComment = sensitiveExecutableComment; + this.executableCommentWords = Collections.unmodifiableList(executableCommentWords); this.placeholderOffsets = Collections.unmodifiableList(placeholderOffsets); this.blockComments = Collections.unmodifiableList(blockComments); this.words = Collections.unmodifiableList(words); @@ -343,6 +442,31 @@ public List<List<String>> getStatements() { return statements; } + /** + * True when the statement has a MySQL or MariaDB executable comment ({@code /*!}, {@code /*M!}) + * that changes how its placeholders are matched to columns when it is run: one holding a + * {@code ?}, a quote, a line comment or a nested block comment, or, in a statement with + * placeholders, a comma, an equals sign, a parenthesis or a semicolon. Different drivers and + * servers read such a statement differently, so no parameter index of it can be matched to a + * column. Never true in {@link Mode#POSTGRESQL}, where these are ordinary comments. + * + * @return {@code true} if such a comment was found; {@code false} otherwise + */ + public boolean hasPlaceholderSensitiveExecutableComment() { + return sensitiveExecutableComment; + } + + /** + * The identifier-like words inside the statement's executable comments, which may name tables + * the statement touches only when the comments are run. Empty unless + * {@link #hasPlaceholderSensitiveExecutableComment()} is true. + * + * @return an unmodifiable list of words + */ + public List<String> getExecutableCommentWords() { + return executableCommentWords; + } + /** * Number of real placeholders. * diff --git a/wrapper/src/main/java/software/amazon/jdbc/plugin/encryption/KmsEncryptionConnectionPlugin.java b/wrapper/src/main/java/software/amazon/jdbc/plugin/encryption/KmsEncryptionConnectionPlugin.java index 79129cf13..85d079099 100644 --- a/wrapper/src/main/java/software/amazon/jdbc/plugin/encryption/KmsEncryptionConnectionPlugin.java +++ b/wrapper/src/main/java/software/amazon/jdbc/plugin/encryption/KmsEncryptionConnectionPlugin.java @@ -245,7 +245,7 @@ private <T, E extends Exception> T handlePrepareStatement( try { final StatementContext ctx = new StatementContext(pluginService.getCallContext()); - checkWrites(ctx.problems, ctx.unboundColumns, ps); + checkWrites(ctx.problems, ctx.unboundColumns, ctx, ps); statementContexts.put(ps, ctx); } catch (SQLException e) { throw WrapperUtils.wrapExceptionIfNeeded(exceptionClass, e); @@ -278,7 +278,7 @@ private void checkExecutedSql(final Object statement) throws SQLException { if (unboundColumns.isEmpty()) { return; } - checkWrites(Collections.emptyList(), unboundColumns, null); + checkWrites(Collections.emptyList(), unboundColumns, null, null); } /** @@ -303,6 +303,9 @@ private static List<UnboundColumn> unboundWriteColumns(final @Nullable PluginCal * * <p>A column configured for encryption written with a client constant is refused whatever * {@code encryptionFailOnUnmappedParameter} says: that value is certain to be stored as plaintext. + * So is a prepared statement with an executable comment that changes placeholder numbering, on a + * table with encrypted columns: its parameters are numbered differently by different drivers and + * servers, so any mapping of them could bind a value to the wrong column. * Parameters the SQL parser could not map to columns, and statements it could not read, are * refused when they concern a table with encrypted columns and * {@code encryptionFailOnUnmappedParameter} is on; otherwise one warning is logged. That check @@ -313,12 +316,15 @@ private static List<UnboundColumn> unboundWriteColumns(final @Nullable PluginCal * * @param problems the parameters the SQL parser could not map; empty for directly executed SQL * @param unboundColumns the columns written with a client constant + * @param ctx the parse results of the statement being prepared, whose parameter mapping and + * tables the executable-comment check reads; null for directly executed SQL * @param ps the statement being prepared, closed when it is refused; null when the SQL is being * executed directly */ private void checkWrites( final List<ParameterMappingProblem> problems, final List<UnboundColumn> unboundColumns, + final @Nullable StatementContext ctx, final @Nullable PreparedStatement ps) throws SQLException { if (problems.isEmpty() && unboundColumns.isEmpty()) { return; @@ -332,10 +338,14 @@ private void checkWrites( final Set<String> unencryptableTables = new LinkedHashSet<>(); final Set<String> unencryptableDetails = new LinkedHashSet<>(); final Set<String> affectedTables = new LinkedHashSet<>(); - final List<String> details = new ArrayList<>(); + final Set<String> details = new LinkedHashSet<>(); + final Set<String> executableCommentTables = new LinkedHashSet<>(); try { collectUnencryptableValues( metadataManager, unboundColumns, unencryptableTables, unencryptableDetails); + if (ctx != null) { + collectExecutableCommentTables(metadataManager, ctx, problems, executableCommentTables); + } collectRelevantProblems(metadataManager, problems, affectedTables, details); } catch (MetadataException e) { if (strict) { @@ -356,6 +366,14 @@ private void checkWrites( throw exception; } + if (!executableCommentTables.isEmpty()) { + final SQLException exception = new SQLException(Messages.get( + "KmsEncryptionConnectionPlugin.executableComment", + new Object[] {String.join(", ", executableCommentTables)})); + closeQuietly(ps, exception); + throw exception; + } + if (details.isEmpty()) { return; } @@ -403,6 +421,53 @@ private static void collectUnencryptableValues( } } + /** + * Adds to {@code tables} every table with encrypted columns that an executable-comment problem + * concerns: the tables the statement writes (or reads, for a statement that does not write), and + * the tables its annotations name or, for an annotation without a table, encrypt that column in, + * since the annotations' parameter indexes are just as uncertain. + */ + private static void collectExecutableCommentTables( + final MetadataManager metadataManager, + final StatementContext ctx, + final List<ParameterMappingProblem> problems, + final Set<String> tables) throws MetadataException { + boolean found = false; + for (ParameterMappingProblem problem : problems) { + if (problem.getType() != ParameterMappingProblem.Type.EXECUTABLE_COMMENT) { + continue; + } + found = true; + final @Nullable String table = problem.getTable(); + if (table != null) { + if (metadataManager.hasEncryptedColumns(table)) { + tables.add(table); + } + } else if (!problem.getCandidateTables().isEmpty()) { + tables.addAll(matchEncryptedTables( + problem.getCandidateTables(), metadataManager.getEncryptedTableNames())); + } + } + if (!found) { + return; + } + // Only annotations are left in the mapping of such a statement. + for (String identifier : ctx.parameterColumnMapping.values()) { + final int dot = identifier.lastIndexOf('.'); + if (dot > 0) { + if (metadataManager.hasEncryptedColumns(identifier.substring(0, dot))) { + tables.add(identifier.substring(0, dot)); + } + continue; + } + for (String table : ctx.tables) { + if (metadataManager.isColumnEncrypted(table, identifier)) { + tables.add(table); + } + } + } + } + /** * Adds the rendered text of every problem that concerns a table with encrypted columns to * {@code details}, and the concerned tables to {@code affectedTables}. @@ -411,7 +476,7 @@ private static void collectRelevantProblems( final MetadataManager metadataManager, final List<ParameterMappingProblem> problems, final Set<String> affectedTables, - final List<String> details) throws MetadataException { + final Set<String> details) throws MetadataException { @Nullable Set<String> encryptedTableNames = null; for (ParameterMappingProblem problem : problems) { final @Nullable String table = problem.getTable(); @@ -543,31 +608,24 @@ private <T, E extends Exception> T handleSetParameter( return jdbcCallable.call(); } - // The parameter mapping carries two value formats. The positional mapping derived from the - // parsed statement holds bare column names, while an /*@encrypt:table.column*/ annotation holds - // a qualified identifier and overwrites the positional entry for that parameter. MetadataManager - // composes its lookup key as tableName + "." + columnName, so a qualified value has to be split - // here: otherwise the composed key is double-prefixed ("users" + "." + "users.ssn"), the lookup - // misses, and encryption is silently skipped for exactly the parameters the user marked - // explicitly. Splitting on the LAST dot keeps a schema-qualified "schema.table.column" intact. - final int separatorIndex = columnIdentifier.lastIndexOf('.'); - final @Nullable String tableName; - final String columnName; - if (separatorIndex > 0 && separatorIndex < columnIdentifier.length() - 1) { - tableName = columnIdentifier.substring(0, separatorIndex); - columnName = columnIdentifier.substring(separatorIndex + 1); - } else { - // A bare column name: the table comes from the statement's parsed table set, as before. - tableName = ctx.tableName; - columnName = columnIdentifier; - } - - if (tableName == null) { + MetadataManager metadataManager = encryptionUtility.getMetadataManager(); + if (metadataManager == null) { return jdbcCallable.call(); } - MetadataManager metadataManager = encryptionUtility.getMetadataManager(); - if (metadataManager == null || !metadataManager.isColumnEncrypted(tableName, columnName)) { + // A mapping value is "table.column". A bare "column" is an /*@encrypt:column*/ annotation whose + // table is not certain; its value is not encrypted (for a write, the SQL parser reported it as + // unmapped). MetadataManager composes its lookup key as tableName + "." + columnName, so the + // value is split here, on the LAST dot so that a schema-qualified "schema.table.column" stays + // intact. + + final int separatorIndex = columnIdentifier.lastIndexOf('.'); + if (separatorIndex <= 0 || separatorIndex == columnIdentifier.length() - 1) { + return jdbcCallable.call(); + } + final String tableName = columnIdentifier.substring(0, separatorIndex); + final String columnName = columnIdentifier.substring(separatorIndex + 1); + if (!metadataManager.isColumnEncrypted(tableName, columnName)) { return jdbcCallable.call(); } @@ -816,7 +874,8 @@ public OldConnectionSuggestedAction notifyConnectionChanged(EnumSet<NodeChangeOp /** Tracks SQL analysis and parameter mappings for a PreparedStatement. */ private static class StatementContext { - final @Nullable String tableName; + /** The tables the statement names, the ones it writes first. */ + final List<String> tables; final Map<Integer, String> parameterColumnMapping; final List<ParameterMappingProblem> problems; final List<UnboundColumn> unboundColumns; @@ -833,7 +892,7 @@ private static class StatementContext { if (tables == null) { throw new SQLException(Messages.get("KmsEncryptionConnectionPlugin.missingParseContext")); } - this.tableName = tables.isEmpty() ? null : (String) tables.iterator().next(); + this.tables = Collections.unmodifiableList(new ArrayList<String>(tables)); this.parameterColumnMapping = new ConcurrentHashMap<>(); Map<Integer, String> paramMapping = ctx.getAttribute(SqlContextKeys.PARAM_MAPPING, Map.class); diff --git a/wrapper/src/main/java/software/amazon/jdbc/plugin/sqlparser/SqlParserConnectionPlugin.java b/wrapper/src/main/java/software/amazon/jdbc/plugin/sqlparser/SqlParserConnectionPlugin.java index 2569ec84f..bfef74925 100644 --- a/wrapper/src/main/java/software/amazon/jdbc/plugin/sqlparser/SqlParserConnectionPlugin.java +++ b/wrapper/src/main/java/software/amazon/jdbc/plugin/sqlparser/SqlParserConnectionPlugin.java @@ -21,7 +21,9 @@ import java.util.Collections; import java.util.HashMap; import java.util.HashSet; +import java.util.LinkedHashSet; import java.util.List; +import java.util.Locale; import java.util.Map; import java.util.Properties; import java.util.Set; @@ -30,6 +32,7 @@ import java.util.regex.Matcher; import java.util.regex.Pattern; import net.sf.jsqlparser.statement.Statement; +import net.sf.jsqlparser.util.TablesNamesFinder; import org.checkerframework.checker.nullness.qual.Nullable; import software.amazon.jdbc.JdbcCallable; import software.amazon.jdbc.JdbcMethod; @@ -204,27 +207,55 @@ private ParsedSqlResult parseSql(String sql) { final @Nullable Statement statement = JSQLParserAnalyzer.parse(cleanSql); JSQLParserAnalyzer.QueryAnalysis analysis = JSQLParserAnalyzer.analyze(statement, cleanSql); - Set<String> tables = new HashSet<>(); + final ParameterColumnMapper.ParameterMapping mapping = ParameterColumnMapper.map(statement, scan); + + // The tables a write statement writes come first, in statement order, then every other table + // the statement names, subqueries included. + final Set<String> tables = new LinkedHashSet<>(mapping.getTargetTables()); for (String table : analysis.tables) { tables.add(table.replace("`", "").replace("\"", "")); } + final boolean allTablesListed = addAllTables(statement, tables); + final @Nullable String onlyTable = allTablesListed ? onlyTable(tables) : null; // Build parameter mapping; annotations take precedence - final ParameterColumnMapper.ParameterMapping mapping = ParameterColumnMapper.map(statement, scan); Map<Integer, String> paramMapping = new HashMap<>(mapping.getColumns()); + final List<ParameterMappingProblem> problems = new ArrayList<>(mapping.getProblems()); for (Map.Entry<Integer, String> annotation : annotations.entrySet()) { final String mapped = paramMapping.get(annotation.getKey()); - if (mapped != null && conflicts(annotation.getValue(), mapped)) { + final String annotated = + resolveAnnotation(annotation.getValue(), mapped, mapping.getTableAliases(), onlyTable); + // An annotation that agrees with the automatic mapping (public.users.ssn and users.ssn) keeps + // the mapping's table name, which is how the encryption metadata names the table. + final String resolved = mapped != null && !conflicts(annotated, mapped) ? mapped : annotated; + if (mapped != null && conflicts(resolved, mapped)) { LOGGER.warning(() -> Messages.get( "SqlParserConnectionPlugin.annotationConflictsWithMapping", new Object[] {String.valueOf(annotation.getKey()), annotation.getValue(), mapped})); + final int mappedDot = mapped.lastIndexOf('.'); + if (mapping.isWriteStatement() && mappedDot > 0) { + // The annotation takes the parameter away from the column the statement assigns it to, so + // if that column is encrypted its value would not be: reported like an unmapped parameter. + problems.add(ParameterMappingProblem.unknownTargetParams( + mapped.substring(0, mappedDot), + Collections.singletonList(mapped.substring(mappedDot + 1)), + Collections.singletonList(annotation.getKey()))); + } + } + paramMapping.put(annotation.getKey(), resolved); + if (resolved.indexOf('.') < 0 && mapping.isWriteStatement()) { + // The table of the annotated column is not certain, so the value is not encrypted; for a + // write that is reported like any other parameter that cannot be mapped. + for (String target : mapping.getTargetTables()) { + problems.add(ParameterMappingProblem.unknownTargetParams( + target, Collections.singletonList(resolved), Collections.singletonList(annotation.getKey()))); + } } - paramMapping.put(annotation.getKey(), annotation.getValue()); } return new ParsedSqlResult( analysis.queryType, tables, paramMapping, cleanSql, - annotations, analysis.forUpdate, routingHint, mapping.getProblems(), + annotations, analysis.forUpdate, routingHint, problems, mapping.getUnboundColumns()); } @@ -240,6 +271,81 @@ private PlaceholderScanner.Mode resolveScannerMode() { return PlaceholderScanner.Mode.GENERIC; } + /** + * The bare name of the only table in {@code tables}, or null when there are several. A table + * listed both with and without its schema ({@code public.users} and {@code users}) is one table. + */ + private static @Nullable String onlyTable(final Set<String> tables) { + @Nullable String only = null; + for (String table : tables) { + final String name = lastSegment(table); + if (only == null) { + only = name; + } else if (!only.equalsIgnoreCase(name)) { + return null; + } + } + return only; + } + + /** + * Adds every table the statement names, subqueries included, to {@code tables}. + * + * @return true when they could all be listed + */ + private static boolean addAllTables(final @Nullable Statement statement, final Set<String> tables) { + if (statement == null) { + return false; + } + try { + for (String table : new TablesNamesFinder().getTables(statement)) { + tables.add(table.replace("`", "").replace("\"", "")); + } + return true; + } catch (RuntimeException e) { + LOGGER.finest(() -> Messages.get( + "SqlParserConnectionPlugin.tableListFailed", new Object[] {e.getClass().getName()})); + return false; + } + } + + /** + * Resolves the table of an annotation against the statement, only where it is certain. A bare + * column takes the table of the automatic mapping of the same parameter when both name the same + * column, or else the statement's only table. A one-part qualifier that is an alias (or a + * differently quoted name) of one of the statement's tables is replaced by that table. Anything + * else is kept as written, so a bare column whose table is not certain stays bare. + * + * @param annotation the annotated column, {@code column} or {@code qualifier.column} + * @param mapped the automatic mapping of the same parameter, or null + * @param aliases the statement's tables keyed by lower-case alias, or name when not aliased + * @param onlyTable the only table the statement names, or null + * @return the column the annotation names, qualified with its table where that is certain + */ + static String resolveAnnotation( + final String annotation, + final @Nullable String mapped, + final Map<String, String> aliases, + final @Nullable String onlyTable) { + final int dot = annotation.lastIndexOf('.'); + final String column = annotation.substring(dot + 1); + if (dot < 0) { + if (mapped != null) { + final int mappedDot = mapped.lastIndexOf('.'); + if (mappedDot > 0 && mapped.substring(mappedDot + 1).equalsIgnoreCase(column)) { + return mapped; + } + } + return onlyTable != null ? onlyTable + "." + column : annotation; + } + final String qualifier = annotation.substring(0, dot); + if (qualifier.indexOf('.') >= 0) { + return annotation; + } + final @Nullable String table = aliases.get(qualifier.toLowerCase(Locale.ROOT)); + return table == null ? annotation : table + "." + column; + } + /** * An annotation conflicts with the automatic mapping when the column differs, or when the last * segment of the table differs (so {@code public.users.ssn} and {@code users.ssn} agree). diff --git a/wrapper/src/main/resources/aws_advanced_jdbc_wrapper_messages.properties b/wrapper/src/main/resources/aws_advanced_jdbc_wrapper_messages.properties index 8d6eb9861..3eadbd2d6 100644 --- a/wrapper/src/main/resources/aws_advanced_jdbc_wrapper_messages.properties +++ b/wrapper/src/main/resources/aws_advanced_jdbc_wrapper_messages.properties @@ -688,6 +688,7 @@ KmsEncryptionConnectionPlugin.fixedIndexParameter=numbered parameter placeholder KmsEncryptionConnectionPlugin.unsupportedStatement={0} statements are not supported KmsEncryptionConnectionPlugin.unencryptableValues=Refusing a statement on table ''{0}'': it writes encrypted columns with values that are not bind parameters, which cannot be encrypted ({1}). Supply each encrypted column''s value as a query parameter rather than as a literal or expression inside the SQL string: a ''?'' placeholder in JDBC, a ''?'' or named parameter with Spring''s JdbcTemplate, or setParameter and entity properties with JPA and Hibernate. KmsEncryptionConnectionPlugin.unboundValue=column {0} is written with {1} +KmsEncryptionConnectionPlugin.executableComment=Refusing to prepare a statement on table ''{0}'': it has encrypted columns, and the statement has a MySQL executable comment (/*! ... */ or /*M! ... */) holding a placeholder, quote or comment. Drivers and server versions number the placeholders of such a statement differently, so which parameter belongs to which column cannot be told. Move the placeholder out of the comment, or remove the comment. KmsEncryptionConnectionPlugin.valueKind.LITERAL=a literal KmsEncryptionConnectionPlugin.valueKind.EXPRESSION=an expression that reads no column KmsEncryptionConnectionPlugin.unsupportedSetterForEncryptedColumn=Parameter {0} maps to encrypted column ''{1}.{2}'', but {3} is not supported for encrypted columns. Use setString, setNString, setBytes or setObject. @@ -701,6 +702,7 @@ SqlAnalysisService.parserFoundTables=Parser analysis found {0} tables SqlAnalysisService.failedColumnMapping=Failed to get column parameter mapping: {0} SqlParserConnectionPlugin.annotationConflictsWithMapping=Encryption annotation maps parameter {0} to ''{1}'', but the statement assigns it to ''{2}''. The annotation is used. +SqlParserConnectionPlugin.tableListFailed=Could not list every table the statement names ({0}); an annotation without a table is left without one. EncryptingDataSource.initialized=EncryptingDataSource initialized with encryption plugin EncryptingDataSource.closeConnectionFailed=Failed to close connection after wrapping failure {0} diff --git a/wrapper/src/test/java/integration/container/tests/KmsWriteShapesIntegrationTest.java b/wrapper/src/test/java/integration/container/tests/KmsWriteShapesIntegrationTest.java index 05a6c6b32..71014558c 100644 --- a/wrapper/src/test/java/integration/container/tests/KmsWriteShapesIntegrationTest.java +++ b/wrapper/src/test/java/integration/container/tests/KmsWriteShapesIntegrationTest.java @@ -76,6 +76,11 @@ * parameters) is passed through to the database, whose HMAC validation accepts ciphertext and * rejects plaintext. * + * <p>A multi-table UPDATE encrypts each bound value with the key of the table its column belongs + * to. A MySQL statement with an executable comment ({@code /*! ... *}{@code /}) holding a placeholder + * is refused on a table with encrypted columns, since which parameter is which depends on the driver + * and the server. + * * <p>Requires {@code KMS_KEY_ID} (preferably a key ARN; {@code AWS_KMS_KEY_ARN} is used as a * fallback) and AWS credentials in the test container. Each test uses its own tables and key rows * and removes them afterward. KMS keys are never created or deleted. @@ -101,10 +106,13 @@ public class KmsWriteShapesIntegrationTest { private static final String EMAIL = "email"; private static final String UNENCRYPTABLE = "not bind parameters"; /** - * A plaintext longer than the PostgreSQL encrypted_data domain's minimum length, so that it is the - * HMAC check that rejects it rather than the domain's length check. + * A plaintext at least 61 bytes long, the minimum length of a valid payload (32 HMAC + 1 type + 12 IV + * + 16 GCM tag). A shorter one is rejected by the PostgreSQL domain's 45-byte check or by the + * trigger's length check ("Invalid encrypted data length") before the HMAC is compared, so it would + * not prove that the HMAC check is what rejects it. */ - private static final String LONG_PLAINTEXT = "plaintext-that-is-long-enough-to-pass-the-length-check"; + private static final String LONG_PLAINTEXT = + "plaintext-that-is-long-enough-to-pass-both-the-domain-and-trigger-length-checks"; private static final String HMAC_FAILURE = "HMAC verification failed"; private static final SecureRandom RANDOM = new SecureRandom(); @@ -515,6 +523,140 @@ void test15MultiStatementWrite() throws Exception { assertEquals(0, countRows(table, "Multi")); } + // --------------------------------------------------------------------------------------------- + // Multi-table statements and executable comments + // --------------------------------------------------------------------------------------------- + + @TestTemplate + @EnableOnDatabaseEngine(DatabaseEngine.MYSQL) + void test18MultiTableUpdateEncryptsColumnOfSecondTable() throws Exception { + final String table = createEncryptedTable("kms_shapes_join", SSN); + final String other = createStagingTable(table + "_other"); + final Connection conn = openPluginConnection(); + insert(conn, table, "Joined", "111-11-1111"); + insert(conn, table, "Comma", "111-11-1111"); + try (Statement stmt = conn.createStatement()) { + stmt.executeUpdate("INSERT INTO " + other + " (name) VALUES ('Joined'), ('Comma')"); + } + + // The encrypted table is the second one, and the first one has an ssn column of its own. + try (PreparedStatement stmt = conn.prepareStatement( + "UPDATE " + other + " o JOIN " + table + " t ON o.name = t.name" + + " SET o." + SSN + " = ?, t." + SSN + " = ? WHERE o.name = ?")) { + stmt.setString(1, "plain-other-value"); + stmt.setString(2, "222-22-2222"); + stmt.setString(3, "Joined"); + assertEquals(2, stmt.executeUpdate()); + } + try (PreparedStatement stmt = conn.prepareStatement( + "UPDATE " + other + " o, " + table + " t SET t." + SSN + " = ? WHERE o.name = t.name AND o.name = ?")) { + stmt.setString(1, "333-33-3333"); + stmt.setString(2, "Comma"); + assertEquals(1, stmt.executeUpdate()); + } + + assertEquals("222-22-2222", readColumn(conn, table, SSN, "Joined")); + assertStoredAsCiphertext(table, SSN, "Joined", "222-22-2222"); + assertEquals("333-33-3333", readColumn(conn, table, SSN, "Comma")); + assertStoredAsCiphertext(table, SSN, "Comma", "333-33-3333"); + // The other table's column is not encrypted, so its value is stored as it was bound. + assertArrayEquals("plain-other-value".getBytes(StandardCharsets.UTF_8), readRaw(other, SSN, "Joined")); + } + + @TestTemplate + @EnableOnDatabaseEngine(DatabaseEngine.MYSQL) + void test22MultiTableUpdateEncryptsEachTableWithItsOwnKey() throws Exception { + final String first = createEncryptedTable("kms_shapes_join_a", SSN); + final String second = createEncryptedTable("kms_shapes_join_b", SSN); + final Connection conn = openPluginConnection(); + insert(conn, first, "Both", "111-11-1111"); + insert(conn, second, "Both", "111-11-1111"); + + try (PreparedStatement stmt = conn.prepareStatement( + "UPDATE " + first + " a JOIN " + second + " b ON a.name = b.name" + + " SET a." + SSN + " = ?, b." + SSN + " = ? WHERE a.name = ?")) { + stmt.setString(1, "666-66-6666"); + stmt.setString(2, "777-77-7777"); + stmt.setString(3, "Both"); + assertEquals(2, stmt.executeUpdate()); + } + + // Each value reads back only with the key of the table it was written to. + assertEquals("666-66-6666", readColumn(conn, first, SSN, "Both")); + assertEquals("777-77-7777", readColumn(conn, second, SSN, "Both")); + assertStoredAsCiphertext(first, SSN, "Both", "666-66-6666"); + assertStoredAsCiphertext(second, SSN, "Both", "777-77-7777"); + + // An unqualified column of a multi-table UPDATE cannot be given a table, so the plugin refuses + // it when it is prepared; nothing is written. + assertRefused(() -> conn.prepareStatement( + "UPDATE " + first + " a JOIN " + second + " b ON a.name = b.name SET " + SSN + " = ?"), + "encryptionFailOnUnmappedParameter"); + assertEquals("666-66-6666", readColumn(conn, first, SSN, "Both")); + assertEquals("777-77-7777", readColumn(conn, second, SSN, "Both")); + } + + @TestTemplate + @EnableOnDatabaseEngine(DatabaseEngine.PG) + void test19PgUpdateFromEncryptsTargetColumn() throws Exception { + final String table = createEncryptedTable("kms_shapes_from", SSN); + final String other = createStagingTable(table + "_other"); + final Connection conn = openPluginConnection(); + insert(conn, table, "From", "111-11-1111"); + try (Statement stmt = conn.createStatement()) { + stmt.executeUpdate("INSERT INTO " + other + " (name) VALUES ('From')"); + } + + try (PreparedStatement stmt = conn.prepareStatement( + "UPDATE " + table + " t SET " + SSN + " = ? FROM " + other + " o" + + " WHERE o.name = t.name AND o.name = ?")) { + stmt.setString(1, "444-44-4444"); + stmt.setString(2, "From"); + assertEquals(1, stmt.executeUpdate()); + } + + assertEquals("444-44-4444", readColumn(conn, table, SSN, "From")); + assertStoredAsCiphertext(table, SSN, "From", "444-44-4444"); + } + + @TestTemplate + @EnableOnDatabaseEngine(DatabaseEngine.MYSQL) + void test20RefusesExecutableCommentHoldingPlaceholder() throws Exception { + final String table = createEncryptedTable("kms_shapes_versioned", SSN); + final Connection conn = openPluginConnection(); + insert(conn, table, "Versioned", "111-11-1111"); + final String sql = "UPDATE " + table + " SET /*!50000 name = ?, */ " + SSN + " = ? WHERE name = ?"; + + assertRefused(() -> conn.prepareStatement(sql), "executable comment"); + + // Refused whatever encryptionFailOnUnmappedParameter says. + final Properties lenient = pluginProps(); + lenient.setProperty(EncryptionConfig.FAIL_ON_UNMAPPED_PARAMETER.name, "false"); + try (Connection lenientConn = DriverManager.getConnection(ConnectionStringHelper.getWrapperUrl(), lenient)) { + assertRefused(() -> lenientConn.prepareStatement(sql), "executable comment"); + } + + assertEquals("111-11-1111", readColumn(conn, table, SSN, "Versioned")); + } + + @TestTemplate + @EnableOnDatabaseEngine(DatabaseEngine.MYSQL) + void test21EncryptsWithExecutableCommentWithoutPlaceholder() throws Exception { + final String table = createEncryptedTable("kms_shapes_lowprio", SSN); + final Connection conn = openPluginConnection(); + insert(conn, table, "LowPriority", "111-11-1111"); + + try (PreparedStatement stmt = conn.prepareStatement( + "UPDATE /*!50000 LOW_PRIORITY */ " + table + " SET " + SSN + " = ? WHERE name = ?")) { + stmt.setString(1, "555-55-5555"); + stmt.setString(2, "LowPriority"); + assertEquals(1, stmt.executeUpdate()); + } + + assertEquals("555-55-5555", readColumn(conn, table, SSN, "LowPriority")); + assertStoredAsCiphertext(table, SSN, "LowPriority", "555-55-5555"); + } + // --------------------------------------------------------------------------------------------- // Helpers // --------------------------------------------------------------------------------------------- diff --git a/wrapper/src/test/java/software/amazon/jdbc/parser/ParameterColumnMapperTest.java b/wrapper/src/test/java/software/amazon/jdbc/parser/ParameterColumnMapperTest.java index bf3c984ba..33ba54166 100644 --- a/wrapper/src/test/java/software/amazon/jdbc/parser/ParameterColumnMapperTest.java +++ b/wrapper/src/test/java/software/amazon/jdbc/parser/ParameterColumnMapperTest.java @@ -106,7 +106,7 @@ static Stream<Arguments> cleanRows() { Arguments.of("A22", "INSERT INTO public.users (name, ssn) VALUES ('n', ?)", mapping(1, "users.ssn")), Arguments.of("A23", "SELECT * FROM users WHERE name = 'x' AND ssn = ?", - mapping(1, "ssn")), + mapping(1, "users.ssn")), Arguments.of("cast", "INSERT INTO users (name, ssn) VALUES (?, CAST(? AS bytea))", mapping(1, "users.name", 2, "users.ssn")), Arguments.of("pgCast", "INSERT INTO users (name, ssn) VALUES (?, ?::bytea)", @@ -141,7 +141,7 @@ static Stream<Arguments> cleanRows() { mapping(1, "users.name", 2, "users.ssn")), Arguments.of("delete", "DELETE FROM users WHERE ssn = ?", mapping()), Arguments.of("selectReversed", "SELECT * FROM users WHERE ? = ssn AND id IN (?, ?)", - mapping(1, "ssn")), + mapping(1, "users.ssn")), Arguments.of("comments", "/* why? */ UPDATE users SET note = 'really?', ssn = ? WHERE id = ? -- what?", mapping(1, "users.ssn"))); @@ -586,6 +586,15 @@ void testMultiTableUpdateUnqualifiedColumnIsCheckedAgainstEveryTable() { Collections.singletonList("ssn"), Collections.singletonList(1)); assertProblem(bound.getProblems().get(1), Type.UNKNOWN_TARGET_PARAMS, "users", Collections.singletonList("ssn"), Collections.singletonList(1)); + + final ParameterMapping expression = + map("UPDATE other o JOIN users u ON o.id = u.id SET ssn = lower(?)", Mode.MYSQL); + assertTrue(expression.getColumns().isEmpty()); + assertEquals(2, expression.getProblems().size()); + assertProblem(expression.getProblems().get(0), Type.PARAMS_IN_EXPRESSION, "other", + Collections.singletonList("ssn"), Collections.singletonList(1)); + assertProblem(expression.getProblems().get(1), Type.PARAMS_IN_EXPRESSION, "users", + Collections.singletonList("ssn"), Collections.singletonList(1)); } @Test @@ -595,6 +604,334 @@ void testPostgresUpdateFromListIsNotATarget() { assertEquals(Arrays.asList("users.ssn:LITERAL"), unbound(result)); } + static Stream<Arguments> multiTableParameterMappings() { + return Stream.of( + Arguments.of("join", "UPDATE t1 JOIN t2 ON t1.id = t2.id SET t2.ssn = ?", Mode.MYSQL, + mapping(1, "t2.ssn")), + Arguments.of("joinAliases", + "UPDATE t1 a JOIN t2 b ON a.id = b.id SET b.ssn = ? WHERE a.id = ?", Mode.MYSQL, + mapping(1, "t2.ssn")), + Arguments.of("innerJoinBothTables", + "UPDATE t1 a INNER JOIN t2 b ON a.id = b.id SET a.note = ?, b.ssn = ?", Mode.MYSQL, + mapping(1, "t1.note", 2, "t2.ssn")), + Arguments.of("leftJoin", "UPDATE t1 a LEFT JOIN t2 b ON a.id = b.id SET b.ssn = ?", + Mode.MYSQL, mapping(1, "t2.ssn")), + Arguments.of("straightJoin", + "UPDATE t1 a STRAIGHT_JOIN t2 b ON a.id = b.id SET b.ssn = ?", Mode.MYSQL, + mapping(1, "t2.ssn")), + Arguments.of("comma", "UPDATE t1, t2 SET t2.ssn = ? WHERE t1.id = t2.id", Mode.MYSQL, + mapping(1, "t2.ssn")), + Arguments.of("commaAliases", + "UPDATE t1 a, t2 b SET b.ssn = ?, a.note = ? WHERE a.id = b.id", Mode.MYSQL, + mapping(1, "t2.ssn", 2, "t1.note")), + Arguments.of("schemaQualified", + "UPDATE db.t1 JOIN db.t2 ON t1.id = t2.id SET db.t2.ssn = ?", Mode.MYSQL, + mapping(1, "t2.ssn")), + Arguments.of("backticks", + "UPDATE `t1` `a` JOIN `t2` `b` ON a.id = b.id SET `b`.`ssn` = ?", Mode.MYSQL, + mapping(1, "t2.ssn")), + Arguments.of("parenthesizedJoin", + "UPDATE t1 a JOIN (t2 b JOIN t3 c ON b.id = c.id) ON a.id = b.id SET b.ssn = ?, c.note = ?", + Mode.MYSQL, mapping(1, "t2.ssn", 2, "t3.note")), + Arguments.of("parenthesizedComma", "UPDATE t1 a, (t2 b) SET b.ssn = ? WHERE a.id = b.id", + Mode.MYSQL, mapping(1, "t2.ssn")), + Arguments.of("withCte", + "WITH c AS (SELECT 1 AS id) UPDATE t1 JOIN c ON t1.id = c.id SET t1.ssn = ?", Mode.MYSQL, + mapping(1, "t1.ssn")), + Arguments.of("pgFrom", "UPDATE t1 SET ssn = ? FROM t2 WHERE t1.id = t2.id", + Mode.POSTGRESQL, mapping(1, "t1.ssn")), + Arguments.of("pgFromAliases", + "UPDATE t1 a SET ssn = ? FROM t2 b WHERE a.id = b.id AND b.k = ?", Mode.POSTGRESQL, + mapping(1, "t1.ssn"))); + } + + @ParameterizedTest(name = "{0}") + @MethodSource("multiTableParameterMappings") + void testMultiTableUpdateMapsEachParameterToItsTable( + final String id, final String sql, final Mode mode, final Map<Integer, String> expected) { + final ParameterMapping result = map(sql, mode); + + assertEquals(expected, result.getColumns(), id); + assertTrue(result.getProblems().isEmpty(), id + ": " + result.getProblems()); + } + + @Test + void testTargetTables() { + assertEquals(Arrays.asList("t1", "t2"), + map("UPDATE t1 a JOIN t2 b ON a.id = b.id SET b.ssn = ?", Mode.MYSQL).getTargetTables()); + assertEquals(Arrays.asList("t1", "t2"), + map("UPDATE t1, t2 SET t2.ssn = ?", Mode.MYSQL).getTargetTables()); + assertEquals(Collections.singletonList("t1"), + map("UPDATE t1 SET ssn = ? FROM t2 WHERE t1.id = t2.id").getTargetTables()); + assertEquals(Collections.singletonList("users"), + map("INSERT INTO users (ssn) VALUES (?)").getTargetTables()); + assertEquals(Arrays.asList("t1", "t2", "t3"), map( + "UPDATE t1 a JOIN (t2 b JOIN t3 c ON b.id = c.id) ON a.id = b.id SET b.ssn = ?", Mode.MYSQL) + .getTargetTables()); + assertEquals(Collections.singletonList("t1"), map( + "WITH c AS (SELECT 1 AS id) UPDATE t1 JOIN c ON t1.id = c.id SET t1.ssn = ?", Mode.MYSQL) + .getTargetTables()); + assertTrue(map("SELECT * FROM users WHERE ssn = ?").getTargetTables().isEmpty()); + } + + @Test + void testTableAliases() { + final Map<String, String> update = map( + "UPDATE t1 a JOIN t2 b ON a.id = b.id SET b.ssn = ?", Mode.MYSQL).getTableAliases(); + assertEquals("t1", update.get("a")); + assertEquals("t2", update.get("b")); + // A table given an alias can only be referred to by that alias. + assertNull(update.get("t1")); + assertNull(update.get("t2")); + + final Map<String, String> pgFrom = + map("UPDATE users u SET ssn = ? FROM staging s WHERE s.id = u.id").getTableAliases(); + assertEquals("users", pgFrom.get("u")); + assertEquals("staging", pgFrom.get("s")); + + final Map<String, String> insert = map("INSERT INTO users (ssn) VALUES (?)").getTableAliases(); + assertEquals("users", insert.get("users")); + + final Map<String, String> select = map( + "SELECT * FROM other o JOIN users U ON o.id = U.id WHERE U.ssn = ?").getTableAliases(); + assertEquals("other", select.get("o")); + assertEquals("users", select.get("u")); + + final Map<String, String> cte = map( + "WITH d AS (SELECT * FROM users) SELECT * FROM d JOIN other ON d.id = other.id").getTableAliases(); + assertNull(cte.get("d")); + assertEquals("other", cte.get("other")); + } + + @Test + void testAliasHidesTableName() { + // users is aliased "other", so the qualifier "other" is the alias, not the table named other. + final ParameterMapping result = + map("SELECT * FROM users other JOIN things t ON other.id = t.id WHERE other.ssn = ?"); + + assertEquals(mapping(1, "users.ssn"), result.getColumns()); + } + + static Stream<Arguments> selectColumnTables() { + return Stream.of( + Arguments.of("qualifiedByAlias", + "SELECT * FROM other o JOIN users u ON o.id = u.id WHERE u.ssn = ?", mapping(1, "users.ssn")), + Arguments.of("qualifiedByOtherAlias", + "SELECT * FROM users u JOIN other o ON o.id = u.id WHERE o.ssn = ?", mapping(1, "other.ssn")), + Arguments.of("qualifiedByName", + "SELECT * FROM other JOIN users ON other.id = users.id WHERE users.ssn = ?", + mapping(1, "users.ssn")), + Arguments.of("unqualifiedSingleTable", "SELECT * FROM users u WHERE ssn = ?", + mapping(1, "users.ssn")), + Arguments.of("unqualifiedWithSubquery", + "SELECT * FROM users WHERE ssn = ? AND id IN (SELECT id FROM other)", + mapping(1, "users.ssn")), + // Not certain which table the column belongs to: not mapped. + Arguments.of("unqualifiedSeveralTables", + "SELECT * FROM other o JOIN users u ON o.id = u.id WHERE ssn = ?", mapping()), + Arguments.of("derivedTable", "SELECT * FROM (SELECT * FROM users) d WHERE d.ssn = ?", + mapping()), + Arguments.of("commonTableExpression", + "WITH d AS (SELECT * FROM users) SELECT * FROM d WHERE ssn = ?", mapping()), + Arguments.of("commonTableExpressionQualified", + "WITH d AS (SELECT * FROM users) SELECT * FROM d WHERE d.ssn = ?", mapping()), + Arguments.of("unqualifiedNextToDerivedTable", + "SELECT * FROM users u JOIN (SELECT id, ssn FROM accounts) d ON d.id = u.id WHERE ssn = ?", + mapping()), + Arguments.of("qualifiedNextToDerivedTable", + "SELECT * FROM users u JOIN (SELECT id, ssn FROM accounts) d ON d.id = u.id WHERE u.ssn = ?", + mapping(1, "users.ssn")), + Arguments.of("unqualifiedNextToTableFunction", + "SELECT * FROM users CROSS JOIN generate_series(1, 2) g WHERE ssn = ?", mapping()), + Arguments.of("parenthesizedTables", + "SELECT * FROM users u JOIN (other o JOIN things t ON o.id = t.id) ON u.id = o.id WHERE o.ssn = ?", + mapping(1, "other.ssn")), + Arguments.of("aliasesDifferingInCase", + "SELECT * FROM t1 a JOIN t2 \"A\" ON a.id = \"A\".id WHERE a.ssn = ?", mapping())); + } + + @ParameterizedTest(name = "{0}") + @MethodSource("selectColumnTables") + void testSelectColumnIsQualifiedWithItsTable( + final String id, final String sql, final Map<Integer, String> expected) { + assertEquals(expected, map(sql).getColumns(), id); + } + + static Stream<Arguments> aliasesDifferingInCase() { + return Stream.of( + Arguments.of("twoAliases", "UPDATE t1 a JOIN t2 A ON a.id = A.id SET a.ssn = ?"), + Arguments.of("aliasMatchingOtherTableName", + "UPDATE t1 JOIN t2 T1 ON t1.id = T1.id SET t1.ssn = ?")); + } + + @ParameterizedTest(name = "{0}") + @MethodSource("aliasesDifferingInCase") + void testMultiTableUpdateAliasesDifferingInCaseAreNotResolved(final String id, final String sql) { + final ParameterMapping result = map(sql, Mode.MYSQL); + + assertTrue(result.getColumns().isEmpty(), id + ": " + result.getColumns()); + assertEquals(2, result.getProblems().size(), id + ": " + result.getProblems()); + assertProblem(result.getProblems().get(0), Type.UNKNOWN_TARGET_PARAMS, "t1", + Collections.singletonList("ssn"), Collections.singletonList(1)); + assertProblem(result.getProblems().get(1), Type.UNKNOWN_TARGET_PARAMS, "t2", + Collections.singletonList("ssn"), Collections.singletonList(1)); + } + + @Test + void testMultiTableStatementWideProblemNamesEveryWrittenTable() { + final ParameterMapping result = + map("UPDATE other o JOIN users u ON o.id = u.id SET u.ssn = ?1", Mode.MYSQL); + + assertEquals(2, result.getProblems().size(), result.getProblems().toString()); + assertProblem(result.getProblems().get(0), Type.FIXED_INDEX_PARAMETER, "other", + Collections.emptyList(), Collections.singletonList(1)); + assertProblem(result.getProblems().get(1), Type.FIXED_INDEX_PARAMETER, "users", + Collections.emptyList(), Collections.singletonList(1)); + } + + static Stream<Arguments> executableComments() { + return Stream.of( + Arguments.of("versioned", "UPDATE users SET /*!50000 name = ?, */ ssn = ? WHERE id = ?", 2), + Arguments.of("unversioned", "UPDATE users SET /*! name = ?, */ ssn = ? WHERE id = ?", 2), + Arguments.of("sixDigitVersion", + "UPDATE users SET /*!800100 name = ?, */ ssn = ? WHERE id = ?", 2), + Arguments.of("mariadb", "UPDATE users SET /*M!100100 name = ?, */ ssn = ? WHERE id = ?", 2), + Arguments.of("afterParameter", "INSERT INTO users (id, ssn) VALUES (? /*!50000 + ? */, ?)", 2), + Arguments.of("quote", "UPDATE users SET note = /*!50000 'x */ ? WHERE id = ?", 2), + Arguments.of("doubleQuote", "UPDATE users SET note = /*!50000 \"x */ ? WHERE id = ?", 2), + Arguments.of("backtick", "UPDATE users SET note = /*!50000 `x */ ? WHERE id = ?", 2), + Arguments.of("dashComment", "UPDATE users SET ssn = ? /*!50000 -- x */ WHERE id = ?", 2), + Arguments.of("hashComment", "UPDATE users SET ssn = ? /*!50000 # x */ WHERE id = ?", 2), + Arguments.of("nestedComment", "UPDATE users SET ssn = ? /*!50000 /* x */ WHERE id = ?", 2)); + } + + @ParameterizedTest(name = "{0}") + @MethodSource("executableComments") + void testExecutableCommentChangingPlaceholdersIsReportedAndNothingIsMapped( + final String id, final String sql, final int placeholders) { + for (Mode mode : Arrays.asList(Mode.MYSQL, Mode.GENERIC)) { + final ParameterMapping result = map(sql, mode); + + assertTrue(result.getColumns().isEmpty(), id + " " + mode + ": " + result.getColumns()); + final List<ParameterMappingProblem> problems = tableProblems(result); + assertEquals(1, problems.size(), id + " " + mode + ": " + result.getProblems()); + assertProblem(problems.get(0), Type.EXECUTABLE_COMMENT, "users", Collections.emptyList(), + range(placeholders)); + } + } + + @ParameterizedTest(name = "{0}") + @MethodSource("executableComments") + void testExecutableCommentIsAnOrdinaryCommentForPostgres( + final String id, final String sql, final int placeholders) { + assertTrue(problemsOfType(map(sql, Mode.POSTGRESQL), Type.EXECUTABLE_COMMENT).isEmpty(), id); + } + + @Test + void testExecutableCommentInMultiTableUpdateNamesEveryWrittenTable() { + final ParameterMapping result = map( + "UPDATE other o JOIN users u ON o.id = u.id SET /*!50000 o.note = ?, */ u.ssn = ?", Mode.MYSQL); + + assertTrue(result.getColumns().isEmpty(), result.getColumns().toString()); + final List<ParameterMappingProblem> problems = tableProblems(result); + assertEquals(2, problems.size(), result.getProblems().toString()); + assertEquals("other", problems.get(0).getTable()); + assertEquals("users", problems.get(1).getTable()); + } + + @Test + void testExecutableCommentInUnparsedWriteNamesItsIdentifiers() { + final ParameterMapping result = + map("UPDATE users SET ssn = /*!50000 ? */ WHERE id = ?", Mode.MYSQL); + + final List<ParameterMappingProblem> problems = problemsOfType(result, Type.EXECUTABLE_COMMENT); + assertEquals(1, problems.size(), result.getProblems().toString()); + assertNull(problems.get(0).getTable()); + assertTrue(problems.get(0).getCandidateTables().contains("users")); + assertEquals(range(1), problems.get(0).getParameterIndexes()); + } + + @Test + void testExecutableCommentInSelectIsReportedForItsTables() { + final ParameterMapping result = map( + "SELECT * FROM users u JOIN other o ON u.id = o.id WHERE /*!50000 o.note = ? AND */ u.ssn = ?", + Mode.MYSQL); + + assertTrue(result.getColumns().isEmpty(), result.getColumns().toString()); + final List<ParameterMappingProblem> problems = tableProblems(result); + assertEquals(2, problems.size(), result.getProblems().toString()); + assertEquals(Arrays.asList("users", "other"), + Arrays.asList(problems.get(0).getTable(), problems.get(1).getTable())); + } + + @Test + void testExecutableCommentHoldingTheOnlyPlaceholderIsReported() { + final ParameterMapping result = + map("UPDATE users SET ssn = COALESCE(/*!?,*/ ssn) WHERE id = 1", Mode.MYSQL); + + final List<ParameterMappingProblem> problems = tableProblems(result); + assertEquals(1, problems.size(), result.getProblems().toString()); + assertProblem(problems.get(0), Type.EXECUTABLE_COMMENT, "users", Collections.emptyList(), + Collections.emptyList()); + } + + @Test + void testExecutableCommentInUnparsedStatementWithoutOuterPlaceholderIsReported() { + final ParameterMapping result = map("UPDATE users SET ssn = /*!50000 ? */ WHERE", Mode.MYSQL); + + final List<ParameterMappingProblem> problems = problemsOfType(result, Type.EXECUTABLE_COMMENT); + assertEquals(1, problems.size(), result.getProblems().toString()); + assertTrue(problems.get(0).getCandidateTables().contains("users")); + assertTrue(problemsOfType(result, Type.UNPARSED_STATEMENT).isEmpty()); + } + + static Stream<Arguments> harmlessExecutableComments() { + return Stream.of( + Arguments.of("modifier", "UPDATE /*!50000 LOW_PRIORITY */ users SET ssn = ? WHERE id = ?"), + Arguments.of("keywords", "UPDATE users SET ssn = ? WHERE id = ? /*!50000 AND TRUE */"), + Arguments.of("optimizerHint", "UPDATE /*+ NO_MERGE() */ users SET ssn = ? WHERE id = ?"), + Arguments.of("ordinaryComment", "UPDATE users SET /* why? 'x */ ssn = ? WHERE id = ?")); + } + + @ParameterizedTest(name = "{0}") + @MethodSource("harmlessExecutableComments") + void testCommentWithoutPlaceholderSensitiveTextIsMapped(final String id, final String sql) { + final ParameterMapping result = map(sql, Mode.MYSQL); + + assertEquals(mapping(1, "users.ssn"), result.getColumns(), id); + assertTrue(result.getProblems().isEmpty(), id + ": " + result.getProblems()); + } + + private static List<ParameterMappingProblem> problemsOfType( + final ParameterMapping mapping, final Type type) { + final List<ParameterMappingProblem> problems = new ArrayList<>(); + for (ParameterMappingProblem problem : mapping.getProblems()) { + if (problem.getType() == type) { + problems.add(problem); + } + } + return problems; + } + + /** The executable-comment problems reported for a parsed table, not for the statement's words. */ + private static List<ParameterMappingProblem> tableProblems(final ParameterMapping mapping) { + final List<ParameterMappingProblem> problems = new ArrayList<>(); + for (ParameterMappingProblem problem : problemsOfType(mapping, Type.EXECUTABLE_COMMENT)) { + if (problem.getTable() != null) { + problems.add(problem); + } + } + return problems; + } + + private static List<Integer> range(final int count) { + final List<Integer> result = new ArrayList<>(); + for (int i = 1; i <= count; i++) { + result.add(i); + } + return result; + } + @Test void testUnboundColumnToStringHasNoSqlText() { final ParameterMapping result = map("INSERT INTO users (ssn) VALUES ('secret-literal')"); @@ -614,4 +951,39 @@ private static void assertProblem( assertEquals(columns, problem.getColumns(), problem.toString()); assertEquals(indexes, problem.getParameterIndexes(), problem.toString()); } + + static Stream<Arguments> tablesOnlyInExecutableComment() { + return Stream.of( + Arguments.of("write", "UPDATE plain SET a = ? /*!50000 , users.ssn = ? */"), + Arguments.of("unparsedWrite", "UPDATE plain /*!, users SET users.ssn = ? */"), + Arguments.of("select", "SELECT 1 /*! FROM users WHERE ssn = ? */"), + Arguments.of("subquery", + "SELECT * FROM plain WHERE uid IN (SELECT id FROM users WHERE ssn = ?) /*! AND x = ? */")); + } + + @ParameterizedTest(name = "{0}") + @MethodSource("tablesOnlyInExecutableComment") + void testExecutableCommentProblemNamesEveryWordOfTheStatement(final String id, final String sql) { + boolean named = false; + for (ParameterMappingProblem problem + : problemsOfType(map(sql, Mode.MYSQL), Type.EXECUTABLE_COMMENT)) { + named |= problem.getTable() == null && problem.getCandidateTables().contains("users"); + } + assertTrue(named, id); + } + + @Test + void testStructureInExecutableCommentIsReported() { + final ParameterMapping result = + map("UPDATE users SET nickname /*!12345 = nickname, ssn */ = ? WHERE id = 1", Mode.MYSQL); + + assertTrue(result.getColumns().isEmpty(), result.getColumns().toString()); + assertEquals("users", problemsOfType(result, Type.EXECUTABLE_COMMENT).get(0).getTable()); + } + + @Test + void testTableNamesDifferingInCaseAreDifferentTables() { + assertTrue(map("SELECT * FROM \"Users\" JOIN users ON 1 = 1 WHERE ssn = ?", Mode.POSTGRESQL) + .getColumns().isEmpty()); + } } diff --git a/wrapper/src/test/java/software/amazon/jdbc/parser/PlaceholderScannerTest.java b/wrapper/src/test/java/software/amazon/jdbc/parser/PlaceholderScannerTest.java index a819140e9..7005b2c92 100644 --- a/wrapper/src/test/java/software/amazon/jdbc/parser/PlaceholderScannerTest.java +++ b/wrapper/src/test/java/software/amazon/jdbc/parser/PlaceholderScannerTest.java @@ -196,4 +196,61 @@ void testSemicolonsInLiteralsCommentsAndDollarQuotesDoNotSplit() { assertEquals(1, PlaceholderScanner.scan("SELECT 'a;b', `c;d` # ;\n", Mode.MYSQL).getStatements().size()); } + + static Stream<Arguments> executableComments() { + return Stream.of( + Arguments.of("UPDATE t SET /*!50000 a = ?, */ b = ?", true), + Arguments.of("UPDATE t SET /*! a = ?, */ b = ?", true), + Arguments.of("UPDATE t SET /*M!100100 a = ?, */ b = ?", true), + Arguments.of("UPDATE t SET /*m! a = ?, */ b = ?", true), + Arguments.of("UPDATE t SET b = /*!50000 'x */ ?", true), + Arguments.of("UPDATE t SET b = /*!50000 \"x */ ?", true), + Arguments.of("UPDATE t SET b = /*!50000 `x */ ?", true), + Arguments.of("UPDATE t SET b = ? /*!50000 -- x */", true), + Arguments.of("UPDATE t SET b = ? /*!50000 # x */", true), + Arguments.of("UPDATE t SET b = ? /*!50000 /* x */", true), + Arguments.of("UPDATE t SET b = ? /*!50000 ?", true), + Arguments.of("UPDATE /*!50000 LOW_PRIORITY */ t SET b = ?", false), + Arguments.of("UPDATE t SET b = ? /*!*/", false), + Arguments.of("UPDATE t SET b = ? /*!", false), + Arguments.of("UPDATE t SET b = ? /* ? 'x */", false), + Arguments.of("UPDATE /*+ BKA(t) ? */ t SET b = ?", false), + Arguments.of("UPDATE t SET b = '/*! ? */' WHERE c = ?", false), + Arguments.of("UPDATE t SET b = ? -- /*! ? */", false), + Arguments.of("UPDATE t SET /*M a = ? */ b = ?", false), + // Structure changes which column a placeholder is assigned to, when there is one. + Arguments.of("UPDATE t SET a /*!12345 = a, b */ = ? WHERE id = 1", true), + Arguments.of("INSERT INTO t (a /*!, b */) VALUES (?, ?)", true), + Arguments.of("UPDATE t SET a = 1 /*!50000 , b = 2 */", false), + Arguments.of("UPDATE t SET b = ? WHERE id = ? /*!50000 AND TRUE */", false)); + } + + @ParameterizedTest(name = "{0}") + @MethodSource("executableComments") + void testPlaceholderSensitiveExecutableComment(final String sql, final boolean expected) { + assertEquals(expected, + PlaceholderScanner.scan(sql, Mode.MYSQL).hasPlaceholderSensitiveExecutableComment(), "MYSQL"); + assertEquals(expected, + PlaceholderScanner.scan(sql, Mode.GENERIC).hasPlaceholderSensitiveExecutableComment(), "GENERIC"); + assertFalse( + PlaceholderScanner.scan(sql, Mode.POSTGRESQL).hasPlaceholderSensitiveExecutableComment(), + "POSTGRESQL"); + } + + @Test + void testExecutableCommentIsSkippedLikeOtherComments() { + final PlaceholderScanner.Result result = + PlaceholderScanner.scan("UPDATE t SET /*!50000 a = ?, */ b = ?", Mode.MYSQL); + + assertEquals(1, result.getPlaceholderCount()); + assertEquals(1, result.getBlockComments().size()); + } + + @Test + void testExecutableCommentWords() { + assertEquals(Arrays.asList("users", "ssn"), PlaceholderScanner.scan( + "UPDATE plain SET a = ? /*!50000 , users.ssn = ? */", Mode.MYSQL).getExecutableCommentWords()); + assertTrue(PlaceholderScanner.scan("UPDATE /*!50000 LOW_PRIORITY */ t SET b = ?", Mode.MYSQL) + .getExecutableCommentWords().isEmpty()); + } } diff --git a/wrapper/src/test/java/software/amazon/jdbc/plugin/encryption/KmsEncryptionConnectionPluginTest.java b/wrapper/src/test/java/software/amazon/jdbc/plugin/encryption/KmsEncryptionConnectionPluginTest.java index b227e1cc8..97d450eb8 100644 --- a/wrapper/src/test/java/software/amazon/jdbc/plugin/encryption/KmsEncryptionConnectionPluginTest.java +++ b/wrapper/src/test/java/software/amazon/jdbc/plugin/encryption/KmsEncryptionConnectionPluginTest.java @@ -163,7 +163,7 @@ void test_setString_encryptsForEncryptedColumn() throws Exception { // Set up statement context by calling prepareStatement first when(mockPreparedStatement.getConnection()).thenReturn(mockConnection); - populateContext("users", java.util.Collections.singletonMap(2, "ssn")); + populateContext("users", java.util.Collections.singletonMap(2, "users.ssn")); plugin.execute( PreparedStatement.class, SQLException.class, mockConnection, "Connection.prepareStatement", () -> mockPreparedStatement, @@ -253,11 +253,10 @@ void test_setString_splitsSchemaQualifiedColumnOnLastDot() throws Exception { } /** - * A bare column name keeps using the statement's parsed table, so the positional mapping path is - * unchanged by the qualified-identifier handling. + * A bare column name is an annotation whose table is not certain, so its value is not encrypted. */ @Test - void test_setString_bareColumnStillUsesParsedTable() throws Exception { + void test_setString_bareColumnIsNotEncrypted() throws Exception { when(mockPreparedStatement.getConnection()).thenReturn(mockConnection); populateContext("users", java.util.Collections.singletonMap(1, "ssn")); @@ -266,21 +265,24 @@ void test_setString_bareColumnStillUsesParsedTable() throws Exception { "Connection.prepareStatement", () -> mockPreparedStatement, "INSERT INTO users (ssn) VALUES (?)"); - when(mockMetadataManager.isColumnEncrypted("users", "ssn")).thenReturn(false); - + boolean[] called = {false}; plugin.execute( Void.class, SQLException.class, mockPreparedStatement, - "PreparedStatement.setString", () -> null, + "PreparedStatement.setString", () -> { + called[0] = true; + return null; + }, 1, "123-45-6789"); - verify(mockMetadataManager).isColumnEncrypted("users", "ssn"); + assertTrue(called[0]); + verify(mockMetadataManager, never()).isColumnEncrypted(anyString(), anyString()); } @Test void test_setString_passesThrough_forNonEncryptedColumn() throws Exception { when(mockPreparedStatement.getConnection()).thenReturn(mockConnection); - populateContext("users", java.util.Collections.singletonMap(1, "name")); + populateContext("users", java.util.Collections.singletonMap(1, "users.name")); plugin.execute( PreparedStatement.class, SQLException.class, mockConnection, "Connection.prepareStatement", () -> mockPreparedStatement, @@ -350,7 +352,7 @@ void test_getString_decryptsForEncryptedColumn() throws Exception { @Test void test_setString_zeroizesDataKey_whenEncryptionFails() throws Exception { when(mockPreparedStatement.getConnection()).thenReturn(mockConnection); - populateContext("users", java.util.Collections.singletonMap(2, "ssn")); + populateContext("users", java.util.Collections.singletonMap(2, "users.ssn")); plugin.execute( PreparedStatement.class, SQLException.class, mockConnection, "Connection.prepareStatement", () -> mockPreparedStatement, @@ -1268,4 +1270,176 @@ void test_subscribedMethods_containsExpectedMethods() { assertTrue(methods.contains("ResultSet.getInt")); assertTrue(methods.contains("ResultSet.getObject")); } + + // --------------------------------------------------------------------------------------------- + // Multi-table statements, bare and aliased columns, and executable comments. + // --------------------------------------------------------------------------------------------- + + private void useMysqlDialect() { + when(mockPluginService.getTargetDriverDialect()) + .thenReturn(new software.amazon.jdbc.targetdriverdialect.MysqlConnectorJTargetDriverDialect()); + } + + static Stream<Arguments> chainedMultiTableCases() { + return Stream.of( + Arguments.of("joinSecondTable", + "UPDATE other o JOIN users u ON o.id = u.id SET o.note = ?, u.ssn = ? WHERE o.id = ?", 3, + indexes(2)), + Arguments.of("joinFirstTable", + "UPDATE users u JOIN other o ON o.id = u.id SET o.ssn = ?, u.ssn = ? WHERE o.id = ?", 3, + indexes(2)), + Arguments.of("comma", "UPDATE other o, users u SET u.ssn = ? WHERE o.id = u.id", 1, + indexes(1)), + Arguments.of("tableNames", + "UPDATE other JOIN users ON other.id = users.id SET users.ssn = ?", 1, indexes(1)), + Arguments.of("parenthesizedJoin", + "UPDATE other o JOIN (users u JOIN things t ON u.id = t.id) ON o.id = u.id SET u.ssn = ?", + 1, indexes(1)), + Arguments.of("parenthesizedComma", "UPDATE other o, (users u) SET u.ssn = ? WHERE o.id = u.id", + 1, indexes(1)), + Arguments.of("bareAnnotation", + "UPDATE other o JOIN users u ON o.id = u.id SET u.ssn = /*@encrypt:ssn*/ ? WHERE o.id = ?", + 2, indexes(1)), + Arguments.of("aliasAnnotation", + "SELECT * FROM users u WHERE lower(u.ssn) = /*@encrypt:u.ssn*/ ?", 1, indexes(1)), + Arguments.of("selectJoinQualifier", + "SELECT * FROM other o JOIN users u ON o.id = u.id WHERE u.ssn = ?", 1, indexes(1)), + Arguments.of("selectJoinOtherQualifier", + "SELECT * FROM users u JOIN other o ON o.id = u.id WHERE o.ssn = ?", 1, indexes()), + // Columns whose table is not certain are not encrypted. + Arguments.of("selectJoinUnqualified", + "SELECT * FROM other o JOIN users u ON o.id = u.id WHERE ssn = ?", 1, indexes()), + Arguments.of("selectDerivedTable", + "SELECT * FROM (SELECT * FROM users) d WHERE d.ssn = ?", 1, indexes()), + Arguments.of("selectCommonTableExpression", + "WITH d AS (SELECT * FROM users) SELECT * FROM d WHERE ssn = ?", 1, indexes()), + Arguments.of("selectBareAnnotationSeveralTables", + "SELECT * FROM other o JOIN users u ON o.id = u.id WHERE lower(u.ssn) = /*@encrypt:ssn*/ ?", + 1, indexes())); + } + + static Stream<Arguments> chainedUncertainWriteCases() { + return Stream.of( + Arguments.of("unqualifiedSet", + "UPDATE other o JOIN users u ON o.id = u.id SET ssn = ? WHERE o.id = 1"), + Arguments.of("bareAnnotationInExpression", + "UPDATE other o JOIN users u ON o.id = u.id SET o.note = lower(/*@encrypt:ssn*/ ?)"), + Arguments.of("aliasesDifferingInCase", + "UPDATE users a JOIN other A ON a.id = A.id SET a.ssn = ?"), + Arguments.of("annotationOverridingEncryptedColumn", + "INSERT INTO users (ssn) SELECT /*@encrypt:o.ssn*/ ? FROM other o")); + } + + @ParameterizedTest(name = "{0}") + @MethodSource("chainedUncertainWriteCases") + void test_chained_writeColumnWithUncertainTable_strict_throws(String id, String sql) throws Exception { + stubEncryptedUsersSsn(); + useMysqlDialect(); + + SQLException e = assertThrows(SQLException.class, () -> prepareChained(sql), id); + + verify(mockPreparedStatement).close(); + assertTrue(e.getMessage().contains("users"), e.getMessage()); + assertTrue(e.getMessage().contains("encryptionFailOnUnmappedParameter"), e.getMessage()); + } + + @ParameterizedTest(name = "{0}") + @MethodSource("chainedUncertainWriteCases") + void test_chained_writeColumnWithUncertainTable_nonStrict_warnsAndDoesNotEncrypt(String id, String sql) + throws Exception { + stubEncryptedUsersSsn(); + useMysqlDialect(); + when(mockUtility.isFailOnUnmappedParameter()).thenReturn(false); + + List<LogRecord> warnings = captureWarnings(() -> { + assertEquals(mockPreparedStatement, prepareChained(sql)); + assertEquals(indexes(), boundEncrypted(1), id); + }); + + assertEquals(1, warnings.size(), id); + } + + + static Stream<Arguments> chainedExecutableCommentCases() { + return Stream.of( + Arguments.of("versioned", "UPDATE users SET /*!50000 name = ?, */ ssn = ? WHERE id = ?"), + Arguments.of("unversioned", "UPDATE users SET /*! name = ?, */ ssn = ? WHERE id = ?"), + Arguments.of("mariadb", "UPDATE users SET /*M!100100 name = ?, */ ssn = ? WHERE id = ?"), + Arguments.of("insert", "INSERT INTO users (id, ssn) VALUES (? /*!50000 + ? */, ?)"), + Arguments.of("encryptedSecondTable", + "UPDATE other o JOIN users u ON o.id = u.id SET /*!50000 o.note = ?, */ u.ssn = ?"), + Arguments.of("annotationOnly", + "UPDATE other SET /*!50000 a = ?, */ b = /*@encrypt:users.ssn*/ ? WHERE id = ?"), + Arguments.of("unparsed", "UPDATE users SET ssn = /*!50000 ? */ WHERE id = ?"), + Arguments.of("onlyPlaceholderInComment", + "UPDATE users SET ssn = COALESCE(/*!?,*/ ssn) WHERE id = 1"), + Arguments.of("selectWithAnnotation", + "SELECT * FROM users WHERE /*!50000 id = ? AND */ ssn = /*@encrypt:users.ssn*/ ?"), + Arguments.of("bareAnnotation", + "INSERT INTO other (a, b) SELECT /*!50000 ?, */ id, name FROM users" + + " WHERE ssn = /*@encrypt:ssn*/ ?"), + Arguments.of("structureOnly", + "UPDATE users SET nickname /*!12345 = nickname, ssn */ = ? WHERE id = 1"), + Arguments.of("columnListStructure", "INSERT INTO users (name /*!, ssn */) VALUES (?, ?)"), + Arguments.of("tableOnlyInComment", "UPDATE other SET a = ? /*!50000 , users.ssn = ? */"), + Arguments.of("tableInSubquery", + "SELECT * FROM other WHERE uid IN (SELECT id FROM users WHERE ssn = ?) /*! AND x = ? */")); + } + + @ParameterizedTest(name = "{0}") + @MethodSource("chainedExecutableCommentCases") + void test_chained_executableComment_throwsAndClosesEvenWhenNotStrict(String id, String sql) + throws Exception { + stubEncryptedUsersSsn(); + useMysqlDialect(); + when(mockUtility.isFailOnUnmappedParameter()).thenReturn(false); + + SQLException e = assertThrows(SQLException.class, () -> prepareChained(sql), id); + + verify(mockPreparedStatement).close(); + assertTrue(e.getMessage().contains("users"), e.getMessage()); + assertTrue(e.getMessage().contains("executable comment"), e.getMessage()); + assertFalse(e.getMessage().contains(sql), e.getMessage()); + } + + @Test + void test_chained_executableCommentOnTableWithoutEncryptedColumns_isPrepared() throws Exception { + stubEncryptedUsersSsn(); + useMysqlDialect(); + + assertEquals(mockPreparedStatement, + prepareChained("UPDATE other SET /*!50000 name = ?, */ note = ? WHERE id = ?")); + assertEquals(indexes(), boundEncrypted(3)); + verify(mockPreparedStatement, never()).close(); + } + + @Test + void test_chained_executableCommentWithoutPlaceholder_encrypts() throws Exception { + stubEncryptedUsersSsn(); + useMysqlDialect(); + + assertEquals(mockPreparedStatement, + prepareChained("UPDATE /*!50000 LOW_PRIORITY */ users SET ssn = ? WHERE id = ?")); + assertEquals(indexes(1), boundEncrypted(2)); + } + + @Test + void test_chained_executableCommentForPostgres_isAnOrdinaryComment() throws Exception { + stubEncryptedUsersSsn(); + when(mockPluginService.getTargetDriverDialect()).thenReturn(new PgTargetDriverDialect()); + + assertEquals(mockPreparedStatement, + prepareChained("UPDATE users SET /*! name = ?, */ ssn = ? WHERE id = ?")); + // The PostgreSQL dialect does not bind ciphertext with setBytes, so pass-through is checked. + Set<Integer> passedThrough = new TreeSet<>(); + for (int i = 1; i <= 2; i++) { + final int index = i; + plugin.execute(Void.class, SQLException.class, mockPreparedStatement, + "PreparedStatement.setString", () -> { + passedThrough.add(index); + return null; + }, i, "v" + i); + } + assertEquals(indexes(2), passedThrough); + } } diff --git a/wrapper/src/test/java/software/amazon/jdbc/plugin/sqlparser/SqlParserConnectionPluginTest.java b/wrapper/src/test/java/software/amazon/jdbc/plugin/sqlparser/SqlParserConnectionPluginTest.java index 5b2964e8a..adaaf29f3 100644 --- a/wrapper/src/test/java/software/amazon/jdbc/plugin/sqlparser/SqlParserConnectionPluginTest.java +++ b/wrapper/src/test/java/software/amazon/jdbc/plugin/sqlparser/SqlParserConnectionPluginTest.java @@ -27,6 +27,7 @@ import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; +import java.util.Arrays; import java.util.Collections; import java.util.HashMap; import java.util.List; @@ -328,7 +329,8 @@ public void close() { logger.removeHandler(handler); } - assertEquals("public.users.ssn", paramMapping().get(2)); + // The annotation agrees with the automatic mapping, whose table name the metadata uses. + assertEquals("users.ssn", paramMapping().get(2)); assertTrue(warnings.isEmpty()); } @@ -399,4 +401,153 @@ void test_preparedStatementExecuteWithSql_parsesThatSqlNotThePreparedOne() throw assertEquals(QueryType.SELECT, callContext.getAttribute(SqlContextKeys.QUERY_TYPE, QueryType.class)); assertTrue(unboundColumns().isEmpty()); } + + private List<LogRecord> warningsWhilePreparing(String sql) throws Exception { + final Logger logger = Logger.getLogger(SqlParserConnectionPlugin.class.getName()); + final List<LogRecord> warnings = new ArrayList<>(); + final Handler handler = new Handler() { + @Override + public void publish(LogRecord record) { + if (record.getLevel() == Level.WARNING) { + warnings.add(record); + } + } + + @Override + public void flush() { + } + + @Override + public void close() { + } + }; + logger.addHandler(handler); + try { + executeWithSql(sql); + } finally { + logger.removeHandler(handler); + } + return warnings; + } + + @Test + void test_multiTableUpdate_tablesListWrittenTablesInStatementOrder() throws Exception { + executeWithSql("UPDATE other o JOIN users u ON o.id = u.id SET u.ssn = ?"); + + Set<String> tables = callContext.getAttribute(SqlContextKeys.TABLES, Set.class); + assertEquals(Arrays.asList("other", "users"), new ArrayList<>(tables)); + } + + @Test + void test_bareAnnotation_takesTableOfPositionalMapping() throws Exception { + final List<LogRecord> warnings = warningsWhilePreparing( + "UPDATE other o JOIN users u ON o.id = u.id SET u.ssn = /*@encrypt:ssn*/ ? WHERE o.id = ?"); + + assertEquals(Collections.singletonMap(1, "users.ssn"), paramMapping()); + assertTrue(warnings.isEmpty()); + } + + @Test + void test_aliasAnnotation_resolvesToItsTable() throws Exception { + executeWithSql("SELECT * FROM users u WHERE lower(u.ssn) = /*@encrypt:u.ssn*/ ?"); + + assertEquals(Collections.singletonMap(1, "users.ssn"), paramMapping()); + } + + @Test + void test_aliasAnnotationAgreeingWithMapping_doesNotWarn() throws Exception { + final List<LogRecord> warnings = + warningsWhilePreparing("UPDATE users u SET ssn = /*@encrypt:u.ssn*/ ? WHERE u.id = ?"); + + assertEquals(Collections.singletonMap(1, "users.ssn"), paramMapping()); + assertTrue(warnings.isEmpty()); + } + + @Test + void test_resolveAnnotation() { + final Map<String, String> aliases = new HashMap<>(); + aliases.put("u", "users"); + aliases.put("other", "other"); + + assertEquals("users.ssn", + SqlParserConnectionPlugin.resolveAnnotation("ssn", "users.ssn", aliases, null)); + assertEquals("ssn", SqlParserConnectionPlugin.resolveAnnotation("ssn", "users.name", aliases, null)); + assertEquals("ssn", SqlParserConnectionPlugin.resolveAnnotation("ssn", null, aliases, null)); + assertEquals("users.ssn", SqlParserConnectionPlugin.resolveAnnotation("ssn", null, aliases, "users")); + assertEquals("users.ssn", SqlParserConnectionPlugin.resolveAnnotation("U.ssn", null, aliases, null)); + assertEquals("other.ssn", SqlParserConnectionPlugin.resolveAnnotation("other.ssn", null, aliases, null)); + assertEquals("public.u.ssn", + SqlParserConnectionPlugin.resolveAnnotation("public.u.ssn", null, aliases, null)); + } + + @Test + void test_bareAnnotation_singleTableStatement_takesThatTable() throws Exception { + executeWithSql("SELECT * FROM users WHERE lower(ssn) = /*@encrypt:ssn*/ ?"); + + assertEquals(Collections.singletonMap(1, "users.ssn"), paramMapping()); + } + + @Test + void test_bareAnnotation_schemaQualifiedSingleTable_takesBareTableName() throws Exception { + executeWithSql("UPDATE public.users SET note = lower(/*@encrypt:ssn*/ ?) WHERE id = 1"); + assertEquals(Collections.singletonMap(1, "users.ssn"), paramMapping()); + for (ParameterMappingProblem problem : problems()) { + assertFalse(problem.getParameterIndexes().contains(1) + && problem.getType() == ParameterMappingProblem.Type.UNKNOWN_TARGET_PARAMS, problem.toString()); + } + + executeWithSql("SELECT * FROM public.users WHERE lower(ssn) = /*@encrypt:ssn*/ ?"); + assertEquals(Collections.singletonMap(1, "users.ssn"), paramMapping()); + } + + @Test + void test_bareAnnotation_tablesInDifferentSchemas_stayBare() throws Exception { + executeWithSql("SELECT * FROM a.users JOIN b.other ON users.id = other.id" + + " WHERE lower(ssn) = /*@encrypt:ssn*/ ?"); + + assertEquals(Collections.singletonMap(1, "ssn"), paramMapping()); + } + + @Test + void test_bareAnnotation_subqueryTableCounts_staysBare() throws Exception { + executeWithSql("SELECT * FROM other WHERE id IN (SELECT id FROM users WHERE lower(ssn) = /*@encrypt:ssn*/ ?)"); + + assertEquals(Collections.singletonMap(1, "ssn"), paramMapping()); + } + + @Test + void test_bareAnnotationWithUncertainTableInWrite_reportsUnmappedParameter() throws Exception { + executeWithSql("UPDATE other o JOIN users u ON o.id = u.id SET u.ssn = lower(/*@encrypt:ssn*/ ?)"); + + assertEquals(Collections.singletonMap(1, "ssn"), paramMapping()); + boolean reported = false; + for (ParameterMappingProblem problem : problems()) { + reported |= problem.getType() == ParameterMappingProblem.Type.UNKNOWN_TARGET_PARAMS + && "users".equals(problem.getTable()) + && problem.getColumns().equals(Collections.singletonList("ssn")); + } + assertTrue(reported, problems().toString()); + } + + @Test + void test_annotationOverridingPositionalColumnInWrite_reportsThatColumn() throws Exception { + executeWithSql("INSERT INTO users (ssn) SELECT /*@encrypt:o.ssn*/ ? FROM other o"); + + assertEquals(Collections.singletonMap(1, "o.ssn"), paramMapping()); + boolean reported = false; + for (ParameterMappingProblem problem : problems()) { + reported |= problem.getType() == ParameterMappingProblem.Type.UNKNOWN_TARGET_PARAMS + && "users".equals(problem.getTable()) + && problem.getColumns().equals(Collections.singletonList("ssn")) + && problem.getParameterIndexes().equals(Collections.singletonList(1)); + } + assertTrue(reported, problems().toString()); + } + + @Test + void test_schemaQualifiedAnnotationWithoutMapping_isKeptAsWritten() throws Exception { + executeWithSql("SELECT * FROM public.users WHERE lower(ssn) = /*@encrypt:public.users.ssn*/ ?"); + + assertEquals(Collections.singletonMap(1, "public.users.ssn"), paramMapping()); + } }

view thread (7+ messages)  latest in thread

Message-ID: <gh-aws-aws-advanced-jdbc-wrapper-2253@github.com>
Permalink:  ../gh-aws-aws-advanced-jdbc-wrapper-2253@github.com/

 ·  · 


This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox