Getting SQL string to work in ASP

Discussion in 'ASP General' started by Penny, May 15, 2005.

  1. Penny

    Penny Guest

    Hi again,

    Thanks to those who helped before but I'm afraid I waisted your time and
    effort(especially Bob's) by not detailing the way I
    was trying to dynamically create the select statement. I thought if I showed
    you a simpler 'hard coded' version it would be
    easier to give pointers but all it did was remove the crucial elements. Here
    is what I'm attempting to do.

    ....................................................

    Dim intWeightTotal
    Dim strCountry
    Dim strZone
    Dim strWeightBand

    intWeightTotal = Request.Form("weightTotal")
    strCountry = Request.Form("country")

    Select Case strCountry
    Case "Australia"
    strZone = "Australia"
    Case "Austria"
    strZone = "A"
    Case "Botswanna"
    strZone = "B"
    Case Else
    strZone = "Australia"
    End Select

    Select Case True
    Case intWeightTotal < 251
    strWeightBand = "250"
    Case intWeightTotal < 501
    strWeightBand = "500"
    Case intWeightTotal < 751
    strWeightBand = "750"
    Case intWeightTotal < 1001
    strWeightBand = "1000"
    Case Else
    strWeightBand = "2000"
    End Select

    Dim strSQL
    Dim rsShipping

    strSQL = "SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName = " &
    strZone & " AND WeightCategory = " & strWeightBand & ""

    Set rsShipping = Server.CreateObject("ADODB.Recordset")
    rsShipping.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
    Source=" & Server.MapPath("../ppdatabase.mdb")
    rsShipping.Source = strSQL
    rsShipping.CursorType = 0
    rsShipping.CursorLocation = 2
    rsShipping.LockType = 1
    rsShipping.Open()

    ......................................................

    I get error messages from the browser indicating "Syntax error", "expected
    end of statement" and "expected parameters" when I
    add and remove quotes. I'm sure the problem has something to do with the
    string variable generated in the select case and
    referenced in the SQL but no end of 'playing' has made it work.

    If I comment out all the recordset stuff and place something like this

    <%= strZone & "<BR>" %>
    <%= strWeightBand %>

    in the <body> then the page runs and this

    A
    500

    is displayed so I know both select cases work fine.

    Any ideas on how to pass strZone and strWeightBand to the SQL?

    B.T.W, I have no line breaks in my code. I'm just useless at formatting
    Usenet messages. In the database, both ZoneName and WeightCategory are text
    fields.

    Regards

    Penny.
    Penny, May 15, 2005
    #1
    1. Advertising

  2. Penny wrote:
    > Hi again,
    >
    > Thanks to those who helped before but I'm afraid I waisted your time
    > and effort(especially Bob's) by not detailing the way I
    > was trying to dynamically create the select statement. I thought if I
    > showed you a simpler 'hard coded' version it would be
    > easier to give pointers but all it did was remove the crucial
    > elements. Here is what I'm attempting to do.
    >


    I have the same advice I provided in my previous message (I knew this was
    what you were trying to do - nobody hard-codes values into a query ... )

    Anyways, nothing has changed. You are still creating a sql statement using
    dynamic sql. Given that, your goal is still to create a sql statement that
    will run without modification in your database environment (the Access Query
    Builder). The only way to troubleshoot problems with that sql statement is
    to write it to the Response object so you can see the statement you created:

    Response.Write strSQL

    THAT is what you (and we) need to see in order to figure out what is going
    on. Hint: if you open your database in Access and create a query that works
    correctly, you can switch to SQL View and SEE what the result of your
    response.write is supposed to look like.


    Go back and re-read my post, especially the links to previous posts I had
    made on this topic. In addition to the advice about alternatives to using
    dynamic sql, the first link provides detailed instructions in how to create
    sql using dynamic sql.

    By using dynamic sql, you are doing this the HARD way. And not only is it
    the hard way, it is also the insecure way, in that it opens the door for
    hackers to use sql injection to compromise your database and your site.
    Granted, in this case, since you are never using the user's input directly
    in your sql statement, this particular usage of dynamic sql will provide no
    avenue for hackers to exploit. But, I'm pretty sure this is not the only
    place you are using dynamic sql ...

    Anyways, if you continue to attempt to use dynamic sql and are still having
    problems you wish us to help with, you need to provide us with three pieces
    of information:

    1. The result of your "response.write strsql" statement
    2. The exact text of the error message you received when attempting to use
    the sql statement (if you made several attempts, you need to show us each
    attempt - the statement, and the error it caused)
    3. The datatypes of the fields in your query - we cannot create a sql
    statement without knowing the datatypes of the fields involved, which you
    can read about here:
    http://groups-beta.google.com/group.../713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], May 15, 2005
    #2
    1. Advertising

  3. Penny

    Joe Iano Guest

    Perhaps you need single quotes one either side of the variables:

    strSQL = "SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName = '" &
    strZone & "' AND WeightCategory = '" & strWeightBand & "'"


    "Penny" <> wrote in message
    news:...
    > Hi again,
    >
    > Thanks to those who helped before but I'm afraid I waisted your time and
    > effort(especially Bob's) by not detailing the way I
    > was trying to dynamically create the select statement. I thought if I

    showed
    > you a simpler 'hard coded' version it would be
    > easier to give pointers but all it did was remove the crucial elements.

    Here
    > is what I'm attempting to do.
    >
    > ...................................................
    >
    > Dim intWeightTotal
    > Dim strCountry
    > Dim strZone
    > Dim strWeightBand
    >
    > intWeightTotal = Request.Form("weightTotal")
    > strCountry = Request.Form("country")
    >
    > Select Case strCountry
    > Case "Australia"
    > strZone = "Australia"
    > Case "Austria"
    > strZone = "A"
    > Case "Botswanna"
    > strZone = "B"
    > Case Else
    > strZone = "Australia"
    > End Select
    >
    > Select Case True
    > Case intWeightTotal < 251
    > strWeightBand = "250"
    > Case intWeightTotal < 501
    > strWeightBand = "500"
    > Case intWeightTotal < 751
    > strWeightBand = "750"
    > Case intWeightTotal < 1001
    > strWeightBand = "1000"
    > Case Else
    > strWeightBand = "2000"
    > End Select
    >
    > Dim strSQL
    > Dim rsShipping
    >
    > strSQL = "SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName = " &
    > strZone & " AND WeightCategory = " & strWeightBand & ""
    >
    > Set rsShipping = Server.CreateObject("ADODB.Recordset")
    > rsShipping.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
    > Source=" & Server.MapPath("../ppdatabase.mdb")
    > rsShipping.Source = strSQL
    > rsShipping.CursorType = 0
    > rsShipping.CursorLocation = 2
    > rsShipping.LockType = 1
    > rsShipping.Open()
    >
    > .....................................................
    >
    > I get error messages from the browser indicating "Syntax error", "expected
    > end of statement" and "expected parameters" when I
    > add and remove quotes. I'm sure the problem has something to do with the
    > string variable generated in the select case and
    > referenced in the SQL but no end of 'playing' has made it work.
    >
    > If I comment out all the recordset stuff and place something like this
    >
    > <%= strZone & "<BR>" %>
    > <%= strWeightBand %>
    >
    > in the <body> then the page runs and this
    >
    > A
    > 500
    >
    > is displayed so I know both select cases work fine.
    >
    > Any ideas on how to pass strZone and strWeightBand to the SQL?
    >
    > B.T.W, I have no line breaks in my code. I'm just useless at formatting
    > Usenet messages. In the database, both ZoneName and WeightCategory are

    text
    > fields.
    >
    > Regards
    >
    > Penny.
    >
    >
    Joe Iano, May 15, 2005
    #3
  4. Penny

    Penny Guest

    Thanks Bob and Joe,

    I did as you said Bob and got the page to show me the select statement being
    generated. And as you said it did allow to play around with it and find a
    way to work the single quotes in. Works nicely now.

    What is SQL Injection?

    Regards,

    Penny
    Penny, May 17, 2005
    #4
  5. Penny wrote:
    > Thanks Bob and Joe,
    >
    > I did as you said Bob and got the page to show me the select
    > statement being generated. And as you said it did allow to play
    > around with it and find a way to work the single quotes in. Works
    > nicely now.
    >
    > What is SQL Injection?
    >

    It is a tactic that hackers can use to gain entry to and control of your
    site:

    http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
    http://www.nextgenss.com/papers/advanced_sql_injection.pdf
    http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
    http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf

    It depends on the use of dynamic sql. If parameters are used, sql injection
    is not possible.


    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], May 17, 2005
    #5
  6. Penny

    Penny Guest

    Thanks for the info Bob,

    "Ya gotta be vigilant!!" - George Costanza

    Regards,

    Penny.
    Penny, May 18, 2005
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. craig dicker
    Replies:
    9
    Views:
    647
    Juan T. Llibre
    Jul 7, 2005
  2. Larry
    Replies:
    0
    Views:
    352
    Larry
    Oct 12, 2005
  3. ecoolone
    Replies:
    0
    Views:
    731
    ecoolone
    Jan 3, 2008
  4. naveenduttvyas
    Replies:
    0
    Views:
    928
    naveenduttvyas
    Nov 26, 2008
  5. Ed Garcia
    Replies:
    4
    Views:
    176
    Guinness Mann
    Aug 7, 2003
Loading...

Share This Page