Encrypting Database Entries

Encrypting All Entries In A Column of an InnoDB Table

Imagine you just made a huge mysql dump and you want to create encryted passwords for a login script. First, you need to encrypt some data so passwords can be stored and retrieved in an encrypted format. Below is an example showing how to encrypt all entries in one of 2 database columns.

<?php

$success = true;
//start transaction
$command = "SET AUTOCOMMIT=0";
$result = mysqli_query($db, $command);
$command = "BEGIN";
$result = mysqli_query($db, $command);

$command = "SELECT tablename.id, tablename.username from tablename ";
$result = mysqli_query($db, $command);
if ($result && mysqli_num_rows($result) > 0){
while($row = mysqli_fetch_assoc($result)) {
echo "<br/><br/>Result 1 is:".$result."<br/><br/>";
$myids = $row['id'];
$user_name = $row['username'];
$user_name= md5($user_name);
echo "hello".$user_name;
$command2 = "UPDATE tablename SET username='$user_name' WHERE id='$myids' ORDER BY id DESC";
$result2 = mysqli_query($db, $command2);
echo "<br/><br/>Result 2 is".$result2." and id is ".$myids." and encrypted username is ".$user_name."";
if (($result == false) ||
(mysqli_affected_rows() == 0)) {
$success = false;
}
}
}

$command = "COMMIT";
$result = mysqli_query($db, $command);
#set session variable
$command = "SET AUTOCOMMIT=1";  //return to autocommit
$result = mysqli_query($db, $command);
?>

Encrypting All Entries In A Column of a MYISAM Table

$command = "SELECT tablename.id, tablename.username from tablename ";
$result = mysqli_query($db, $command);
if ($result && mysqli_num_rows($result) > 0){
while($row = mysqli_fetch_assoc($result)) {
echo "<br/><br/>Result 1 is:".$result."<br/><br/>";
$myids = $row['id'];
$user_name = $row['username'];
$user_name= md5($user_name);
echo "hello".$user_name;
$command2 = "UPDATE tablename SET username='$user_name' WHERE id='$myids' ORDER BY id DESC";
$result2 = mysqli_query($db, $command2);
echo "<br/><br/>Result 2 is".$result2." and id is ".$myids." and encrypted username is ".$user_name."";

}
}