sql-query-anonymizer 0.1.2

Tool for anonymizing schema data in SQL queries.

SQL Query Anonymizer

A Python tool for anonymizing SQL queries while preserving their structure and maintaining the ability to de-anonymize them later. Perfect for query optimization workflows, security testing, or data sharing scenarios where you need to hide sensitive identifiers.

✨ Features

• 🔒 Anonymization: Replace table names, column names, and literals with generic placeholders
• 🔓 De-anonymization: Restore original identifiers from anonymized queries
• 💾 Persistent Mappings: Pickle-based storage maintains mappings across sessions
• 🎯 Structure Preservation: SQL syntax and query structure remain intact
• 📊 Table Alias Detection: Quantifies table aliases in SELECT statements
• 🔄 Roundtrip Guarantee: Perfect roundtrip anonymization ↔ de-anonymization
• 🖥️ CLI & Python API: Use via command-line or import in Python code
• 📁 File Processing: Process SQL files in batch
• 🧪 Comprehensive Tests: 73 passing tests with pytest

📦 Installation

# Clone the repository
git clone https://github.com/ncarsner/sql-query-anonymizer.git
cd sql-query-anonymizer

# Install dependencies (using uv)
uv sync

# Or using pip
pip install -e .

# After installation, the sql-anonymizer command will be available
sql-anonymizer --help

🚀 Quick Start

Command Line Interface

# Anonymize a SQL query
sql-anonymizer anonymize "SELECT name, email FROM users WHERE age > 25"
# Output: SELECT identifier_1 , identifier_2 FROM table_1 WHERE identifier_3 > literal_1

# De-anonymize back to original
sql-anonymizer deanonymize "SELECT identifier_1 , identifier_2 FROM table_1 WHERE identifier_3 > literal_1"
# Output: SELECT name , email FROM users WHERE age > 25

# Show current mappings
sql-anonymizer show-mappings

# Process SQL files
sql-anonymizer anonymize -f input.sql -o anonymized.sql

Python API

from src.sql_query_anonymizer.utils import Anonymizer

# Create an anonymizer instance with default mapping file
anonymizer = Anonymizer()

# Anonymize a query
original = "SELECT customer_id, name FROM customers WHERE age > 30"
anonymized = anonymizer.anonymize_query(original)
print(anonymized)  # SELECT identifier_1 , identifier_2 FROM table_1 WHERE identifier_3 > literal_1

# Save mappings to persist for later
anonymizer.save()

# Later session - create new anonymizer and load mappings
anonymizer2 = Anonymizer()
anonymizer2.load()

# De-anonymize back to original
restored = anonymizer2.de_anonymize_query(anonymized)
print(restored)  # SELECT customer_id , name FROM customers WHERE age > 30

# Or use context manager for automatic save/load
with Anonymizer() as anon:
    anonymized = anon.anonymize_query(original)
    # Mappings automatically saved on exit

📁 Project Structure

sql-query-anonymizer/
├── .github/
│   └── workflows/
│       ├── publish.yml           # Automated PyPI publishing
│       └── tests.yml             # CI/CD testing
├── src/
│   └── sql_query_anonymizer/
│       ├── __init__.py
│       ├── anonymize.py          # Anonymization preprocessing
│       ├── cli.py                # Command-line interface
│       ├── constants.py          # SQL keywords and configuration
│       ├── helper_utilities.py   # File I/O utilities
│       ├── tokenize.py           # SQL tokenization engine
│       └── utils.py              # Core Anonymizer class
├── tests/
│   ├── __init__.py
│   ├── test_anonymize.py         # Anonymization tests
│   ├── test_cli.py               # CLI tests
│   └── test_utils.py             # Core utilities tests
├── data/
│   ├── _raw/                     # Sample raw SQL files
│   ├── _anonymized/              # Anonymized outputs
│   ├── _optimized/               # Optimized queries
│   └── _deanonymized/            # De-anonymized outputs
├── .pre-commit-config.yaml       # Pre-commit hooks configuration
├── CLI_USAGE.md                  # CLI quick reference
├── pyproject.toml                # Project configuration
├── uv.lock                       # Dependency lock file
├── README.md
└── LICENSE

🎯 Use Cases

1. Query Optimization: Anonymize queries before sending to external optimization services
2. Security Testing: Share query structures without exposing sensitive database schema
3. Documentation: Create generic examples from real queries
4. Training: Generate training datasets with anonymized queries
5. Debugging: Share problematic queries with support teams without revealing confidential data

🔧 CLI Commands

Command	Description
anonymize	Anonymize SQL query from string or file
deanonymize	De-anonymize query back to original form
show-mappings	Display current mapping statistics
clear-mappings	Clear all stored mappings
export-mappings	Export mappings to another pickle file
import-mappings	Import mappings from a pickle file
interactive	Start interactive mode

Examples:

# Anonymize a query
sql-anonymizer anonymize "SELECT * FROM users"

# Show current mappings
sql-anonymizer show-mappings

# Export mappings for backup
sql-anonymizer export-mappings backup.pkl

# Use custom mapping file
sql-anonymizer -m custom.pkl anonymize "SELECT * FROM products"

💾 Persistent Storage

Mappings are stored as pickle files in ~/.sql_anonymizer/mappings.pkl and persist across sessions. This ensures:

• Consistent anonymization: Same identifiers always map to the same placeholders
• Session persistence: Mappings survive between CLI sessions
• Backup and restore: Export/import mappings as needed
• Context manager support: Automatic save/load using Python's with statement

The pickle format stores mappings, reverse mappings, and counters efficiently.

🧪 Testing

# Run all tests
uv run pytest

# Run with coverage
uv run pytest --cov=src/sql_query_anonymizer

# Run specific test file
uv run pytest tests/test_utils.py

# Run with verbose output
uv run pytest -v

📋 Requirements

• Python 3.13+
• pytest (for testing)
• pytest-cov (for coverage reports)

🔍 How It Works

1. Tokenization: SQL query is parsed into tokens (keywords, identifiers, literals, operators)
2. Classification: Tokens are classified by type (table names, column names, literals, etc.)
3. Mapping: Each unique identifier is mapped to a generic placeholder
4. Replacement: Original identifiers are replaced with placeholders
5. Persistence: Mappings are saved for future de-anonymization
6. De-anonymization: Placeholders are mapped back to original identifiers

🎨 Example Transformations

-- Original
SELECT c.customer_id, c.first_name, o.order_date, SUM(o.amount)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.status = 'active' AND o.date >= '2024-01-01'
GROUP BY c.customer_id, c.first_name, o.order_date;

-- Anonymized
SELECT c.identifier_1 , c.identifier_2 , o.identifier_3 , SUM ( o.identifier_4 )
FROM table_1 c
JOIN table_2 o ON c.identifier_1 = o.identifier_1
WHERE c.identifier_5 = literal_1 AND o.identifier_6 >= literal_2
GROUP BY c.identifier_1 , c.identifier_2 , o.identifier_3 ;

🛠️ Configuration

Custom mapping file location:

sql-anonymizer -m custom_mappings.pkl anonymize "SELECT * FROM users"

Disable auto-save:

sql-anonymizer --no-auto-save anonymize "SELECT * FROM users"

Python API with custom location:

# Use custom mapping file
anonymizer = Anonymizer(mapping_file="project_mappings.pkl")
anonymizer.load()  # Load existing mappings

# Or use absolute path
anonymizer = Anonymizer(mapping_file="/path/to/mappings.pkl")

🔍 Feature Status

✅ Completed

• Core anonymization/de-anonymization engine
• Pickle-based persistent mapping storage
• Context manager support (with statement)
• Command-line interface with multiple commands
• File processing capabilities
• Table alias detection and quantification
• Comprehensive test suite (73 passing tests)
• Export/import functionality

🚧 Future Enhancements

• Support for additional SQL dialects (PostgreSQL, MySQL, etc.)
• GUI interface
• API server mode
• Pattern-based anonymization rules
• Integration with query optimization tools
• Publish to PyPI

🧾 License

MIT License - See LICENSE for details

✍️ Author

Developed by Nicholas Carsner

Contributions, issues, and feature requests are welcome!