jdaugherty commented on code in PR #16631: URL: https://github.com/apache/grails-core/pull/16631#discussion_r4351167877
########## grails-datamapping-core/src/main/groovy/org/grails/datastore/gorm/jdbc/sqltail/SqlTailRedactor.java: ########## @@ -0,0 +1,363 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * https://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.grails.datastore.gorm.jdbc.sqltail; + +import java.util.ArrayList; +import java.util.List; + +/** + * Takes the values out of a statement before it is captured. Every string, number and + * dollar-quoted literal becomes {@link #PLACEHOLDER} and every comment is dropped, so that a value + * written into the SQL text, a password in a native query or a key in a comment, never reaches the + * capture file. Only the recorded copy is redacted: the statement the database runs is not changed. + * + * <p>Databases disagree on parts of the lexical syntax: whether a backslash escapes a quote, whether + * comments nest, whether double quotes delimit an identifier or a string, and whether {@code #} + * starts a comment. A statement is redacted under every reading its {@link Syntax} allows, and is + * recorded only when all of them give the same text. When they do not, or when a literal or a + * comment never ends, the statement is withheld: {@link #WITHHELD} is recorded in its place, never + * the statement.</p> + * + * @since 8.1 + */ +public final class SqlTailRedactor { + + /** What a literal is replaced with. */ + public static final String PLACEHOLDER = "?"; + + /** What is recorded in place of a statement whose values cannot be told apart from its SQL. */ + public static final String WITHHELD = "(statement withheld: its values could not be redacted)"; + + private static final boolean[] NO = {false}; + private static final boolean[] EITHER = {false, true}; + + /** + * The lexical syntax of the database a statement runs on, as far as finding its values goes. + */ + public enum Syntax { + + /** Not known: a statement is recorded only when the readings of every syntax below agree. */ + UNKNOWN, + + /** Standard SQL, where double quotes delimit identifiers: H2, PostgreSQL, Oracle, DB2 and most others. */ + STANDARD, + + /** MySQL and MariaDB: double quotes delimit strings, and {@code #} and {@code -- } start comments. */ + MYSQL, + + /** Microsoft SQL Server and Sybase: square brackets delimit identifiers, and comments nest. */ + TRANSACT_SQL; + + /** + * @param openQuote the character a dialect opens a quoted identifier with + * @return the syntax of the databases that quote identifiers with it + */ + public static Syntax forIdentifierQuote(char openQuote) { + return switch (openQuote) { + case '"' -> STANDARD; + case '`' -> MYSQL; + case '[' -> TRANSACT_SQL; + default -> UNKNOWN; + }; + } + } + + private SqlTailRedactor() { + } + + /** + * Redacts a statement. + * + * @param sql the statement + * @param syntax the syntax of the database it runs on + * @return the statement with its literals replaced and its comments dropped, or {@link #WITHHELD} + */ + public static String redact(String sql, Syntax syntax) { + if (sql == null) { + return null; + } + String redacted = null; + for (Reading reading : readings(sql, syntax == null ? Syntax.UNKNOWN : syntax)) { + String candidate = reading.redact(sql); + if (candidate == null || (redacted != null && !redacted.equals(candidate))) { + return WITHHELD; + } + redacted = candidate; + } + return redacted; + } + + /** + * The readings a statement has to agree under. A reading that depends on a character the + * statement does not contain is the same as the one that does not, so it is left out. + */ + private static List<Reading> readings(String sql, Syntax syntax) { + boolean[] backslash = sql.indexOf('\\') >= 0 ? EITHER : NO; + int comment = sql.indexOf("/*"); + boolean[] nesting = comment >= 0 && sql.indexOf("/*", comment + 2) >= 0 ? EITHER : NO; + List<Reading> readings = new ArrayList<>(); + if (syntax == Syntax.STANDARD || syntax == Syntax.UNKNOWN) { + for (boolean backslashEscapes : backslash) { + for (boolean nestedComments : nesting) { + readings.add(new Reading(Syntax.STANDARD, backslashEscapes, nestedComments)); + } + } + } + if (syntax == Syntax.MYSQL || syntax == Syntax.UNKNOWN) { + for (boolean backslashEscapes : backslash) { + readings.add(new Reading(Syntax.MYSQL, backslashEscapes, false)); + } + } + if (syntax == Syntax.TRANSACT_SQL || syntax == Syntax.UNKNOWN) { + readings.add(new Reading(Syntax.TRANSACT_SQL, false, true)); + } + return readings; + } + + /** + * One way of reading a statement. Only MySQL delimits strings with double quotes and identifiers + * with backticks, starts a comment with {@code #}, and needs whitespace after {@code --}; only + * Transact-SQL delimits identifiers with square brackets. + * + * @param syntax the syntax read in, never {@link Syntax#UNKNOWN} + * @param backslashEscapes whether a backslash escapes the character after it in a string + * @param nestedComments whether a block comment can contain another + */ + private record Reading(Syntax syntax, boolean backslashEscapes, boolean nestedComments) { + + /** + * @return the redacted statement, or null when a literal, a quoted identifier or a comment never ends + */ + String redact(String sql) { + boolean mysql = syntax == Syntax.MYSQL; + int length = sql.length(); + StringBuilder out = new StringBuilder(length); + int i = 0; + while (i < length) { + char c = sql.charAt(i); + char next = i + 1 < length ? sql.charAt(i + 1) : 0; + char previous = i > 0 ? sql.charAt(i - 1) : 0; + int dollarTag = c == '$' && !isIdentifierPart(previous) ? dollarTagLength(sql, i) : 0; + int end; + if (c == '\'') { + end = endOfQuotedLiteral(sql, i); + out.append(PLACEHOLDER); + } + else if (c == '"') { Review Comment: Fixed in a562379a66. Double-quoted text is now recorded as `?` in every syntax except standard SQL. In Transact-SQL the quoted text ends at the same place whether `QUOTED_IDENTIFIER` is on or off, so it's replaced rather than withheld. The cost is that a double-quoted name in a hand-written SQL Server statement is recorded as `?` too. Your batch is now covered through the capture API in `SqlTailCaptureSpec` with the capture's syntax set to Transact-SQL, and in the Transact-SQL table of `SqlTailRedactorSpec`, along with a `""` escape inside the quotes. Sybase had the same leak through a different path. Its dialect quotes identifiers with `"`, so the inspector classed it as standard SQL, but Sybase's `QUOTED_IDENTIFIER` is off by default. Both inspectors now pick the syntax from the dialect class instead of the quote character: - SQL Server and every Sybase dialect (ASE, SQL Anywhere) are Transact-SQL. - Dialects that quote identifiers with backticks (MySQL, MariaDB, Spanner) read as MySQL. - Only H2, PostgreSQL, CockroachDB, Oracle, DB2, HSQLDB and HANA, plus Derby on Hibernate 5, are standard SQL. - Any other dialect, including SQLite and Informix, which read double-quoted text as a string by default, is read in every syntax, so a double-quoted run there withholds the statement. `SqlTailStatementInspector.syntaxOf` is covered with real dialects in both Hibernate specs, and the redaction section of the guide now describes this. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
