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.

A few suggestions.

(3) why do you constantly set rs = createobject("ADODB.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.querystring into your SQL

statements unchecked? Have you tried something like...

DisplaySortableTickets.asp?strStatus=a';DELETE%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]

...

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.

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...goodbye 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...

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]

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

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.

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

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'Brien'

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''Brien'

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.

 

[Bob Barrows]

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.






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

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?

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.createobject("adodb.recordset")" statement or not,
you will get an adodb.recordset object from the connection.execute 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.execute, 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 adExecuteNoRecords 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.execute 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.

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

 

[Mike]

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.






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

My question is, is this enough?






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/DesktopDefault.aspx?tabindex=2&tabid=3

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

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

 

[Bob Barrows]

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

 

[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

 

[Ray at]

Whoah...


<%
Set rs = YourADOConnection.Execute(YourSQLQuery)
Response.Write VarType(rs)
'or
Response.Write IsObject(rs)
'etc.
%>

Ray at work

 

[middletree]

OK, thanks.

...


aye, remember to close the connection aswell when you're done with it.


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

Replacing the ' I believe is one solution...


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



It wont 'take' them away - it simply replaces them with 2 apostrophes
put
an evil SQL

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

 

[Aaron Bertrand - MVP]

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.

Except that's 10 months out of date.

I'm working on a new solution.

 

[middletree]

(5) why are you using ADODB.Recordset at all? These all seem to be
forward-only, static recordsets.
of recordset?

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

Yes, I have read this article before. What I got out of it was that you
shouldn't do a RS if you're not getting any data returned. I was thinking
that I was getting data returned in the code to which Aaron was referring.

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.

I will download it and you are correct when you say it's great of Aaron to
do that, to have that site, etc. I've learned a lot from him, the site, and
this forum.
Having said that, I won't be reading it over the weekend. Something about
having a pregnant wife, twin 2-year-olds, a yard to mow, and a cat to feed.
Well, I can always choose the pdf over the cat.

 

[Ray at]

Perhaps you could schedule it to update automatically. Here's how you can
schedule things. http://www.aspfaq.com/2143

Ray at work

 

[middletree]

Hope this helps.

Yes, it does, thanks

 

[middletree]

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.createobject("adodb.recordset")" statement or not,
you will get an adodb.recordset object from the connection.execute 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.execute, 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 adExecuteNoRecords 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.execute 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.

I really don't understand the above too well, but I'll keep digesting it.

Thanks

 

[middletree]

See http://www.sqlsecurity.com/DesktopDefault.aspx?tabindex=2&tabid=3

This is a helpful article. Thanks

 

[Ray at]

Well, I can always choose the pdf over the cat.

echo middletree>>C:\vendetta.log

Ray at work, father to seven cats

 

[Aaron Bertrand - MVP]

Ray at work, father to seven cats

Uh, yikes. I would have slept easier tonight, had "father" been replaced by
"owner"...

 

[Jeff Cochran]

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.

I went back and took care of it with this: set rs = nothing
My question is, is this enough?

I do:

rs.close
Set rs=nothing

And similarly, at the end of the connection use, I close the
connection:

conn.close
Set conn = nothing

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

It's a great habit. You never know when that intranet code will be
pasted to an extranet or internet page by someone else. Take a look
at the SQL injection FAQ:

http://www.sqlsecurity.com/DesktopDefault.aspx?tabindex=2&tabid=3

Having said that, I'm always running into older code where I didn't
pay attention either.

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

The best reference to answer this is:

http://www.aspfaq.com/show.asp?id=2191

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 function doubles any single quotes in your string before you use
it in a SQL query. SQL treats a single quote as a delimiter, so if
you had a single quote in your string and tried to INSERT it for
example, it would chop off past the single quote in the string, which
likely isn't what you want. Doubled single quotes escapes the single
quote in the query.

Jeff

 

[Ray at]

I did write "owner" at first, but then deleted it. I was listening to the
slavery story on NPR this morning on the way to work, and I didn't want to
mis-speak about my cats.

Ray at work

 

[Jeff Cochran]

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"

Just because you don't explicitly create the object doesn't mean it
doesn't exist. Any code I've seen without a CreateObject has
implicitly created the object anyway.

Jeff