Enterprise Java

SQL Injection in Java Application

In this post we will discuss what is an SQL Injection attack. and how its may affect any web application its use the back end database. Here i concentrate on java web application. Open Web Application Security Project(OWAP) listed that SQL Injection is the top vulnerability attack for web application. Hacker’s Inject the SQL code in web request to the web application and take the control of back end database, even that back end database is not directly connected to Internet. And we will see how to solve and prevent the SQL Injection in java Web Application.

For this purpose we need 1 tools. these tool are completely open source. SQL Map – SqlMap is an open source penetration testing tool that automates the process of detecting and exploiting SQL Injection. we can get it from here.

SQLInjection

SQL injection is the technique to extract the database information through web application.
Scenario:

We have one database server [MySQL] and web application server [Tomcat]. consider that database server is not connected to internet. but its connected with application server. Now we will see using web application how to extract the information using sql-injection method.

Before see the sql-injection, we create small web application. It contain single jsp page like this

<form action='userCheck'>

<input type='text' name='user' value=''/>

<input type='submit' value='Submit'/>

</form>

In userCheck Servlet receives the user input field and connect to databse server and fire the sql query based on user input and receive the ResultSet and iterate it print into the web page.
userCheck servlet

protected void processRequest(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
        response.setContentType('text/html;charset=UTF-8');
        PrintWriter out = response.getWriter();
        try {

            String user = request.getParameter('user');
            Connection conn = null;
            String url = 'jdbc:mysql://192.168.2.128:3306/';
            String dbName = 'anvayaV2';
            String driver = 'com.mysql.jdbc.Driver';
            String userName = 'root';
            String password = '';
            try {
                Class.forName(driver).newInstance();
                conn = DriverManager.getConnection(url + dbName, userName, password);

                Statement st = conn.createStatement();
                String query = 'SELECT * FROM  User where userId='' + user + ''';
                out.println('Query : ' + query);
                System.out.printf(query);
                ResultSet res = st.executeQuery(query);

                out.println('Results');
                while (res.next()) {
                    String s = res.getString('username');
                    out.println('\t\t' + s);
                }
                conn.close();

            } catch (Exception e) {
                e.printStackTrace();
            }
        } finally {
            out.close();
        }

When we execute the above code. In normal input execution look like follows

When we give the normal value like ‘ramki’ then click the submit button then output like this

Its perfectly correct in normal behaviour. What happens when I put some special character or some sql statement in input box like this

when we click the submit button then it show all rows in my table like this

It is a big security breach in my application. what happened… is one kind of sql injection.

Let’s see what happened.

When I enter normal value in input box my servlet receives and substitute in the sql query and execute it.

SELECT * FROM User where userId='ramki'

it’s correct and we got correct output.

What happens when I put sdfssd’ or ‘1’=’1

SELECT * FROM User where userId =’sdfssd’ or ‘1’=’1

its means

SELECT * FROM User where userId ='sdfssd' or '1'='1'

like this. So our query is altered. now new query have 2 condition. 2nd condition always true. 1st condition may be or may not be true. but these 2 condition are connected with or logic. So where clause always true for all rows. the result is they bring all rows from our tables.

This is called blind sql injection. If u want more details of sql injection the check here

Now we can enter the sql statement directly in input box

like

ramki’ UNION SELECT * FROM mysql.`user` u —

then

SELECT * FROM User where userId=’ramki’ UNION SELECT * FROM mysql.`user` u —

then its means

SELECT * FROM User where userId ='ramki' UNION SELECT * FROM mysql.`user` u --'

Here they wont use * because its not matched with first table. So they find how many columns then use Union with second table.the user particular column they want. As result the get mysql database user information its exposed through our web application.

sqlmap

It comes with a powerful detection engine, many niche features for the ultimate penetration tester and a broad range of switches lasting from database fingerprinting, over data fetching from the database

Install the sqlmap in ur system or use BackTrack Linux

Here I used backtrack linux, because it’s already pre installed lots of applications like sqlmap.

In backtrack, sqlmap is located in /pentest/web/scanner/sqlmap

sqlmap commands

retrieve all databases

./sqlmap.py -u http://localhost:8080/SQLInject/userCheck?user=ramki --dbs

retrieve all tables

./sqlmap.py -u http://localhost:8080/SQLInject/userCheck?user=ramki -D test --tables

retrieve all columns from particular table

./sqlmap.py -u http://localhost:8080/SQLInject/userCheck?user=ramki -D test -T User --columns

Dump all column valued from particular table

./sqlmap.py -u http://localhost:8080/SQLInject/userCheck?user=ramki -D test -T User --dump

Dump some column valued from particular table

./sqlmap.py -u http://localhost:8080/SQLInject/userCheck?user=ramki -D test -T User -C userId,password --dump

See the video for full demo (watch in HD):

http://www.youtube.com/watch?feature=player_embedded&v=C5PQ86nWMkM

How To Prevent SQL Injection

  • Before substitute into query, we need to do the validation. for remove ir escaped the special character like single quote, key words like select, Union…
  • Use Prepared Statement with placeholder
PreparedStatement  preparedStatement=conn.prepareStatement('SELECT * FROM  usercheck where username=?') ;
preparedStatement.setString(1, user);

that setXXX() method do all the validation and escaping the special charcter

Now if use same blind sql injection like

sdfssd’ or ‘1’=’1 then

SELECT * FROM User where userId='sdfssd\' or \'1\'=\'1'

Here all special character are escaped When we use JPA kind of ORM tools like Hibernate, EclipseLink, TopLink that time also may be sqlinjection is possible.

To prevent the SQL injection we need to use NamedQuery instead of normal Query. Because NamedQuery internally used PreparedStement but normal query used norma Stement in java.

Normal Query in JPA

String q='SELECT r FROM  User r where r.userId=''+user+''';
Query query=em.createQuery(q);
List users=query.getResultList();

So don’t use normal query, use Named query like this

Query query=em.createNamedQuery('User.findByUserId');
query.setParameter('userId', user);
List users=query.getResultList();

 
U can download the demo code from GitHub (or) Google code
 

Reference: Beware of SQLInjection in Java Application from our JCG partner Rama Krishnan at the Ramki Java Blog blog.

Ramki

Ramki is a Application Developer working in the C-DAC, Pune. He has Extensive Design and Development experience in Java, Java Server Faces, Servlets, Java Persistent API (Hibernate), CDI, EJB and experience in applying Design Patterns of JavaEE Architecture.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

8 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Javi
11 years ago

Just, use parameters in queries.

Nation84
11 years ago

Y U NO USE HIBERNATE WITH CRITERIA?

Rafael Camargo
10 years ago

this post is long overdue. this is no longer a problem over 15 years

James
James
9 years ago
Reply to  Rafael Camargo

As a newby who is trying to come up to speed in stopping injection attacks, I highly disagree. For people like me, this information is highly relevant and important.

Rehman
Rehman
9 years ago

Agreed with James,
my question to author , did you apply all these sql injection techniques while running in browser or in eclipse. Because when I am doing these same things(in eclipse browser) after click on submit button blank page appeared.

Please confirm.

Ramakrishna
Ramakrishna
7 years ago

I would like to propose a Java custom Annotation as a solution for this: You can take a look at this tiny library : https://github.com/rkpunjal/sql-injection-safe/

This verifies if datastrings are sql-injection-safe. This provides an annotation. The usage looks something like this.

private @SQLInjectionSafe String id;

and a utility Method whose usage is like this:

SqlSafeUtil.isSqlInjectionSafe(“my-string-value”)

You could also refer to this article to know more about it:

https://dzone.com/articles/custom-annotation-in-java-for-sql-injection-safe-p

Ognjen
Ognjen
5 years ago
Reply to  Ramakrishna

Hi Ramakrishna, is there a possibility to use your annotation in Spark java micro framework for example and not in Spring boot ?

hemanth
hemanth
5 years ago

Hey! TOMCAT is not an application server. It is a web server.

Back to top button