Problem using variable for column name 3

[scuttleButt]

May be a stupid question but I am trying to create a stored procedure which I pass 2 parameters, 1 for the column name and 1 for the column value. I am working in SQL Server 2000 using vbscript on asp pages.

If I create a stored procedure in SQL like this:

CREATE PROCEDURE crFiltered
AS
select s.lName, s.fName, d.shortName as dir, dt.shortName as dept, ds.shortName as subs, c.compName as compname, cs.compDate, dateAdd(day,c.compExp,cs.compDate) as expDate
from crCompStat cs
LEFT JOIN crComp c ON (cs.compID = c.compID)
LEFT JOIN crStaff s ON (cs.staffID = s.staffID)
LEFT JOIN aDirs d ON (s.dirID = d.dirID)
LEFT JOIN aDepts dt ON (s.deptID = dt.deptID)
LEFT JOIN aSubs ds ON (s.subsID = ds.subsID)
WHERE cs.dirID = 2
order by cs.dirID, cs.deptID, cs.subsID,cs.compID
GO

it works fine... but if I change the where clause to this:

CREATE PROCEDURE crFiltered
@theField varchar(10), @theValue int
AS
select s.lName, s.fName, d.shortName as dir, dt.shortName as dept, ds.shortName as subs, c.compName as compname, cs.compDate, cs.compExp as expDate
from crCompStat cs
LEFT JOIN crComp c ON (cs.compID = c.compID)
LEFT JOIN crStaff s ON (cs.staffID = s.staffID)
LEFT JOIN aDirs d ON (s.dirID = d.dirID)
LEFT JOIN aDepts dt ON (s.deptID = dt.deptID)
LEFT JOIN aSubs ds ON (s.subsID = ds.subsID)
WHERE @theField = @theValue
order by cs.dirID, cs.deptID, cs.subsID,cs.compID
GO

I get the error: "error converting the varchar value 'cs.dirID' to a column of data type int" when I call the page in the browser. I am passing values from an asp page...

http://www.someplace/somefolder/somefile.asp?theField=cs.dirID&theValue=2

I've tried doing a CINT() in the asp page on "theValue" before passing it through but still doesn't work.... Any ideas what I am doing wrong?

 

[cmmrfrds]

In order to have a variable column name the select statement needs to be put into a variable and then the variable executed.

declare sqlvar varchar(2000)

set sqlvar = 'select s.lName, s.fName, d.shortName as dir, dt.shortName as dept, ds.shortName as subs, c.compName as compname, cs.compDate, dateAdd(day,c.compExp,cs.compDate) as expDate
from crCompStat cs
LEFT JOIN crComp c ON (cs.compID = c.compID)
LEFT JOIN crStaff s ON (cs.staffID = s.staffID)
LEFT JOIN aDirs d ON (s.dirID = d.dirID)
LEFT JOIN aDepts dt ON (s.deptID = dt.deptID)
LEFT JOIN aSubs ds ON (s.subsID = ds.subsID)
WHERE ' + @theField ' = ' + @theValue +
'order by cs.dirID, cs.deptID, cs.subsID,cs.compID'

exec (sqlvar)

 

[EdVassie]

The response given by cmmrfrds will work, but opens up a VERY big security hole.

The problem is known as 'SQL Injection'. This allows a malicious user to add additional code to the data they enter to bypass security. Try running the above code with the value of @theValue set to "dummy';xp_cmdshell 'dir C:\' --"

Similar techniques can be used to drop databases, format disks, etc. It might be wise to have a site standard prevnting the use of exec.

You can achieve what you want but with good security by using sp_executesql instead of exec. sp_executesql allows you to supply the changing data using parameters. BOL shows you how to use sp_executesql. Using sp_executesql with parameterisation prevents the security problem shown above.



This posting is a personal opinion only, and may not reflect reality.

 

[scuttleButt]

Thank you... BOTH posts were very helpful