jdaugherty commented on code in PR #16631: URL: https://github.com/apache/grails-core/pull/16631#discussion_r4362041015
########## grails-datamapping-core/src/main/groovy/org/grails/datastore/gorm/jdbc/sqltail/SqlTailRedactor.java: ########## @@ -0,0 +1,452 @@ +/* + * 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, whether a quote written + * twice escapes it or ends one string and starts another, whether a quote written three times + * opens a string that quotes can appear in, whether {@code #} starts a comment, and whether + * {@code #} and {@code @} are part of a name or operators. 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[] YES = {true}; + 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, or a database that can read double-quoted text as a string although it quotes + * identifiers with double quotes, such as SQLite and Informix: 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, HSQLDB and HANA. + * They differ on {@code #} and {@code @}, which are operators in PostgreSQL and part of a name in Oracle + * and DB2, so a statement that has one next to a number is withheld. + */ + STANDARD, + + /** MySQL and MariaDB: double quotes delimit strings, and {@code #} and {@code -- } start comments. */ + MYSQL, + + /** + * Microsoft SQL Server and Sybase: square brackets delimit identifiers, comments nest, a dollar before + * digits is an amount, and double quotes delimit a string when {@code QUOTED_IDENTIFIER} is off, as it is + * by default on Sybase. + */ + TRANSACT_SQL, + + /** + * GoogleSQL, the dialect of Cloud Spanner: backticks delimit identifiers, single and double quotes + * delimit strings, a string that opens with its quote written three times ends only at the quote + * written three times again and holds any other quote, a backslash always escapes the character + * after it, a quote written twice is two strings, and {@code #} starts a comment. + */ + GOOGLE_SQL + } + + 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; + boolean[] symbols = sql.indexOf('#') >= 0 || sql.indexOf('@') >= 0 ? EITHER : YES; + List<Reading> readings = new ArrayList<>(); + if (syntax == Syntax.STANDARD || syntax == Syntax.UNKNOWN) { + for (boolean backslashEscapes : backslash) { + for (boolean nestedComments : nesting) { + for (boolean symbolsInIdentifiers : symbols) { + readings.add(new Reading(Syntax.STANDARD, backslashEscapes, nestedComments, + symbolsInIdentifiers)); + } + } + } + } + if (syntax == Syntax.MYSQL || syntax == Syntax.UNKNOWN) { + for (boolean backslashEscapes : backslash) { + readings.add(new Reading(Syntax.MYSQL, backslashEscapes, false, true)); + } + } + if (syntax == Syntax.TRANSACT_SQL || syntax == Syntax.UNKNOWN) { + readings.add(new Reading(Syntax.TRANSACT_SQL, false, true, true)); + } + if (syntax == Syntax.GOOGLE_SQL || syntax == Syntax.UNKNOWN) { + readings.add(new Reading(Syntax.GOOGLE_SQL, true, false, true)); + } + return readings; + } + + /** + * One way of reading a statement. MySQL and GoogleSQL delimit identifiers with backticks and start a + * comment with {@code #}; only MySQL needs whitespace after {@code --}; only Transact-SQL delimits + * identifiers with square brackets. Outside standard SQL, double quotes can delimit a string: in + * MySQL unless {@code ANSI_QUOTES} is set, in Transact-SQL when {@code QUOTED_IDENTIFIER} is off, and + * in GoogleSQL always. Either way the quoted run ends at the same place, so it is replaced as a + * value. A GoogleSQL string is read on its own terms: a quote written twice ends it, a backslash + * escapes, and a quote written three times opens a string that ends only at the next three. In + * Transact-SQL a dollar before digits prefixes an amount, which is a value; elsewhere it prefixes a + * parameter or a name. + * + * @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 + * @param symbolsInIdentifiers whether {@code #} and {@code @} are part of a name, as in Oracle, DB2 and + * Transact-SQL, rather than operators, as in PostgreSQL, where a number after one is a value + */ + private record Reading(Syntax syntax, boolean backslashEscapes, boolean nestedComments, + boolean symbolsInIdentifiers) { + + /** + * @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; + boolean googleSql = syntax == Syntax.GOOGLE_SQL; + boolean doubleQuotedValue = syntax != Syntax.STANDARD; + 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 (googleSql && (c == '\'' || c == '"')) { + end = endOfGoogleSqlString(sql, i); + out.append(PLACEHOLDER); + } + else if (c == '\'') { + end = endOfQuotedLiteral(sql, i); + out.append(PLACEHOLDER); + } + else if (c == '"') { + end = endOfQuoted(sql, i, '"', mysql && backslashEscapes, true); + if (end >= 0) { + out.append(doubleQuotedValue ? PLACEHOLDER : sql.substring(i, end)); + } + } + else if (c == '`' && (mysql || googleSql)) { + end = endOfQuoted(sql, i, '`', googleSql, mysql); + if (end >= 0) { + out.append(sql, i, end); + } + } + else if (c == '[' && syntax == Syntax.TRANSACT_SQL) { + end = endOfQuoted(sql, i, ']', false, true); + if (end >= 0) { + out.append(sql, i, end); + } + } + else if (c == '/' && next == '*') { + end = endOfBlockComment(sql, i); + out.append(' '); + } + else if ((c == '-' && next == '-' && (!mysql || i + 2 >= length || Character.isWhitespace(sql.charAt(i + 2)))) || Review Comment: Fixed in 0dc78c283a. `--` now starts a MySQL comment before any character at or below U+0020 or at U+007F, which is what the server's whitespace-or-control test accepts for ASCII. A character outside ASCII after `--` depends on the connection's character set: a no-break space is whitespace in latin1 and a name character in utf8mb4 (checked against MySQL 8.0 and 9.x and MariaDB), so a statement with one is read both ways and withheld. While here I ran a lexical probe against every database the inspector maps (H2, HSQLDB, Derby and SQLite in memory; PostgreSQL, CockroachDB, MySQL, MariaDB, SQL Server, Oracle, Db2, Firebird, Informix and the Spanner emulator in containers) and replaced the one `STANDARD` reading with a `SqlTailSyntax` per database, as they did not share its rules: H2 reads `//` as a line comment, which was kept before; Oracle accepts the quote itself as the `q'` delimiter, so `q''a''` is a string; CockroachDB, Oracle, Informix and MySQL end a line comment only at a line feed; MySQL runs the content of `/*!...*/` depending on the server version, so such a statement is withheld. -- 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]
