Webmaster Forum / ASP / Database Access / May 2008

? I see nothing here that would leave you vulnerable to sql injection -
everything is hard-coded: no concatenating user input.
Also, it's a very strange looking query: I'm really puzzled as to the
purpose of that IN () statement. And the DISTINCT seems unnecessary as
well.

Is the zip code supposed to vary based on user input? If so, if you
wanted to avoid stored procedures for some reason, you should do this:

dim zip
zip=request.form("zip") 'or wherever you're getting it
sql = "SELECT c.id cityid, city_name city, state_id st " & _
"FROM city c inner join zipcode z on c.id=z.city_id " & _
"WHERE z.zipcode = ?"
set cmd=createobject("adodb.command")
cmd.commandtype = 1 'adCmdText
cmd.commandtext = sql
set cmd.activeconnection = oconn
set rs=cmd.execute(,array(zip))
if not rs.eof then rsarr = rs.getrows
'etc.

Of course I prefer stored procedures. In your database, run this script:

CREATE PROCEDURE CityDataSpecifiedZip (
@zip    varchar(6)) AS
SELECT c.id cityid, city_name city, state_id st
FROM city c inner join zipcode z on c.id=z.city_id
WHERE z.zipcode = @zip

Then you can do this in vbscript:
dim zip
zip=request.form("zip") 'or wherever you're getting it
set rs = createobject("ADODB.Recordset")
oconn.CityDataSpecifiedZip zip, rs
if not rs.eof then rsarr = rs.getrows
etc.