Primer: Introduction to SQL Injection Attacks

Firesand - End-to-End Security for your Business

Introduction

This article is one of a collection of primer articles, providing a basic introduction to various topics, for reference in other – more in-depth – articles. These primer articles are written as people come to security pages from various backgrounds, be it a software engineer who wants to understand an issue they need to fix (or prevent one from occurring in the first place) or a penetration tester with no background in software engineering, who wants to understand and know how to exploit a particular issue.

This primer is regarding SQL Injection Attacks.

SQL Injection Attacks

The issue of SQL Injection comes to the fore when applications are constructing SQL queries using invalidated external input, such a scenario could look like the following:

Note: This scenario follows on from the previous primer article: https://www.firesand.co.uk/articles-research/posts/2023/october/primer-introduction-to-no-sql-databases-and-mongodb/ 

Assuming the application is built poorly, in this type of scenario it would be possible to bypass such an authentication system, simply by injecting database commands and/or operators.

Because the SQL Query is built directly from the user input, and because (in this scenario) no input validation is performed, a malicious user could simply provide the following input into the username:

Yossarian' --

This would result in the construction of the following SQL statement, by the application:

SELECT UserId FROM TUser WHERE UserName = 'Yossarian' -- ' AND Password = ''

The '--' piece comments out the SQL commands that follow it, thus, such an attack would result in an effective following SQL:

SELECT UserId FROM TUser WHERE UserName = 'Yossarian'

This, of course, would enable an attacker to log into Yossarian's account without knowledge of his password, as demonstrated in the following diagram:

How this can come about is demonstrated by the following sample C#.NET code:

private static int LogUserIn(string username, string password) 
{
    // Default the return value
    var result = -1;

    // Create connection
    using (var connection = new SqlConnection("ConnectionStringToSQLServer"))
    {
        // Open connection
        connection.Open();

        // SQL Statement (vulnerable)
        var authenticationSQLStatement = "SELECT UserId FROM TUser WHERE UserName = '" + username + "' AND Password = '" + password + "'";

        // Execute SQL Statement
        using (var authenticationSQLCommand = new SqlCommand(authenticationSQLStatement, connection))
        {
            // Extract the UserId
            using (var resultReader = authenticationSQLCommand.ExecuteReader())
            {
                        
                while (resultReader.Read())
                {
                    try
                    {
                        result = resultReader.GetInt32(0);
                    }
                    catch(Exception ex){ }
                }
            }
        }

        return result;
    }
}

SQL Injections work here, primarily because the sample C#.NET code does not validate the input data.

Important note: this is not an issue specific to C#.NET, this type of vulnerability exists in all programming languages (e.g. C, C++, Java, PHP, Python, Perl etc) where the software engineer has not written input validation.

There are a range of solutions to the SQL Injection attack problem, the key defences to this are [1]:

1. Use of Prepared Statements with Parameterised Queries.
2. Input validation (using a white-list approach).
3. Input escaping.

Final Thoughts

SQL Injection is a common security vulnerability that arises from predominantly poor (or missing) input validation. Given that arbitrary SQL code can be supplied, it is can be devastating. SQL Code can be constructed to extract all information from the target system, alter information, destroy information, and under the right circumstances even gain a remote shell onto the database server itself. 

However, SQL Injection - as with most injection attacks - is relatively easy to defend!

References

[1] - OWASP Foundation, https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html, accessed on 23rd September 2023

About the author

https://www.linkedin.com/in/chrisblake/

Chris Blake has over 20 years of experience in the information and cyber security field, and is a passionate and qualified Enterprise Security Architect and Privacy Professional who leads and delivers innovative solutions at Firesand Limited, a company he co-founded in 2016. His specialities include application security, enterprise security architecture, and privacy, with a strong track record of building and implementing ISO 27001 compliant and certified information security practices, application security programmes, and enterprise security architectures. He has a thirst for continual learning and a commitment to excellence, as demonstrated by his academic and professional credentials from prestigious institutions such as the University of Oxford, (ISC)², IAPP, SABSA, The Open Group, and ISACA.

Chris holds an MSc in Software and Systems Security at the University of Oxford, and an array of professional certifications: CISSP, ISSAP, CSSLP, CCSP, SSP.NET, SSP.JAVA, CISA, CISM, CIPP/E, CIPM, CIPT, FIP, SCF, TOGAF, CPSA, and CEH.

Chris' experience spans multiple sectors: Retail & eCommerce; Financial Services, Banking, & Payments; i-Gaming; Energy (Oil & Gas); Property Management & PropTech; and Data Science; as well as Defence. 

His areas of interests include: penetration testing; regulation & privacy, including the impact on society; access control in software; security automation in development; application of cryptography; security architecture; risk modelling & analysis; HTTP architecture & web security; IoT Security.