Last active
January 13, 2022 17:47
-
-
Save craigmccreath/31bb7bc3e958b724a81949c1c50e4b13 to your computer and use it in GitHub Desktop.
Redact sensitive data from MySQL Databases (PHP 5.1+)
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| /** | |
| * THE GREAT REDACTOR | |
| * | |
| * PHP Version 5.1+ | |
| * | |
| * Use this in cases where data needs to be copied to a test environment. This will | |
| * strip all data from fields as defined $redact. If you need to exclude some | |
| * tables, add this to $excluded_tables. | |
| * | |
| * Usage: php redactor.php, then follow prompts. | |
| * | |
| * @author Craig McCreath <craigmccreath@gmail.com> | |
| */ | |
| if (empty($argv)) { | |
| exit("Must be run from CLI"); | |
| } | |
| $excluded_tables = array('admin_menu'); | |
| $redact = array( | |
| 'firstname', 'lastname', 'first_name', 'last_name', 'surname', | |
| 'address1', 'address2', 'email', 'contact_no', 'mobile_no', | |
| 'email_address', 'phone', 'phone_number' | |
| ); | |
| $host = input('MySQL Host (localhost): '); | |
| $user = input('MySQL User: '); | |
| $pass = input('MySQL Password: '); | |
| $host = empty($host) ? 'localhost' : $host; | |
| $pdo = new PDO("mysql:host={$host};port=3306", $user, $pass); | |
| $databases = fetch_databases($pdo); | |
| $updates = array(); | |
| foreach ($databases as $database) { | |
| $tables = fetch_tables($pdo, $database, $excluded_tables); | |
| foreach ($tables as $table) { | |
| $fields = fetch_fields($pdo, $database, $table, $redact); | |
| foreach ($fields as $field) { | |
| $updates[] = compact('database', 'table', 'field'); | |
| } | |
| } | |
| } | |
| if (empty($updates)) { | |
| exit("No data to be redacted\n"); | |
| } | |
| // Warn before we go ahead: | |
| echo "\n------\nThe following data will now be redacted:\n-----\n"; | |
| foreach ($updates as $update) { | |
| echo implode('.', $update) . PHP_EOL; | |
| } | |
| echo "-----\n"; | |
| $prompt = input("To continue, please type 'YES': "); | |
| if ($prompt !== 'YES') { | |
| exit("Canceled\n"); | |
| } | |
| if ($prompt === 'YES') { | |
| foreach ($updates as $update) { | |
| extract($update); | |
| echo implode('.', $update); | |
| $pdo->exec("UPDATE `{$database}`.`{$table}` SET `{$field}` = 'REDACTED';"); | |
| echo " ✅" . PHP_EOL; | |
| } | |
| } | |
| function fetch_databases($pdo) | |
| { | |
| $stmt = $pdo->query('show databases where `Database` != "information_schema";'); | |
| return $stmt->fetchAll(PDO::FETCH_COLUMN); | |
| } | |
| function fetch_tables($pdo, $database, $excluded_tables) | |
| { | |
| $tables = array(); | |
| $stmt = $pdo->query("show tables from `{$database}`;"); | |
| while ($table = $stmt->fetch(PDO::FETCH_COLUMN)) { | |
| if (!in_array($table, $excluded_tables)) { | |
| $tables[] = $table; | |
| } | |
| } | |
| return $tables; | |
| } | |
| function fetch_fields($pdo, $database, $table, $redact_list) | |
| { | |
| $fields = array(); | |
| $stmt = $pdo->query("describe `{$database}`.`{$table}`"); | |
| while ($row = $stmt->fetch()) { | |
| if (in_array($row['Field'], $redact_list)) { | |
| $fields[] = $row['Field']; | |
| } | |
| }; | |
| return $fields; | |
| } | |
| function input($prompt = null) | |
| { | |
| echo $prompt; | |
| $handle = fopen("php://stdin", "r"); | |
| $output = fgets($handle); | |
| return trim($output); | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment