Encrypt MySQL data using AES techniques

the data will be encrypted and stored in the database for providing an additional level of security for the data. In MySQL AES(Advanced Encryption Standard) algorithm can be used to encrypt and decrypt the data being inserted and retrieved.

•  AES_ENCRYPT(data,encryption_key) - the method that can be used to encrypt the data being inserted. 

        e.g:- AES_ENCRYPT('chathuranga','abc123');

• AES_DECRYPT(encrypted_data,encryptyed_key) 

suppose you need to encrypt and store the username and email of the every user in the database. these encrypted values are stored in the database as binary strings. therefore you must give suitable data types for the columns in the table to accept and hold binary string inputs. therefore we must use varbinary instead of varchar. create the following table in the database.

create table user_table(

user_id int auto_increment primary key,

username varbinary(100),

email varbinary(100));

then insert following data into the table created. you can see that  am using AES_ENCRYPT function with cha123 as the key.

insert into user_table values('',AES_ENCRYPT('chathuranga','cha123'),AES_ENCRYPT('chathuranga.t@gmail.com','cha123'));



insert into user_table values('',AES_ENCRYPT('darshana','cha123'),AES_ENCRYPT('chathurangat@lankacom.net','cha123'));

you can see that the data has been stored in a encrypted format. see below screen dump.

 the result of the select query is displayed in the encrypted format. if you need to see the original values (decrypted values) you have to use the AES_DECRYPT function to decrypt the values stored.  the command is as follows.

select AES_DECRYPT(username,'cha123') As username_origianal , AES_DECRYPT(email,'cha123') As email_origianal from user_table;

refer the below screen shot.

 finally try out  the following select statement to understand how to use where clause with an encrypted columns.

select AES_DECRYPT(username,'cha123') As username_origianal , 

AES_DECRYPT(email,'cha123') As email_origianal 

from user_table 

where username = AES_ENCRYPT('chathuranga','cha123');

Hope this will helpful for you!!!

Thanks and Regards,
Chathuranga Tennakoon
chathuranga.t@gmail.com

Adapter Pattern

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;


interface TargetNumberSorter{
   
   
    public int[] sortIntegerList(int[] arr);
   
   
}



class AdapterNumberSorter implements TargetNumberSorter{
   
   
    public int[] sortIntegerList(int[] arr){
       
       
        //convert array to List<Integer>
        List<Integer> arrList =  new ArrayList<Integer>();
        for(int i=0;i<arr.length;i++){
           
            arrList.add(arr[i]);
        }
 
        //sort List<Integer> with Adaptee
       
        AdapteeNumberSorter ans = new AdapteeNumberSorter();
        List<Integer> sortedList = ans.sortIntegerList(arrList);
       
        int sortedArr[] =new int[sortedList.size()];
       
        //convert List<Integer>
        for(int index=0; index<sortedList.size();index++){
           
            sortedArr[index] = sortedList.get(index);
        }
       
         return sortedArr;
       
    }
   
   
}






class AdapteeNumberSorter{
   
    public List<Integer> sortIntegerList(List<Integer> inputList){
       
       
        Collections.sort(inputList);
       
        return inputList;
    }
   
}





class ApplicationClient{
   
   
    public static void main(String[]args){
       
        int arr[]={45,10,1,46,98,22,30,8};
       
        System.out.println("array before sorting Data ");
       
        for(int i=0;i<arr.length;i++){
           
            System.out.println("index ["+i+"] = "+arr[i]);
        }
       
        System.out.println("array after sorting Data ");
       
       
        TargetNumberSorter tns =  new AdapterNumberSorter();
       
        int sortredArr[] = tns.sortIntegerList(arr);
       
       
        for(int i=0;i<sortredArr.length;i++){
           
            System.out.println("index ["+i+"] = "+sortredArr[i]);
        }
       
       
    }
}

JasperReports with PHP

Today i am going to write this article on the use of JasperReports in PHP as a reporting tool. Here i have used IReport-4.5.0 IDE for designing the JasperReport Template (known as jrxml file).In addition, you require a PHP library(third party) that generates PDF reports from the given jrxml document. this library can be downloaded from the following Git Repository

https://github.com/chathurangat/PhpJasperLibrary

or else you can downloaded through following link

Download Php Jasper Library

In order to design the template, please follow the steps given below.

1. load the IReport Designer tool
(executable file is available inside the bin directory)


2.after loading the IReport Tool,select new report template to start the report designing.
(File -> New)
then you will get a new window with a list of available report templates. select a template out of the available templates. in my case i have selected the Blank A4 template. please refer below.

then click the Launch Report Wizard to load the selected report template.



3. then give a Report Name and browse a Location where the report file (jrxml file) should be saved.  Please refer below.

4. then click on Next to proceed with next phase. please refer below.

 As you can see in the Screen-shot, you will be displayed a list of available data source connections that can be used in your report. if you wish to create a new data source connection, you are free to do so by clicking New button.


 5. if you click the New button to create new data source, you will be shown the below screen to select the the data source type. ( the data source type should be selected based on the application requirements. ) since this is PHP based web application, i have selected Database JDBC Connection as the data source type.

6. once you select the data source, click on next to proceed with next step. next will be the below screen.

here you have to provide the JDBC connection details.

 Name : just give any name for this connection for later reference  identification purpose

JDBC Driver:  select the most suitable JDBC driver from the given list based on your database. since i am using MySQL server database, i have selected  MySQL-JDBC Driver.

JDBC URL: make sure to edit the jdbc url based on your sever and database name. the default value of the jdbc url does not change, based on the value you provided for server and database. therefore please edit it manually.

Server Address : IP Address of the database sever. if the database is in your local machine, use localhost as IP.

Database : Database name that you are going to connect.

username: Username of the database server.

password : Password of the database server.

after filling all the configuration details, you can test the database conncetion by clicking the Test button provided. sometimes it may ask you to re-enter the database password. if the connection is successful, you will get a successful message as follows. then you can Save your new data source connection.

 7. then you can use your newly created data source for designing the query for your report. select the newly created data source and press Design Query button. then you will get the below window for designing the Query.

 

you will see a list of tables available in the connected database. you can Drag and Drop these tables to the provided area for designing the Query. then customize the columns displayed in the report by using the check boxes provided for each column.

8. once the Query is designed, press OK to finish it. the click Next to proceed with next phase. then next step is to select the required fields for your report out of the available all resulted columns of the Query you designed. add and remove the required database columns for your report with the provided button. please refer the below screen shot.

 9. after completing the above operation, you can press Next button for continue with next phase. next phase is for applying the Group By clause for the report view. then phase is optional and you can skip this step by clicking just Next button. then the initial process of the report design is finished and you will be notified with the below screen. jut click on finish button.

10. then it will load and display the created .jrxml file. you are required to remember that this is the file that contains your report template. you can design the report as you wish by providing preferable title, footer and other required fileds.


11. the designing utilities are available in the Report Inspector window. please refer below screen shot.

as you can see that the selected database column fields are available under the Fields. you can drag and drop the database fields in into the Detail1 Section of your Report.Once you drag a database field, its column header section will be automatically visible under Column Header section. In addition, the data field is visible under the Detail 1 Section. you can edit the column name as you wish.

12. Once the Design is done, you can preview the design using the Preview button. 


13. The Report design elements are available in the palette window. you can get the palette window Window -> Palette

14. Once all the design is done, the it is time to integrate with your PHP application. make sure to download and import the PhpJasperLibrary in your PHP script. (copy both .jasper and .jrxml files into a same directory and give the reference in the PHP script)

report_view.php

<?php

//Import the PhpJasperLibrary
include_once('PhpJasperLibrary/tcpdf/tcpdf.php');
include_once("PhpJasperLibrary/PHPJasperXML.inc.php");


//database connection details

$server="192.168.0.11";
$db="lcs_ims";
$user="web";
$pass="abc123@#";
$version="0.8b";
$pgport=5432;
$pchartfolder="./class/pchart2";


//display errors should be off in the php.ini file
ini_set('display_errors', 0);

//setting the path to the created jrxml file
$xml =  simplexml_load_file("report/chathuReport.jrxml");

$PHPJasperXML = new PHPJasperXML();
//$PHPJasperXML->debugsql=true;
//$PHPJasperXML->arrayParameter=array("parameter1"=>1);
$PHPJasperXML->xml_dismantle($xml);

$PHPJasperXML->transferDBtoArray($server,$user,$pass,$db);
$PHPJasperXML->outpage("I");    //page output method I:standard output  D:Download file


?>

access the report_view.php file in your LAMP/WAMP server. yo will get the report in PDF format.

Hope this will helpful for you!

Thanks and Regards,
Chathuranga Tennakoon
chathuranga.t@gmail.com

Cron Job example with PHP and Linux

today i am going to show you a practical example about cron job in linux environment. i extremely believe that you know what is a cron job and what is the purpose of using a crone job. if you dont know just google and get a proper understanding before proceeding with this article.

here i have assumed that you have successfully installed LAMP server in your PC and php projects are running without having any issue. today i am going to show you how to run Cron job in linux that executes the executes the php script in every 1 minute. the php script will insert a record to the given database table at each execution.

1. first set up the database and the table as follows.

/* create the database */
create database cron_job_example;

/* use the database */
use cron_job_example;

/* create the table */
create table cron_job_data(
id int,
name varchar(100),
date_time varchar(100));

2. set up the Php Script in the www directory of your LAMP server as follows.

in my case, it is located at /var/www and create a directory called sample_site

then copy the following Php file to the sample_site directory.

sample.php

<?php

//database connection details

$db_server="localhost";
$db_username="root";
$db_password="abc123@#";
$db_name="cron_job_example";

$con = mysql_connect($db_server,$db_username,$db_password);

if (!$con)
{
    die('Could not connect: ' . mysql_error());
}

mysql_select_db($db_name, $con);

$date = date("Y-m-d");

mysql_query("INSERT INTO cron_job_data (id, name,date_time)
VALUES (1, 'chathuranga','".$date."')");

?>

3. then use Linux terminal to create the cron job file to achieve the target.

you can use any of preferred text editor (vi, gedit etc...)  to create cron file. use following syntax. i have used gedit text editor for this example.

gedit  name_for_file.cron  (it is importnat that you must use .cron extension with the file name)

in my case,

gedit chathuranga.cron


then add the following entry in the newly opened file.

*/1 * * * *  wget http://localhost/cron_job/cron_job_file.php


general syntax of the above entry
<time_specified>  <command>

<time_specified> - */1 * * * *
<command> - wget http://localhost/cron_job/cron_job_file.php

 more description as follows ......

* * * * * command to be executed
- - - - -
| | | | |
| | | | +----- day of week (0 - 6) (Sunday=0)
| | | +------- month (1 - 12)
| | +--------- day of month (1 - 31)
| +----------- hour (0 - 23)
+------------- min (0 - 59) 


then save the file and use following command in the Linux terminal to execute the cron job you created. (it is good if you can give the full permission(777) for your cron job file before doing following operation)

crontab chathuranga.cron


now your cron job will execute from hereafter.

ps: just google to find more on cronetab commands ;)

hope this will helpful for you!

cheers!!!
Chathuranga Tennakoon
chathuranga.t@gmail.com
chathurangat.blogspot.com