Understanding common SQL Injection attacks and protection methods

SQL Injection (SQLi) is one of the most common attacks on web applications used to gain unauthorized access to databases. This attack can have serious consequences for data security and system integrity.

Classic SQL Injection

Classic SQL injection attacks often occur when an attacker injects malicious SQL code through an unsanitized input field in an application that communicates with a database. For example, if an application has a user search input field, the attacker can inject SQL code that can modify, delete, or gain access to data in the database for which they have no access rights.

public bool AuthenticateUser(string username, string password) {

string query = “SELECT * FROM Users WHERE Username='” + username + “‘ AND Password='” + password + “‘”;

SqlCommand command = new SqlCommand(query, connection);

SqlDataReader reader = command.ExecuteReader();

return reader.HasRows;

} 

A malicious user can enter any existing username and utilize the following SQL code for the password field to exploit this vulnerability:

‘ OR ‘1‘=’1‘ –

Consequently, the final query looks like this:

SELECT * FROM Users WHERE Username=’admin’ AND Password=’ OR ‘1’=’1' —

The result of this operation is logging into the application without knowing the appropriate username-password combination!

Union SQLi

This type of SQL injection involves inserting a SQL UNION statement into the original SQL query to combine the results of two or more SELECT queries into one resulting set. This technique allows attackers to extract data from other tables within the database to which they would not normally have access.

public void ListProductsFromCategory(string categoryId) {
    string query = "SELECT Name, Description FROM Products WHERE CategoryID = " + categoryId;
    SqlCommand command = new SqlCommand(query, connection);
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
    
    while (reader.Read()) {
        Console.WriteLine($"Name: {reader["Name"]}, Description: {reader["Description"]}");
    }

    connection.Close(); // Close the connection after use
}

By entering this SQL as the categoryId, we can exploit this vulnerability:

1 UNION SELECT username, password FROM Users —

The final query looks like this:

SELECT Name, Description FROM Products WHERE CategoryID = 1 UNION SELECT username, password FROM Users —

This query combines the results of the original query with the results of the injected query, allowing usernames and passwords from the Users table to be extracted. — is used to comment out the rest of the original query.

Mitigation

To prevent this type of attack, always use parameterized queries or stored procedures for dynamically constructing SQL queries, and avoid directly concatenating user input into your “hardcoded” SQL.

public bool AuthenticateUser(string username, string password)

{

string query = “SELECT * FROM Users WHERE Username=@Username AND Password=@Password”;

SqlCommand command = new SqlCommand(query, connection);

command.Parameters.AddWithValue(“@Username”, username);

command.Parameters.AddWithValue(“@Password”, password);

SqlDataReader reader = command.ExecuteReader();

return reader.HasRows;

}