SQL Variables as Column names in Where Clause

Question

I need some help with my SQL logic, and I've been working (and researching) this for 2 days now with zero success.

My goal is to try an pass a variable from an ASP page to a stored procedure, which is utilizing the variable as criteria for a column name in the where clause.

So for example (a simplified version of my query):

@strDept nvarchar(10), @strUser nvarchar(30)
-- The asp page will pass f18 to @strDept & Ted Lee to strUser
-- f18 is the column name in my database that I need in the where.

select x, y, z from table1 where @strDept in (@strUser)
-- and this is the select statement, notice the where clause.

The stored procedure does execute, but it returns no values and I know its treating the @strDept as a literal nvarchar and not a column name.

So I guess my question is, how do I get SQL Server 2005 to treat my @sqlDept variable as a column name?

No, @struser is referencing a textbox with a string in it, which is being passed to the stored produced. — SyD, Aug 21 '13 at 14:39

score 7 · Answer 1 · answered Aug 21 '13 at 14:46

7

The reason you can't find guidance on how to do this is that it's a really bad idea.

Sooner or later, someone is going to pass a "column name" of 1 ;drop database badidea. Which will be a blessing for all concerned.

Read up on SQL Injection, and rethink your design.

answered Aug 21 '13 at 14:46

podiluska

50,950
7
98
104

1

Yep, every avenue regarding dynamic SQL has led me down a 1-way SQL injection street. But here's the thing, the user can't pass anything to the stored procedure explicitly - is this still open to an injection attack even if the page alone is passing the variable? – SyD Aug 21 '13 at 14:51
If the parameter is tightly controlled, then no. But here's what happens. You deploy it. Then sooner or later, someone says "can we choose any field we like?", and you're not in, and someone says "Oh, that looks like a simple change. I'll just tweak this bit.. – podiluska Aug 21 '13 at 14:59
@SyD Besides, you said `No, @struser is referencing a textbox with a string in it`. – podiluska Aug 21 '13 at 15:12
1

I hear what you're saying, and there is someone here (my non-programming manager) that has done just that in a different sense in my absence. It did screw something up, but it was a simple fix for me, however the company was at a stand still for the 1-hour I was gone. – SyD Aug 21 '13 at 15:26

score 5 · Accepted Answer · answered Aug 21 '13 at 17:20

If this is an internal company application why is everyone re-iterating and beating SQL Injection to death... Its very simple to just use Dynamic SQL. If you are comfortable that these are only internal users using this then its very simple. Here is the concept. You essentially write a SQL Statement that writes a string that is really a SQL statement and then execute it.

CREATE Procedure myDynamicProcedure
@strDept nvarchar(10), 
@strUser nvarchar(30)

as 

BEGIN

1. Declare a variable to store the SQL Statement.

 DECLARE @SQL varchar(max)

2. SET your @SQL Variable to be the SELECT Statement. Basically you are building it so it returns what you are wanting to write. Like this:

   SET @SQL = 'select x, y, z from table1 where' + @strDept + 
 ' in ' + @strUser

3. Execute the @SQL Statement and it will be exactly like you ran: SELECT x,y,z from table1 where f18 = 'Ted Lee'

EXEC (@SQL)
END

Users are just clicking a link, the page is written to display what they need. The injection portion is done by my code and its why I'm considering he dynamic procedures. Btw - Thank you, this looks exactly like what I was trying to write. — SyD, Aug 21 '13 at 17:39
@SyD - no problem sir... some of the answers were somewhat confusing. Of course had you needed this for a public facing website I would agree 100% with all the injection safety features. Best of luck to you! — logixologist, Aug 21 '13 at 21:11

score 3 · Answer 3 · answered Aug 21 '13 at 14:35

3

Why do you want to make column name dynamic? What do you plan to achieve? You can use dynamic query like answer above but injection attacks may start.

If you explain what you want to do with that maybe we can recommend another solution.

answered Aug 21 '13 at 14:35

Kuzgun

4,649
4
34
48

1

Basically, My company needs a dashboard of reports. The dashboard will have a manager view & individual view. Essentially pipelines, but the problem is that the database has different fields for departments, and these fields have the names of the individual employees. The easiest solution is to just pass an number to the procedure and copy and paste a bunch of IF statements with the correct field, but that is time consuming and messy. I wanted to dynamically enter the column reference based on the departmental asp page. – SyD Aug 21 '13 at 14:45

HotblackDesiato · Answer 4 · 2013-08-21T14:59:42.343

1

You can use some dynamic sql e.g.

DECLARE @sqlDept VARCHAR(100)='CURRENT_TIMESTAMP';

EXEC('SELECT '+@sqlDept)

In your case this will be

DECLARE @strDept nvarchar(10)='dept1'
,@strUser nvarchar(30)='user1';

DECLARE @DynamicSql nvarchar(1000);

SET @DynamicSql='select x, y, z from table where '+@strDept+' in ('''+@strUser+''')';

Then

SELECT @DynamicSql;

Will give you:

select x, y, z from table where dept1 in ('user1')

To execute this statement you do this as

EXEC(@DynamicSql);

edited Aug 21 '13 at 14:59

answered Aug 21 '13 at 14:32

HotblackDesiato

350
1
8

I'm not sure that I understand what the above code is doing - can you please explain? – SyD Aug 21 '13 at 14:36
I agree with the other comments about injection, but I've updated the answer to be a little clearer. – HotblackDesiato Aug 21 '13 at 15:00

Fabien TheSolution · Answer 5 · 2013-08-23T17:22:45.243

0

I think the best way is to build a dynamic SQL and add a lookup to see if the column exist and prevent SQL injection in the column name.

declare @strDept nvarchar(10), @strUser nvarchar(30), 
        @sql nvarchar(300), @found smallint
set @strDept = 'f18'
set @strUser = 'Ted Lee'

set @found = (SELECT count(*) 
              FROM syscolumns 
              WHERE id=OBJECT_ID('table1') AND name=''+@strDept+'')

set @sql = 'select x, y, z from table1 where ' + @strDept + ' in ('''+@strUser+''')'

if @found = 1 exec (@sql)

SQL injection testing : See SQL FIDDLE : http://www.sqlfiddle.com/#!6/df3f6/18/0

edited Aug 23 '13 at 17:22

answered Aug 21 '13 at 15:30

Fabien TheSolution

5,055
1
18
30

1

Unless they are crazy enough to use a column called [1; DROP TABLE USERS] – StingyJack Aug 21 '13 at 17:16

score 0 · Answer 6 · answered Aug 21 '13 at 17:15

Another alternative is to use a small bit of substitution in the proc. This still uses dynamic SQL, but you are never executing user supplied values.

DECLARE @userSuppliedValue VARCHAR(50) = 'JOHNNY DROP TABLES'


DECLARE @substValue VARCHAR(50)

IF @userSuppliedValue = 'Table1'
  SET @substValue = 'Table1'

IF @userSuppliedValue = 'Table2'
  SET @substValue = 'Table2'

/*Repeat for N permutations*/

/* Throw an error if you think its necessary to do so when no match is found*/
IF @substValue IS NULL
  RAISERROR(1,1,'errah')

EXEC ('SELECT * FROM ' + @substValue)

Very interesting methodology, thank you. :) – SyD Aug 21 '13 at 17:37 — SyD, Aug 21 '13 at 17:37

score 0 · Answer 7 · answered Jan 03 '15 at 11:25

0

DECLARE @value varchar(10)  
SET @value = 'intStep'  
DECLARE @sqlText nvarchar(1000); 

SET @sqlText = N'SELECT ' + @value + ' FROM dbo.tblBatchDetail'
Exec (@sqlText)

answered Jan 03 '15 at 11:25

IR.Programmer

119
3
4

SQL Variables as Column names in Where Clause

7 Answers7

Linked