How to mysql database encrypt and use in Coldfusion

Question

Our client sends CSV file (average 0.3 million records) that file. we import in our mysql database.

Is there a way while importing data from CSV file, I can encrypt (SHA1)?? How can I send queries(select,insert & update) to database? from coldfusion or mysql.

if we can't encrypt data while importing from CSV file. what is the solution? This new requirement came from client recently, after importing data from csv file. we have to dispose of csv file properly from our system.

Database schema already exist and current data that we have to encrypt and upcoming CSV data also.

Answer 1

DATABASE:

INSERT:

 INSERT INTO en_phoneBook_encrypted 
    (fname,Lname,cell) 
    Values
    (AES_ENCRYPT(fname,'ph2015'),
    AES_ENCRYPT(Lname,'ph2015'), 
    AES_ENCRYPT(cell,'ph2015')) 
    from en_phoneBook_encrypted

SELECT:

select CAST(AES_DECRYPT(fname,'ph2015')AS CHAR (50)) AS FirstName, 
CAST(AES_DECRYPT(Lname,'ph2015')AS CHAR (50)) AS LastName, 
CAST(AES_DECRYPT(cell,'ph2015')AS CHAR (50)) AS CellNumber 
from en_phoneBook_encrypted

COLDFUSION:

INSERT:

<cfquery>
INSERT INTO  en_phoneBook_encrypted
(fname,Lname,cell) 
Values
(
  AES_ENCRYPT('21234562','dbkey'),  
  AES_ENCRYPT('ABC','dbkey'),  
  AES_ENCRYPT('XYZ','dbkey') 
);
</cfquery>    

UPDATE:

<cfquery   result="upd" >
        UPDATE en_phoneBook_encrypted
            SET 
            fname   = AES_ENCRYPT('ABC','dbkey')
            ,Lname  = AES_ENCRYPT('XYZ','dbkey')
            ,cell   = AES_ENCRYPT('123','dbkey')    
            where  CAST(AES_DECRYPT(PKID,'dbkey')  AS CHAR(50)) = '2123456' 
</cfquery>
<cfdump var="#upd#">

Hope it will help someone :)

---