c# - Creating a parametrized field name for a SELECT clause -

i'm working on data api , generate sql query based on requested field names. field names publicly available , users able use alias rename data returned api.

the records available in tables won't filed "top secret" still want prevent sql injection using parametrized field name , alias avoid people adding unwanted data.

declare @requestedfieldname nvarchar(max) = 'firstname';

declare @alias nvarchar(max) = 'first name';

select @requestedfieldname @alias mytable

there's plenty of examples using parameters in clauses , other clause involving value matched/assigned/set field... however, couldn't find example involving parametrized field name / alias in sql server (there's question jdbc , mysql none sql server)

is there way parametrized field name or should consider building intermediate interface hold list of every available fields user can request? (i know second option used lot have lot of table , structure change on regular basis).

use varchar parameters columns list , table name.
check values of parameters against sys.columns table.
if columns match can safely use values create dynamic sql , execute it. if need user pass conditions build clause, same test columns there.

update i've created sample code, think not best solution you, unless can find way dynamically create stored procedure each table. reason clause, , fact each table have different number of columns different data types, making creation of clause parameter list table dependent. (and actually, creation of number of select parameters dependent on table).
therefore, i'm not sure practical solution, fun t-sql challenge me nevertheless, i've created code sample, , perhaps find way use such procedure, i'll share it:

first, ddl sample table:

create table [dbo].[tbldays](     [day_id] [int] identity(1,1) not null,     [day_date] [date] not null,     [day_name] [nchar](10) not null,  constraint [pk_tbldays] primary key clustered  (     [day_id] asc ) 

and sample safe dynamic sql stored procedure:

create procedure dynamicalyselectfromtbldays (     @errormessage varchar(100) output,     @selectcol1 sysname,     @selectcol2 sysname = null,     @selectcol3 sysname = null,     @wherecol1 sysname = null,     @wherecol2 sysname = null,     @wherecol3 sysname = null,     @wherevalue1 int = null,     @wherevalue2 date = null,     @whervalue3 nchar(10) = null )  declare @exceptedcolumncount int,         @actualcolumnscount int,         @tablename sysname = 'tbldays',         @sql varchar(max)  -- number of columns expcected sys.columns select @exceptedcolumncount = count(*)  (values (@selectcol1), (@selectcol2), (@selectcol3)) x(a) not null   -- number of columns sys.columns select @actualcolumnscount = count(*) sys.columns c inner join sys.tables t on(c.object_id = t.object_id) t.name = @tablename , c.name in(@selectcol1, @selectcol2, @selectcol3)  -- if of non null columns sys.columns table if @exceptedcolumncount = @actualcolumnscount , (@exceptedcolumncount = 0 or @actualcolumnscount > 0) begin      -- same test clause columns     select @exceptedcolumncount = count(*)      (values (@wherecol1), (@wherecol2), (@wherecol3)) x(a)     not null       select @actualcolumnscount = count(*)     sys.columns c     inner join sys.tables t on(c.object_id = t.object_id)     t.name = @tablename     , c.name in(@wherecol1, @wherecol2, @wherecol3)      if @exceptedcolumncount = @actualcolumnscount , (@exceptedcolumncount = 0 or @actualcolumnscount > 0)     begin          -- dynamically build sql statement:         set @sql = 'select '+ coalesce(@selectcol1 +', ', '') + coalesce(@selectcol2 +', ', '')+ coalesce(@selectcol3, '') +                    ' '+ @tablename           if coalesce(@wherecol1, @wherecol2, @wherecol3) not null         begin              set @sql = @sql + ' '             if @wherecol1 not null                  set @sql = @sql + @wherecol1 +' = '+ cast(@wherevalue1 varchar(10))             if @wherecol2 not null                  set @sql = @sql + ' , ' + @wherecol2 +' = '''+ convert(char(10), @wherevalue2, 120) +''''             if @wherecol3 not null                  set @sql = @sql + ' , ' + @wherecol3 +' = '''+ @whervalue3 +''''         end          print @sql         -- exec(@sql) -- commented out since it's better print first , @ results, , execute it.     end     else     begin         -- perhaps using raise error instead suit needs better         select @errormessage = 'where columns not match table columns'     end end else begin     -- perhaps using raise error instead suit needs better     select @errormessage = 'select columns not match table columns' end  go 

and of course, sample execute code:

declare @errormessage varchar(100),         @selectcol1 sysname = 'day_id',         @selectcol2 sysname = 'day_date',         @selectcol3 sysname = 'day_name',         @wherecol1 sysname = 'day_id',         @wherecol2 sysname = 'day_date',         @wherecol3 sysname = null,         @wherevalue1 int = 1,         @wherevalue2 date = convert(date, '14/04/2015', 103),         @whervalue3 nchar(10) = null  exec dynamicalyselectfromtbldays    @errormessage output,                                      @selectcol1,                                      @selectcol2,                                      @selectcol3,                                     @wherecol1,                                     @wherecol2,                                     @wherecol3,                                     @wherevalue1,                                     @wherevalue2,                                     @whervalue3   print @errormessage 

and result of mess this:

select day_id, day_date, day_name tbldays day_id = 1 , day_date = '2015-04-14' 

you can columns list each table c# code , build dynamic query there. shorter , faster code.