Problem parameterizing query using adCmdText 1

[billmack30]

I posted over in ASP forum with no luck. The problem with the one answer I did get was I'd have to change design of table/logic. My job is solely to secure all queries page by page by parameterizing them. Design/Programming changes are not allowed.

Having an issue parameterizing an SQL command.

Backround-
Our website is made up of 3 sections. At any time 0,1,2, or 3 sections could be down for "Maintenance, etc."

The 3 sections are "Football", "Baseball", and "Basketball"

We keep the site status information in a table with the following columns (not designed the best way, but I didn't make it)

Football (have a value of either 'O' for Open or 'C' for Closed)
Baseball (have a value of either 'O' for Open or 'C' for Closed)
Basketball (have a value of either 'O' for Open or 'C' for Closed)
Football_Time (expected time for site to be open if closed)
Baseball_Time (expected time for site to be open if closed)
Basketball_Time (expected time for site to be open if closed)
Football_Msg (message to be displayed if site is closed)
Baseball_Msg (message to be displayed if site is closed)
Basketball_Msg (message to be displayed if site is closed)

The original way we got the site status information was:
sql = "SELECT " & thesite & " AS Status," & thesite & "_Time AS theTime, " & thesite & "_Msg FROM SITESTATUS"

for example
Call SiteStatus("Football") would produce the sql:
"Select Football as Status, Football_Time as thetime, Football_Msg From Sitestatus"

we would have a recordset return
rs("Status") = O or C
rs("thetime") = time site was to be back up

If status is O do nothing, if C display message with time and msg

My job is to secure all queries so I need to parameterize the sql statement.

So I wrote:

sql=""SELECT ? AS Status, ? AS theTime, ? FROM SiteStatus"

Set cmdObj = Server.CreateObject("ADODB.Command")
cmdObj.ActiveConnection = Conn
cmdObj.CommandType = adCmdText
cmdObj.Commandtext = sql
cmdObj.Parameters.Append(cmdObj.Createparameter("@THESITE",adChar,adParamInput,10,thesite))
cmdObj.Parameters.append(cmdObj.Createparameter("@THETIME",adChar,adParamInput,15,thesite & "_Time"))
cmdObj.Parameters.append(cmdObj.Createparameter("@THEMSG",adChar,adParamInput,14,thesite & "_Msg"))
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3 'adUseClient
rs.CursorType = 0 'adOpenForwardOnly
rs.LockType = 1 'adLockReadOnly
Set rs = cmdObj.Execute

The values I get back from the recordset are:
rs("Status") = Football (supposed to be O or C)
rs("thetime") = Football_Time (supposed to be datetime)

So now the test:
status = rs("Status")
IF TRIM(Status)="O" THEN
EXIT SUB
ELSE
//DISPLAY MESSAGE
END IF

Always displays the message even if the site is open. Any ideas?

 

[ESquared]

What you're trying to do is not going to work. You don't actually HAVE any parameters. That is, it's not that you are retrieving values into parameters or passing in values, you actually wish to use a different column object. Do this in query analyzer:

SELECT 'Football' FROM SiteStatus
SELECT Football FROM SiteStatus

You're trying to parameterize the column name, but the column name is a sysname. It isn't the varchar string 'Football.'

It would be instructive if you ran SQL Profiler, tried your statement, and looked at the resulting command actually issued to the SQL Server. (You'll need the "RPC started/completed" event to see the actual command with its parameters). It will be something like this:

exec sp_executesql N'SELECT @P1 AS Status, @P2 AS theTime, @P3 FROM SiteStatus', N'@P1 char(10), @P2 char(15), @P3 char(14)', 'Football', 'Football_Time', 'Football_Msg'

-- or effectively:

SELECT 'Football' AS Status, 'Football_Time' AS theTime, 'Football_Msg' FROM SiteStatus

Which will return exactly what you told it to return, three varchar values. There are no column names in your query!

[tt]Status theTime (unnamed column)
Football Football_Time Football_Msg[/tt]

As you said, the original way we got the site status information was:
sql = "SELECT " & thesite & " AS Status," & thesite & "_Time AS theTime, " & thesite & "_Msg FROM SITESTATUS"

This is correct. No parameterization is necessary. I could show you how to use a CASE statement to return the correct column based on a parameter, but this is a pointless exercise for this query. Put it back the way it was and use it. It's a function of your table design, having data in a single row instead of in columns:

rows:
[tt]Football Baseball Basketball ...[/tt]

columns:
[tt]Sport Status Time Msg
Football O ... ...
Baseball O ... ...
Basketball C ... ...[/tt]

NOW you can parameterize it with

'SELECT Status, Time, Msg FROM SiteStatus WHERE Sport = ?'

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[billmack30]

You're trying to parameterize the column name, but the column name is a sysname. It isn't the varchar string 'Football.'

Lightbulb goes on. I'll have to leave it. Unfortunately, my job is to secure our pages and I am not allowed to make any changes except for the usp calls. Although this is one of those times when you read someone else's code and go "Why in the world would they set it up like that?

 

[PWise]

try this

Select * from (
Select Football as Sport,Football_Time,Football_Msg from
SiteStatus 
union 
select Baseball, Baseball_Time,Baseball_Msg from
SiteStatus 
union 
Select Basketball ,Basketball_Time ,Basketball_Msg from
SiteStatus )SportsStatus 
where SportsStatus.Sport =?

 

[billmack30]

Although that might work, I don't want to reinvent the wheel. I've decided to use a Case statement to make sure only "Football", "Basketball", or "Baseball" are being passed as a parameter. Anything else will get kicked out. It isn't 100% what I wanted but it accomplishes my main goal of preventing SQL injection.

 

[ESquared]

billmack30,

I think that's a reasonable way to handle it. To slavishly follow a requirement to "parameterize everything" could just slow your application down for no real security improvement.

Another reasonable approach is using a stored procedure. Although you said you can't change anything.

CREATE PROCEDURE ReturnSiteStatus @Sport varchar(15)
AS
IF @Sport NOT IN ('Football', 'Basketball', 'Baseball') BEGIN
   RAISERROR ('You must specify a valid sport.', 16, 1)
   RETURN -1
END
IF @Sport = 'Football'
   SELECT Football, Football_Time, Football_Msg FROM SiteStaus
ELSE IF @Sport = 'Basketball'
   ...
ELSE IF @Sport = 'Baseball'
   ...