How can I prevent sql injection with groovy?

Question

I have a sql like:

String sql = """SELECT id, name, sex, age, bron_year, address, phone, state, comment, is_hbp, is_dm, is_cva, is_copd, is_chd, is_cancer, is_floating, is_poor, is_disability, is_mental
 FROM statistics_stin WHERE 1=1
 ${p.team_num == 0 ? "" : "AND team_num = ${p.team_num}"}
 ${p.zone == 0 ? "" : "AND team_id = ${p.zone}"}
 ${p.is_hbp == 2 ? "" : "AND is_hbp = ${p.is_hbp}"}
 ${p.is_dm == 2 ? "" : "AND is_dm = ${p.is_dm}"}
 ${p.is_chd == 2 ? "" : "AND is_chd = ${p.is_chd}"}
 ${p.is_cva == 2 ? "" : "AND is_cva = ${p.is_cva}"}
 ${p.is_copd == 2 ? "" : "AND is_copd = ${p.is_copd}"}
 ${p.is_cancer == 2 ? "" : "AND is_cancer = ${p.is_cancer}"}
 ${p.is_floating == 2 ? "" : "AND is_floating = ${p.is_floating}"}
 ${p.is_poor == 2 ? "" : "AND is_poor = ${p.is_poor}"}
 ${p.is_disability == 2 ? "" : "AND is_disability = ${p.is_disability}"}
 ${p.is_mental == 2 ? "" : "AND is_mental = ${p.is_mental}"}
 ${p.is_aged == 2 ? "" : (p.is_aged == 1 ? " AND age >= 65" : " AND age < 65")}
 ${p.is_prep_aged == 2 ? "" : (p.is_prep_aged == 1 ? "AND (age BETWEEN 60 AND 64)" : "AND (age < 60 OR age > 64)")}
 ${p.is_young == 2 ? "" : (p.is_young == 1 ? " AND age < 60" : " AND age >= 60")}
 ORDER BY team_id ASC, id ASC
 LIMIT ${start}, ${page_size}
 """;

Then use :

def datasource = ctx.lookup("jdbc/mysql");
def executer = Sql.newInstance(datasource);
def rows = executer.rows(sql);

Here the p is a json object like:

p = {is_aged=2, is_cancer=2, is_chd=1, is_copd=2, is_cva=2, is_disability=2, is_dm=2, is_floating=2, is_hbp=1, is_mental=2, is_poor=2, pn=1, team_num=0, zone=0}

This way has sql injection. I know I can use params type like:

executer.rows('SELECT * from statistics_stin WHERE is_chd=:is_chd', [is_chd: 1]);

But this case has to many AND conditions, use or not will be decided by the json p.

How to do this please?

https://stackoverflow.com/questions/43782702/using-prepared-statement-in-groovy — metters, Nov 23 '18 at 12:02
@metters Thank you, but I do not think it can prevent sql injection. — xunitc, Nov 23 '18 at 12:17

Marmite Bomber · Answer 1 · 2018-11-25T09:46:22.290

You have a problem of dynamic SQL binding, i.e. the number of bind parameters is not constant, but depend on the input.

There is an elegant solution from Tom Kyte which has even more elegant implementation in Groovy

The basic idea is simple bind all variables, the variables that have an input value and should be used are processed normally, e.g

 col1 = :col1

the variables that have no input (and shall be ignored) are binded with a dummy construct:

 (1=1 or :col2 is NULL)

i.e. they are effectively ignored by shortcut evaluation.

Here two examples for three columns

 def p = ["col1" : 1, "col2" : 2, "col3" : 3]

This input leads to a full query

SELECT col1, col2, col3
 FROM tab WHERE
 col1 = :col1 AND
 col2 = :col2 AND
 col3 = :col3
ORDER by col1,col2,col3

For limited input

 p = [ "col3" : 3]

you get this query

SELECT col1, col2, col3
 FROM tab WHERE
 (1=1 or :col1 is NULL) AND
 (1=1 or :col2 is NULL) AND
 col3 = :col3
ORDER by col1,col2,col3

Here the Groovy creation of the SQL Statement

String sql = """SELECT col1, col2, col3
 FROM tab WHERE   
 ${(!p.col1) ? "(1=1 or :col1 is NULL)" : "col1 = :col1"} AND
 ${(!p.col2) ? "(1=1 or :col2 is NULL)" : "col2 = :col2"} AND
 ${(!p.col3) ? "(1=1 or :col3 is NULL)" : "col3 = :col3"}  
ORDER by col1,col2,col3
"""

You can even get rid of the ugly 1=1 predicate;)

Thank you. by this way, it can work, even sql is not clear. – xunitc Nov 25 '18 at 01:12 — xunitc, Nov 25 '18 at 01:12

score 1 · Answer 2 · answered Nov 23 '18 at 16:32

Another option is to build your bindings as you build the query then execute the appropriate implementation of rows

def query = new StringBuilder( "SELECT id, name, sex, age, bron_year, address, phone, state, comment, is_hbp, is_dm, is_cva, is_copd, is_chd, is_cancer, is_floating, is_poor, is_disability, is_mental FROM statistics_stin WHERE 1=1" )
def binds = []
if ( p.team_num == 0 ) {
    query.append( ' AND team_num = ? ' )
    binds << p.team_num
}
if ( p.zone == 0 ) {
    query.append( ' AND team_id = ? ' )
    binds << p.zone == 0
}
...
executer.rows(query.toString(), binds);

Nice alternative with building the bind array. – Marmite Bomber Nov 25 '18 at 09:46 — Marmite Bomber, Nov 25 '18 at 09:46

How can I prevent sql injection with groovy?

2 Answers2