Query causing sql injection issue-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

You're putting tableName and primaryKeyName unfiltered into a SQL-query. If there is a semicolon in one of those strings, it is possible to inject a different query from what you thought it to be. Though I don't know where you take those strings from. Fix the creation of your deletequery.

string deletequery "UPDATE @tableName SET IsDeleted = 1 WHERE @primaryKeyname = @id"
SqlCommand command = new SqlComand(deletequery);
command.Parameters.Add("@tableName", SqlDbType.String);
command.Parameters["@tableName"].Value = tableName;

command.Parameters.Add("@primaryKeyname", SqlDbType.String);
command.Parameters["@primaryKeyname"].Value = primaryKeyname;

command.Parameters.Add("@id", SqlDbType.String);
command.Parameters["@id"].Value = entry.OriginalValues[primaryKeyName];

Hope this helps.