pg.ddx.io  aws/aws-advanced-jdbc-wrapper GitHub issues and pull requests  
help / color / mirror / Atom feed
[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
7+ messages / 2 participants
[nested] [flat]

* [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
@ 2026-11-18 00:17 "aaron-congo (@aaron-congo)" <noreply+aaron-congo@github.com>
  0 siblings, 0 replies; 7+ messages in thread

From: aaron-congo (@aaron-congo) @ 2026-11-18 00:17 UTC (permalink / raw)
  To: aws/aws-advanced-jdbc-wrapper <noreply+aws-aws-advanced-jdbc-wrapper@github.com>

### 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()); + } }
^ permalink raw [nested|flat] 7+ messages in thread

* Re: [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
@ 2026-11-19 00:22 ` "sophia-bq (@sophia-bq)" <noreply+sophia-bq@github.com>
  5 siblings, 0 replies; 7+ messages in thread

From: sophia-bq (@sophia-bq) @ 2026-11-19 00:22 UTC (permalink / raw)
  To: aws/aws-advanced-jdbc-wrapper <noreply+aws-aws-advanced-jdbc-wrapper@github.com>

(on wrapper/src/main/resources/aws_advanced_jdbc_wrapper_messages.properties)

i believe the scanner now also refuses ,, =, (, ) and ;

^ permalink  raw  [nested|flat] 7+ messages in thread

* Re: [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
@ 2026-11-19 00:26 ` "sophia-bq (@sophia-bq)" <noreply+sophia-bq@github.com>
  5 siblings, 0 replies; 7+ messages in thread

From: sophia-bq (@sophia-bq) @ 2026-11-19 00:26 UTC (permalink / raw)
  To: aws/aws-advanced-jdbc-wrapper <noreply+aws-aws-advanced-jdbc-wrapper@github.com>

(on wrapper/src/main/java/software/amazon/jdbc/plugin/sqlparser/SqlParserConnectionPlugin.java)

I think this treats some cases as the same when they are not. Because a.users and b.users with different schemas are not the same. Also if there is a time when case matters this would treat them the same then as well

^ permalink  raw  [nested|flat] 7+ messages in thread

* Re: [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
@ 2026-11-19 19:06 ` "aaron-congo (@aaron-congo)" <noreply+aaron-congo@github.com>
  5 siblings, 0 replies; 7+ messages in thread

From: aaron-congo (@aaron-congo) @ 2026-11-19 19:06 UTC (permalink / raw)
  To: aws/aws-advanced-jdbc-wrapper <noreply+aws-aws-advanced-jdbc-wrapper@github.com>

(on wrapper/src/main/java/software/amazon/jdbc/plugin/sqlparser/SqlParserConnectionPlugin.java)

Good point, thanks. Merging by last segment and ignoring case was only meant to stop `public.users` and `users` (the same table, reported once by the mapper and once by JSqlParser's table finder) from counting as two tables, but it also merged genuinely different tables. I've changed it to count only the table finder's names, compared exactly (no case folding, schema kept), and to use the bare name only when exactly one table remains. `a.users JOIN b.users` and `"Users" JOIN users` now leave a bare annotation unresolved, so it isn't encrypted and is reported as unmapped in a write.


^ permalink  raw  [nested|flat] 7+ messages in thread

* Re: [aws/aws-advanced-jdbc-wrapper] PR #2253 (approved): fix: map KMS parameters per table in multi-table statements, and refuse MySQL executable comments that change placeholders
@ 2026-11-19 19:35 ` "sophia-bq (@sophia-bq)" <noreply+sophia-bq@github.com>
  5 siblings, 0 replies; 7+ messages in thread

From: sophia-bq (@sophia-bq) @ 2026-11-19 19:35 UTC (permalink / raw)
  To: aws/aws-advanced-jdbc-wrapper <noreply+aws-aws-advanced-jdbc-wrapper@github.com>



^ permalink  raw  [nested|flat] 7+ messages in thread

* Re: [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
@ 2026-11-19 20:57 ` "aaron-congo (@aaron-congo)" <noreply+aaron-congo@github.com>
  5 siblings, 0 replies; 7+ messages in thread

From: aaron-congo (@aaron-congo) @ 2026-11-19 20:57 UTC (permalink / raw)
  To: aws/aws-advanced-jdbc-wrapper <noreply+aws-aws-advanced-jdbc-wrapper@github.com>

@aaron-congo merged this (commit 2edb664617c2).

^ permalink  raw  [nested|flat] 7+ messages in thread

* Re: [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
@ 2026-11-19 20:57 ` "aaron-congo (@aaron-congo)" <noreply+aaron-congo@github.com>
  5 siblings, 0 replies; 7+ messages in thread

From: aaron-congo (@aaron-congo) @ 2026-11-19 20:57 UTC (permalink / raw)
  To: aws/aws-advanced-jdbc-wrapper <noreply+aws-aws-advanced-jdbc-wrapper@github.com>

@aaron-congo closed this.

^ permalink  raw  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2026-11-19 20:57 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-18 00:17 [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 "aaron-congo (@aaron-congo)" <noreply+aaron-congo@github.com>
2026-11-19 00:22 ` "sophia-bq (@sophia-bq)" <noreply+sophia-bq@github.com>
2026-11-19 00:26 ` "sophia-bq (@sophia-bq)" <noreply+sophia-bq@github.com>
2026-11-19 19:06 ` "aaron-congo (@aaron-congo)" <noreply+aaron-congo@github.com>
2026-11-19 19:35 ` Re: [aws/aws-advanced-jdbc-wrapper] PR #2253 (approved): fix: map KMS parameters per table in multi-table statements, and refuse MySQL executable comments that change placeholders "sophia-bq (@sophia-bq)" <noreply+sophia-bq@github.com>
2026-11-19 20:57 ` "aaron-congo (@aaron-congo)" <noreply+aaron-congo@github.com>
2026-11-19 20:57 ` "aaron-congo (@aaron-congo)" <noreply+aaron-congo@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