Querying the database with ExecuteScalar(): Must declare the table variable "@tableName"

asp.net asp.net-core c# entity-framework entity-framework-core

Question

I am trying to return the values from a column in a table in the database using ExecuteScalar().

The software is throwing an exception on ExecuteScalar() stating Must declare the table variable "@tableName".

However, @tableName is declared so I am not sure why I am getting this error.

Code:

public object GetListOfDatabases(string tableName)
        {
            switch (tableName)
            {
                case "PMS":
                    tableName = "CALCULATION_SCHEDULE";
                    break;
                case "UKPMS":
                    tableName = "UKPMS_CALCULATION_SCHEDULE";
                    break;
                case "RCI":
                    tableName = "RCI_CALCULATION_SCHEDULE";
                    break;
            }

            if(tableName != null)
            {
                var connection = calculationContext.Database.GetDbConnection();

                if (connection.State.Equals(ConnectionState.Closed))
                    connection.Open();

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = @"
                    SELECT DATABASE_NAME FROM @tableName 
                    ";

                    var tableNameParam = command.CreateParameter();
                    tableNameParam.ParameterName = "@tableName";
                    tableNameParam.Value = tableName;
                    command.Parameters.Add(tableNameParam);
                    return command.ExecuteScalar();
                }
            }
            else
            {
                return null;
            }
}

Here is a similar query from the same interface that works, I don't understand why this one works and the other one doesn't:

public bool tableExists(string schema, string tableName)
        {
            var connection = calculationContext.Database.GetDbConnection();

            if (connection.State.Equals(ConnectionState.Closed))
                connection.Open();

            using (var command = connection.CreateCommand())
            {
                command.CommandText = @"
                    SELECT 1 FROM INFORMATION_SCHEMA.TABLES 
                    WHERE TABLE_SCHEMA = @Schema
                    AND TABLE_NAME = @TableName";

                var schemaParam = command.CreateParameter();
                schemaParam.ParameterName = "@Schema";
                schemaParam.Value = schema;
                command.Parameters.Add(schemaParam);

                var tableNameParam = command.CreateParameter();
                tableNameParam.ParameterName = "@TableName";
                tableNameParam.Value = tableName;
                command.Parameters.Add(tableNameParam);

                return command.ExecuteScalar() != null;
            }
        }
1
-1
2/13/2020 4:55:57 PM

Accepted Answer

Table names cannot be parameterised.

You'll have to tackle this by string interpolation or similar:

command.CommandText = $@"SELECT DATABASE_NAME FROM {tableName}";

Of course this opens your query to SQL injection, so you need to white-list table names.

Maybe by throwing an exception:

switch (tableName)
{
    case "PMS":
        tableName = "CALCULATION_SCHEDULE";
        break;
    case "UKPMS":
        tableName = "UKPMS_CALCULATION_SCHEDULE";
        break;
    case "RCI":
        tableName = "RCI_CALCULATION_SCHEDULE";
        break;
    default:
        throw new ArgumentException("Invalid table");
}
1
2/13/2020 11:42:37 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow