SQL Injection attacks

A SQL injection attack consists of insertion of a SQL query via the input data from the client to the application. A successful injection of SQL query can able to read the sensitive data from database.

Example

/* Step 1: Import sql package */
import java.sql.*;
import java.io.*;

public class SampleApp {
    
    /* Update username, password and driver details here */
    static Connection getConnection() throws ClassNotFoundException, SQLException{
         /* Step 2: Load Driver */
        System.out.println("Loading/Registering driver");  
       
        Class.forName("com.mysql.jdbc.Driver");
         
        /* Step 3: Open connection to database */
        System.out.println("Connecting to database");
        String url = "jdbc:mysql://localhost/world";
        String userName = "root";
        String pasword = "tiger";
        return DriverManager.getConnection(url, userName, pasword);
    }
    
    public static void main(String args[]) throws Exception{
        Connection conn = getConnection();
        BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
        
          /* Create table employee */
        String query = "CREATE TABLE employee (id int, name varchar(30), PRIMARY KEY(id))";
        Statement stmt = conn.createStatement();
        stmt.execute(query);     
        
        /* Insert data to employee table */
        query = "INSERT INTO employee values(1, \"Krishna\")";
        stmt.execute(query);
        query = "INSERT INTO employee values(2, \"Arjun\")";
        stmt.execute(query);
        
        query = "SELECT * FROM employee";
        ResultSet rs = stmt.executeQuery(query);
        
        while(rs.next()){
            int id  = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println(id +" " + name);
        }
        
        System.out.println("Enter name of the employee");
        String userName = br.readLine();
        rs = stmt.executeQuery("SELECT * from employee WHERE name='"+ userName + "';");
        
        while(rs.next()){
            int id  = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println(id +" " + name);
        }
        
        System.out.println("Dropping table employee");
        query = "DROP TABLE employee";
        stmt.execute(query);
        
 rs.close();
        stmt.close();
        conn.close();
    }
}

Above example writes some records to the database and asks the user for employee name to display the information about the specific employee. But we can inject a SQL query as input to this Application and get all the employee details.

Sample Run

Loading/Registering driver
Connecting to database
1 Krishna
2 Arjun
Enter name of the employee
dummy or like '*'
1 Krishna
2 Arjun
Dropping table employee

name of employee is passed as " dummy or like '*' ",  it displayed all the employee details.

How to defence SQL Injection Attack

1. Use Prepared statements

Prepared statements ensure that an attacker is not able to change the intent of a query, even if SQL commands are inserted by an attacker.

/* Step 1: Import sql package */
import java.sql.*;
import java.io.*;

public class SampleApp {
    
    /* Update username, password and driver details here */
    static Connection getConnection() throws ClassNotFoundException, SQLException{
         /* Step 2: Load Driver */
        System.out.println("Loading/Registering driver");  
       
        Class.forName("com.mysql.jdbc.Driver");
         
        /* Step 3: Open connection to database */
        System.out.println("Connecting to database");
        String url = "jdbc:mysql://localhost/world";
        String userName = "root";
        String pasword = "tiger";
        return DriverManager.getConnection(url, userName, pasword);
    }
    
    public static void main(String args[]) throws Exception{
        Connection conn = getConnection();
        BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
        PreparedStatement prep = conn.prepareStatement("SELECT * from employee WHERE name=?");
        
          /* Create table employee */
        String query = "CREATE TABLE employee (id int, name varchar(30), PRIMARY KEY(id))";
        Statement stmt = conn.createStatement();
        stmt.execute(query);     
        
        /* Insert data to employee table */
        query = "INSERT INTO employee values(1, \"Krishna\")";
        stmt.execute(query);
        query = "INSERT INTO employee values(2, \"Arjun\")";
        stmt.execute(query);
        
        query = "SELECT * FROM employee";
        ResultSet rs = stmt.executeQuery(query);
        
        while(rs.next()){
            int id  = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println(id +" " + name);
        }
        
        System.out.println("Enter name of the employee");
        String userName = br.readLine();
        prep.setString(1, userName);
        rs = prep.executeQuery();
        
        while(rs.next()){
            int id  = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println(id +" " + name);
        }
        
        System.out.println("Dropping table employee");
        query = "DROP TABLE employee";
        stmt.execute(query);
        
 rs.close();
        stmt.close();
        conn.close();
    }
}

Sample output

Loading/Registering driver
Connecting to database
1 Krishna
2 Arjun
Enter name of the employee
krishna or name like '*'
Dropping table employee

2. Check the user input thoroughly, before sending it to SQL Query.

Related Links

https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

Prevoius                                                 Next                                                 Home