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

CodePudding user response：

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

CodePudding user response：

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