Basic SQL Injection on JDBC by Mohamed Ahmed

The following entry documents how to perform a basic SQL Injection in a Java program with Java DataBase Connectivity (JDBC). In addition, some possible solutions to this vulnerability will be exposed. For this I have created a simple database and I have done a test code in Java that queries this database.

Creating the SQL Database

The database will simply consist of a table called 'Users' which collects four attributes: an identifier, a user name, a password and a secret. In the latter we will store a 'secret' information for each user, which can only be viewed by this user once logged in. Both the password and the secret are in plain text, being the ideal to encrypt them to give security. But as it is a basic test, we will leave it to see it more easily.

Then we will simply add several users with different passwords. We can see the SQL script:

the Code:

----------------------------------------------------------------------------------------------------------------------------------------------------------------

/ We create the database /

CREATE DATABASE dbftwr CHARACTER SET utf8 COLLATE utf8spanish2ci;

/ We create the tables /

CREATE TABLE Users (

id INTEGER NOT NULL AUTO_INCREMENT UNIQUE,

name TEXT NOT NULL,

pass TEXT NOT NULL,

secret TEXT NOT NULL,

PRIMARY KEY (id));

/ We create the inserts /

INSERT INTO Users (name, pass, secret) VALUES

('pedro', '123456', 'Peter really is Paul'),

('pablo', '987654321', 'Pablo is the father of Juan'),

('miguel', 'miguel', 'Miguel has no friends');

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

Vulnerability

In order to query the database and log in, I used a code that generates a graphical window (Swing) with a small login form. We can observe the code here:

the code :

---------------------------------------------------------------------------------------

package es.fwhibbit.sqli;

import java.awt.event.ActionEvent;

import java.awt.event.WindowEvent;

import java.awt.event.WindowListener;

import java.io.IOException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import javax.swing.JButton;

import javax.swing.JFrame;

import javax.swing.JLabel;

import javax.swing.JTextField;

import javax.swing.SwingConstants;

public class SQLInjection extends JFrame implements WindowListener {

private static final long serialVersionUID = 1L;

// Graphic elements

JLabel lblUser = new JLabel ("User:");

JLabel lblPass = new JLabel ("Pass:");

JTextField txtUsername = new JTextField ("");

JTextField txtPass = new JTextField ("");

JLabel lblSecret = new JLabel ("Secret", SwingConstants.CENTER);

JButton btnAccept = new JButton ("OK");

JButton btnClean = new JButton ("Clean");

public SQLInjection () throws ClassNotFoundException, SQLException {

// General window settings

setLayout (null);

setSize (400,250);

setTitle ("SQL Injection Test");

setLocationRelativeTo (null);

setVisible (true);

// Add elements to the window

add (lblUser); lblUsuario .setBounds (1, 1, 100, 50);

add (txtUser); txtUser.setBounds (100, 1, 300, 50);

add (lblPass); lblPass .setBounds (1, 60, 100, 50);

add (txtPass); txtPass .setBounds (100, 60, 300, 50);

add (lblSecret); lblSecret .setBounds (0, 120, 400, 50);

add (btnClean); btnClean .setBounds (95, 180, 100, 50);

add (btnAccept); btnAccept .setBounds (205, 180, 100, 50);

// Connection block to the database

Class.forName ("org.mariadb.jdbc.Driver");

String dbpass = "";

Connection connection = DriverManager.getConnection ("jdbc: mysql: // localhost / dbftwr", "root", dbpass);

Statement s = connection.createStatement ();

// Listeners

addWindowListener (this);

// Button whose function is to clear the text fields

btnLimpiar.addActionListener ((ActionEvent event) -> {

txtUser.setText ("");

txtPass.setText ("");

});

// Button to log in, this section of code will be in charge of making the queries

btnAccess.addActionListener ((ActionEvent event) -> {

try {

ResultSet rs = s.executeQuery ("select secret from Users where (name = '" + txtUsuario.getText () + "' and pass = '" + txtPass.getText () + "');");

rs.next ();

lblSecret.setText (rs.getString (1));

}

catch (SQLException e) {e.printStackTrace ();}

});

//connection.close ();

}

public static void main (String args) throws NumberFormatException, IOException, ClassNotFoundException, SQLException {new SQLInjection ();}

public void actionPerformed (ActionEvent ae) {}

public void windowActivated (WindowEvent we) {}

public void windowClosed (WindowEvent we) {}

public void windowClosing (WindowEvent we) {this.setVisible (false);}

public void windowDeactivated (WindowEvent we) {}

public void windowDeiconified (WindowEvent we) {}

public void windowIconified (WindowEvent we) {}

public void windowOpened (WindowEvent we) {}

}

--------------------------------------------------------------------------------------------------------------------

By executing the program, we can observe the following result. We are Pedro and we know his password: 123456. For what will be our guinea pig

---------------------------------------------------------

user:

pass:

.............................. secret........................

................clear............accept..................

---------------------------------------------------------

So that by logging in correctly, you will return Peter's secret.

---------------------------------------------------------

user: pedro

pass:123456

............................ Pedro really is Pablo........................

................clear............accept..................

---------------------------------------------------------

The two blocks of code that we are dealing with are the following: the connection block to the database and the contents of the accept button, which stores the query.

Code:

-----------------------------------------------------------------------------------------------------------------------------------------------------------

// Connection block to the database

Class.forName ("org.mariadb.jdbc.Driver");

String dbpass = "";

Connection connection = DriverManager.getConnection ("jdbc: mysql: // localhost / dbftwr", "root", dbpass);

Statement s = connection.createStatement ();

// Button to log in, this section of code will be in charge of making the queries

btnAccess.addActionListener ((ActionEvent event) -> {

try {

ResultSet rs = s.executeQuery ("select secret from Users where (name = '" + txtUsuario.getText () + "' and pass = '" + txtPass.getText () + "');");

rs.next ();

lblSecret.setText (rs.getString (1));

}

catch (SQLException e) {e.printStackTrace ();}

});

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

As we see the query:

-select secret from Users where (name = '"+ txtUsuario.getText () +"' and pass = '"+ txtPass.getText () +"');

It is vulnerable to a SQL Injection, since when storing the variables directly in the SQL statement, it will send the text string as it picks it from the text fields. So we can play. A typical query, following the example of Peter, would be:

-select secret from Users where (name = 'pedro' and pass = '123456');

This statement would show the secret of the Users table, where the name is pedrro and its password 123456. With this we verify that the password corresponds to the user. Being a name like pedro will return a boolean true; being the password 123456 will also return a boolean true. True AND True = True: Session started successfully. But what if we insert SQL code, to deceive the server and to show us the secret of who we want, without needing to know your password ?. We can do this by exploiting this vulnerability.

-select secret from Users where (name = 'pedro' or '1' = '1' and pass = '');

In this way we have injected code (in red). In this way we assign the name, is pedro (is true). Or (1 = 1 (obviously true sentence) AND pass = "(blank string, obviously false)). True OR (True AND False) = True OR False = True. We finally get a real boolean, without knowing the password, which will return the secret of this user. We can perform a test with the user Miguel, who does not know his password.

Safe use

How can we solve it? In order to avoid this type of attacks on our program we must use the safe method: replace the Statement with the PreparedStatement. We see the block of code that we have to edit: first we comment our Statement, since we will not give you use. We replace it with the PreparedStatement, where we directly indicate the SQL statement; replacing the elements to be inserted with signs of close interrogation (?) (omit quotation marks). With the order ps (PreparedStatement) .setString and the position number of the element we have placed in the statement, we will indicate the value to give, either a variable or a getText () as in my case.

code:

------------------------------------------------------------------------------------------------------------------------------

// Connection block to the database

Class.forName ("org.mariadb.jdbc.Driver");

String dbpass = "";

Connection connection = DriverManager.getConnection ("jdbc: mysql: // localhost / dbftwr", "root", dbpass);

// Statement s = connection.createStatement ();

// Button to log in, this section of code will be in charge of making the queries

btnAccess.addActionListener ((ActionEvent event) -> {

try {

PreparedStatement ps = connection.prepareStatement ("select secret from Users where (name =? And pass =?)");

ps.setString (1, txtUsuario.getText ());

ps.setString (2, txtPass.getText ());

ResultSet rs = ps.executeQuery ();

rs.next ();

lblSecret.setText (rs.getString (1));

}

catch (SQLException e) {e.printStackTrace ();}

});

-----------------------------------------------------------------------------------------------------------------------------------------------------------

This way we will avoid that our code is vulnerable to attacks of the type SQL Injection, at least to the most known. You know, we'll never be 100% safe !.

Safe use (alternative)

A valid way to solve this vulnerability is to make a single sentence with no possibility of editing. And in turn play with the answers. The code will be similar, so that:

code :

---------------------------------------------------------------------

// Connection block to the database

Class.forName ("org.mariadb.jdbc.Driver");

String dbpass = "";

Connection connection = DriverManager.getConnection ("jdbc: mysql: // localhost / dbftwr", "root", dbpass);

Statement s = connection.createStatement ();

// Button to log in, this section of code will be in charge of making the queries

btnAccess.addActionListener ((ActionEvent event) -> {

try {

ResultSet rs = s.executeQuery ("select * from Users;");

while (rs.next ()) {

if (rs.getString (2) .equals (txtUsuario.getText ()) && rs.getString (3) .equals (txtPass.getText ())) {

lblSecret.setText (rs.getString (4));

break;

}

else {lblSecret.setText ("ERROR");}

}

}

catch (SQLException e) {e.printStackTrace ();}

});

--------------------------------------------------------------------------------------------

As you will see, the connection block remains intact to the first option. The inside of the button will simply have the sentence "select * from Users" (select all from the Users table). From the results you get, as long as you have been going one by one, checking that the content of the user text field is the same as the user name of the table, and the content of the text field of the password is equal to the password of the user of the table. If these conditions are met, the secret will be displayed, but, it will show an error.

In this way, by not modifying the statement directly, but playing with its results will avoid this particular vulnerability.

In this simple way, we have exploited and solved the basic SQL Injection vulnerability in our software. This injection, I reiterate that is basic, there are other compounds with more potential and complexity, but those, I leave them for another entry ... I hope you liked it, greetings !.

Thank you for reading! :)