MySQL Data Retriever and Remover - Export Tool and Remover

This tool provides two main functions: (1) connects to a MySQL database, retrieves account data, parses DG1 and DG2 fields, and outputs the results to a CSV file with extracted personal images, and (2) safely removes users from the database when their passports expire and need to be replaced with updated passport data.

Location: This is the export version located in scripts/directDatabaseManagement/ containing all necessary files for data extraction.

Features

• Connects to MySQL database with configurable parameters
• Retrieves all data from the account table or filters by specific UIDs
• Parses DG1 data to extract MRZ (Machine Readable Zone) information
• Extracts images from DG2 data (JPEG/JPEG2000 format)
• Creates organized folder structure for personal images
• Outputs comprehensive CSV with parsed data and image paths
• NEW: Filter by specific UIDs (supports both numeric and string UIDs with special characters)

Installation

Install required dependencies:

pip3 install -r requirements.txt

Usage

From the scripts/directDatabaseManagement/ directory:

# Make sure you're in the directDatabaseManagement directory
cd scripts/directDatabaseManagement/

# Install dependencies
pip3 install -r requirements.txt

# Run the data retriever
python3 dataRetreiver.py \
    --host localhost \
    --port 3306 \
    --username your_username \
    --password your_password \
    --database your_database \
    --output account_data.csv \
    --images-dir personalImages

Or use the example script:

# Edit example_usage.sh with your database credentials, then run:
./example_usage.sh

Parameters

• --host: MySQL server hostname/IP (required)
• --port: MySQL server port (default: 3306)
• --username: MySQL username (required)
• --password: MySQL password (required)
• --database: MySQL database name (required)
• --output: Output CSV filename (default: account_data.csv)
• --images-dir: Directory for extracted images (default: personalImages)
• --uid-filter: Comma-separated list of UIDs to filter (optional). Use quotes for UIDs with special characters
• --expired-before: Filter users with passports expired before this date (YYYY-MM-DD format). Users will be saved in userWithExpiredPassport subfolder

UID Filtering

The --uid-filter parameter allows you to download only specific accounts by their UID. This is useful when you only need data for particular accounts instead of the entire database.

Basic Usage

# Download all accounts (default behavior)
python3 dataRetreiver.py --host localhost --username user --password pass --database mydb

# Download only specific numeric UIDs
python3 dataRetreiver.py --host localhost --username user --password pass --database mydb --uid-filter '1,5,10'

# Download only specific string UIDs
python3 dataRetreiver.py --host localhost --username user --password pass --database mydb --uid-filter 'user1,admin,abc123'

UIDs with Special Characters

IMPORTANT: UIDs containing special characters like @, ., +, spaces, or other symbols MUST be quoted to prevent shell interpretation.

# Email addresses (REQUIRES QUOTES)
python3 dataRetreiver.py --host localhost --username user --password pass --database mydb --uid-filter '"user@domain.com","admin@company.org"'

# Domain names with dots (REQUIRES QUOTES)
python3 dataRetreiver.py --host localhost --username user --password pass --database mydb --uid-filter '"admin.test","user.example.com"'

# Mixed special characters (REQUIRES QUOTES)
python3 dataRetreiver.py --host localhost --username user --password pass --database mydb --uid-filter '"user+tag@example.org","admin.test"'

# Using single quotes instead of double quotes
python3 dataRetreiver.py --host localhost --username user --password pass --database mydb --uid-filter "'user@domain.com','admin.test'"

Mixed UID Types

You can mix numeric and string UIDs in the same filter:

python3 dataRetreiver.py --host localhost --username user --password pass --database mydb --uid-filter '1,user2,"admin@test.com",100'

Performance Benefits

• Faster execution: Only queries specific records instead of the entire table
• Reduced network traffic: Transfers only the data you need
• Lower resource usage: Less memory and processing required

Expired Passport Filtering

The --expired-before parameter allows you to find users whose passports have expired before a specific date. This feature:

• Parses DG1 data to extract passport expiration dates
• Compares expiration dates with the provided cutoff date
• Saves expired passport users in a separate userWithExpiredPassport subfolder
• Prints expired users to console with their names and expiration dates
• Skips users without DG1 data or invalid expiration dates

Usage Examples

# Find users with passports expired before January 1, 2023
python3 dataRetreiver.py --host localhost --username user --password pass --database mydb --expired-before '2023-01-01'

# Find users with passports expired before December 31, 2022
python3 dataRetreiver.py --host localhost --username user --password pass --database mydb --expired-before '2022-12-31'

# Combine with custom output file and images directory
python3 dataRetreiver.py --host localhost --username user --password pass --database mydb --expired-before '2023-06-01' --output expired_passports.csv --images-dir passport_images

Output Structure for Expired Passports

When using --expired-before, the tool creates a special folder structure:

personalImages/
├── userWithExpiredPassport/    # Users with expired passports
│   ├── [document_number_1]/
│   │   └── [document_number_1].jpg
│   └── [document_number_2]/
│       └── [document_number_2].jp2
└── [other_documents]/          # Non-expired passports
    └── [document_number].jpg

Console Output

The tool will display expired users in the console:

=== USERS WITH EXPIRED PASSPORTS (expired before 2023-01-01) ===
UID: user123 | Name: SMITH, JOHN | Expiry Date: 2022-12-15
UID: user456 | Name: DOE, JANE | Expiry Date: 2022-11-30
Total expired users found: 2
============================================================

Output Structure

CSV File

The CSV contains selected database fields plus parsed DG1 data:

• Original fields: uid, country, expires, aaSigAlgo, aaCount, aaLastAuthn
• Image path: path to extracted personal image
• Parsed DG1 fields: document_type, issuing_state, surname, given_names, document_number, nationality, date_of_birth, sex, date_of_expiry

Image Organization

personalImages/
├── [document_number_1]/
│   └── [document_number_1].jpg
├── [document_number_2]/
│   └── [document_number_2].jp2
└── ...

Database Schema

The script expects an account table with the following structure:

• uid
• country
• sodId
• expires
• aaPublicKey
• aaSigAlgo
• aaCount
• aaLastAuthn
• dg1 (hex-encoded MRZ data)
• dg2 (hex-encoded biometric image data)

Examples

Download All Accounts

python3 dataRetreiver.py \
    --host 192.168.1.100 \
    --username passport_user \
    --password secure_password \
    --database passport_db \
    --output passport_data.csv \
    --images-dir extracted_photos

This will:

1. Connect to MySQL server at 192.168.1.100
2. Query the account table in passport_db
3. Parse DG1 and DG2 data for each record
4. Save images to extracted_photos/[document_number]/
5. Generate passport_data.csv with all parsed information

Download Specific Accounts by UID

# Download only specific numeric UIDs
python3 dataRetreiver.py \
    --host 192.168.1.100 \
    --username passport_user \
    --password secure_password \
    --database passport_db \
    --uid-filter '1,5,10' \
    --output filtered_data.csv

# Download accounts with email UIDs (requires quotes)
python3 dataRetreiver.py \
    --host 192.168.1.100 \
    --username passport_user \
    --password secure_password \
    --database passport_db \
    --uid-filter '"user@domain.com","admin@company.org"' \
    --output email_accounts.csv

Quick Start with Example Script

# Edit the example_usage.sh file with your database credentials
nano example_usage.sh

# Run the example script to see all usage patterns
./example_usage.sh

User Deletion Workflow

This section describes the recommended two-step process for safely deleting users when their passports expire and they need to be replaced with the same user ID. The workflow involves first identifying and retrieving users with expired passports, then using the removal tool to delete them so they can be re-registered with updated passport data.

Step 1: Identify Users with Expired Passports

Before deleting users, it's crucial to first identify which users have expired passports and need to be replaced. The dataRetreiver.py script provides the best method for this:

Find Users with Expired Passports

This is the primary use case - finding users whose passports have expired and need to be replaced with updated passport data:

# Find users with passports expired before a specific date
python3 dataRetreiver.py \
    --host localhost \
    --username your_username \
    --password your_password \
    --database your_database \
    --expired-before 'YYYY-MM-DD' \
    --output expired_users.csv \
    --images-dir expired_passport_images

This command will:

• Identify all users with passports expired before January 1, 2023
• Save their data to expired_users.csv for backup purposes
• Extract their images to expired_passport_images/userWithExpiredPassport/
• Display expired users in the console with their names and expiration dates
• Provide the UIDs needed for the deletion step

Alternative: Retrieve Specific Users by UID

If you already know the specific UIDs of users with expired passports:

# Retrieve specific users by their UIDs (for backup before deletion)
python3 dataRetreiver.py \
    --host localhost \
    --username your_username \
    --password your_password \
    --database your_database \
    --uid-filter '1,5,10' \
    --output users_to_replace.csv \
    --images-dir backup_images

Step 2: Delete Users with Expired Passports

After identifying users with expired passports, use the dataRemover.py script to safely remove them from the database so they can be re-registered with updated passport data:

# Delete users with expired passports by their UIDs
python3 dataRemover.py \
    --host localhost \
    --username your_username \
    --password your_password \
    --database your_database \
    --uid-filter '1,5,10'

Important Notes:

• The removal script will show you the user details before deletion
• You'll be prompted to confirm each deletion
• The script removes data from both account and sod tables
• This action is irreversible - make sure you have backups
• After deletion, the same UID can be used to register the user with updated passport data

Complete Workflow Example

Here's a complete example of replacing users with expired passports:

# Step 1: Find users with passports expired before 2023-01-01
python3 dataRetreiver.py \
    --host localhost \
    --username admin \
    --password secure_pass \
    --database passport_db \
    --expired-before '2023-01-01' \
    --output expired_passports_2023.csv \
    --images-dir backup_expired_2023

# Review the output CSV and console messages to identify UIDs
# Example console output:
# === USERS WITH EXPIRED PASSPORTS (expired before 2023-01-01) ===
# UID: user123 | Name: SMITH, JOHN | Expiry Date: 2022-12-15
# UID: user456 | Name: DOE, JANE | Expiry Date: 2022-11-30
# Total expired users found: 2

# Step 2: Delete the users with expired passports
python3 dataRemover.py \
    --host localhost \
    --username admin \
    --password secure_pass \
    --database passport_db \
    --uid-filter 'user123,user456'

# Step 3: Re-register users with updated passport data using the same UIDs
# (This step is done through your normal registration process)

Safety Recommendations

1. Always backup first: Run the retriever script to create a backup before deletion
2. Review the data: Check the CSV output and console messages to verify you're deleting the right users
3. Test with a small batch: Start with a few UIDs to test the process
4. Use expired passport filtering: This is the safest way to identify users for replacement
5. Keep backups: The retriever script creates both CSV and image backups
6. Verify UID availability: After deletion, confirm the UID is available for re-registration

Batch Processing

For large-scale passport renewals, you can process users with expired passports in batches:

# Process expired users in batches of 10
# First batch
python3 dataRemover.py \
    --host localhost \
    --username admin \
    --password secure_pass \
    --database passport_db \
    --uid-filter 'user1,user2,user3,user4,user5,user6,user7,user8,user9,user10'

# Second batch
python3 dataRemover.py \
    --host localhost \
    --username admin \
    --password secure_pass \
    --database passport_db \
    --uid-filter 'user11,user12,user13,user14,user15,user16,user17,user18,user19,user20'

Troubleshooting

UID Filtering Issues

Problem: UIDs with special characters not working Solution: Always quote UIDs containing special characters:

# Wrong - will cause shell interpretation errors
--uid-filter user@domain.com,admin.test

# Correct - properly quoted
--uid-filter '"user@domain.com","admin.test"'

Problem: No records found when using UID filter Solutions:

1. Check that the UIDs exist in your database
2. Verify UID format matches exactly (case-sensitive)
3. Ensure proper quoting for special characters
4. Check for extra spaces in your UID list

Problem: Shell interpretation errors Solution: Use proper quoting based on your shell:

# For bash/zsh - use single quotes around the entire filter
--uid-filter '1,2,"user@domain.com"'

# For complex cases - escape quotes
--uid-filter "1,2,\"user@domain.com\""