top of page

What is SQL Injection Attack ?

Updated: Jan 5


Buzz Basics for SMBs - SQL Injection Attack
Buzz Basics for SMBs - SQL Injection Attack

In almost all cyber-attacks, hackers exploit the security bugs (aka. vulnerability) in the victim's networks, machines, or software installed. One such particularly severe vulnerability is "SQL Injection". This vulnerability is of such significance that attackers can dump entire databases of victims. This might lead to the forced closure of the organization. In this article, you will understand what is SQL Injection attack and what causes SQL Injection attacks to happen.

We will also explain a few of the basic prevention techniques for SQL Injection attacks. So, let's get started.

This article is divided into below sections. You can jump directly to any section.


Definition of SQL Injection attack

SQL Injection attack is an injection type of attack in which an attacker injects malicious SQL statements (aka payloads) in the user-provided input fields of the application. These input fields are then used as is to prepare SQL statements that will be executed on the database server.


In other words, in an SQL Injection attack, an attacker can execute unauthorized SQL statements on the victim's database through the application's input fields. Using this attack, an attacker can get a complete hold of the victim's database.


The database under attack has no way of knowing whether the SQL statement being executed is a normal SQL statement triggered by the application or is a manipulated SQL statement executed as a part of an SQL injection attack by hackers. Let's now understand how this attack works internally and what are the main causes of the attack.


How does an SQL Injection attack work?

Now, we will explain how SQL injection attack works with an example that you can relate to your often, daily required action i.e. a Login form.


Consider the below example of a "Login Form" SQL Injection attack. As can be seen, the user needs to enter his username and password in order to log in.

SQL Injection attack example login form
Login form SQL Injection attack

Below are the contents of the users table containing the username and password to be checked (Although it's a bad table design where passwords are kept in unencrypted form, let's keep the table super simple to understand the SQLi attack)

Table vulnerable to SQL Injection attack
SQL Injection vulnerable database table

Once the user clicks on the "LOG IN" button, an API call to the backend server will be made with a payload containing the entered username & password. We directly look at server-side logic that validates entered credentials with the credentials in the database table.

public static void main(String[] args) {
    String username = "john";
    String password = "John@123";
    //password = "John@1231' OR '1'='1";    // SQLi payload input
    try {
        int userid = validateCredentialsAndFetchUserId(username, password);
        System.out.println("User is found. User Id is :" + userid);
    } catch (InvalidUserException e) {
        System.out.println("Wrong username or password entered. No user found.");
    }
}

private static int validateCredentialsAndFetchUserId(String username, String password) {
    Connection con = null;
    Statement st = null;
    ResultSet resultSet = null;

    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "mydbuser", "Mydbpassword@123");
        st = con.createStatement();

        // Below SQL fetches the user with given username and password combination
        String sql = "select * from users where username='" + username + "' and password='" + password + "'";
        System.out.println("SQL to be fired :"+sql);

        resultSet = st.executeQuery(sql);
        if (resultSet.next()) {
            // We got the results. This means the user with given userid and password exist.
            // There can be max 1 record with given combination.
            // Now just return user id of the first result.
            return resultSet.getInt("userid");
        } else {
            throw new InvalidUserException("Invalid user with username :" + username);
        }

In the above code, we are building an SQL query in the below form.

select * from users where username='john' and password='John@123'

If this query returns at least 1 result, then it means entered credentials exist in the database and the user is valid. The query is built using the string concatenation technique as below-

String sql = "select * from users where username='" + username + "' and password='" + password + "'";

This code snippet is vulnerable to SQL Injection attacks. It is an example of Error based SQL Injection. Here, the user-provided input fields are passed as is to build a database query using the string concatenation technique. An attacker can embed an actual SQL statement in any of the input fields and our code will concatenate the user's input as is to the final SQL query to be executed on the database server.


Let's understand with some example input test cases. Note, that we have added some debug logs (using system.out).


Demonstration of SQL Injection

The test case with valid credentials

For username="john" and password="John@123", below is the output.

SQL to be fired:-
select * from users where username='john' and password='John@123'

User is found. User Id is: 100


The test case with "invalid" credentials

For username="john" and password="John@123456", below is the output.

SQL to be fired:-
select * from users where username='john' and password='John@123456'

Wrong username or password entered. No user found.

This is working well. As a developer, you will be happy that the program is working well as you have checked both positive and negative test cases. Now, the time is for some impressive part, the hacker's test case -


The hacker's test case

For username="john" and password="John@1231' OR '1'='1", below is the output. Observe the quotes in the password. They are important so that a valid SQL statement is generated in the backend.

SQL to be fired:-
select * from users where username='john' and password='John@1231' OR '1'='1'

User is found. User Id is: 100

As you can see, the hacker has entered the wrong password of the user (but injected the SQL injection payload in the password field) and is able to fetch the user ID (i.e. bypassed authentication). The SQLi payload used in the above test case is John@1231' OR '1'='1.


Prevention of SQL Injection attack

As you have seen in the above sample, the input values entered by the user are passed as is to build the final SQL query to be executed on the database server. This is the main cause of SQL injection here. The prevention of SQL Injection deserves a separate article on its own, but here are bullet points for quick reference -


Prevention Technique 1: Use PreparedStatement with parameters

By using the JDBCs PreparedStatement API for building the SQL query, we can avoid SQL Injection. The PreparedStatement API will ensure the input parameters will not alter the the structure of SQL query being executed. The input parameters are substituted at the right place in the lifecycle of query execution.


Prevention Technique 2: Validate input parameters

To prevent SQL Injection, you need to validate the values that are provided by users in the input fields. For example, if the input field is the user's mobile number (+91-90901234) then we know that values will be numeric only (with maybe one special character '-'). If we find any other character, then we can safely throw an error. If such a validation is done for each and every input field, then we will never let the attacker's input reach our database queries.


Does SQL injection still work in 2023?

The answer is Yes. Despite SQL Injection attacks being well-known for quite a long time, they are still prevalent in the year 2023. It's one of the reasons why they are at number 3 in OWASP Top 10 for the year 2023. So, it's highly important to know how SQL Injection attacks work internally.


Conclusion

As you have seen, the prevention techniques of SQL Injection attacks are basic principles that can be followed during the implementation phase. If all developers possess knowledge about the causes of SQL Injection attacks and are well-versed in the techniques to prevent them, we are already well-equipped to defend against such attacks.


At BUZZ, we have found SQL Injection issues in various applications having a vast user base and critical financial information. There may be a hidden SQLi time bomb in your application, check once or contact BUZZ experts before any attacker hosts your database on Telegram for free download.

Comments


Commenting has been turned off.
bottom of page