Please help - trying to return XML from a recordset

[Alan Silver]

Hello,

I have an ASP that takes a connection string and SQL statement in the
querystring and is supposed to return the XML representation of the
recordset to the Response stream (don't worry, this is a local page, not
one on the Internet). I had it working fine with row-returning SQL, such
as SELECT, but was trying to get it to work with non-row-returnign ones
as well (such as UPDATE and DELETE). Not only can't I get this bit to
work, I seem to have broken the row-returning bit as well!!

The full ASP is shown below. Can anyone see why this isn't working? It
is easy to test, assuming it's called xml.asp, and you have the
Northwind database installed on a local server, you can use the
following URL...

https://127.0.0.1/dap.asp?connstr=dr...(local);uid=sa
;pwd=;Network+L ibrary=dbmssocn ;Database=North wind&SQL=select +top+2+*+from
+Categories

I had this working fine, so it showed the XML in IE. I don't know what I
did, but now it gives an error that the XML is badly formed. I presume
that this is because it is throwing an error somewhere, and the error
message is being sent to the browser. Unfortunately, I can't see what or
where the error is.

The SendErrRs is meant to create and return a recordset in case of an
error occurring. That way, the client that picks this up can see what
the error was. It was working fine with SELECT queries, but even that
has stopped working. My intention was that for UPDATEs, it would return
a simple recordset (maybe using the SendErrRs Sub) with a field set to
indicate success.

Here is the full code in the ASP. If anyone can see what is going wrong,
I would be very grateful. TIA
<%@ Language=VBScri pt %>
<%option explicit%>
<%
Dim SQL, ConnStr, Conn, Rs, adPersistXML, i
adPersistXML = 1

Response.Conten tType = "text/xml"

ConnStr = Request.QuerySt ring("ConnStr")
SQL = Request.QuerySt ring("sql")

Set Conn = Server.CreateOb ject("ADODB.Con nection")
Conn.Connection String = ConnStr
On Error Resume Next
Conn.Open
If Err.Number <> 0 Then
SendErrRs Err.Number, Err.Description
Else
Set Rs = Server.CreateOb ject("ADODB.Rec ordset")
With Rs
.ActiveConnecti on = Conn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOpti mistic
.Source = SQL
.Open
If Err.Number <> 0 Then
SendErrRs Err.Number, Err.Description
Else
' We know the command executed OK, but if it was a command that didn't return rows, we
' won't have a recordset. If so, trying to save the recordset will generate an error.
' Check to see if we have a recordset before saving.
i = .RecordCount
Select Case Err.Number
Case 0
' no error, therefore rows returned
.Save Response, adPersistXML
Case 3704
' no rows, ie the SQL was an update, delete, etc
SendErrorRs 0, "No rows returned"
Case Else
' genuine error
SendErrRs Err.Number, Err.Description
End Select
End If
End With
End If

Sub SendErrRs(Num, Desc)
Dim Rs
Set Rs = Server.CreateOb ject("ADODB.Rec ordset")
'response.write ("<p>" & num & " - " & desc & "</p>"): exit sub
With Rs
.CursorLocation = adUseClient
.Fields.Append "ErrorNumbe r", adInteger
.Fields.Append "ErrorDescripti on", adVarChar, 400
.Open

.AddNew
.Fields("ErrorN umber") = Num
.Fields("ErrorD escription") = Desc
.Update

.Save Response, adPersistXML
End With
End Sub
%>

--
Alan Silver
(anything added below this line is nothing to do with me)

[Steven Burn]

My guess would be your retrieving ConnStr and SQL but not the rest of them....... if you want/need to use all of them, you need to retrieve them

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

"Alan Silver" <al*********@no spam.thanx> wrote in message news:JQ******** ******@nospamth ankyou.spam...

Hello,

I have an ASP that takes a connection string and SQL statement in the
querystring and is supposed to return the XML representation of the
recordset to the Response stream (don't worry, this is a local page, not
one on the Internet). I had it working fine with row-returning SQL, such
as SELECT, but was trying to get it to work with non-row-returnign ones
as well (such as UPDATE and DELETE). Not only can't I get this bit to
work, I seem to have broken the row-returning bit as well!!

The full ASP is shown below. Can anyone see why this isn't working? It
is easy to test, assuming it's called xml.asp, and you have the
Northwind database installed on a local server, you can use the
following URL...

https://127.0.0.1/dap.asp?connstr=dr...(local);uid=sa
;pwd=;Network+L ibrary=dbmssocn ;Database=North wind&SQL=select +top+2+*+from
+Categories

I had this working fine, so it showed the XML in IE. I don't know what I
did, but now it gives an error that the XML is badly formed. I presume
that this is because it is throwing an error somewhere, and the error
message is being sent to the browser. Unfortunately, I can't see what or
where the error is.

The SendErrRs is meant to create and return a recordset in case of an
error occurring. That way, the client that picks this up can see what
the error was. It was working fine with SELECT queries, but even that
has stopped working. My intention was that for UPDATEs, it would return
a simple recordset (maybe using the SendErrRs Sub) with a field set to
indicate success.

Here is the full code in the ASP. If anyone can see what is going wrong,
I would be very grateful. TIA


<%@ Language=VBScri pt %>
<%option explicit%>
<%
Dim SQL, ConnStr, Conn, Rs, adPersistXML, i
adPersistXML = 1

Response.Conten tType = "text/xml"

ConnStr = Request.QuerySt ring("ConnStr")
SQL = Request.QuerySt ring("sql")

Set Conn = Server.CreateOb ject("ADODB.Con nection")
Conn.Connection String = ConnStr
On Error Resume Next
Conn.Open
If Err.Number <> 0 Then
SendErrRs Err.Number, Err.Description
Else
Set Rs = Server.CreateOb ject("ADODB.Rec ordset")
With Rs
.ActiveConnecti on = Conn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOpti mistic
.Source = SQL
.Open
If Err.Number <> 0 Then
SendErrRs Err.Number, Err.Description
Else
' We know the command executed OK, but if it was a command that didn't return rows, we
' won't have a recordset. If so, trying to save the recordset will generate an error.
' Check to see if we have a recordset before saving.
i = .RecordCount
Select Case Err.Number
Case 0
' no error, therefore rows returned
.Save Response, adPersistXML
Case 3704
' no rows, ie the SQL was an update, delete, etc
SendErrorRs 0, "No rows returned"
Case Else
' genuine error
SendErrRs Err.Number, Err.Description
End Select
End If
End With
End If

Sub SendErrRs(Num, Desc)
Dim Rs
Set Rs = Server.CreateOb ject("ADODB.Rec ordset")
'response.write ("<p>" & num & " - " & desc & "</p>"): exit sub
With Rs
.CursorLocation = adUseClient
.Fields.Append "ErrorNumbe r", adInteger
.Fields.Append "ErrorDescripti on", adVarChar, 400
.Open

.AddNew
.Fields("ErrorN umber") = Num
.Fields("ErrorD escription") = Desc
.Update

.Save Response, adPersistXML
End With
End Sub
%>

--
Alan Silver
(anything added below this line is nothing to do with me)

[Bob Barrows [MVP]]

Here are a couple xml demos I wrote. One of them may help.
http://www.davidpenton.com/testsite/tips/

Bob Barrows
Alan Silver wrote:

Hello,

I have an ASP that takes a connection string and SQL statement in the
querystring

This is incredibly bad practice security-wise Why give hackers a head start
in their efforts to find out your database structure? Pass data via form and
querystring variabes Never metadata Avoid dynamic sql so as to avoid the ris
of hackers using dyac sql against you Here are some links about sql
injection:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/adva..._injection.pdf
http://www.nextgenss.com/papers/more..._injection.pdf

Here are some of my posts about the alternatives to dynamic sql:
http://groups-beta.google.com/group/...e36562fee7804e
http://tinyurl.com/jyy0

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Michael D. Kersey]

Alan Silver wrote:

Hello,

I have an ASP that takes a connection string and SQL statement in the
querystring and is supposed to return the XML representation of the
recordset to the Response stream (don't worry, this is a local page, not
one on the Internet). I had it working fine with row-returning SQL, such
as SELECT, but was trying to get it to work with non-row-returnign ones
as well (such as UPDATE and DELETE). Not only can't I get this bit to
work, I seem to have broken the row-returning bit as well!!

The full ASP is shown below. Can anyone see why this isn't working? It
is easy to test, assuming it's called xml.asp, and you have the
Northwind database installed on a local server, you can use the
following URL...

https://127.0.0.1/dap.asp?connstr=dr...(local);uid=sa
;pwd=;Network+L ibrary=dbmssocn ;Database=North wind&SQL=select +top+2+*+from
+Categories

I had this working fine, so it showed the XML in IE. I don't know what I
did, but now it gives an error that the XML is badly formed. I presume
that this is because it is throwing an error somewhere, and the error
message is being sent to the browser. Unfortunately, I can't see what or
where the error is.

The SendErrRs is meant to create and return a recordset in case of an
error occurring. That way, the client that picks this up can see what
the error was. It was working fine with SELECT queries, but even that
has stopped working. My intention was that for UPDATEs, it would return
a simple recordset (maybe using the SendErrRs Sub) with a field set to
indicate success.

Here is the full code in the ASP. If anyone can see what is going wrong,
I would be very grateful. TIA
<%@ Language=VBScri pt %>
<%option explicit%>
<%
Dim SQL, ConnStr, Conn, Rs, adPersistXML, i
adPersistXML = 1

Response.Conten tType = "text/xml"

ConnStr = Request.QuerySt ring("ConnStr")
SQL = Request.QuerySt ring("sql")

Set Conn = Server.CreateOb ject("ADODB.Con nection")
Conn.Connection String = ConnStr
On Error Resume Next
Conn.Open
If Err.Number <> 0 Then
SendErrRs Err.Number, Err.Description
Else
Set Rs = Server.CreateOb ject("ADODB.Rec ordset")
With Rs
.ActiveConnecti on = Conn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOpti mistic
.Source = SQL
.Open
If Err.Number <> 0 Then
SendErrRs Err.Number, Err.Description
Else
' We know the command executed OK, but if it was a command that didn't return rows, we
' won't have a recordset. If so, trying to save the recordset will generate an error.
' Check to see if we have a recordset before saving.
i = .RecordCount
Select Case Err.Number
Case 0
' no error, therefore rows returned
.Save Response, adPersistXML
Case 3704
' no rows, ie the SQL was an update, delete, etc
SendErrorRs 0, "No rows returned"
Case Else
' genuine error
SendErrRs Err.Number, Err.Description
End Select
End If
End With
End If

Sub SendErrRs(Num, Desc)
Dim Rs
Set Rs = Server.CreateOb ject("ADODB.Rec ordset")
'response.write ("<p>" & num & " - " & desc & "</p>"): exit sub
With Rs
.CursorLocation = adUseClient
.Fields.Append "ErrorNumbe r", adInteger
.Fields.Append "ErrorDescripti on", adVarChar, 400
.Open

.AddNew
.Fields("ErrorN umber") = Num
.Fields("ErrorD escription") = Desc
.Update

.Save Response, adPersistXML
End With
End Sub
%>

Take out the "On Error Resume Next" to see if that yields more information.

[Alan Silver]

Bob,

First off, I should point out that I spotted the problem with my
existing code when I looked again this morning. I had called the sub
SendErrorRs, when in fact it was actually named SednErrRs.

The weird thing is that IE seemed to be caching the page, so when I made
changes, I didn't see any difference. I have no idea why this is as have
my cache switched off in IE and I was using Ctrl-f5 to refresh the page.
Anyway, when I closed IE and restarted it, I could see the changes.

Now, about the other bit ...

This is incredibly bad practice security-wise Why give hackers a head
start in their efforts to find out your database structure?

Well, I did point out that this was for internal use only ;-)

However, you have raised a point that I was going to ask about here
anyway. This started because the DB admin want to close the port that
SQL Server uses on the server, to prevent anyone having direct access to
the databases. He suggested the ASP approach, and having our client
applications grab the recordsets from the URL. The URL of the page would
be encrypted in the application, so no-one would know it existed, and
the page itself would have Windows security on it, so even if they know
it exists, they would have to find out the UID and password to get at
it.

Having said all that, it didn't seem like such a great idea to me, even
internally. I'm sure that there must be plenty of machines out on the
Internet that run SQL Server, but don't have port 1433 (I think it is)
open. How do they allow the client application to interact with the
server?

Maybe this is the wrong place to ask this question as I guess it's more
related to the client application (which in our case is written in VB),
but since you raised the issue, I thought I would ask.

Any comments welcome. Thanks.

--
Alan Silver
(anything added below this line is nothing to do with me)

[Alan Silver]

>Take out the "On Error Resume Next" to see if that yields more

information.

It didn't, which is why I was getting so frustrated the other day, but I
just discovered that IE was caching the page, so even when I made
changes to the source, I got the same thing in the browser.

Sorted now, ta.

--
Alan Silver
(anything added below this line is nothing to do with me)

[Bob Barrows [MVP]]

Alan Silver wrote:

Bob,

This is incredibly bad practice security-wise Why give hackers a head
start in their efforts to find out your database structure?
Well, I did point out that this was for internal use only ;-)

Not a good answer. A large percentage of hacks are done by disgruntled
employees ...

However, you have raised a point that I was going to ask about here
anyway. This started because the DB admin want to close the port that
SQL Server uses on the server, to prevent anyone having direct access
to the databases.
Not a bad idea. Think Blaster and Code Red ...
He suggested the ASP approach, and having our client
applications grab the recordsets from the URL. The URL of the page
would be encrypted in the application, so no-one would know it
existed, and the page itself would have Windows security on it, so
even if they know it exists, they would have to find out the UID and
password to get at it.
All of which is child's play to serious hackers.

Having said all that, it didn't seem like such a great idea to me,
even internally. I'm sure that there must be plenty of machines out
on the Internet that run SQL Server, but don't have port 1433 (I
think it is) open. How do they allow the client application to
interact with the server?
Most people configure a different port. Plus, they do not allow direct
access to internal servers from external locations. VPN is often used. Also,
many people use a separate sql server outside the dmz, to which they
replicate the data they want to have accessible to the outside world.

Maybe this is the wrong place to ask this question as I guess it's
more related to the client application (which in our case is written
in VB), but since you raised the issue, I thought I would ask.

There is a .sqlserver.secu rity newsgroup that you should browse.

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.