Need dynamic column names based off dynamic table name

[RSBLaird]

I am afraid of 'SQL Injection' problems, but don't know a good way to go about this with out using dynamic SQL.

Here's what I need. I am creating a "data dictionary" of sorts. The person who requested this, requested that we put a link within the report (SSRS '05) to another report showing details of the "look up table" for each column that has a look up code.

Of course I do all my work within the SQL query so that's why I'm on this forum...I wouldn't dare do this in the reporting side.

So, say you have the Employees table -
Within this table are lookup values for
Cost Centers
Departments
Supervisors

--"Cost Centers" & "Departments"
have column names of 'Abbrv', 'Name', 'Description'
--But "Supervisors"
has column names of 'Code', 'Name', 'Description'

I need all three columns of each table, but it depends on the table as to which columns it possesses. And there are too many look up tables in the system to do a case statement for which table has which columns.

I've looked all over and tried many different things.
And like I said I still am afraid of what I keep hearing about SQL Injections...as this report that will utilize whatever code (Procedure) I come up with will be accessable on our Report Manager.

Anybody got an clever suggestions?

I tried this to get the column names for the given table:

--drop table #test
declare @TableName varchar(200)
SET @TableName = 'EMPLOYEES'


--get column names for particualr table

SELECT name
INTO #Test
FROM sys.columns 
WHERE [object_id] = OBJECT_ID(@TableName)
AND Name in ('Code','Abbreviation','Name','Description');

					
SELECT DISTINCT	ColumnName = substring((SELECT DISTINCT ( ', ' + [Name] )
										FROM #Test 
										FOR XML PATH( '' )),3,1000)

Works great for getting the column names...but don't know where to go from here. *sigh*

Any help would be so greatly appreciated.

Thank you,
RSBLaird

 

[RSBLaird]

To avoid confusion, I should have made the sample table one of the lookup tables I referred to...so the code should have looked like this instead.

I am afraid of 'SQL Injection' problems, but don't know a good way to go about this with out using dynamic SQL.

Here's what I need.  I am creating a "data dictionary" of sorts.  The person who requested this, requested that we put a link within the report (SSRS '05) to another report showing details of the "look up table".

Of course I do all my work within the SQL query so that's why I'm on this forum...I wouldn't dare do this in the reporting side.

So say you have the Employees table - 
Within this table is a lookup value for 
Cost Centers
Departments
Supervisors

--"Cost Centers" & "Departments" 
have column names of 'Abbrv', 'Name', 'Description'
--But "Supervisors" 
has column names of 'Code', 'Name', 'Description'

I need all three columns of each table, but it depends on the table as to which columns it possesses.  And there are too many look up tables in the system to do a case statement for which table has which columns.

I've looked all over and tried many different things.
I tried concatenating all the column names from the givein table , but don't know how it fits into a select statement.
And like I said I still am afraid of what I keep hearing about SQL Injections...as this report that will utilize whatever code (Procedure) I come up with will be accessable on our Report Manager.

Anybody got an clever suggestions?

I tried this to get the column names for the given table:
[code]
--drop table #test
declare @TableName varchar(200)
SET @TableName = 'Cost_Center_lk'--'department_lk'


--get column names for particualr table

SELECT name, [object_id]
INTO #Test
FROM sys.columns 
WHERE [object_id] = OBJECT_ID(@TableName)
AND Name in ('Code','Abbreviation','Name','Description');

					
SELECT DISTINCT	ColumnName = substring((SELECT DISTINCT ( ', ' + [Name] )
										FROM #Test 
										FOR XML PATH( '' )),3,1000)

Works great for getting the column names...but don't know where to go from here. *sigh*

Any help would be so greatly appreciated.

Thank you,
RSBLaird
[/code]

sorry for any confusion, and I hope this all makes sense to someone out there.

~RSBLaird

 

[gmmastros]

I don't know how this interacts with SSRS, but...

I would create a stored procedure where you pass in the table name. There are several ways to prevent SQL Injection. The strictest (and probably the safest) way is to compare your table name with a hard coded list. Ex:

Declare @TableName VarChar(200)

Set @TableName = 'department_lk'

-- Check your list of table names.

If @TableName = 'department_lk'
  Select * From department_lk
Else If @TableName = 'Cost_Center_lk'
  Select * From Cost_Center_lk

If someone tries to 'sql inject' this code, they will NOT get any data they shouldn't, and they will NOT be able to do any damage to your database.

Alternatively, you could check the name of the table against the list of tables. Ex:

Declare @TableName VarChar(200)

Set @TableName = 'department_lk'

Declare @SQL VarChar(1000)

If Exists(Select * From Information_Schema.Tables Where Table_Name = @TableName)
  Begin
    Set @SQL = 'Select * From [' + @TableName + ']'
    Exec (@SQL)
  End

The second example ought to be pretty safe, too. The only potential problem would occur if you had a really strange table name. This, of course, could occur if someone SQL Injects somewhere else to create a bad table name (like "[!]Blah]; Drop Database Whatever[/!]").



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RSBLaird]

Thank you George, for your input...I appreciate your response.
That will definitely help with the dynamic nature of the Table, and I had something similar, but I think your code will be better. and at least I feel better about the SQL Injection potential. Thank you.

However, my BIGGEST problem is still the dynamic columns.
I just don't know how to even approach the columns issue.

Anyone?

 

[gmmastros]

I don't ordinarily recommend it, but....

Don't you want to return all the column? Select *

Alternatively, you could "normalize" your column names.

In your original question, you said:

--"Cost Centers" & "Departments"
have column names of 'Abbrv', 'Name', 'Description'
--But "Supervisors"
has column names of 'Code', 'Name', 'Description'

Basically, 2 tables have Abbrv, and one has Code. What you could do is 'create' a column in the supervisors table named Abbrv. Then, all 3 tables would have the same structure.

Please keep reading.

I'm not suggesting that you actually add another "real" column to the table. What you could do is create a computed column that simply returns the data from the code column.

Ex:

Alter Table Supervisors Add Abbrv As Code

This will create a computed column. In this case, there really isn't any computation involved, but it will appear as though the Abbrv column exists. Whenever you change the data in the CODE column, Abbrv will reflect that change immediately.

If you create the necessary computed columns in all the tables involved, you should be able to completely avoid dynamic sql (and therefore SQL Injection).


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RSBLaird]

This could work theoretically...but honestly the three tables I gave as examples, were just that, examples. There are MANY, MANY tables that this has to apply to , and as it is, I don't know which ones have the Abbrv and which ones have the CODE fields...I just know that they are not consistant through out all these look up tables...I've just been instructed to get these values from all of the look up tables in the database.

But I think you've definitely given me some ideas I think I might be able to pull this off afterall. I do not have the capacity to alter all these tables, but there might be some way around it.

Thanks again!

Hope you have a great day.

~RSBLaird

 

[gmmastros]

Another thought....

You could create a view for each table that has the columns you are looking for.

Ex:

Create View vw_Report_Supervisor
As
Select Code As Abbrv, Name
From   Supervisor

Create View vw_Report_Whatever
As
Select EyeColor As Abbrv, Name
From   People

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom