1

Is it possible to add the alias of a column as an SqlParameter to an SQL command? If so, how is it specified?

As a simplified example, say I have an SQL command that is constructed like this:

SqlCommand GetCommand(string columnName)
{
    string filter = String.Format("SELECT MyColumn1 AS '{0}' FROM MyTable", columnName);

    return new SqlCommand(filter);
}

This command does nothing to prevent an SQL Injection attack, so I want to follow the standard procedure and parameterize the command.

I'm used to converting statements in the WHERE clause to use parameters. The statements look similar to the above, for example:

SqlCommand command("SELECT * FROM MyTable WHERE name = '{0}'", name);

When I convert this, it becomes:

SqlCommand command("SELECT * FROM MyTable WHERE name = @name");
command.Parameters.Add(new SqlParameter("name", SqlDbType.NVarChar) { Value = name });

That works well. Taking the same approach here with the GetCommand() method gives:

SqlCommand GetCommand(string columnName)
{
    string filter = "SELECT MyColumn1 AS @columnName FROM MyTable";

    SqlCommand command = new SqlCommand(filter);
    command.Parameters.Add(new SqlParameter("columnName", SqlDbType.NVarChar) 
        { Value = columnName });

    return command;
}

But, when the command is executed, it results in:

An exception of type 'System.Data.SqlClient.SqlException' occurred in MyApplication.exe but was not handled in user code

Additional information: Incorrect syntax near '@columnName'.

I see there are plenty of questions on SO and the web in general about use of SqlParameter, but seemingly none that touch on their use with column aliases.

There's no indication in Microsoft's SqlParameter documention either. From this, I noticed that the SqlParameter.Direction property defaults to ParameterDirection.Input; the column alias is used in output, so I tried InputOutput and Output, but it made no difference to the results.

Improve this question
2
  • 1
    Why do you need to pass in the alias for a column? You can't pass in the alias as a parameter. I can't for the life of me come up with why this would be needed anyway. The alias is just used to name the column in the result set. If this is for display there are better ways of handling that. Commented Sep 26, 2018 at 16:09
  • @SeanLange This is the way the software I've inherited works. There are multiple apps involved and at the moment I want to secure it by changing as little as possible. Commented Sep 26, 2018 at 16:24

4 Answers 4

Reset to default
3

Short answer: you can't.

Column Aliases are not parameterizable. They are identifiers in the SQL language, not values - just like the column name itself, or the table name.

"Get me column X from table Y and name it Z in the result set." None of X, Y or Z are parameterizable.

Note that this is not a limitation of SqlParameter but of the SQL language as implemented by Sql Server.

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

Comments

2

Parameters are not designed for aliasing TSQL columns. If you need an alias, just give it one in the TSQL. Additionally, the In/Out aspect of the parameter is for cases where the query modifies the parameter during running. Such as an output parameter of a stored procedure.

In truth, what it appears you're trying to do is get a dataset where the returned column name is based upon an inputted value.

I would use a data adapter to fill a data table, and then just rename the column to the desired value. 

dataTable.Columns["MyColumn1"].ColumnName = columnName;
Improve this answer

2 Comments

This is a good answer and good suggestion of how to work around the problem. But I'm interested to know what your evidence is that parameters are not designed for aliasing?
The documentation describes them as objects to pass values to a sql statement or stored procedure. And an alias is not a value from a sql point of view. learn.microsoft.com/en-us/dotnet/framework/data/adonet/…
0

Strange thing to do, but you can build some sql with the parameter and then exec it.

Declare @sql VarChar(255)
Set @sql = 'SELECT ClientNumber AS ' + @columnName + ' FROM Ib01'
Exec(@sql)
Improve this answer

4 Comments

This technique is still vulnerable to SQL injection. Would be a good idea to use the QUOTENAME function - Set @sql = 'SELECT ClientNumber AS ' + QUOTENAME(@columnName) + ' FROM Ib01'
this would work in a SQL Stored Procedure, but not in C# code. Additionally, you still have issues with SQL Injection as @columnName is coming from the user experience, and running an EXEC on a parameter passed into a stored procedure opens you up to such.
Even if you use the Parameters collection on the Query object? You sure?
Yes even using the parameters because this just builds up a string and executes it.
0

You can't parameterize schema names and aliases. If you must have dynamic aliases, you'll need to protect yourself from SQL injection in the application layer.

Consider creating a whitelist of valid aliases, and only select from the whitelist.

If the alias comes from user input, you'll have to validate/sanitize the input.

Edit: It's a bad practice to rely on the application layer to prevent SQL injection attacks. But sometimes business drivers or other reasons force this to happen. My suggestions here are ways to mitigate the risk of SQL injection if you are forced to do this.

Improve this answer

2 Comments

You should NEVER us the application layer as the protection from sql injection. It should be done at the data layer. You can use the application also but trusting the application is a dangerous path.
This is an important point, and I agree whole heartedly. That's why I qualified my statement with "if you must have dynamic aliases" - I don't assume that OP has control over these decisions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.