Query causing sql injection issue

Question

Type entryEntityType = entry.Entity.GetType();
string tableName = GetTableName(entryEntityType);
string primaryKeyName = GetPrimaryKeyName(entryEntityType);
string deletequery = string.Format("UPDATE {0} SET IsDeleted = 1 WHERE {1} = @id", tableName, primaryKeyName);         
    
Database.ExecuteSqlCommand(deletequery, new SqlParameter("@id", entry.OriginalValues[primaryKeyName]));

After running the sonar scan above query is giving a security hotspot for sql injection.How can this be handled?

You should use SQL parameters for every parameter (denoted with @ prefix) to avoid SQL injection. w3schools.com/sql/sql_injection.asp — Zserbinator
– Zserbinator, Commented Feb 3, 2023 at 10:20
If possible, switch to EF 7 - it has the ability to perform updates directly in the DBMS. / Look at Extensions. Some of them allow you to perform updates efficiently, without loading entities to the client. I recommend linq2db.EntityFrameworkCore. — Alexander Petrov
– Alexander Petrov, Commented Feb 3, 2023 at 15:33
@Zserbinator the two values that are inserted through string.Format cannot be sent as parameters. The one value that can be a parameter is one — Hans Keﬆing
– Hans Keﬆing, Commented Feb 4, 2023 at 15:51

Palle Due · Accepted Answer · 2023-02-03 14:24:42Z

It doesn't look like table name and primary key name are dependent on user input, so I would suppress the Sonar error around this code. If you insist on fixing it you can do something like this (pseudo code):

Do this once, if you will, make it static:

var deleteQueries = new Dictionary<Type, string>();
foreach (Type entryEntityType in AllEntityTypes) // I don't know how you will get all entities
{
    string tableName = GetTableName(entryEntityType);
    string primaryKeyName = GetPrimaryKeyName(entryEntityType);
    string deletequery = string.Format("UPDATE {0} SET IsDeleted = 1 WHERE {1} = @id", tableName, primaryKeyName);         
    deleteQueries.Add(entryEntityType, deleteQuery);
}

When executing delete do this:

Type entryEntityType = entry.Entity.GetType();
string deleteQuery = deleteQueries[entryEntityType];
string primaryKeyName = GetPrimaryKeyName(entryEntityType);
Database.ExecuteSqlCommand(deletequery, new SqlParameter("@id", entry.OriginalValues[primaryKeyName]));

As I said, I would just suppress the error.

David Browne - Microsoft · Accepted Answer · 2023-02-04 15:34:44Z

1

Also when injecting identifiers into dynamic SQL for SQL Server, you should sanitize the string by using a delimited identifier.

In TSQL you do this with the QUOTENAME function, and here's a C# version of it.

private static string QuoteName(string identifier)
{
    var sb = new StringBuilder(identifier.Length + 3, 1024);
    sb.Append('[');
    foreach (var c in identifier)
    {
        if (c == ']')
            sb.Append(']');
        sb.Append(c);
    }
    sb.Append(']');
    return sb.ToString();
}

answered Feb 4, 2023 at 15:34

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Collectives™ on Stack Overflow

Query causing sql injection issue

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related