Encryption, Decryption and MySQL in PHP is very important these days with hacker after hacker out there always ready to find new crafty ways to steal your customer’s information. Do not be a victim here because you do not have to be! Be proactive in your efforts to make your customers’ websites be a fortress of security.
UPDATE: I included a new way to search for items in the new file download on the next page.
PHP Encryption Decryption Key
In order to start this process we will need to create a simple key, I like to keep mine about 45 characters long but you can do what you like. Here is the script you will need to follow along in this tutorial.
//THE KEY FOR ENCRYPTION AND DECRYPTION $key = 'qkwjdiw239&&jdafweihbrhnan&^%$ggdnawhd4njshjwuuO';
PHP Encryption Code
Before inserting content into a database, encrypt it. You cannot rely upon the MySQL structure of binary or varbinary to encrypt data because all someone has to do in phpMyAdmin is import it then convert to text, varchar or something else that makes it human readable. The best things in life are free, right? Well, here is a free script that is going to protect you and your clients from hackers stealing private information.
Encryption Function in PHP
This PHP function is all you need to do the work of encryption before storing it in your database. This will take your data along with the key and encrypt the information.
//ENCRYPT FUNCTION
function encryptthis($data, $key) {
$encryption_key = base64_decode($key);
$iv = openssl_random_pseudo_bytes(openssl_cipher_iv_length('aes-256-cbc'));
$encrypted = openssl_encrypt($data, 'aes-256-cbc', $encryption_key, 0, $iv);
return base64_encode($encrypted . '::' . $iv);
}
//DECRYPT FUNCTION
function decryptthis($data, $key) {
$encryption_key = base64_decode($key);
list($encrypted_data, $iv) = array_pad(explode('::', base64_decode($data), 2),2,null);
return openssl_decrypt($encrypted_data, 'aes-256-cbc', $encryption_key, 0, $iv);
}
PHP Encryption Decryption Code Example
Here is an example of how these codes work with your POST variables. If you need more instructions on how to create forms, please see my tutorials FORM PROCESSING $_POST METHOD. As long as you have your server running you can copy and paste this PHP code example and it will work to show you how encryption and decryption is performed.
<?php
date_default_timezone_set('America/New_York');
?>
<html>
<head>
<title>PHP ENCRYPTION DECRYPTION MYSQL | The Best PHP Encryption Tutorial</title>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" type="text/css" >
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/animate.css/3.5.2/animate.min.css">
</head>
<body>
<div class="jumbotron"><h1>The Best PHP Encryption Tutorial</h1></div>
<div class="container">
<div class="row">
<div class="col-sm-3"></div>
<div class="col-sm-6">
<?php
//THE KEY FOR ENCRYPTION AND DECRYPTION
$key = 'qkwjdiw239&&jdafweihbrhnan&^%$ggdnawhd4njshjwuuO';
//ENCRYPT FUNCTION
function encryptthis($data, $key) {
$encryption_key = base64_decode($key);
$iv = openssl_random_pseudo_bytes(openssl_cipher_iv_length('aes-256-cbc'));
$encrypted = openssl_encrypt($data, 'aes-256-cbc', $encryption_key, 0, $iv);
return base64_encode($encrypted . '::' . $iv);
}
//DECRYPT FUNCTION
function decryptthis($data, $key) {
$encryption_key = base64_decode($key);
list($encrypted_data, $iv) = array_pad(explode('::', base64_decode($data), 2),2,null);
return openssl_decrypt($encrypted_data, 'aes-256-cbc', $encryption_key, 0, $iv);
}
if(isset($_POST['submit'])){
//GET POST VARIABLES
$firstName=$_POST['firstName'];
$email=$_POST['email'];
//THE ENCRYPTION PROCESS
$nameencrypted=encryptthis($firstName, $key);
$emailencrypted=encryptthis($email, $key);
//THE DECRYPTION PROCESS
$namedecrypted=decryptthis($nameencrypted, $key);
$emaildecrypted=decryptthis($emailencrypted, $key);
//DISPLAY RESULTS
echo '<h2>Original Data</h2>';
echo '<p>Name: '.$firstName.'</p>';
echo '<p>Email: '.$email.'</p>';
echo '<h2>Encrypted Data</h2>';
echo '<p>Name Encrypted: </p><p style="background-color:yellow">'.$nameencrypted.'</p>';
echo '<p>Email Encrypted: </p><p style="background-color:yellow; word-break: break-all;">'.$emailencrypted.'</p>';
echo '<h2>Decrypted Data</h2>';
echo '<p>Name Decrypted: '.$namedecrypted.'</p>';
echo '<p>Email Decrypted: '.$emaildecrypted.'</p>';
echo '<h2>Insert Results Into Database</h2>';
echo '<p>We will insert the encrypoted information into the database with this code.</p>'; ?>
<pre> mysqli_query($con,"INSERT INTO people(`name`, `email`)
VALUES ('$nameencrypted','$emailencrypted')");
Retrieve Results From Database
We will retrieve the results from the database with this code.
$con = new mysqli("$host", "$username", "$password", "$dbname");
$result = $con->query("SELECT * FROM people") ;
while ($row = $result->fetch_assoc()) {
echo decryptthis($row['name'], $key);
echo decryptthis($row['email'], $key);
}
//SEPERATOR
echo '<div class="well"><h2>Our Form</h2>';
//FORM FOR OUR EXAMPLE
echo '<form method="post">
<div class="form-group">
<label for="firstName">Enter Name Here</label>
<input type="text" class="form-conrtol" name="firstName">
</div>
<div class="form-group">
<label for="email">Enter Email Here</label>
<input type="email" class="form-conrtol" name="email">
</div>
<input type="submit" name="submit" class="btn btn-success btn-lg" value="submit">
</form>';
?>
</div>
</div>
<div class="col-sm-3"></div>
</div>
</div>
</body>
</html>
Prepare A Database
Create a database in phpMyAdmin and call it “users”. Then you will want to navigate to the SQL tab at the top and insert the following code that will give you a table called “people” with 4 tables in it.
CREATE TABLE `people` ( `id` int(11) NOT NULL, `name` text NOT NULL, `email` text NOT NULL, `reg_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
30 Comments
I get the following error when trying to decrypt: Warning: openssl_decrypt(): IV passed is 17 bytes long which is longer than the 16 expected by selected cipher, truncating in /home/skyttesystem/amager.yyyyyyy.dk/Admin/UserAdm/test4.php on line 24
The code is as following:
function decryptthis($data, $key) {
$encryption_key = base64_decode($key);
list($encrypted_data, $iv) = array_pad(explode(‘::’, base64_decode($data), 2),2,null);
XXXX return openssl_decrypt($encrypted_data, ‘aes-256-cbc’, $encryption_key, 0, $iv); XXXX
}
The line with XXXX is line 24.
Hope you can see what I am doing wrong.
This should be your decrypt function.
//DECRYPT FUNCTION
function decryptthis($data, $key) {
$encryption_key = base64_decode($key);
list($encrypted_data, $iv) = array_pad(explode('::', base64_decode($data), 2),2,null);
return openssl_decrypt($encrypted_data, 'aes-256-cbc', $encryption_key, 0, $iv);
}
Also are you calling your key in properly?
Have you had any troubles with decrypting dates? I had a date field that I encrypted and when trying to decrypt it just returns today’s date/day the I inserted the data. It works fine for DateTime fields.
foreach($stmt as $row){
$e = date_create(decryptthis($row[‘*******’],$key));
echo date_format($e, “Y-m-d”) . ”;
}
echo “”;
returns:
2021-12-30
2021-12-30
2021-12-30
…..
Sorry for the delay. Interesting find that you have but I have not tested it yet. If that is inside a function, you will have to use the Global variable like global $key; in order for it to work. I will test this out when I get some time. 🙂
Hello sir, i really liked your videos and they seem very helpful. I also subscribed to your YouTube channel plus i followed you on Instagram ! But i have a problem here. I have a simple project and i cannot figure out how to add this functionality to my project. Could you please add it for me if i send you the code ? It’s something very easy and very small ! Thank you very much for your time reading my email.
HI Panos, can you post the code here so I can see it? Make sure you let me know what kind of server you are using. You can surround your code in [code]code here[/code] tags. Thanks Maximus
Hi Maximus – thanks for the tutorial its working great, however can you explain how to Select all records from a database where the email is equal to an encrypted value.
e.g
instead of
$result = $con->query(“SELECT * FROM people”) ;
I would like to SELECT * FROM people WHERE email = $emailencrypted
Hi Harry, thanks for the question. I did a follow up lesson here on searching encrypted information. So what you would have to do in decrypt the email addresses in order to find a match.
Hi Maximus.
Thanks for making great tutorials and free scripts!
I’ve copy-pasted your encryption and decryption scripts and provided my own key, which I get from an included PHP-file. At first it works perfectly, but after a while, when I encrypt some new data, it somehow gets currupted: All other encrypted entries are 56 characters long, but the new one are 60 characters long. Then the decrypt script can’t decrypt the data.
The key hasn’t changed, the script hasn’t changed, the connection to MySQL hasn’t changed, so… Do you know what is going on?
Did you try just putting the key directly back into the script? I’ve been having funny things going on today in my programming. I could not even get an image to show up for the longest time. What happened I think is I copy and pasted my code from the website. It brought in invisible characters. However, I brought up notepad and copy and pasted into that then copied it again and pasted into my notepad plus plus then woo hoo there was an image! lol Funny things do happen. Do you have the script online or are you working locally? Let me know or just shoot it to me in an email. max@a1websitepro.com and I can try it out here. 🙂
Thanks for the fast reply!
Yeah, I tried putting it in directly into the script.
I’m still working locally. If I mail it to you, how much du you need? Just the key and the script? Or should I just post it here, and create a new key when we find a sollution? 😀
just zip up the whole thing and mail it to me. I can put it on my local here and see if I can see whats going on, lol.
please sir decryption doesn’t work on textarea. it works with input fields well. i want to know why?
It certainly does work on textarea. I use an application right now that I created at https://a1websitepro.com/work . When a customer enters details about their work order it is in a textarea.
Am having syntax error. Unexpected end of file
Can you share it with me?
Hi, I’m finding the way to encrypt and decrypt the exist database on WordPress. I don’t know how to edit this sample script in specific way to work with WordPress, and where i can add the script ?
Can you help me ?
Hi Kelbin, The way WordPress is configured may be a little bit of a problem. This script is specifically for developers that develop their own Content Management System, CRM’s and more. I am afraid that if you try to do this on WordPress you will have issues in the future with their updates. Let me know if you have any more questions. I would be glad to help.
how do I decrypt my data in the table that I created in my website?
I am uploading a new video and a new script right now. Give me 5 minutes.
Hello,
I am implementing your solution in one of my projects. Must say that your solution is very easy to use for people like me who are not developers.
I can insert into the database encrypted and retrieve from the database and decrypt rows. My problem is when I try to use the “where” clause. Then I think I should be able to retrieve a row, but it seems the decryption doesn’t work right there.
This is what my code looks like:
function login($user = ‘x’, $pass = ‘x’){
global $con;
global $key;
$sql = “SELECT id, user, pass FROM users WHERE user = ‘”.encryptthis($user, $key).”‘ and pass = ‘”.encryptthis($pass, $key).”‘ LIMIT 1″;
Gives me 0 rows
function login($user = ‘x’, $pass = ‘x’){
global $con;
global $key;
$sql = “SELECT id, user, pass FROM users “;
$result = $con->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
if(decryptthis($row[“user”], $key) === $user && decryptthis($row[“pass”], $key) === $pass){
echo “OK!!!!!!!!!!!”;
breaks;
}else{
echo “NOT ____________________”;
}
}
}else {
echo “0 results”;
}
Works, but not practical.
So the question is why can’t I search for an encrypted row?
You have to decrypt it before you search it. This is the follow-up article to what you are asking. https://a1websitepro.com/how-to-search-php-encrypted-database/
I don’t see how this can work. You are calling base64_decode() on a key that has never been put thru base64_encode().
Try running that $key value thru an online base64_decode and you will get “Invalid characters”.
These are two PHP functions for encrypting and decrypting data using AES-256-CBC algorithm with a randomly generated initialization vector (IV).
The encryptthis() function takes two arguments: the data to be encrypted ($data) and the encryption key ($key). It first decodes the base64-encoded key and then generates a random IV using the openssl_random_pseudo_bytes() function. The openssl_encrypt() function is then used to encrypt the data using AES-256-CBC algorithm with the key and IV, and returns the encrypted data encoded in base64 format along with the IV separated by a double colon (::).
The decryptthis() function takes two arguments: the encrypted data ($data) and the encryption key ($key). It first decodes the base64-encoded key and then splits the encrypted data and IV using the explode() function. The openssl_decrypt() function is then used to decrypt the encrypted data using AES-256-CBC algorithm with the key and IV, and returns the original plaintext.
It is important to note that encryption alone does not ensure complete security, as the key needs to be stored and transmitted securely to prevent unauthorized access to the encrypted data. It is also recommended to use strong and unique keys, to further enhance security. But yes, It works, I use it everyday! 😉
Your reply did not address the issue I raised except to confirm it. You wrote “It first decodes the base64-encoded key”, yet my point was you *never* base64_encoded the key in your example, so as is, this can’t work.
$key = ‘qkwjdiw239&&jdafweihbrhnan&^%$ggdnawhd4njshjwuuO’;
This is *not* a base64_encoded key, as special characters are not part of the base64 encoding set.
Then you do this without ever running base64_encode:
$nameencrypted=encryptthis($firstName, $key);
I apologize for the confusion in my previous response. You are correct that the encryption key in the code you provided is not base64-encoded.
In order for the code to work, the key would need to be base64-encoded before passing it to the encryptthis() function. So, if you want to use the key as it is in the code you provided, you would need to remove the base64_decode() function calls in both the encryptthis() and decryptthis() functions, and make sure to base64-encode the key before passing it to the encryptthis() function, like this:
$key = 'qkwjdiw239&&jdafweihbrhnan&^%$ggdnawhd4njshjwuuO';
$base64_encoded_key = base64_encode($key);
$nameencrypted = encryptthis($firstName, $base64_encoded_key);
Then, in the decryptthis() function, you would need to base64-decode the key before using it to decrypt the data:
function decryptthis($data, $key) {$encryption_key = base64_decode($key);
list($encrypted_data, $iv) = array_pad(explode('::', base64_decode($data), 2),2,null);
return openssl_decrypt($encrypted_data, 'aes-256-cbc', $encryption_key, 0, $iv);
}
$base64_encoded_key = base64_encode('qkwjdiw239&&jdafweihbrhnan&^%$ggdnawhd4njshjwuuO');
$namedecrypted = decryptthis($nameencrypted, $base64_encoded_key);
I hope this helps clarify the issue.
Well thanks for replying. I think you misunderstand however. This was not code I had provided. This is YOUR code, from this web page. I was trying to help you out by letting you know there is a mistake in your code and you are going to mislead a lot of people with this post. Please base64_encode your key before running encryptthis.
OK Ill get it changed as soon as I can, thank you so much for your input. I have been programming all week on my Inventory App https://a1sold.com and have not been around much.
Weldone……thanks for the tutorial.
Please how can I encrypt an integer into the database in php
Well, I don’t think that you want to do that however if you wanted to, you would not use integer, you would just use text or varchar and then encrypt the number. The database structure for int only accepts numbers so there would be no way you can encrypt that using the integer.