Abhishek Abhishek - 6 months ago 8
SQL Question

MySQL Query Parsing using java

I need to parse a query which a user enters, say in a text box, and then what I need, is that I want to encrypt all the values in query leaving the query keywords. To convert it into an equivalent query that can be performed on an encrypted database.

Such as,

select name from employee where salary = 10000


I need an equivalent query as,

select name_enc from employee_enc where salary_enc = 10000_enc


where
name_enc
,
employee_enc
,
salary_enc
and
10000_enc
are the encrypted values of
name
,
employee
,
salary
and
10000
. I need to do this in java and the the database I'm using is MySQL Server where the table
Employee
is already encrypted.

Please provide any necessary help. Thanks in advance.

Answer

If you don't need to do it manually use SQL's included encryption and encoding operations.

If you need to do it manually split your SQL query string by spaces and ignore SQL key words as you loop to encrypt. Remember to encode your cipher results with base 64 or hex to ensure data integrity.

private String encryptSQLQuery(String plainSQLQuery){
    StringBuilder cipherQuery = new StringBuilder();
    String plainQuery = plainSQLQuery;
    String[] splitQuery = plainQuery.split("\\s+");
    for(String queryWord : splitQuery){
       if(!isSQLKeyWord(queryWord))
           queryWord = cryptoObject.encryptAndEncode(queryWord);
       cipherQuery.append(queryWord);
       cipherQuery.append(" ");
    }
    return cipherQuery.toString();
}

Note that you will have to implement the isSQLKeyWord() and CryptoObject.encryptAndEncode() methods.

Comments