How to avoid sql injection in JasperReports without using prepareStatement

Question

i have a program that print reports using JasperReports 5.5.0. The report works perfectly but when I'm thinking about sql injection in my program i can fix it using prepareStatement. My problem is when I use JRDesignQuery to write query because it creates sql injection.

I'm stuck with code jasper report that looks like this

try{
        JasperDesign jasperDesign = JRXmlLoader.load(PathSystem.getPath("rpt","report.jrxml"));
        String sql = "SELECT * FROM Users WHERE UserId = '" + txtUser.getText() + "'";
        JRDesignQuery newQuery = new JRDesignQuery();
        newQuery.setText(sql);
        jasperDesign.setQuery(newQuery);
        JasperReport jasperReport = JasperCompileManager.compileReport(jasperDesign);
        JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport,null, conn);
        JasperViewer.viewReport(jasperPrint,false);
    } catch (Exception e) {
        e.printStackTrace();
        JOptionPane.showMessageDialog(null, e);
    }

I can use sql injection with using input "' or 1='1" without the quotes.The sql looks like this :

SELECT * FROM Users WHERE UserId = '' or 1='1'

The report can print all UserId.

I have done research to solve it and found the way to avoid sql injection with using prepareStatement.

This is the code with using prepareStatement :

try {
        JasperDesign jasperDesign = JRXmlLoader.load(PathSystem.getPath("rpt","report.jrxml"));
        pst = conn.prepareStatement("SELECT * FROM Users WHERE UserId = ?");
        pst.setString(1, txtUser.getText());
        JRDesignQuery newQuery = new JRDesignQuery();
        newQuery.setText(pst.toString().split("\\:")[1]);
        jasperDesign.setQuery(newQuery);
        JasperReport jasperReport = JasperCompileManager.compileReport(jasperDesign);
        JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport,null, conn);
        JasperViewer.viewReport(jasperPrint,false);
    } catch (Exception e) {
        e.printStackTrace();
        JOptionPane.showMessageDialog(null, e);
    }

I wonder if there are any better way to avoid sql injection without using prepareStatement instead.

This post can help you: [JasperReports: Passing parameters to query](http://stackoverflow.com/q/11871042/876298) — Alex K, Jul 21 '14 at 06:30

Nir Alfasi · Accepted Answer · 2014-07-21T05:48:20.073

1

I don't understand your reasons to avoid using prepareStatement, it is the best solution!

That said, if you want to avoid sql-injection (without using prepareStatement) you should run validations and clean txtUser.getText().

For example:

"clean" the input from quotes by using string-replace - you can replace it with double-quotes or simply escape it!
if you know that the input should contain an integer, trim() it and validate that it's a number (you can do it in different ways: Integer.valueOf(), using regex, etc).

edited Jul 21 '14 at 05:48

answered Jul 21 '14 at 05:36

Nir Alfasi

53,191
11
86
129

Because in my code i don't use pst.execute() to execute the query and Jasper Report just needs the statement. – Ungapps Jul 21 '14 at 05:49
1

@StevenGunanto got it. The following is a good read: https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet – Nir Alfasi Jul 21 '14 at 05:52

Satyendra · Answer 2 · 2014-07-31T11:21:59.833

I understand! for any existing-application (that is not using PreparedStatement) it won't be easy to go and change the code at every location.

So, the best solution for you would be to use:

ESAPI : OWASP Enterprise Security API.

It will implement a layer of security before your application, thus, no code change needed.
You need to create a Filter, eg.
public class MySecurityFilter extends ESAPIWebApplicationFirewallFilter
and just mention it in your application's web.xml.
viz.
<filter>
<filter-name>WebSecurityFilter</filter-name>
<filter-class>com.your.filter.class.path.MySecurityFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>WebSecurityFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>

Also, you will get a full control on the filter by creating a policy file that will define <blacklist> and <whitelist> of the request parameters/headers.

Yes,you got it i need to change the code at every location to use prepareStatement.What if use desktop application ? my program is totally desktop. — Ungapps, Jul 21 '14 at 07:12

How to avoid sql injection in JasperReports without using prepareStatement

2 Answers2