SQL Injection is one of the top 10 web application vulnerabilities. In simple words, SQL Injection means injecting/inserting SQL code in a query via user-inputted data. It can occur in any applications using relational databases like Oracle, MySQL, PostgreSQL and SQL Server.
To perform SQL Injection, a malicious user first tries to find a place in the application where he can embed SQL code along with data. It can be the login page of any web application or any other place. So when data embedded with SQL code is received by the application, SQL code will be executed along with the application query.
Suppose we have a database table named tbluser which stores data of application users. The userId is the primary column of the table. We have functionality in the application, which lets you get information via userId. The value of userId is received from the user request.
Let’s have a look at the below example code.
String userId = {get data from end user};
String sqlQuery = "select * from tbluser where userId = " + userId;
When the above query is executed with valid data i.e. userId value 132, it will look like below.
Input Data: 132
Executed Query: select * from tbluser where userId=132
Result: Query will return data of user having userId 132. No SQL Injection is happening in this case.
A hacker can alter user requests using tools like Postman, cURL, etc. to send SQL code as data and this way bypassing any UI side validations.
Input Data: 2 or 1=1
Executed Query: select * from tbluser where userId=2 or 1=1
Result: Now the above query is having two conditions with SQL OR expression.
Let’s look at the four types of SQL injections.
The above example is a case of Boolean Based SQL Injection. It uses a boolean expression that evaluates to true or false. It can be used to get additional information from the database. For example;
Input Data: 2 or 1=1
SQL Query: select first_name, last_name from tbl_employee where empId=2 or 1=1
SQL union operator combines data from two different queries with the same number of columns. In this case, the union operator is used to get data from other tables.
Input Data: 2 union select username, password from tbluser
Query: Select first_name, last_name from tbl_employee where empId=2 union select username, password from tbluser
By using Union Based SQL Injection, an attacker can obtain user credentials.
In Time Based SQL Injection, special functions are injected in the query which can pause execution for a specified amount of time. This attack slows down the database server. It can bring down your application by affecting the database server performance. For example, In MySQL:
Input Data: 2 + SLEEP(5)
Query: select emp_id, first_name, last_name from tbl_employee where empId=2 + SLEEP(5)
In the above example, query execution will pause for 5 seconds.
In this variation, the attacker tries to get information like an error code and a message from the database. The attacker injects SQL which are syntactically incorrect so database server will return error code and messages which can be used to get database and system information.
We will use a simple Java Web application to demonstrate SQL Injection. We have Login.html, which is a basic login page that takes username and password from the user and submit them to LoginServlet.
The LoginServlet gets username and password from request and validates them against database values. If authentication is successful then Servlet redirects the user to the home page otherwise it will return an error.
Login.html Code:
<!DOCTYPE html>
<html lang="en">
<head>
<title>Sql Injection Demo</title>
</head>
<body>
<form name="frmLogin" method="POST" action="https://localhost:8080/Web1/LoginServlet">
<table>
<tr>
<td>Username</td>
<td><input type="text" name="username"></td>
</tr>
<tr>
<td>Password</td>
<td><input type="password" name="password"></td>
</tr>
<tr>
<td colspan="2"><button type="submit">Login</button></td>
</tr>
</table>
</form>
</body>
</html>
LoginServlet.java Code:
package com.journaldev.examples;
import java.io.IOException;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;
@WebServlet("/LoginServlet")
public class LoginServlet extends HttpServlet {
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (Exception e) {}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
boolean success = false;
String username = request.getParameter("username");
String password = request.getParameter("password");
// Unsafe query which uses string concatenation
String query = "select * from tbluser where username='" + username + "' and password = '" + password + "'";
Connection conn = null;
Statement stmt = null;
try {
conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/user", "root", "root");
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
if (rs.next()) {
// Login Successful if match is found
success = true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
stmt.close();
conn.close();
} catch (Exception e) {}
}
if (success) {
response.sendRedirect("home.html");
} else {
response.sendRedirect("login.html?error=1");
}
}
}
Database Queries [MySQL]:
create database user;
create table tbluser(username varchar(32) primary key, password varchar(32));
insert into tbluser (username,password) values ('john','secret');
insert into tbluser (username,password) values ('mike','pass10');
Input username: john
Input username: secret
Query: select * from tbluser where username=‘john’ and password = ‘secret’
Result: Username and Password exists in the database so authentication is successful. The user will be redirected to the home page.
Input username: dummy
Input password: ’ or ‘1’='1
Query: select * from tbluser where username=‘dummy’ and password = ‘’ or ‘1’=‘1’
Result: Inputted Username and Password doesn’t exist in the database but authentication is successful. Why?
It is due to SQL Injection as we have entered ’ or ‘1’='1 as password. There are 3 conditions in the query.
Now combining all 3 conditions i.e false and false or true => Final result will be true.
In the above scenario, we have used the boolean expression to perform SQL Injection. There are some other ways to do SQL Injection. In the next section, we will see ways to prevent SQL injection in our Java application.
The simplest solution is to use PreparedStatement instead of Statement to execute the query.
Instead of concatenating username and password into the query, we provide them to query via PreparedStatement’s setter methods.
Now, the value of username and password received from the request is treated as only data so no SQL Injection will happen.
Let’s look at the modified servlet code.
String query = "select * from tbluser where username=? and password = ?";
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/user", "root", "root");
stmt = conn.prepareStatement(query);
stmt.setString(1, username);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
// Login Successful if match is found
success = true;
}
rs.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
stmt.close();
conn.close();
} catch (Exception e) {
}
}
Let’s understand what’s happening in this case.
Query: select * from tbluser where username = ? and password = ?
The question mark (?) in the above query is called a positional parameter. There are 2 positional parameters in the above query. We don’t concatenate username and password to query. We use methods available in the PreparedStatement to provide user Input.
We have set the first Parameter by using stmt.setString(1, username)
and the second parameter by using stmt.setString(2, password)
. The underlying JDBC API takes care of sanitizing the values to avoid SQL injection.
That’s all for Java SQL Injection, I hope nothing important got missed here.
You can download the sample java web application project from the below link.
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.
Sign up for Infrastructure as a Newsletter.
Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
But in my case with dummy and ‘ or ‘1’=’1 it is not giving me access.
- nik
Thank you for the clear information
- RGK Badithaboina
Clearly explained. Thank you very much :)
- Ranjit
Very clear and useful wroten. If I understood well, we don’t have to think about SQL injection when we use Hibernate or Spring Data JPA?
- Perkone
Thanks a lot for the sharing! It is comprehensive and very useful. ???
- Gürkan Şahin