Skip to content

INT-1414 - Implement credential obfuscation in SQL statements for Snowflake STAGE objects #7214

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Merged
filipelautert merged 4 commits into from
Aug 25, 2025
Merged

INT-1414 - Implement credential obfuscation in SQL statements for Snowflake STAGE objects #7214

filipelautert merged 4 commits into from
Aug 25, 2025

Conversation

@filipelautert
Copy link
Collaborator

@filipelautert filipelautert commented Aug 20, 2025 •
edited
Loading

Impact

  • Bug fix (non-breaking change which fixes expected existing functionality)
  • Enhancement/New feature (adds functionality without impacting existing logic)
  • Breaking change (fix or feature that would cause existing functionality to change)

Description

This pull request introduces a robust mechanism for obfuscating sensitive credentials in Snowflake STAGE SQL statements within the LoggingExecutor. The main goal is to prevent accidental logging of secrets such as AWS keys, tokens, and master keys by replacing their values with asterisks in any output. In addition, a comprehensive suite of unit tests has been added to verify the correctness and security of the obfuscation logic across a wide range of scenarios.

Credential Obfuscation Improvements:

  • Added two regular expressions to LoggingExecutor:
    • One to match and obfuscate specific credential fields (e.g., AWS_KEY_ID, AWS_SECRET_KEY, AWS_TOKEN, AZURE_SAS_TOKEN, MASTER_KEY) only within CREDENTIALS or ENCRYPTION blocks, ensuring context-aware obfuscation. [1] [2]
  • Implemented the obfuscateCredentials and obfuscateCredentialsInBlock methods to scan SQL statements, find relevant blocks, and replace credential values with asterisks, preserving original formatting and quotes.
  • Integrated this obfuscation logic into the SQL output process, ensuring all statements written to logs or output streams have secrets masked.

Testing and Validation:

  • Added a new test class LoggingExecutorCredentialsTest with extensive test coverage for:
    • All supported credential types, case insensitivity, quote handling, whitespace variations, word boundary precision, multiple blocks, international characters, large credential values, and complex credential values.
    • Ensured that only exact matches in the correct context are obfuscated, and that unrelated SQL content is unaffected.
    • Verified performance and security edge cases, including handling of both CREDENTIALS and ENCRYPTION blocks.

github-advanced-security[bot]
github-advanced-security bot found potential problems Aug 20, 2025
View reviewed changes
liquibase-standard/src/test/java/liquibase/executor/LoggingExecutorTest.java Outdated

Check notice

Code scanning / CodeQL

Unread local variable Note test

Variable 'String output' is never read.

Copilot Autofix

AI 4 months ago

To fix the problem, simply remove the declaration and assignment of the unused local variable output in the method testNullStatementHandling (line 119). Since the variable is not read or used in any way, and there are no side effects in the assignment (calling outputWriter.toString() is safe to remove), this change will not affect the functionality of the test. No other changes, imports, or definitions are required.

Suggested changeset 1
liquibase-standard/src/test/java/liquibase/executor/LoggingExecutorTest.java

Autofix patch

Autofix patch
Run the following command in your local git repository to apply this patch
cat << 'EOF' | git apply
diff --git a/liquibase-standard/src/test/java/liquibase/executor/LoggingExecutorTest.java b/liquibase-standard/src/test/java/liquibase/executor/LoggingExecutorTest.java
--- a/liquibase-standard/src/test/java/liquibase/executor/LoggingExecutorTest.java
+++ b/liquibase-standard/src/test/java/liquibase/executor/LoggingExecutorTest.java
@@ -116,7 +116,6 @@
         // Test that null statements don't cause exceptions
         loggingExecutor.execute(new RawSqlStatement(null));
         
-        String output = outputWriter.toString();
         // Should not throw exception and output should be handled gracefully
     }
 
EOF
@@ -116,7 +116,6 @@
// Test that null statements don't cause exceptions
loggingExecutor.execute(new RawSqlStatement(null));

String output = outputWriter.toString();
// Should not throw exception and output should be handled gracefully
}

Copilot is powered by AI and may make mistakes. Always verify output.
Unable to commit as this autofix suggestion is now outdated
@filipelautert filipelautert marked this pull request as ready for review August 20, 2025 16:25
@filipelautert filipelautert changed the title Simplify and improve credential obfuscation in SQL statements Implement credential obfuscation in SQL statements for Snowflake STAGE objects Aug 20, 2025
@filipelautert filipelautert self-assigned this Aug 20, 2025
@filipelautert filipelautert requested a review from SvampX August 21, 2025 19:20
@filipelautert
INT-1414: Mask credentials in Snowflake STAGE SQL commands
4837ab9 
Implement credential obfuscation for AWS_KEY_ID, AWS_SECRET_KEY, AWS_TOKEN, and AZURE_SAS_TOKEN in LoggingExecutor to prevent sensitive data exposure in SQL logs.

Changes:
- Add unified regex pattern for all credential types with ReDoS protection
- Implement context-aware obfuscation within CREDENTIALS blocks only
- Preserve credentials in SQL comments for documentation purposes
- Add comprehensive test coverage in LoggingExecutorCredentialsTest
- Maintain backward compatibility with existing LoggingExecutorTest

The implementation uses possessive quantifiers to prevent ReDoS attacks, word boundaries for precise matching, and backreferences to ensure proper quote handling.
@filipelautert
INT-1414: Simplify credential obfuscation logic in SQL statements and…
06b16ed 
… enhance comments
@filipelautert
INT-1414: Extend obfuscation to handle ENCRYPTION blocks in Snowflake…
af6944a 
… SQL

Enhancements:
- Add support for obfuscating ENCRYPTION blocks alongside CREDENTIALS in LoggingExecutor.
- Update regex pattern for context-aware obfuscation of both block types.
- Ensure sensitive values like MASTER_KEY are masked in SQL statements.
- Add unit tests in LoggingExecutorCredentialsTest to verify new functionality.
@filipelautert
Add KMS_KEY_ID to the credential obfuscation pattern in LoggingExecutor
b4dff38
@filipelautert filipelautert changed the title Implement credential obfuscation in SQL statements for Snowflake STAGE objects INT-1414 - Implement credential obfuscation in SQL statements for Snowflake STAGE objects Aug 25, 2025
@filipelautert filipelautert merged commit 64a7131 into master Aug 25, 2025
92 of 93 checks passed
@filipelautert filipelautert deleted the INT-1414 branch August 25, 2025 19:29
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@wwillard7800 wwillard7800 wwillard7800 approved these changes

@SvampX SvampX SvampX approved these changes

@KushnirykOleh KushnirykOleh Awaiting requested review from KushnirykOleh

@HorbatenkoYehor HorbatenkoYehor Awaiting requested review from HorbatenkoYehor

Assignees

@filipelautert filipelautert

Labels

runFunctional&HarnessTests TypeEnhancement

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

3 participants

@filipelautert @wwillard7800 @SvampX