general questions: best practices

[middletree]

Yesterday, I posted a problem which, by the way, I haven't been able to
solve yet. But in Aaron's reply, he questioned why I did several things the
way I did. My short answer is that I have a lot to learn, but now I'd like
to ask anyone who reads this, including Aaron, for some clarification. I
imagine others might benefit, too.

"Aaron Bertrand - MVP" <aa***@TRASHasp faq.com> wrote

A few suggestions. (3) why do you constantly set rs = createobject("A DODB.Recordset" ) but never destroy any of them?

I went back and took care of it with this: set rs = nothing

My question is, is this enough?

(4) why are you allowing values from request.queryst ring into your SQL statements unchecked? Have you tried something like... DisplaySortable Tickets.asp?str Status=a';DELET E%20TKT_STATUS; SELECT%20' b

Never thought of that. Is that really an issue for an Intranet, though?
(5) why are you using ADODB.Recordset at all? These all seem to be forward-only, static recordsets.

I don't really understand this question/statement. Is there another kind of
recordset?

Here is a rewrite of the first portion. <!-- #INCLUDE FILE="includes/functions.asp" -->
<!-- #INCLUDE FILE="includes/argodbinc.asp" -->
<!-- #INCLUDE FILE="includes/colors.inc" -->
<%
function fixVal(s)
s = replace(request .QueryString(s) , "'", "''"))
end function

Does this just take the apostrophes from the querystring? Is that just to
keep it from being used by a malicious person who would put an evil SQL
statement?

[Rob Meade]

"middletree " wrote ...

I went back and took care of it with this: set rs = nothing
My question is, is this enough?
aye, remember to close the connection aswell when you're done with it.

DisplaySortable Tickets.asp?str Status=a';DELET E%20TKT_STATUS; SELECT%20' b

Never thought of that. Is that really an issue for an Intranet, though?

Yeppo - imagine the fun that could be had by a clued up developer just
before he leaves disgruntled...g oodbye important database content...you can
read up on this - try doing a Google search for SQL Injection.

Replacing the ' I believe is one solution...
I don't really understand this question/statement. Is there another kind of recordset?
You can use 'curosors' within recordsets, the allow you to move BACK and
FORTH between the records you return in the recordset, if you do not need to
ever move back, and only forward - as Aaron mentions above etc...

s = replace(request .QueryString(s) , "'", "''"))

Does this just take the apostrophes from the querystring?
It wont 'take' them away - it simply replaces them with 2 apostrophes
Is that just to keep it from being used by a malicious person who would put an evil SQL statement?

hehe - 'evil' - hehe - yes :)

Note, I am not as clued up as many here, so the above are only my
suggestions/opinions and thoughts - dont take 'em as gospel (unless I'm
correct in which case feel free to send me crates of copperfields sweet
sherry!)

Hope I helped,

Regards

Rob

[Ray at]

"middletree " <mi********@hto mail.com> wrote in message
news:Om******** ********@TK2MSF TNGP11.phx.gbl. ..

I went back and took care of it with this: set rs = nothing

My question is, is this enough?
Do:

rs.Close
Set rs = Nothing

(4) why are you allowing values from request.queryst ring into your SQL

statements unchecked? Have you tried something like...

DisplaySortable Tickets.asp?str Status=a';DELET E%20TKT_STATUS; SELECT%20' b

Never thought of that. Is that really an issue for an Intranet, though?

Yes! Sure, you can fire someone for dropping a table or something, but that
doesn't mean you should allow it to happen.

(5) why are you using ADODB.Recordset at all? These all seem to be

forward-only, static recordsets.

I don't really understand this question/statement. Is there another kind

of recordset?

YEah. If Aaron did not already post this link, read this when you have a
few moments.
http://www.aspfaq.com/2191

function fixVal(s)
s = replace(request .QueryString(s) , "'", "''"))
end function

Does this just take the apostrophes from the querystring? Is that just to
keep it from being used by a malicious person who would put an evil SQL
statement?

It's to keep the ' from your SQL query, since ' is a delimiter in SQL.
Example:

sVar = "O'Brien"
sSQL = "SELECT Something FROM SomeTable WHERE LastName='" & sVar & "'"

That will result in a string of:
SELECT Something FROM SomeTable WHERE LastName='O'Bri en'

That ' in the O'Brien will be the end of the string, and then SQL will see
Brien' and get confused. The way to escape the ' is to double it up, so the
string would wind up looking like:
SELECT Something FROM SomeTable WHERE LastName='O''Br ien'

Passing the value through the fixVal function will double up any instances
of '.

Does that make sense?

Ray at work

p.s. For pleasure reading, download the whole aspfaq.com FAQ in pdf format
and read it over the weekend. It's very cool of Aaron to offer that whole
thing as a pdf.

[Peter Foti]

"middletree " <mi********@hto mail.com> wrote in message
news:Om******** ********@TK2MSF TNGP11.phx.gbl. ..

"Aaron Bertrand - MVP" <aa***@TRASHasp faq.com> wrote

A few suggestions.

(3) why do you constantly set rs = createobject("A DODB.Recordset" ) but

never destroy any of them?

I went back and took care of it with this: set rs = nothing

My question is, is this enough?

You should close them when you are done with them:

rs.close
set rs = nothing

(4) why are you allowing values from request.queryst ring into your SQL

statements unchecked? Have you tried something like...

DisplaySortable Tickets.asp?str Status=a';DELET E%20TKT_STATUS; SELECT%20' b

Never thought of that. Is that really an issue for an Intranet, though?

Do you want to risk it? One day you might wake up with no records in the
database an not know what happened to them. :)

Here is a rewrite of the first portion.

<!-- #INCLUDE FILE="includes/functions.asp" -->
<!-- #INCLUDE FILE="includes/argodbinc.asp" -->
<!-- #INCLUDE FILE="includes/colors.inc" -->
<%
function fixVal(s)
s = replace(request .QueryString(s) , "'", "''"))
end function

Does this just take the apostrophes from the querystring? Is that just to
keep it from being used by a malicious person who would put an evil SQL
statement?

This replaces one single quotes with two single quotes. This is how you
escape the single quote. It should take care of the evil SQL statement I
think, but it also takes care of situations where your data contains a
single quote. For example, if I wanted to insert the string:

I don't like this

Then your SQL string would end when it encountered the single quote in that
string. The code above would replace the single quote so the string looked
like this:

I don''t like this

Your SQL string would then be valid.

Hope this helps.

Regards,
Peter Foti

[Bob Barrows]

middletree wrote:

Yesterday, I posted a problem which, by the way, I haven't been able
to solve yet. But in Aaron's reply, he questioned why I did several
things the way I did. My short answer is that I have a lot to learn,
but now I'd like to ask anyone who reads this, including Aaron, for
some clarification. I imagine others might benefit, too.

"Aaron Bertrand - MVP" <aa***@TRASHasp faq.com> wrote

A few suggestions.

(3) why do you constantly set rs = createobject("A DODB.Recordset" )
but

never destroy any of them?

I went back and took care of it with this: set rs = nothing

My question is, is this enough?

There are situations where an open recordset cannot be set to nothing. It is
best to explicitly close it before setting it to nothing:
rs.close:set rs=nothing

(4) why are you allowing values from request.queryst ring into your
SQL

statements unchecked? Have you tried something like...

DisplaySortable Tickets.asp?str Status=a';DELET E%20TKT_STATUS; SELECT%20'
b

Never thought of that. Is that really an issue for an Intranet,
though?

Yes. Has there never been a disgruntled employee in your company?
Some very secure networks have been hacked. Are you sure yours is up to the
task of defeating a determined hacker?

(5) why are you using ADODB.Recordset at all? These all seem to be

forward-only, static recordsets.

Ummm, this is a contradiction :-) I'm sure the word "static" was used
accidently here.


I don't really understand this question/statement. Is there another
kind of recordset?

No. It's Aaron's way of saying that you do not have to explicitly use
createobject to create the recordset object. Bottom line: whether you use an
explicit "Set rs=server.creat eobject("adodb. recordset")" statement or not,
you will get an adodb.recordset object from the connection.exec ute method.

However, there is a slight performance hit when you use createobject to
create the recordset. Here's why: when you use createobject, you're telling
ADO to build a recordset object, and set a reference to that object in your
variable. This is great if you are planning to create an ad hoc recordset by
appending fields to the Fields collection, or if you are planning to use the
Open method to open a non-default recordset after setting the appropriate
cursor type and location properties.

However, when you use connection.exec ute, ADO does the following:
1. creates a Command object to handle the execution of the query
2. creates a Recordset object to contain the results of the query (unless
you tell it not to by using the adExecuteNoReco rds option in the third
argument of the Execute statement)
3. Sets a reference to the new recordset object in the variable which is
receiving the results.If that variable already references another object,
that reference is destroyed, causing the referenced object to be destroyed
as well.

So, if you use createobject, you've created a recordset that will only be
destroyed when the connection.exec ute method is run. It's a performance hit,
but it's very slight. However:

Best practice is to skip the createobject statement when using Execute to
retrieve data into a recordset.

Here is a rewrite of the first portion.

<!-- #INCLUDE FILE="includes/functions.asp" -->
<!-- #INCLUDE FILE="includes/argodbinc.asp" -->
<!-- #INCLUDE FILE="includes/colors.inc" -->
<%
function fixVal(s)
s = replace(request .QueryString(s) , "'", "''"))
end function

Does this just take the apostrophes from the querystring?

No. It replaces single apostrophes with two apostrophes, so that the
database engine will interpret them as literal quotes instead of delimiters.
Is that
just to keep it from being used by a malicious person who would put
an evil SQL statement?

Yes, but a better technique would be to use a stored procedure and a Command
object (or the procedure-as-connection-method technique).

HTH,
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.

[Mike]

"middletree " <mi********@hto mail.com> wrote in message
news:Om******** ********@TK2MSF TNGP11.phx.gbl. ..

Yesterday, I posted a problem which, by the way, I haven't been able to
solve yet. But in Aaron's reply, he questioned why I did several things the way I did. My short answer is that I have a lot to learn, but now I'd like
to ask anyone who reads this, including Aaron, for some clarification. I
imagine others might benefit, too.

"Aaron Bertrand - MVP" <aa***@TRASHasp faq.com> wrote

A few suggestions.

(3) why do you constantly set rs = createobject("A DODB.Recordset" ) but

never destroy any of them?

I went back and took care of it with this: set rs = nothing

My question is, is this enough?

(4) why are you allowing values from request.queryst ring into your SQL

statements unchecked? Have you tried something like...

DisplaySortable Tickets.asp?str Status=a';DELET E%20TKT_STATUS; SELECT%20' b

Never thought of that. Is that really an issue for an Intranet, though?

I trust my intranet users less than I trust the general public!
See http://www.sqlsecurity.com/DesktopDe...ndex=2&tabid=3

(5) why are you using ADODB.Recordset at all? These all seem to be

forward-only, static recordsets.

I don't really understand this question/statement. Is there another kind

of recordset?
I almost never (read never) need a recordset object.

Here is a rewrite of the first portion.

<!-- #INCLUDE FILE="includes/functions.asp" -->
<!-- #INCLUDE FILE="includes/argodbinc.asp" -->
<!-- #INCLUDE FILE="includes/colors.inc" -->
<%
function fixVal(s)
s = replace(request .QueryString(s) , "'", "''"))
end function

Does this just take the apostrophes from the querystring? Is that just to
keep it from being used by a malicious person who would put an evil SQL
statement?

[Bob Barrows]

Mike wrote:

I almost never (read never) need a recordset object.

:-)
So you never need to retrieve data from a database?

Are you sure you didn't mean to say "I never use createobject to create my
recordset objects"?

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.

[Mike]

The key word there is "object." If I never create an object using
createobject do I ever have an object. I say no! So to reiterate. "I
almost never (read never) need a recordset object"

:-)

Mike
"Bob Barrows" <re******@NOyah oo.SPAMcom> wrote in message
news:eW******** ******@tk2msftn gp13.phx.gbl...

Mike wrote:

I almost never (read never) need a recordset object.

:-)
So you never need to retrieve data from a database?

Are you sure you didn't mean to say "I never use createobject to create my
recordset objects"?

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.

[Ray at]

Whoah...
<%
Set rs = YourADOConnecti on.Execute(Your SQLQuery)
Response.Write VarType(rs)
'or
Response.Write IsObject(rs)
'etc.
%>

Ray at work

"Mike" <Mi**@nospam.co m> wrote in message
news:%2******** ********@tk2msf tngp13.phx.gbl. ..

The key word there is "object." If I never create an object using
createobject do I ever have an object. I say no! So to reiterate. "I
almost never (read never) need a recordset object"

:-)

Mike

[middletree]

OK, thanks.

"Rob Meade" <ro********@N O-SPAM.kingswoodw eb.net> wrote in message
news:Az******** *************@n ews-text.cableinet. net...

"middletree " wrote ...

I went back and took care of it with this: set rs = nothing
My question is, is this enough?
aye, remember to close the connection aswell when you're done with it.

DisplaySortable Tickets.asp?str Status=a';DELET E%20TKT_STATUS; SELECT%20'

b

Never thought of that. Is that really an issue for an Intranet, though?

Yeppo - imagine the fun that could be had by a clued up developer just
before he leaves disgruntled...g oodbye important database content...you

can read up on this - try doing a Google search for SQL Injection.

Replacing the ' I believe is one solution...

I don't really understand this question/statement. Is there another kind of

recordset?

You can use 'curosors' within recordsets, the allow you to move BACK and
FORTH between the records you return in the recordset, if you do not need

to ever move back, and only forward - as Aaron mentions above etc...
s = replace(request .QueryString(s) , "'", "''"))

Does this just take the apostrophes from the querystring?

It wont 'take' them away - it simply replaces them with 2 apostrophes

Is that just to keep it from being used by a malicious person who would

put an evil SQL

statement?

hehe - 'evil' - hehe - yes :)

Note, I am not as clued up as many here, so the above are only my
suggestions/opinions and thoughts - dont take 'em as gospel (unless I'm
correct in which case feel free to send me crates of copperfields sweet
sherry!)

Hope I helped,

Regards

Rob