Upload and save image file in mysql database

Mysql database has data type blob where we can store image data. Create a table which will keep mime type and image content. Table structure should be like below:


create table img(id int primary key auto_increment,
image blob,
ext varchar(255));

Create a PHP script with form having property method “post” and enctype “multipart/form-data”. Add file container tag in the form with submit button. See below example:

<form method=”post” enctype=”multipart/form-data”><input type=”file” name=”photo”>
<input type=”submit” name=”submit” value=”submit”>
</form>

PHP CODE IS:

/*
Save Images in the database
Please create below table inorder to execute this code;

********** IMG TABLE ********************
create table img(
id int primary key auto_increment,
image blob,
ext varchar(255));
********** IMG TABLE ********************
*/
$db = mysql_connect("localhost", "root", "");
mysql_select_db("test", $db);
if (isset($_POST['submit'])) {
    if (isset($_FILES['photo']))
        {
             $imginfo = getimagesize($_FILES['photo']['tmp_name']);
               $mime = $imginfo['mime'];
             $data = file_get_contents($_FILES['photo']['tmp_name']);            
             $data = mysql_real_escape_string($data);
             // Preparing data to be used in MySQL query
             mysql_query("INSERT INTO img
                                set ext='$mime', image = '$data'") or die(mysql_error());
        }
}
$sql = "select * from img";
$res = mysql_query($sql);
if ($res) {
    while($row = mysql_fetch_assoc($res)) {
        $image = $row['image'];
        echo '<img src= "data:'.$row['ext'].';base64,'.base64_encode($image).'" />';

    }
}

Encrypt MySQL data using AES techniques

Sometimes clients want that the information they collected from the user should be encrypted and stored in database. Data encryption and decryption is a common technique for secured data. In this article I’ll show how could you use mysql’s built in function to encrypt and decrypt data.
Suppose you’ve a table where you want to encrypt user’s name & address. So look below the structure of the table. In this table we will store name and address as encrypted.

CREATE  TABLE `user` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`first_name` VARBINARY(100) NULL ,
`address` VARBINARY(200) NOT NULL ,
PRIMARY KEY (`id`)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

To encrypt & decrypt mysql data we will use AES_ENCRYPT() and AES_DECRYPT() functions. These functions used the official AES (Advanced Encryption Standard) algorithm & encode data with a 128-bit key length. 128 bits is much faster and secure enough for most purposes.

Why we used VARBINARY data type instead of VARCHAR:

Because AES_ENCRYPT() encrypts a string and returns a binary string. AES_DECRYPT() decrypts the encrypted string and returns the original string.
AES is a block-level algorithm. So when data encrypted it is padded. So you can calculate the length of the result string using this formula:

1	16 × (trunc(string_length / 16) + 1)

So if your address field structure is = VARCHAR(100) ; //100 length of varchar
Then before your encryption it should be converted
= 16 * (trunc(100/ 16) + 1)
= 16 * (6.25 + 1)
= 16 * 7.25
= 116
So VARCHAR(100) should be converted to  VARBINARY(116) minimum. I suggest use little more like VARBINARY(150) in this case.
Because if AES_DECRYPT() detects invalid data or incorrect padding, it will return NULL. But it is also possible for AES_DECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key is invalid.
Syntax:

1. AES_ENCRYPT(str, key_str);
2. AES_DECRYPT(crypt_str,key_str);

Please remember, the encryption and decryption will occur based on a key. So you’ve to keep that key in a secret place and using variable you could pass the key to mysql to encrypt and decrypt data.
Now look how I insert data using AES_ENCRYPT, where I used key ‘sachin′ :

1	INSERT into user (first_name, address) VALUES (AES_ENCRYPT('Obama', 'usa2010'),AES_ENCRYPT('Obama', 'sachin'));

Now look how I decrypt data using AES_DECRYPT:

1	SELECT AES_DECRYPT(first_name, 'usa2010'), AES_DECRYPT(address, 'sachin') from user;

AES_ENCRYPT() and AES_DECRYPT() can be considered the most cryptographically secure encryption functions currently available in MySQL.