Jasper Reports Dynamic Query With Parameter

Question

Below is a SQL statement that I have running in my Jasper Report. I have figured out how to do a dynamic query through the report parameters. My fromDate and toDate parameters work fine. The problem I am having is with adding additional SQL to the WhereClause and having additional parameters evaluated within that SQL.

The value of $P!{userName_sql} is

" and u.u_username = $P{userName} "

I end up with a SQL error because the userName parameter is not being evaluated when it is appended into the SQL. I can get around the problem by concatenating like this:

 " and u.u_username = '" + $P{userName} + "'"

But the parameter can then be SQL injected. Is there a way to get that parameter to evaluate without having to concatenate it?

SQL

SELECT tw.time_dt, ca.claimaudit_no,concat(u.u_firstnm, ' ', u.u_lastnm) user, ca.ca_claimtype, tw.time_workflowqueue, sum(tw.time_minutes / 60) hrs,
                    (
                                    select count(*) cnt from hcfasline hsline
                                    where hsline.hcfa_no = phases.hcfa_no
                                    union
                                    select count(*) cnt from ubdtlsline dtlsline
                                    inner join ubsline usline on usline.ubsline_no = dtlsline.ubsline_no
                                    where usline.ub_no = phases.ub_no
                                    order by cnt desc
                                    limit 1
                    ) linecnt
     FROM timeworksheet tw
    inner join users u on tw.users_no = u.users_no
    inner join claimaudit ca on tw.claimaudit_no = ca.claimaudit_no
    inner join claimauditphases phases on tw.claimauditphase_no = phases.claimauditphase_no
    where tw.time_dt between $P{fromDate} and $P{toDate}$P!{userName_sql}
    group by claimaudit_no, user, ca.ca_claimtype, tw.time_workflowqueue, tw.time_dt
    order by user, ca.ca_claimtype

Sharad Sharad · Accepted Answer · 2014-09-20T06:14:35

I don't understand why you are passing query to the parameter to filter the records by name, simply you can create a parameter (userName) to pass the value of user. For Example :-

   Where tw.time_dt between $P{fromDate} and $P{toDate} 
        and (u.u_username = $P{userName} or $P{userName} is null)

or $P{userName} is null condition to make parameter optional.

But still if you want to use the query in parameter then add one more parameter to pass user name in the $P!{userName_sql} parameter.

Jasper Reports Dynamic Query With Parameter

1 Answers