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");
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.
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();
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.
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;
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.
