473,382 Members | 1,261 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

Please help - trying to return XML from a recordset

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+Library=dbmssocn;Database=Northwind& 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=VBScript %>
<%option explicit%>
<%
Dim SQL, ConnStr, Conn, Rs, adPersistXML, i
adPersistXML = 1

Response.ContentType = "text/xml"

ConnStr = Request.QueryString("ConnStr")
SQL = Request.QueryString("sql")

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionString = ConnStr
On Error Resume Next
Conn.Open
If Err.Number <> 0 Then
SendErrRs Err.Number, Err.Description
Else
Set Rs = Server.CreateObject("ADODB.Recordset")
With Rs
.ActiveConnection = Conn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.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.CreateObject("ADODB.Recordset")
'response.write("<p>" & num & " - " & desc & "</p>"): exit sub
With Rs
.CursorLocation = adUseClient
.Fields.Append "ErrorNumber", adInteger
.Fields.Append "ErrorDescription", adVarChar, 400
.Open

.AddNew
.Fields("ErrorNumber") = Num
.Fields("ErrorDescription") = Desc
.Update

.Save Response, adPersistXML
End With
End Sub
%>

--
Alan Silver
(anything added below this line is nothing to do with me)
Jul 22 '05 #1
6 1706
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*********@nospam.thanx> wrote in message news:JQ**************@nospamthankyou.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+Library=dbmssocn;Database=Northwind& 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=VBScript %>
<%option explicit%>
<%
Dim SQL, ConnStr, Conn, Rs, adPersistXML, i
adPersistXML = 1

Response.ContentType = "text/xml"

ConnStr = Request.QueryString("ConnStr")
SQL = Request.QueryString("sql")

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionString = ConnStr
On Error Resume Next
Conn.Open
If Err.Number <> 0 Then
SendErrRs Err.Number, Err.Description
Else
Set Rs = Server.CreateObject("ADODB.Recordset")
With Rs
.ActiveConnection = Conn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.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.CreateObject("ADODB.Recordset")
'response.write("<p>" & num & " - " & desc & "</p>"): exit sub
With Rs
.CursorLocation = adUseClient
.Fields.Append "ErrorNumber", adInteger
.Fields.Append "ErrorDescription", adVarChar, 400
.Open

.AddNew
.Fields("ErrorNumber") = Num
.Fields("ErrorDescription") = Desc
.Update

.Save Response, adPersistXML
End With
End Sub
%>

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


Jul 22 '05 #2
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"
Jul 22 '05 #3
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+Library=dbmssocn;Database=Northwind& 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=VBScript %>
<%option explicit%>
<%
Dim SQL, ConnStr, Conn, Rs, adPersistXML, i
adPersistXML = 1

Response.ContentType = "text/xml"

ConnStr = Request.QueryString("ConnStr")
SQL = Request.QueryString("sql")

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionString = ConnStr
On Error Resume Next
Conn.Open
If Err.Number <> 0 Then
SendErrRs Err.Number, Err.Description
Else
Set Rs = Server.CreateObject("ADODB.Recordset")
With Rs
.ActiveConnection = Conn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.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.CreateObject("ADODB.Recordset")
'response.write("<p>" & num & " - " & desc & "</p>"): exit sub
With Rs
.CursorLocation = adUseClient
.Fields.Append "ErrorNumber", adInteger
.Fields.Append "ErrorDescription", adVarChar, 400
.Open

.AddNew
.Fields("ErrorNumber") = Num
.Fields("ErrorDescription") = Desc
.Update

.Save Response, adPersistXML
End With
End Sub
%>

Take out the "On Error Resume Next" to see if that yields more information.
Jul 22 '05 #4
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)
Jul 22 '05 #5
>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)
Jul 22 '05 #6
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.security 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.
Jul 22 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Adrian Parker | last post by:
Hi. I would like to query a database, given several where clauses to refine my search, and return the value of one single field in the database. eg: I have a table that lists teachers. Their...
4
by: Skully Matjas | last post by:
I am using the following code (created by the wizard) to allow to bring my form to a particular entery. But when I edit the entery (ex: put new information into a blank cell), it puts that record...
2
by: Jayjay | last post by:
When it comes to access, I'm pretty good using the built in features and can come up with some pretty complex functions to get what I need. But we have this database I'm doing for work that is...
9
by: hope | last post by:
Hi Access 97 I'm lost on this code please can you help ================================= Below is some simple code that will concatenate a single field's value from multiple records into a...
0
by: Bruce | last post by:
I need to re-write an old .ASP application in a C# webservice. No problem, right? Well, I also need to use a DCom object to connect to our SAP system. Unfortunately, I'm unable to make that work....
0
by: rempit | last post by:
Is it true???Help me please.. I want to search data from Database..And..The result is view in new "DIV".. <%@ Page Language="vb" AutoEventWireup="false" Codebehind="new.aspx.vb"...
1
by: Lazster | last post by:
Hi, I'd really appreciate some help here people. I am trying to insert some values in a table in another database and not getting the results I am expecting. I am simply trying to insert values...
2
by: hotflash | last post by:
Hi Master CroCrew, I found a good PURE ASP that will allow you to upload 10MB file to the server and the file contents such as Network, Author, Title, etc... will insert to MS Access at the same...
12
by: Dooza | last post by:
I have a stored procedure that takes a number of inputs, does a bulk insert, and then outputs a recordset. When I run the stored procedure in Server Management Studio I also get a return value from...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.