C# Is it safe to concatenate constant strings to form a SQL Query?-CodePudding

I need to change the table name dynamically based on specific conditions.

Is it safe to build my sql query the following way or am I prone to SQL Injection?

string GenerateSQL(string tableName) {
    return $"SELECT * FROM {tableName};";
}

const string tableName1 = "MyTable1"; 
const string tableName2 = "MyTable2";

string sql;
if (condition1) {
    sql = GenerateSQL(tableName1);
} else if (condition2)
    sql = GenerateSQL(tableName1);
}

CodePudding user response：

While this should not present any security problem as presented. There should not be any possibility for SQL injection since it does not involve any user input.

I would still argue for using parametrized queries whenever possible, because code change. There is a risk some future developer modifies the query to add a user injected parameter, or copies the example for some other purpose that does present a SQL injection vulnerability. Using parametrized queries everywhere would simplify your code guidelines and review.

But as with everything related to security, it does depend on your specific application, threat model and other factors that only you can determine.

CodePudding user response：

I would suggest changing the string to an enum to prevent any type of string manipulation in your SQL query:

enum TableName {
    Unknown = 0, #default, always check and throw if Unknown
    MyTable1 = 1,
    MyTable2 = 2
}

string GenerateSQL(TableName tableName) {
    if(Unknown)throw new ArgumentOutOfRangeException();
    
    # could also use a [DescriptionAttribute] instead of ToString
    string name = tableName.ToString();
    return $"SELECT * FROM {name};";
}

# some method that needs the sql
TableName tableName = condition1 
    ? TableName.MyTable1 
    : TableName.MyTable2;

string sql = GenerateSQL(tableName);