Getting SQL string to work in ASP

Penny · May 15, 2005

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.

Bob Barrows [MVP] · May 15, 2005

Penny said:
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

Joe Iano · May 15, 2005

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

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

Penny · May 17, 2005

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

Bob Barrows [MVP] · May 17, 2005

Penny said:
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

Penny · May 18, 2005

Thanks for the info Bob,

"Ya gotta be vigilant!!" - George Costanza

Regards,

Penny.

SQL works in Access but not ASP	4	May 14, 2005
simple_html_dom: simple use-case - getting a scipt to work	0	Mar 2, 2020
First steps in setting up VSCode to work with Python.	2	Mar 13, 2023
Procedure Invalid Identifier in SQL / PLSQL	1	Jul 26, 2022
connect string ASP / SQL EXPRESS	0	Jul 11, 2009
Copy string from 2D array to a 1D array in C	1	Nov 1, 2023
Getting carousel to continually rotate	0	Sep 27, 2017
MS SQL ASP Update timestamp	4	Feb 26, 2008

Getting SQL string to work in ASP

Penny

Bob Barrows [MVP]

Joe Iano

Penny

Bob Barrows [MVP]

Penny

Ask a Question

Similar Threads

Members online

Forum statistics

Latest Threads