CODEWITHSUNDEEP
c#sql.netsql-server
Mike
Mike

Reputation: 101

Query a SQL table with string list of column names

I have two questions 1. I have a list of string which is the column names. How can i use it in the SQL query? 2. Is the method prone to SQL injection?

This is what i have right now,

List<string> Columnnames = new List<string>();

 cmd = new SqlCommand("Select "+Columnnames+" from test");

Upvotes: 2

Views: 2293

Answers (5)

Marc Gravell
Marc Gravell

Reputation: 1062855

If the column names are coming from an outside source, then yes, you would have to concatenate them into the string. You should ideally use [/], but that just allows for column names with spaces etc - it doesn't change anything about SQL injection, and yes: allowing column names to be specified by a user would be a SQL injection risk, unless you white-list them first. So perhaps:

if(Columnnames.Count == 0) throw new ArgumentException(
    "You need to specify at least one column");

var sql = new StringBuilder("select ");
bool first = true;
foreach(var name in Columnnames) {
    if(!IsKnownColumnName(name)) { // <=== very important test
        throw new ArgumentException("Invalid column name: " + name);
    }
    sql.Append(first ? "[" : ",[").Append(name).Append("]");
    first = false;
}
sql.Append(" from test");
...
cmd.CommandText = sql.ToString();

Upvotes: 1

heikkim
heikkim

Reputation: 2975

You should concatenate the column names to the query so that each column name is separated with a comma from the next, e.g. (pseudo)

define sql = "SELECT "

for columnName in columnNames:
    concatenate sql,columnName

    if columnName is not last:
        concatenate sql,","
    end if
end for

concatenate sql," FROM test"

Vulnerability depends on the source of the column names. If you have internal immutable list(s) of column names, then there's no risk but if they somehow originate from an external source, such as a user using the application, then it certainly is prone to SQL-injection.

Upvotes: 0

marcel
marcel

Reputation: 313

First check if Columnames haves any value. Second make a function to create something like dus columna, columnb

SqlCommend should look like this select columna , columb from test;

Upvotes: 0

Gavin Sutherland
Gavin Sutherland

Reputation: 1686

To answer your questions:

Question 1 - change your code to something like:

List<string> Columnnames = new List<string>();

// Code that populates Columnnames here

cmd = new SqlCommand("Select " + string.Join(",", Columnnames) + " from test");

Question 2 - depends on how you are populating Columnnames. If this is being populated from input over the web then yes.

Upvotes: 1

Jainendra
Jainendra

Reputation: 25143

You can convert list of Columnnames to comma separated column values. Like this:

string columns = Columnnames.Aggregate((x,y) => x + "," + y);
cmd = new SqlCommand("Select "+columns+" from test");

This method is prone to sql injection as someone can easily send column name like column ;drop database yourDB so its a bad idea.

Upvotes: 0

Related Questions