Using Parameters.AddWithValue to pass database name fails

Question

I've been trying to perform a login query. I think my main problem with this function is the Parameters.AddWithValue portion, but don't really understand what is wrong.

Following code returns an error when ran:

Must declare the table variable "@database"

Code:

 public static bool clsFuncLogin(string USER, string PASS, 
         string conStr, string strDatabase)
{
    SqlConnection conn = new SqlConnection(
       ConfigurationManager.ConnectionStrings[conStr].ConnectionString);
    conn.Open();


    using (SqlCommand StrQuer = 
         new SqlCommand("SELECT COUNT(*) FROM @database "+ 
            "WHERE Username = @userid AND Password = @password", conn))
    {
        StrQuer.Parameters.AddWithValue("@userid", USER);
        StrQuer.Parameters.AddWithValue("@password", PASS);
        StrQuer.Parameters.AddWithValue("@database", strDatabase);
        int DataQuery = Convert.ToInt32(StrQuer.ExecuteScalar().ToString());
        if (DataQuery == 1)
        {
            System.Web.HttpContext.Current.Session["User"] = USER;
            System.Web.HttpContext.Current.Session["Pass"] = PASS;
            System.Web.HttpContext.Current.Session["loggedIn"] = "True";

            return true;

        }
        else if (DataQuery > 1)
        {
           //to tell if a double is created in the db
           //probably to be removed

            System.Web.HttpContext.Current.Session["Double"] = USER;
            return false;
        }
        else 
        {
            return false;

        }
    }
}

Ive also done the query as

"SELECT COUNT(*) FROM" + strDatabase + " WHERE Username = " + USER + 
        " AND Password = " + PASS;

but I was told that that is bad practice. Any advice?

do you mean error exist or confused about why that work? Don't do Password = PASS directly because it may be hit by SQL injection attack — linpingta, May 15 '14 at 01:20
sorry for the confusion, but the function posted dosnt work. returns a "Must declare the table variable "@database" when ran, and I don't get why it doesn't work. and I plan on encrypting and salting later this is just a logic test — Andrew Diamond, May 15 '14 at 01:27
off topic, but i'd suggest looking at [naming conventions](http://msdn.microsoft.com/en-us/library/ms229043(v=vs.110).aspx) and trying to normalize yours — Jonesopolis, May 15 '14 at 01:33
I've changed title - please make sure it matches your intention (clearly matches the answer). Please next time avoid "new here"/"thank you" and instead inline detailed information about error - messages, stack,... and consider not using misleading names like `strQuer` (instead of `query`). — Alexei Levenkov, May 15 '14 at 02:02
[Don't use AddWithValues()](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) in the first place — Joel Coehoorn, May 15 '14 at 02:13
There's no way you are not subject to SQL Injection with a design like that because if the table name is coming from the user. If it's in coming from your application, it depends on how much does the database trusts on the application. — Paulo Morgado, May 15 '14 at 10:32
I would recommend using a stored procedure and passing a parameter that the stored procedure can safely use to select the correct table. — Paulo Morgado, May 15 '14 at 10:33

score 2 · Accepted Answer · edited May 23 '17 at 12:13

I've never seen table names passed as a parameter, and based on other posts (this and this for example), I don't think it can be easily done... at least, not via SqlCommand.Parameters.

It sounds like there's only two tables - admins and regular users. As an alternative, you could just pass a bool to the method, like isAdmin, then have two queries based on whether the user is an admin or a regular user.

public static bool clsFuncLogin(string user, string pass, string conStr, bool isAdmin)
{
    ...

    var query = isAdmin
        ? "SELECT COUNT(*) FROM ADMIN_TABLE WHERE Username = @userid AND Password = @password"
        : "SELECT COUNT(*) FROM REGULAR_TABLE WHERE Username = @userid AND Password = @password";

    using (var sqlCommand = new SqlCommand(query, conn))
    {
        sqlCommand.Parameters.AddWithValue("@userid", user);
        sqlCommand.Parameters.AddWithValue("@password", pass);

        ...
        ...

ok.so the issue with the statment is that you cant replace the table name with parameters? — Andrew Diamond, May 15 '14 at 01:53
it works like you said, thanks for the help. I assumed that Parameters.AddWithValue replaces some text in the query. thanks for clarifying! — Andrew Diamond, May 15 '14 at 02:03

score -1 · Answer 2 · answered May 15 '14 at 02:09

Main reason to use

SELECT * FROM TABLE WHERE column=@column

with parameters is to avoid all complications of providing correct formatting for the value. it is even more important if values are supplied as free text by user of your application since you will open all possibilities with SQL injection attacks if you fail to properly address all formatting issues (which is basically not worth the effort given solution with parameters).

I really hope that you don't allow users to supply table name and it's supplied by your own code, so you can quite safely use

var query = String.Format("SELECT * FROM {0} WHERE column=@column", tableName);

However I would just recommend to create two separate queries instead to properly separate concerns.

Using Parameters.AddWithValue to pass database name fails

2 Answers2

Linked