Understanding Classic ASP

Question

I am looking through some old code and found a piece that i cant' seem to understand the point of....As far as i can tell, it's just a simple insert. but why did they do it this way? would it be okay to rewrite into an insert, or could i potentially break something?

please see below:

Set TextRS = Server.CreateObject("ADODB.RecordSet")
 Set TextRS.ActiveConnection = Conn
 TextRS.Source = "SELECT IDX,TIMESTAMP,CURRENTFLAG,TEXT FROM " & TextTable & " WHERE 1=2 FOR UPDATE"
 TextRS.CursorLocation = 2
 TextRS.CursorType = 3
 TextRS.Open ,,,3

 TextRS.AddNew
 TextRS(0).Value = IDX
 TextRS(1).Value = Timestamp
 TextRS(2).Value = "Y"
  TextRS(3).AppendChunk TextPiece
 TextRS.Update

 TextRS.Close

Its one way to parametrize the insert statement and avoid SQL Injection. If you do change this to an insert statement make sure that you parametrize the query. — John Hartsock, Jan 10 '12 at 19:55

Jordan · Accepted Answer · 2012-01-11T19:09:55.450

5

This part of the source confused me a bit.... where 1 = 2??? Apparently it had a purpose to ensure no match.

Anyway this style of programming is fairly old using ADO technology and people coming from DAO to ADO would often open up a cursor to iterate over the database this way... it does not follow modern best practices on how to do things, you can and should replace it with an insert statement!

It is possible that it was written pre jet4.0/access 2000 in which case it was an attempt to simulate a parameterized stored procedure. Though if the system is at all more modern than that I would strongly recommend using a stored procedure as it has multiple benefits. Cached Execution Plans, Parameters to reduce the chances of SQL injection

I actually used to write code very much like that 12 years ago or so :p Mostly because I just didn't know better, regardless of the tech in use.

edited Jan 11 '12 at 19:09

answered Jan 10 '12 at 19:55

Jordan

2,708
4
22
35

1

That code is there to make sure no results are returned, when all the code is for is inserting a record. – Andrew Barber Jan 10 '12 at 20:09
+1 for the answer that has really spotted where this style of DB coding comes from. – AnthonyWJones Jan 10 '12 at 20:12
1

-1: I think you should give the original programmer some credit. For one thing, there is no iteration being done, and the `1 = 2` is not useless; it is used to retrieve an empty recordset that contains the schema definition—actually a nice trick. This was done as an easy way to simulate a paramterized query and thus avoid SQL injection. You propose rewriting as an INSERT, but do not suggest using a parameterized query, thus negating one of the most important benefits of the technique being used. – D'Arcy Rittich Jan 10 '12 at 22:45
I couldn't give the original programmer credit unless I knew what the system was. Perhaps if it was JET & PRIOR to Jet 4.0/Access 2000 i'd be more forgiving since they didn't have stored procedures. If anything else then it was just a poor way to do it VS creating an SP[which would prevent SQL Injection anyway, with cached execution plans etc] The only benefit I can see IS treating it like a client side cursor, hence the iteration point. My point was assuming stylistically they thought that way, it was really a benefit of the doubt. I think the -1 is uncalled for but it's your vote ;) – Jordan Jan 10 '12 at 23:37
Your comment contains much better reasoning than your response, which mostly said "useless", "old", and "bad" without much qualification. However, beware of promoting stored procedures as the cure for SQL injection. This is not necessarily true. I have seen many a SP subject to SQL injection. – D'Arcy Rittich Jan 11 '12 at 14:36
You'll need to edit your answer for me to change my vote. Perhaps moving your comments there would be helpful. – D'Arcy Rittich Jan 11 '12 at 14:37
Just because you don't know what something does, don't call it useless. This means the recordset object is returned with no records but a valid schema, you can then just do an `.AddNew` and you are safe from SQL injection without having to go through a parameterised query, which is a lot harder in classic ASP. – Richard Benson Jan 11 '12 at 15:49
@RedFilter ok I will edit it right now as your point is well taken. Though to be fair, I mostly agree with your disagreement of useless. It IS old and I meant bad in the sense of not as useful today but fair enough! – Jordan Jan 11 '12 at 19:05

score 2 · Answer 2 · answered Jan 10 '12 at 20:01

Ah, good old classic ASP ;)

The 1 = 2 forces the sql to never return a match. It's basically a way of building up the command (?) so that you can then "conveniently" change the values and then an update will store it.

I've seen it done before, but never did it that way myself. As others have said, a simple paremetised INSERT statement will be better, IMO.

score 1 · Answer 3 · answered Jan 10 '12 at 19:56

I would rewrite this using parameterized ADO query. The method being used has an unnecessary SELECT, which makes the INSERT slower.

That code seems a bit obscure, but all they are doing is creating an empty instance of a recordset row so the values can be set and the recordset resaved. This is bound to be much slower than doing a straight INSERT.

score 1 · Answer 4 · answered Jan 10 '12 at 19:57

I would utilize an insert statement. The above mentioned code seems a little bit "lazy"... as in "let ADO do the work for me". However, there is nothing really wrong with it. The where 1=2 part was there to return an "empty table"... um... I mean recordset.

Understanding Classic ASP

4 Answers4