Prevent SQL queries built from strings

Docs > Datadog Security > Code Security > Static Code Analysis (SAST) > SAST Rules > Prevent SQL queries built from strings

Metadata

ID: csharp-security/sql-injection

Language: C#

Severity: Error

Category: Security

CWE: 89

Description

Never build SQL queries manually. Always have the query built with parameters and then pass the parameters to the prepared statement.

Learn More

CWE-89: Improper Neutralization of Special Elements used in an SQL

Non-Compliant Code Examples

using System.Xml;

class MyClass {
    public static void doQuery(Int32 userId)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
                var sql = $"SELECT Id, Username, Email, IsAdmin FROM Users WHERE Username LIKE '%{name}%'";
                
        }
    }
}

using System.Xml;

class MyClass {
    public static void doQuery(Int32 userId)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand("SELECT attr FROM table WHERE id=" + userID, connection);
        }
    }
}

using System.Xml;

class MyClass {
    public static void goQuery(Int32 userID)
    {
        String query1 = "SELECT attr, LOWER(email) FROM table WHERE id=" + userID;
    }
}

class MyClass {
    public static void doQuery(string role)
    {
        var sql = $"SELECT * FROM Users WHERE role = {(role == "admin" ? "admin" : role)}";
    }
}

class MyClass {
    public static void doQuery(string firstName, string lastName)
    {
        var sql = $"SELECT * FROM Users WHERE name = '{firstName + " " + lastName}'";
    }
}

class MyClass {
    public static void doQuery(string name)
    {
        var sql = $"SELECT * FROM Users WHERE name = '{name ?? "default"}'";
    }
}

Compliant Code Examples

using System.Xml;

class MyClass {
    public static void doQuery(Int32 userID)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand("SELECT attr FROM table WHERE id=@ID", connection);
            command.Parameters.Add("@ID", SqlDbType.Int);
            command.Parameters["@ID"].Value = userID;
        }
    }
}

using System.Data.SqlClient;

class MyClass {
    public static void doQuery(int recordMoveID)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            var SQL = $@"
                SELECT * FROM ApplicationControls
                WHERE EntityTypeID = {(int)EntityType.Project}
                AND RecordMoveID = @RecordMoveID";
        }
    }
}