2

I have been racking my brain over this all day today. I have the following ASP code that uses a Request.Querystring input from a dropdown box to launch a select statement. The querystrinch does show in the ?= URL but will only work on columns in the Microsoft SQL DB that are numeric. I cant lookup names or simple 3 character fields.

CODE:
If Request.QueryString("m") > 0 Then
filterID = Request.QueryString("m")
filterColmn = "imDegree"
  Else filterID = 0
End If

If filterID > 0 Then
     SQlQuery = "SELECT * FROM v_InternImport WHERE iID IN (SELECT iID FROM v_InternImport WHERE " &   filterColmn & " = " & filterID & ")"
  End If
End If

I understand that this select statement as a sub select stament in it but I cant even get a staight reuturn from my DB. The select statement references the same view that populates the main asp page that loads before and the shows fine?

user1188287
  • 25
  • 1
  • 3
  • 1
    Use parameterised queries. This will solve your problem and not leave you open to [SQL injection](http://en.wikipedia.org/wiki/SQL_injection). Absolutely anything can be passed in the query string. – Martin Smith Feb 03 '12 at 19:31
  • How are you determining if it is numeric or a string? `> 0`? That won't work... I don't see a select statement for a string value in your question. – Fosco Feb 03 '12 at 19:33
  • The Select statment is SELECT * FROM v_InternImport WHERE iID IN (SELECT iID FROM v_InternImport WHERE " & filterColmn & " = " & filterID & ")" End If The column is a column in the DB and the filterId is the incoming Querystring. Maybe I should edit the querystring to add the single quote I tried before but didnt work. I am wondering if even though the SQL shows for SELECT * is couuld there be a datatype problem with my DB that is causing this issue. I hope not. – user1188287 Feb 03 '12 at 19:45
  • 1
    Can you step through your code, and copy/paste an example of the value of SqlQuery into your answer for reference? Check to see if the strings you're passing are 'really' the same as what's in the database, and in particular if there are any trailing spaces or anything like that. – Brandon Moore Feb 03 '12 at 20:01
  • @MartinSmith: +1 for your comment but it would have been better as an Answer with just a little more explanation. – AnthonyWJones Feb 05 '12 at 18:44

1 Answers1

1

When you pass a string to SQL Server, you need to surround it with single quotes.

When you pass a number, you don't use the quotes.

So, when you say (summarizing)

SELECT * FROM table WHERE filterColumn = filterID

you should be sending a number.

To match a string: SELECT * FROM table WHERE filterColumn = 'filterID'

This assumes that you have solved any other problems mentioned by the commenters about whether you even have a value in the filterID variable. I heartly concur with the recommendation to use parameterized queries.

Edit: The single quotes go inside the double quotes.

SQlQuery = "SELECT * FROM v_InternImport 
WHERE iID IN (SELECT iID FROM v_InternImport 
WHERE " &   filterColmn & " = '" & filterID & "')"
DOK
  • 32,337
  • 7
  • 60
  • 92
  • Yes I do have values in the filterID variable I can see them in the ASP URL (page.asp?m=XXX) and I added the single quotes to my select statement like this [filterColmn & " = ' " & filterID & " ')"] one before " & and one after the & " surrounding the variable but no luck. – user1188287 Feb 03 '12 at 19:41
  • -1: Any answer that perpeutates dangerous programming techniques that expose a site to a SQL injection atack must be downvoted, sorry. What if query string m resolves to "'); DELETE v_InternImport; KILL ALL_HUMANS; --" ? – AnthonyWJones Feb 05 '12 at 18:35
  • Well, Anthony, I answered the question that was asked. In addition to encouraging the use of parameterized queries in my own answer, there is an upvoted comment recommending the same. In general, I try to actually answer the question, and not make general recommendations about coding best practice, particularly in areas that I don't know much about, like classic ASP. – DOK Feb 05 '12 at 19:01