Replace Parameters in SQL query text with XXXXX

I m writing a small utility that captures and logs SQL statements, but will have to remove sensitive data from the Query text and replace with with some dummy text (i.e:XXXXX).

What is a good way to parse the SQL query in java and replace parameters value?

for example:

replace

SELECT NAME, ADDRESS, .... FROM USER WHERE SSN IN ('11111111111111', '22222222222222');

with

SELECT NAME, ADDRESS, .... FROM USER WHERE SSN IN (?, ?);

Answer

Using JSQLParser (V0.8.9) this is a solution for your problem:

String sql ="SELECT NAME, ADDRESS, COL1 FROM USER WHERE SSN IN ('11111111111111', '22222222222222');";
Select select = (Select) CCJSqlParserUtil.parse(sql);

//Start of value modification
StringBuilder buffer = new StringBuilder();
ExpressionDeParser expressionDeParser = new ExpressionDeParser() {

    @Override
    public void visit(StringValue stringValue) {
        this.getBuffer().append("XXXX");
    }

};
SelectDeParser deparser = new SelectDeParser(expressionDeParser,buffer );
expressionDeParser.setSelectVisitor(deparser);
expressionDeParser.setBuffer(buffer);
select.getSelectBody().accept(deparser);
//End of value modification 

System.out.println(buffer.toString());
//Result is: SELECT NAME, ADDRESS, COL1 FROM USER WHERE SSN IN (XXXX, XXXX)

This replaces all found String values within your SQL. To replace other types of data e.g. Long values, override the corresponding visit method in ExpressionDeParser.

Don’t use regexp in this case. It turns out quickly to be hard maintainable.