Method returns empty string in ASP.Net

Question

I'm having trouble with this method. It returns empty string, what is wrong with this ?

I have this method:

public static string GetData(string Table1, string Column1, string WhereColumn, string WhereValue)
{
    Table1 = Methods.cleaninjection(Table1); // Some injection method that cleans the string

    SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["connection"].ConnectionString);

    SqlCommand command = new SqlCommand("SELECT " + "@Column1" + " FROM " + Table1 + " WHERE " + "@WhereColumn" + " = " + "@WhereValue", connection);
    command.Parameters.AddWithValue("Column1", Column1);
    command.Parameters.AddWithValue("WhereColumn", WhereColumn);
    command.Parameters.AddWithValue("WhereValue", WhereValue);

    try
    {
        if ((connection.State == ConnectionState.Closed) || (connection.State == ConnectionState.Broken))
        {
            connection.Open();
        }
        string veri = Convert.ToString(command.ExecuteScalar());
        return veri;
    }
    finally
    {
        connection.Close();
    }
}

When I run this, the command string looks like this:

SELECT @Column1 FROM Table1 WHERE @WhereColumn = @WhereValue

It looks like correct but I couldn't find what is wrong.
Any ideas?

Also, the @WhereColumn. You can't use parameters as column or object names. — Dan Guzman
– Dan Guzman, Commented Aug 6, 2015 at 1:02

Felix Pamittan · Accepted Answer · 2015-08-06 01:40:04Z

2

As commented, you cannot parameterize your column names and table names. Instead, do string concatenation:

"SELECT " + Column1 + " FROM " + Table1 + " WHERE " + WhereColumn + " =  @WhereValue";

Here is how your code should be:

public static string GetData(string Table1, string Column1, string WhereColumn, string WhereValue)
{
    Table1 = Methods.cleaninjection(Table1); // My injection method that cleans the string

    string sql = "SELECT " + Column1 + " FROM " + Table1 + " WHERE " + @WhereColumn + " =  @WhereValue";

    using (SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["connection"].ConnectionString))
    {
        using (SqlCommand command = new SqlCommand(sql, connection))
        {
            command.Parameters.Add("@WhereValue", SqlDbType.VarChar, 50).Value = WhereValue;

            connection.Open();

            string veri = Convert.ToString(command.ExecuteScalar());
            return veri;
        }
    }
}

Notes:

Please do not use AddWithValue. Use Parameters.Add() instead. According to this article:

There is a problem with the AddWithValue() function: it has to infer the database type for your query parameter. Here’s the thing: sometimes it gets it wrong.

Wrap your object in Using to ensure proper cleanup of resources.
For additional security purposes, you can wrap your column name and table name in square brackets [].

edited Aug 6, 2015 at 1:40

answered Aug 6, 2015 at 1:13

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Bengi Besceli Over a year ago

Okey thank you, but is this secure? And do you mean in your code like this? : WHERE + WhereColumn + " = @WhereValue";

Jon P Over a year ago

Also make sure to cleanInjection pretty much everything except @WhereValue which is the only actual parameter. And you want to be really sure that cleanInjection is up to the task. All in all I think the approach by @mrbengi is inherently flawed.

Felix Pamittan Over a year ago

For additional security purposes, wrap your table names and columns name with [].

Felix Pamittan Over a year ago

I think in terms of security, both methods are the same. The difference is that AddWithValue() has to infer the DB type, which sometimes is wrong and cause performance issues.

Felix Pamittan Over a year ago

The good thing with Add is you can specify the SQL datatype, unlike with AddWithValue. Say, you have a VARCHAR column, and AddWithValue sends your parameter as NVARCHAR. The DB will not convert the param into VARCHAR as it may lead to data loss. Instead, the column will be converted into NVARCHAR, which makes your WHERE clause non-SARGABLE.

|

Collectives™ on Stack Overflow

Method returns empty string in ASP.Net

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related