MySQL Query Parsing using java

Question

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.

10000_enc this cannot be done because 10000_enc is not a number — mubeen
– mubeen, Commented Jun 3, 2016 at 13:16
SELECT name_enc FROM employee_enc WHERE salary_enc = 10000_enc... So, the column names are encrypted? — Rabbit Guy
– Rabbit Guy, Commented Jun 3, 2016 at 13:16
this link is exactly what you need dev.mysql.com/doc/refman/5.5/en/encryption-functions.html — Matheus Sant Anna de Oliveira
– Matheus Sant Anna de Oliveira, Commented Jun 3, 2016 at 13:32
@ mubeen for that, I'm thinking of an order prevention encryption ..which will work fine for all the integers' encryption. — Abhishek
– Abhishek, Commented Jun 3, 2016 at 15:16

btiernay · Accepted Answer · 2017-03-19 21:25:43Z

1

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().
   }
}

answered Mar 19, 2017 at 21:25

btiernay

8,1795 gold badges45 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

venture · Accepted Answer · 2016-06-03 15:56:51Z

0

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.

edited Jun 3, 2016 at 15:56

answered Jun 3, 2016 at 13:36

venture

7910 bronze badges

3 Comments

Abhishek Over a year ago

@ venture Thank you so much ..and this isSQLKeyWord() is the user defined function right?

venture Over a year ago

@Abhishek Yes. There are quite a few keywords so I'd use a hashset to keep track of all of them.

Abhishek Over a year ago

Thanks. I haven't used it though, but it looks like it'll work. Thank you so much again.

Collectives™ on Stack Overflow

MySQL Query Parsing using java

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related