Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Regular Coder
    Join Date
    May 2003
    Location
    Stockholm, Sweden
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Request.QueryString in a SELECT statement?

    Is it possible to use a Request.QueryString in an
    SELECT statement in the SQL?

    I tried like this:

    Code:
    rs.open "select * from [Request.QueryString("competition")] order by [POINTS] desc, [POS] asc", conn
    I searched for something like this on the forum and on google
    but couldn't find anything about it, probably there is a much better way than the one I'm trying but hey I'm only trying to learn ;)

    I have a link that I want to open a competition
    Code:
    <a href="show.asp?competition=comp1">Competition 1</a><br>
    <a href="show.asp?competition=comp2">Competition 2</a>
    the links are on a page named comp.asp and
    the SQL is on a page named show.asp

    /Speedy

  • #2
    Senior Coder A1ien51's Avatar
    Join Date
    Jun 2002
    Location
    Between DC and Baltimore In a Cave
    Posts
    2,717
    Thanks
    1
    Thanked 94 Times in 88 Posts
    you almost had it

    Code:
    rs.open "select * from [" & Request.QueryString("competition") & "] order by [POINTS] desc, [POS] asc", conn
    Eric
    Tech Author [Ajax In Action, JavaScript: Visual Blueprint]

  • #3
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    Request.QueryString("FieldName") simply returns a string, and can therefore be appended to any other string in the usual way.

    However, you really SHOULD NOT be using querystring values like this without performing some checks first - there are SERIOUS security implications....

    http://www.sitepointforums.com/showthread.php?t=60643
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #4
    Regular Coder
    Join Date
    May 2003
    Location
    Stockholm, Sweden
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you guys,

    M@rco thanks for introducing me to the SQL security issues,
    I'll read them and try to improve my coding skills to be more secure

    /Speedy

  • #5
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    Yes, the risk is quite large, that peticular flaw is called an SQL Injection Attack because someone could alter your query to do something really bad.
    OracleGuy

  • #6
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    check your querystring before using the val in the sql statement... Avoid any char that could be interpreted as being then part of your sql statement.
    If you expect numeric, then do something like:
    Code:
    if Not IsNumeric(var) then
    'do whatever you want (like redirecting your user)
    end if
    My example just gives you the main idea... There are actually several more and better checks to do to avoid such problems... But since it's not the topic of this thread...
    Last edited by jeskel; 11-10-2003 at 11:19 AM.

  • #7
    Regular Coder
    Join Date
    Mar 2003
    Posts
    241
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I agree with what's already been said

    What has not been told is that if you are going to access a request.querystring value more than once, you should put it into a variable. This will make the code more efficient [although you probably will not notice any differance]
    Code:
    Dim qsName
    qsName = Request.QueryString("name")
    '-- then do some validating

  • #8
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    Perfectly true, but since that's one of many *basic* optimization techniques which everyone should know by now (and let's not fill this thread with those, there are are plenty of other threads on that topic already) I was focusing on the serious security ramifications of Speedy/A1ien51's code...

    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #9
    Regular Coder
    Join Date
    Mar 2003
    Posts
    241
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Just because everyone *should* know of them doesn't mean they do, and when I saw this snippet it became obvious to me that the author did not know.

    But yeah, I could have ignored to reply, the difference is real small and these 'mistakes' are made all the time!

    Oh well

  • #10
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    As the ancient japanese sword masters used to say: "perfection can only be reached through repetition". I guess we could adapt this quote to the present discussion. I think it's always good to remember people that kind of details, or just give them a link where those basic things have been discussed. People are not supposed to make those mistakes like they are supposed to have read all the threads related to their question before asking it... huh... realistic? Well... my point is just that it doesn't cost much and that it can be helpfull

    P.S: if it comes to something discussed in a sticky, my point of view is obviously not the same.....

    <edit>
    welll I don't wanna be misunderstood... A perfect forum would obvisouly a forum where all those details would be known. But it will never be the case. My ASP level is quite low I guess, so I'm happy to help with those details ...which doesn't mean that some higher powerd ASP masters have to do it
    </edit>
    Last edited by jeskel; 11-12-2003 at 09:43 PM.

  • #11
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    Caffeine & bouchel - I don't want to be misunderstood either!! I wasn't dissing anyone's contribution to the thread, merely commenting that Speedy (and A1ien51) would IMHO benefit most from being aware of the *serious* problems with their approach, and that basic optimization techniques - important as they are - would be best left for another thread (of which there are doubtless many already).

    However, that's just my opinion.
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #12
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have functions designed specifically to clean up data. Most of you have seen the old versions of my validation/formatting/etc. functions, however I'm giving them an overhaul (to get rid of unnecessary code, make it more understandable, etc.).

    For instance:
    Code:
    Function ExtractAlphaNumeric(ByVal str)
    	If IsNull(str) Then Exit Function
    	Dim objRegEx
    	Set objRegEx = New RegExp
    	objRegEx.Pattern = "[^a-zA-Z0-9]"
    	ExtractAlphaNumeric = objRegEx.Replace(str,"")
    End Function
    
    Function ExtractNumbers(ByVal str)
    	If IsNull(str) Then Exit Function
    	Dim objRegEx
    	Set objRegEx = New RegExp
    	objRegEx.Pattern = "\D"
    	ExtractNumbers = objRegEx.Replace(str,"")
    End Function
    
    Function ExtractWordChars(ByVal str)
    	If IsNull(str) Then Exit Function
    	Dim objRegEx
    	Set objRegEx = New RegExp
    	objRegEx.Pattern = "\W"
    	ExtractWordChars = objRegEx.Replace(str,"")
    End Function
    
    Function ExtractWordCharsAndSpaces(ByVal str)
    	If IsNull(str) Then Exit Function
    	Dim objRegEx
    	Set objRegEx = New RegExp
    	objRegEx.Pattern = "[^A-Za-z0-9_ ]"
    	ExtractWordCharsAndSpaces = objRegEx.Replace(str,"")
    End Function
    In my experience, these functions (and related ones) are important regarding SQL injection attacks (not to mention providing clean data to your client easily!).

    There are also ways you can help avoid these problems using Stored Procedures with parameters and error checking in SQL, and I could go on and on about this, so I'll stop after the next paragraph.

    Really it's up to you to decide what characters must be allowed in your data, while keeping in mind ways that malicious users could compromise not only your data but your application.

    EVERY field that you request could potentially be a security risk, regardless of the request method (GET or POST), since the client can modify them using JavaScript or VBScript.

    For example, don't use sequential numbers (i.e. a primary key in a database) to update records using a hidden field, I see that error made often.

    A nine digit number has 1 billion combinations.

    A nine digit string (including alphabetical characters and numbers) has over 3.6 quadrillion combinations.

    If you are able to use salt (i.e. another variable that is hard to guess, like an email address or last name), then the odds turn astronomically in your favor (beyond my mathematical understanding, anyway), and against the would-be malicious user.
    Last edited by whammy; 11-13-2003 at 08:02 AM.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #13
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    P.S. These same functions are much prettier in C# and JavaScript...
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #14
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    Originally posted by whammy
    P.S. These same functions are much prettier in C# and JavaScript...
    I'm sure they are, but there's plenty of room for improvement here... you can make the above functions into one-liners by modularising your code just a little:
    Code:
    Function RegExReplace(InputString, MatchPattern, ReplacePattern)
    	Dim objRegEx
    	Set objRegEx = New RegExp
    	objRegEx.Pattern = MatchPattern
    	RegExReplace= objRegEx.Replace(InputString, ReplacePattern)
    	Set objRegEx = Nothing
    End Function
    
    Function ExtractWordCharsAndSpaces(ByVal str)
    	If IsNull(str) Then ExtractWordCharsAndSpaces = RegExReplace(str, "[^A-Za-z0-9_ ]", "")
    End Function
    Last edited by M@rco; 11-13-2003 at 04:56 PM.
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #15
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Originally posted by M@rco
    Caffeine & bouchel - I don't want to be misunderstood either!! I wasn't dissing anyone's contribution to the thread, merely commenting that Speedy (and A1ien51) would IMHO benefit most from being aware of the *serious* problems with their approach, and that basic optimization techniques - important as they are - would be best left for another thread (of which there are doubtless many already).
    you're totally right actually... I didn't see it that way. You're even more right that speedy doesn't seem to care a lot about it since he doesn't post anything about it btw I know you weren't dissing, I've never thought that (not your style)
    Originally posted by whammy

    For example, don't use sequential numbers (i.e. a primary key in a database) to update records using a hidden field, I see that error made often.
    hum...... seems like one of those common mistakes that I'm still making That will make a thread of its own pretty soon.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •