-2

I have a java project where PreparedStatements are not being used in any of the SQL query. Now I want to implement PreparedStatements for all those queries. Instead of modifying each query by putting '?' , I want to write a function which takes SQL query string which parses it and returns the map of values of various SQL clauses.

public class SQLParser {

    public static void main(String args[]) {

    String sqlString = "select * from table t where columnA = 'hello' and columnB = 'hai'";
    Map<Integer,String> values = new HashMap<>();
    values = parseQuery(sqlString);
    System.out.println(values);   // prints  { {1,'hello'} , {2,'hai'} }
    }

    private static Map<Integer,String> parseQuery(String sqlString) {
        Map<Integer,String> values = new HashMap<>();
        //
        //  ????  I want this function 
        //
        return values;
        
    }

}

Few examples are

sqlString : select * from table t where columnA = 'hello' AND columnB = 'hai'
output : { {1,'hello'} , {2,'hai'} }

sqlString : select * from table t where columnA IN ('hello' ,'hai')
output : { {1,'hello'} , {2,'hai'} }

sqlString : select * from table t where columnA > 17 AND columnB BETWEEN 10 AND 20;
output : { {1,'17'} , {2, '10' } , {3, '20'} }

Basically It should support all possible clauses and their combinations.

shashank
  • 65
  • 6
  • If the keys of your map are incremental numbers, wouldn't a list be enough? – Federico klez Culloca Mar 30 '21 at 10:30
  • 1
    Anyway, you're basically asking how to write a parser for SQL (which SQL dialect, btw?), which seems no small feat if you ask me. I'm not sure it can fit a StackOverflow answer. – Federico klez Culloca Mar 30 '21 at 10:32
  • 1
    You're going to have to change the code for every query anyway, whether to use your magic statement parser/preparer or to use a `PreparedStatement` directly. Could you actually develop that magic parser/preparer and then modify every query to use it, in less time than it would take to just rewrite every query as a `PreparedStatement`? How many queries are we talking about here? – Kevin Anderson Mar 30 '21 at 11:02
  • I think you miss something here. The point to use PreparedStatement is to avoid SQL injection. But if you first create your SQL as a string from your parameters, the injection can take place at this point and your parser will happily parse the malicious SQL statement. – vanje Mar 30 '21 at 17:25

1 Answers1

1

Proper way to implement it will be creating SQL parser. It is huge task. If you decide to go this way you can look at my parser(Scala), it is not even close to being finished though.

Alternative approach is to create regexp to capture all literal. You only need scanner for that, which is easier.

talex
  • 17,973
  • 3
  • 29
  • 66