Skip to main content

Beware of SQLInjection in Java Application


Hi In this post we will discuss what is SQL Injection attack. and how its may affect ur any  web application its use the backend 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 they Inject the SQL code in web request to the web application and take the control of backend database, even that backend 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
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 happen 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

Its big security breach in my application. what happen... Its one kind of sql injection

lets see what happen
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 '
its correct and we got correct output.

what happen 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
http://www.unixwiz.net/techtips/sql-injection.html
http://www.imperva.com/resources/glossary/sql_injection.html
http://www.applicure.com/blog/owasp-top-10-2010

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 its 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)




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

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.
Preventing 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


so dont use normal query, use Named query like this



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


Comments are welcomed

Popular posts from this blog

Understanding Virtual Host Concept in Tomcat

Hi in this post we will see how to setup virtual host in Apache Tomcat server. Virtual Host is in-built feature that allows to deploy multiple website(domains) in single instance of tomcat server. The main benefit in this way is its cost effective. Scenario: I am going to deploy 3 website with following domain names in single tomcat http://www.ramki.com http://www.krishnan.com http://www.blog.ramki.com The following diagram is my outline. Outline structure of Virtual Host Concept in Tomcat Here my tomcat IP address 192.168.1.15. or any IP address allocated my ISP. but it should be public IP address. How all domain names are pointing to my Tomcat?                   When we purchase the domain name we need to update the our tomcat IP address to it. like or we can simulate same DNS Setup through hosts file in both Linux and Windows. In Linux tha file is located at /etc/hosts Now How Setup...

Docker : Tomcat Clustering with Load Balancer (Tomcat and Nginx)

In this post i will show Tomcat Clustering in Docker Container. In  my previous post i discussed how to achieve tomcat clustering with Nginx Front end . Its almost same scenario, but this time we will achieve via docker container. Docker Docker  is an  open-source  project that automates the deployment of  applications  inside  software containers , by providing an additional layer of abstraction and automation of  operating-system-level virtualization  on  Linux . [4]  Docker uses resource isolation features of the Linux kernel  such as  cgroups  and kernel  namespaces  to allow independent "containers" to run within a single Linux instance, avoiding the overhead of starting and maintaining  virtual machine   --Wikipedia

Virtual Host + Apache httpd server + Tomcat + mod_jk connector

In my last post ( Virtual Host in Tomcat ) we discussed about how setup the virtual host in Tomcat. Its cost effective technique because only one public IP is enough to host multiple domain. If we have big organization and each department want to host their website in locally in different machine. then how to achieve the virtual host concept?. In this post we will see the how we do this. Update :   I posted  Virtual Host + Nginx + Tomcat  Its easy to configure, compare to Apache httpd server Problem Scenario:         In big organization they have multiple department, each department want to host their website in different machine. so these websites are accessed locally with different local IP address. When we mapping to public address then we face the problem. We have two choice either purchase as many public address or Put one server front  and delegate these request. ...