SQL Injection – A Source Code Perspective, All Cyber Professionals Need to Know

We have seen SQL Injection during dynamic testing using some interception tools like BurpSuite. In this blog I will throw some light on source code and try to explain SQL Injection from a source code perspective. This blog will be helpful to people working in cybersecurity as well as the developers.

What is SQL Injection

In straight terms, when someone can inject database queries using an application and database interprets it and gives output for the same, then its SQL Injection. There are different types of SQL injection but let’s not get into it. I have added some links in the reference section where you can learn more about SQL Injection.

Image Source

Have you thought of what goes on in a code when someone puts DB queries in the parameter? Let’s go into the code.

Case 1

String sqlString = "SELECT * FROM db_user WHERE username = '"
                    + username +
                    "' AND password = '" + pwd + "'";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sqlString);

Code Source

The main reason behind every SQL Injection is query formation. If any of the parameters is concatenated in the query, it is likely vulnerable to SQL Injection.

Consider the above simple code; here, username and pwd can be a parameter to a login request. Both the parameters are concated in the query, so it is vulnerable to SQL Injection. Let’s see how the query gets tampered with. Consider username=test'+or+1=1+--+&pwd=mypass as input from frontend.

Let’s interpret the query, how the query would be with this input. SELECT * FROM db_user WHERE username = 'test' or 1=1 -- ' AND password = 'mypass'. In the SQL database, -- is used to comment out. So anything after -- is commented out. So database will not check the password. It will only check if the username with test exists or not, and the rest part 1=1 is a true condition making the whole query true.

To mitigate SQL Injection, it is recommended that the developer uses pre-compiled queries using parameterized queries or a prepared statement. Can anything go wrong using pre-compiled queries?

Case 2

Let’s take similar example from pre-compiled query

String sqlString = "SELECT * FROM db_user WHERE username=" +
                    username + " AND password =" + pwd;     
PreparedStatement stmt = connection.prepareStatement(sqlString);

ResultSet rs = stmt.executeQuery();

Code Source

In the above example, PreparedStatement is used to create a pre-compiled query. But the problem is again the concatenation of username and pwd. Before creating a pre-compiled query, it will concat the data and proceed further, resulting in SQL Injection.

Often, developers try to use custom sanitizers or custom validation on user input. As it is not tested against many test cases, it is highly likely to fail and again result in vulnerability.

Case 3

Let’s take example from custom validation

public boolean isBlackListed(String parameter) {

        List<String> blackList = Arrays.asList("--", ";", "or", "and", "@@", "@", "char", "nchar", "varchar", "nvarchar",
                "table", "alter", "begin", "cast", "create", "cursor", "declare", "delete", "drop", "end", "exec",
                "union", "execute", "fetch", "select", "insert", "kill", "open", "sys", "sysobjects", "syscolumns",
                "update");

        for (int i = 0; i < blackList.size(); i++) {

            String pattern = blackList.get(i);
            Pattern p = Pattern.compile(pattern);
            Matcher m = p.matcher(parameter);

            if (m.find()) {
                return true;
            }
        }
        return false;

}

public void Login(String username, String pwd)
{
    if(isBlackListed(username) || isBlackListed(pwd))
        return;

    String sqlString = "SELECT * FROM db_user WHERE username = '"
                        + username +
                        "' AND password = '" + pwd + "'";
    Statement stmt = connection.createStatement();
    ResultSet rs = stmt.executeQuery(sqlString);
}

At first look, it seems reasonable that it checks all the required things to mitigate SQL Injection. But NO. What about other keywords? What about the combination of CaPS and SMalL?

No checks . . .

Conclusion and Mitigation

For sure, pre-compiled queries can mitigate SQL Injection, but it needs to be used properly. It is suggested not to concat anything in the query. Let’s take a look at how one should do it.

String sqlString ="SELECT * FROM db_user WHERE username=? AND password=?";
PreparedStatement stmt = connection.prepareStatement(sqlString);
stmt.setString(1, username);
stmt.setString(2, pwd);
ResultSet rs = stmt.executeQuery();

To make it understandable, I would say it will add the parameters as a string and then execute the query. In addition to the above solution, one can use DB procedures too.

That’s all for the SQL Injection, I hope you learned something from this.

Reference

PortSwigger

OWASP

W3Schools

SEI CertImage Source