Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
Overview
In a Relational Database Management System (RDBMS), there is a specific language—called SQL (Structured Query language)—which is used to communicate with the database. The query statements written in SQL are used to manipulate the content and structure of the database. A specific SQL statement that creates and modifies the structure of the database is called a DDL (Data Definition Language) statement and the statements that manipulate the content of the database is called a DML (Data Manipulation Language) statement. The engine associated with the RDBMS package parses and interprets the SQL statement and returns the result accordingly. This is the typical process of communication with RDBMS—fire an SQL statement and get back the result, that’s all. The system does not judge the intention of any statement that adheres to the syntax and semantic structure of the language. This also means that there are no authentication or validation processes to check on who fired the statement and the privilege one has on getting the output. An attacker can simply fire an SQL statement with malicious intent and get back information that it is not supposed to get. For example, an attacker can execute a SQL statement with a malicious payload with the harmless looking query to control a Web application’s database server.
How It Works
An attacker can leverage this vulnerability and use it to one’s own advantage. For example, one can bypass an application’s authentication and authorization mechanism and retrieve so-called secure contents from the entire database. An SQL injection can be used to create, update, and delete records from the database. One can, therefore, formulate a query limited to one’s own imagination with SQL.
Typically, an application frequently fires SQL queries to the database for numerous purposes, be it for fetching certain records, creating reports, authenticating user, CRUD transactions, and so forth. The attacker simply needs to find an SQL input query within some application input form. The query prepared by the form then can be used to twine the malicious content so that, when the application fires the query, it carries the injected payload as well.
One of the ideal situations is when an application asks the user for input such as username or user id. The application opened up a vulnerable spot there. The SQL statement can be run unknowingly. An attacker takes advantage by injecting a payload that to be used as a part of the SQL query and processed by the database. For example, the server-side pseudo code for a POST operation for a login form may be:
uname = getRequestString("username");
pass = getRequestString("passwd");
stmtSQL = "SELECT * FROM users WHERE
user_name = '" + uname + "' AND passwd = '" + pass + "'";
database.execute(stmtSQL);
The preceding code is vulnerable to SQL injection attack because the input given to the SQL statement through the variable ‘uname’ and ‘pass’ can be manipulated in a manner that would alter the semantics of the statement.
For example, we can modify the query to run against the database server, as in MySQL.
stmtSQL = "SELECT * FROM users WHERE user_name = '" + uname + "' AND passwd = '" + pass + "' OR 1=1";
This results in modifying the original SQL statement to a degree that enables one to bypasses authentication. This is a serious vulnerability and must be prevented from within the code.
Defence Against an SQL Injection Attack
One of the ways to reduce the chance of SQL injection attack is to ensure that the unfiltered strings of text must not be allowed to appended to the SQL statement before execution. For example, we can use PreparedStatement to perform required database tasks. The interesting aspect of PreparedStatement is that it sends a pre-compiled SQL statement to the database, rather than a string. This means that query and data are separately send to the database. This prevents the root cause of the SQL injection attack, because in SQL injection, the idea is to mix code and data wherein the data is actually a part of the code in the guise of data. In PreparedStatement, there are multiple setXYZ() methods, such as setString(). These methods are used to filter special characters such as a quotation contained within the SQL statements.
For example, we can execute an SQL statement in the following manner.
String sql = "SELECT * FROM employees WHERE emp_no = "+eno;
Instead of putting, say, eno=10125 as an employee number in the input, we can modify the query with the input such as:
eno = 10125 OR 1=1
This completely changes the result returned by the query.
An Example
In the following example code, we have shown how PreparedStatement can be used to perform database tasks.
package org.mano.example;
import java.sql.*;
import java.time.LocalDate;
public class App
{
static final String JDBC_DRIVER =
"com.mysql.cj.jdbc.Driver";
static final String DB_URL =
"jdbc:mysql://localhost:3306/employees";
static final String USER = "root";
static final String PASS = "secret";
public static void main( String[] args )
{
String selectQuery = "SELECT * FROM employees
WHERE emp_no = ?";
String insertQuery = "INSERT INTO employees
VALUES (?,?,?,?,?,?)";
String deleteQuery = "DELETE FROM employees
WHERE emp_no = ?";
Connection connection = null;
try {
Class.forName(JDBC_DRIVER);
connection = DriverManager.getConnection
(DB_URL, USER, PASS);
}catch(Exception ex) {
ex.printStackTrace();
}
try(PreparedStatement pstmt =
connection.prepareStatement(insertQuery);){
pstmt.setInt(1,99);
pstmt.setDate(2, Date.valueOf
(LocalDate.of(1975,12,11)));
pstmt.setString(3,"ABC");
pstmt.setString(4,"XYZ");
pstmt.setString(5,"M");
pstmt.setDate(6,Date.valueOf(LocalDate.of(2011,1,1)));
pstmt.executeUpdate();
System.out.println("Record inserted successfully.");
}catch(SQLException ex){
ex.printStackTrace();
}
try(PreparedStatement pstmt =
connection.prepareStatement(selectQuery);){
pstmt.setInt(1,99);
ResultSet rs = pstmt.executeQuery();
while(rs.next()){
System.out.println(rs.getString(3)+
" "+rs.getString(4));
}
}catch(Exception ex){
ex.printStackTrace();
}
try(PreparedStatement pstmt =
connection.prepareStatement(deleteQuery);){
pstmt.setInt(1,99);
pstmt.executeUpdate();
System.out.println("Record deleted
successfully.");
}catch(SQLException ex){
ex.printStackTrace();
}
try{
connection.close();
}catch(Exception ex){
ex.printStackTrace();
}
}
}
A Glimpse into PreparedStatement
These jobs also can be accomplished with a JDBC Statement interface, but the problem is that it can be quite insecure at times, especially when a dynamic SQL statement is executed to query the database where user input values are concatenated with the SQL queries. This can be a dangerous situation, as we have seen. In most ordinary circumstances, Statement is quite harmless, but PreparedStatement seems to be the better option between the two.It prevents malicious strings from being concatenated due to its different approach in sending the statement to the database. PreparedStatement uses variable substitution rather than concatenation. Placing a question mark (?) in the SQL query signifies that a substitute variable will take its place and supply the value when the query is executed. The position of the substitution variable takes its place according to the assigned parameter index position in the setXYZ() methods.
This technique prevents it from SQL injection attack.
Further, PreparedStatement implements AutoCloseable. This enables it to write within the context of a try-with-resources block and automatically closes when it goes out of scope.
Conclusion
An SQL injection attack can be prevented only by responsibly writing the code. In fact, in any software solution security is mostly breached due to bad coding practices. Here, we have described what to avoid and how PreparedStatement can help us in writing secure code. For a full idea on SQL injection, refer to appropriate materials; the Internet is full of them, and, for PreparedStatement, look into the Java API Documentation for a more detailed explanation.
