Field encryption with existing data (v2023)

[philmills]

I have an existing project with is in production environment
I'd like to start using field encryption for some table fields, but am looking for some clarification on how to convert existing unencrypted data to encrypted.

I have a MEDIUMTEXT field with existing data (including html) which needs to be encrypted, and some VARCHAR fields in a users table containing existing data which should also be encrypted.
If I was starting from a fresh table it would be ok, but the problem is with converting existing unencrypted data to encrypted

Thanks

[philmills]

So I got this far:

• I discovered that encrypted fields containing existing data still display that data correctly which is great
• on save the data then gets encrypted, this is good to know

I don't want users to have to re-save every record
How can i encrypt them all at once?
I'm thinking maybe to create a table purely for this purpose:
e.g.
Table name: Encrypt_Tool
fields: id, targetTable, targetField - plus some custom fields to show encrypted and unecrypted records count for each row for checking

Then add a row_inserted or row_updated event to encrypt the target table's Field for all records

Or is there an easier way?

[arbei]

You may create a simple script yourself to loop through the records and encrypt the fields by using the PhpEncrypt().

When you connect to your database, make sure you use the same encoding as your project, e.g. If you use MySQL 8, you should set name as "utf8bm4".

[philmills]

I got field encryption working in a standalone page as proof of concept, and now I'm tryng to make it work in a custom page with headers included.

My page has a form with table selector (showing all tables in the db)
followed by a field selector which shows the available fields in the selected table.
These are working fine.

The submit button (encrypt_btn) should execute the following function, which loops through all rows in the selected table encrypting values for the selected field which aren't already encrypted:

// Handle form submission for encryption
if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['encrypt_btn'])) {
    $table = $_POST["table_select"];
    $field = $_POST["field_select"];

    // Fetch records from the selected table
    $records_query = "SELECT * FROM $table";
    $records_result = ExecuteRows($records_query);

    if ($records_result) {
        foreach ($records_result as $row) {
            $value = $row[$field];
            if (substr($value, 0, 3) !== "def") { // Check if the value is not already encrypted
                $encrypted_value = PhpEncrypt($value); // Use PhpEncrypt function for encryption

                // Construct the update query without executing it
                $update_query = "UPDATE $table SET $field = '$encrypted_value' WHERE id = " . $row['id'];
                echo "Update Query: $update_query<br>"; // Debugging: Print update queries
            }
        }
        echo "Encryption queries generated successfully.";
    } else {
        echo "No records found in the selected table.";
    }

    // Reset the form after processing
    echo "<script>document.getElementById('table_select').value = ''; document.getElementById('field_select').value = '';</script>";
}

At the moment this code should just echo what's going on, not actually submit.
But all it does is throw an error 400 with nothing useful in the debug log or console.

I'm confused firstly about the PhpEncrypt() function as suggested and how it should be implemented. Is syntax correct in the code above?
I can't find anything in the manual about PhpEncrypt(). In the Field Encryption extension also there are no clues given how to use it in this kinda context.

Help would be appreciated

v2023