ansaurus

Question

Classic ASP: How can I use a value from SQL twice?

Answer 1

+1 A:

You may need to just extract the value to a local Var and use it twice. I have seen weird stuff like this before, a long, long time ago.

EnocNRoll 2009-02-09 23:44:01

Answer 2

+5 A:

Assign it to a variable; don't just pull the value from the Recordset.

Set nItem = Request.QueryString("ID")

strSQL = "SELECT * "_
    & "FROM [Packages] "_
    & "WHERE id='" & nItem & "';" 

Set rstSearch = cnnSearch.Execute(strSQL)

Dim software
software = rstSearch("Software")
' set your other fields as variables...

<title><%= software %></title>
<body>
<center>Software Information</center>
<%= software %> <br />

Should work fine for you.

Wayne M 2009-02-09 23:45:16

The forward-only aspect of recordsets applies to moving to the next record, which The KZA's code doesn't do. There should be no reason why the second reference isn't valid in this case. You should be able to refer to the recordset multiple times provided you don't move to the next record.

Simon Forrest 2009-02-09 23:57:45

I was sure I tried this earlier, but yeah, this is the solution, thanks :)

The KZA 2009-02-09 23:58:00

No problem. I ran into this a few months back since my day job is doing Classic ASP maintenance (yuck). Simon is right in that it *should* work as long as you don't move to the next record, but that's the best idea I have for why it doesn't work.. glad you got it fixed, however.

Wayne M 2009-02-09 23:59:54

For the record Simon Forrest is correct here - forwardonly applies to rows only. Columns on the current row can be referred to as often as you like.

CodeBadger 2009-02-10 00:11:00

Answer 3

+1 A:

Some comments:

1. Possibly call rstSearch.MoveFirst before the second 
   rstSearch.Fields("Software")
2. If that doesn't work write <%=Err.Description%> right after the 
   second rstSearch.Fields("Software") line.
3. Try not to ever use Select * for selecting columns. 
   Always specify which columns you want.
4. Please try and use ASP.Net. It's much better then ASP3.

Bravax 2009-02-09 23:45:35

Cheers. I did start with ASP.NET but kept it simple by using classic ASP following the recommendation from a previous question I posted here :)

The KZA 2009-02-09 23:59:23

Where is this horrible advice so I can vote it down?I guess everyone has to start somewhere, but I would have gone with .net.

Bravax 2009-02-10 00:16:29

Looks like they deleted it, perhaps preempting your vote!

The KZA 2009-02-10 00:25:30

Answer 4

+3 A:

Firstly a quick warning, you are open to SQL injection in your inclusion of nItem in the query. Just an aside but one to watch :)

Other than that there should be no problem referring to a column in your recordset twice. I'd hazard a guess that you have a different problem in the code. It might help if you publish the behaviour / errors you get when trying this page. But I think there may be some other code that we don't see in the snippet above that is causing the issue.

One thing to help you run control tests would be to replace the "select *" with "select Software, Version, ID, License" etc. You could refer to the column by numeric ordinal then which may help.

Good luck with it.

CodeBadger 2009-02-09 23:48:09

Thanks very much. But now I guess I have a new problem :)The site is an internal one with authentication, so the exposure is low, but I'll do my best to plug it up.

The KZA 2009-02-10 00:01:41

No worries. ASP is particularly prone because of it's lack of type safety. A clumsy but perfectly reasonable approach to avoiding SQL injection here is to wrap setting nItem in an explicit integer conversion (CInt). So long as you know it's an int it can't contain nasty SQL injections. Enjoy :)

CodeBadger 2009-02-10 00:14:01

Not only that, you're also vulnerable to HTML injection (leading to script injection and XSS attacks) - remember to Server.HTMLEncode any text you output to the page! Even on an ‘internal’ site, these bugs will bite you when the data contains apostrophes, < signs, etc.

bobince 2009-02-10 01:52:37

Answer 5

A:

Sorry, can't help on the actual answer, but this looks like the prime example of a SQL Injection Attack:

Set nItem = Request.QueryString("ID")

strSQL = "SELECT * "_
    & "FROM [Packages] "_
    & "WHERE id='" & nItem & "';"

Looks like I could just change the URL to "?ID=1'; DROP TABLE STUDENTS; --" to mess stuff up.

Michael Stum 2009-02-09 23:48:27

Bobby Tables, are you messing with databases again? ;-)

Wayne M 2009-02-09 23:50:01

ansaurus

tags:

views:

answers:

Classic ASP: How can I use a value from SQL twice?

related questions