SQL Variables as Column names in Where Clause [dup

问题:

This question already has an answer here:

• Can I pass column name as input parameter in SQL stored Procedure 8 answers

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?

回答1:

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

回答2:

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.

回答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.

回答4:

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);

回答5:

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)

回答6:

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

回答7:

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

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