CODEWITHSUNDEEP
javamysql
Abhishek
Abhishek

Reputation: 76

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.

Upvotes: 0

Views: 1334

Answers (2)

btiernay
btiernay

Reputation: 8139

You may want to consider using code from Alibaba's Druid project. Although designed as a sophisticated connection pooling library, this project supports a very advanced parser and AST for ANSI SQL and non-ANSI dialects such as MySQL, Oracle, SQL Server, etc. The project is open source and bears the very liberal Apache License Version 2.0.

The main entry points into this part of the library is SQLUtils.java. You can use values returned from SQLUtils.parseStatements to access a typed model of the statements:

List<SQLStatement> statements = SQLUtils.parseStatements(sql, JdbcConstants.MYSQL);
for (SQLStatement statement : statements) {
   if (statement instanceof SQLSelectStatement) {
      SQLSelectStatement createTable = (SQLSelectStatement) statement;
      // Use methods like: createTable.getSelect().getQuery().
   }
}

Upvotes: 1

venture
venture

Reputation: 79

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.

Upvotes: 0

Related Questions