Extract column name from select query string using c#?

Question

I'm trying to exclude some column while executing the select query:

string query=@" select c.id,person_id,c.customer_no,c.status,p.first_name,
p.last_name,p.dob as DateOfBirth
from customer c 
inner join person p on p.id=c.person_id";

query = Regex.Replace(query, @"\t|\n|\r", "");
int pFrom = query.IndexOf("select ") + "select ".Length;
int pTo = query.LastIndexOf("from ");
String commaSeperatedFields = query.Substring(pFrom, pTo - pFrom);

string[] IgnoredFields = new string[]{"id", "person_id","DateOfBirth"};
var fieldsWithAlias=commaSeperatedFields.Split(',');
var fields=fieldsWithAlias.Select(x=>(x.Contains(".")?x.Split('.')[1]:x));
fields=fields.Select(x=>(x.Contains(" as ")?x.Split(new string[] { " as " }, StringSplitOptions.None)[1]:x));
var resultArray=fields.Except(IgnoredFields);
var result =String.Join(",",resultArray);

var customQuery=@"select "+result +" from ("+query+")trimmed" ;
Console.WriteLine(customQuery);

Its working as expected, Now I need to add full name by using concat function but I don't know how to extract concated entire field.

Complex Query:

string query=@" select c.id,person_id,c.customer_no,c.status,p.first_name,p.last_name,
p.dob as DateOfBirth,concat(p.first_name,' ',p.last_name) as fullName
from customer c 
inner join person p on p.id=c.person_id";

Please click here to view play ground

It makes no sense -- why would you use C# to modify a query? What is the context of doing something like this -- why not just write the new query? — Hogan, Oct 11 '22 at 15:17
Actually ```IgnoredFields``` is configurable in our application and it would be differ from each client. — Prasanna Kumar J, Oct 11 '22 at 15:19
And ```query``` is the fixed query so I can't make any changes on that. so that I need to recreate customquery — Prasanna Kumar J, Oct 11 '22 at 15:20
You can try [this](https://stackoverflow.com/a/589138/17838896) solution for parsing sql query from c# — Mehdi Kacim, Oct 11 '22 at 15:26
Ok that makes sense that ignoredfields is configurable -- how is it actually stored -- in a table? also how is the merged fields stored in the configuration? — Hogan, Oct 11 '22 at 15:55
It also does not make sense that the list of ignored fields would be a list of ignored fields of a query but instead a list of ignored fields in a table -- isn't that the case? — Hogan, Oct 11 '22 at 15:57
It stored in dotnet core appSetting.json as comma separated string. — Prasanna Kumar J, Oct 11 '22 at 15:57
Currently fullname not in IgnoreList, in case it is in ignore list then it will be as ```FullName``` [Alias Name] — Prasanna Kumar J, Oct 11 '22 at 16:17
Actually, I have a select query with n number of columns then I need to execute that select query with exclude column from specified configuration. So I'm dynamically create new select query without unwanted column. — Prasanna Kumar J, Oct 11 '22 at 16:24
Yes I got all that -- you said "Its working as expected" -- so what is your question then? — Hogan, Oct 11 '22 at 16:30

score 1 · Accepted Answer · answered Oct 12 '22 at 05:54

I have cracked this issue after many attempts: Please find the below code :

string query=@" select c.id,person_id,c.customer_no,c.status,p.first_name,p.last_name,p.dob as DateOfBirth,
                concat(p.first_name,' ',p.last_name) as fullName
                from customer c 
                inner join person p on p.id=c.person_id";
string[] IgnoredFields = new string[]{"id", "person_id","DateOfBirth"};

query = Regex.Replace(query, @"\t|\n|\r", ""); // remove new line character

var reg=new Regex(@"(?is)SELECT(.*?)(?<!\w*"")FROM(?!\w*?"")(.*?)(?=WHERE|ORDER|$)");
var columns=reg.Match(query).Groups[1].Value.Split(new char[]{','},StringSplitOptions.RemoveEmptyEntries);
var fieldsSubString = String.Join(",", columns); // taking string in between select and from keyword

fieldsSubString=Regex.Replace(fieldsSubString, @"(\(|[\s]?(concat\(|min\(|max\(|count\())(.*?)(\)(.*?)([\s|as\s]+))", ""); //remove manipulated columns
var commaSeperatedFields=fieldsSubString.Trim(',').Replace(",,", ","); // remove unwanted commas 
var fieldsWithAlias=commaSeperatedFields.Split(','); 
var fields=fieldsWithAlias.Select(x=>(x.Contains(".")?x.Split('.')[1]:x));
fields=fields.Select(x=>(x.Contains(" as ")?x.Split(new string[] { " as " }, StringSplitOptions.None)[1]:x));
var remains = fields.Except(IgnoredFields);

var customQuery=@"select "+String.Join(",",remains)+" from ("+query+") xc2ty" ;
Console.WriteLine(customQuery);

Please click here to check

score 0 · Answer 2 · answered Oct 11 '22 at 16:28

I don't think this is a good way to implement this kind of customization. It requires a lot of work at run-time and adds complexity to the solution that is not needed. I expect you are not directly involved with the design, but I can't not point out two better solutions that come to mind

Instead of having the "ignore list" in the configuration have the "custom select list". The the code just inserts that string where the old select list was -- easy. The down side here is security -- running code from a file risks injection attacks. I'd argue that the risk isn't that great since to change the config file you would need access to the application server and once an intruder has that access you are already kaput. However, this would not fly with risk adverse situations (eg Banks) since they will lock down every server and even being on the application server might mean they still don't have access to the more secure DB. This solution would bypass that security.
Use a custom view. In this design the select is all fields from a view. During application customization if a different set of fields is needed then a custom view is created that will return those fields. (In this design the client can make any type of fancy fields they want - fullname etc and it does not change the design and integration -- if they can put it in the view they can use it.) This is my recommended solution

Extract column name from select query string using c#?

2 Answers2