Fix SDReader throwing exception when given SQL command with parameters in C#

Question

I am trying to create an MVC Razor Webpage on C# an in order to display some content I am trying to get some data from a SQL Table.

I am trying to create a SQL command that I can add parameters to in order to prevent SQL Injection attacks.

string cmdClientAccess = "SELECT * FROM @Table WHERE [User] = '@User'";
int i = 0;

foreach (var client in Clients) {

    using (SqlConnection sConnection = new SqlConnection(SqlConnectionString))
    {

        SqlCommand sUserAccess = new SqlCommand(cmdClientAccess);
        sUserAccess.Parameters.AddWithValue("@Table", ClientUserTables[i]);
        sUserAccess.Parameters.AddWithValue("@User", AccessingUser);

        sConnection.Open();

        using (SqlDataReader SDReader = sUserAccess.ExecuteReader())
        {
            while (SDReader.Read())
            {
                if (SDReader["Requirement2"].ToString() != "")
                {
                    List1.Add(client);
                }

                if (SDReader["Requirement2"].ToString() == "Yes")
                {
                    List2.Add(client);
                }
            }
        }

        sConnection.Close();
    }
    i++;
}

The problem is that as soon as the program reaches this line:

using (SqlDataReader SDReader = sUserAccess.ExecuteReader())

Visual Studio gives the following message:

System.InvalidOperationException: 'ExecuteReader: Connection property has not been initialized.'

`'@User'` should be `@User`. Table name alas can't be parameterised the way you are trying to. — mjwills, Oct 14 '19 at 01:05

score 0 · Accepted Answer · answered Oct 14 '19 at 01:00

You haven't passed the connection to your command.

Change :

SqlCommand sUserAccess = new SqlCommand(cmdClientAccess);

To :

SqlCommand sUserAccess = new SqlCommand(cmdClientAccess, sConnection );

Further documentation at Microsoft for the SqlCommand object : https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand?view=netframework-4.8

Fix SDReader throwing exception when given SQL command with parameters in C#

1 Answers1