Please help - trying to return XML from a recordset

Discussion in 'ASP General' started by Alan Silver, Mar 20, 2005.

  1. Alan Silver

    Alan Silver Guest

    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=driver=sql server;server=(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)
     
    Alan Silver, Mar 20, 2005
    #1
    1. Advertising

  2. Alan Silver

    Steven Burn Guest

    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" <> wrote in message news:...
    > 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=driver=sql server;server=(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)
     
    Steven Burn, Mar 20, 2005
    #2
    1. Advertising

  3. 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/advanced_sql_injection.pdf
    http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf

    Here are some of my posts about the alternatives to dynamic sql:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e
    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"
     
    Bob Barrows [MVP], Mar 20, 2005
    #3
  4. 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=driver=sql server;server=(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.
     
    Michael D. Kersey, Mar 21, 2005
    #4
  5. Alan Silver

    Alan Silver Guest

    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, Mar 21, 2005
    #5
  6. Alan Silver

    Alan Silver Guest

    >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)
     
    Alan Silver, Mar 21, 2005
    #6
  7. 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.
     
    Bob Barrows [MVP], Mar 21, 2005
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Brent Mondoux
    Replies:
    0
    Views:
    345
    Brent Mondoux
    Jun 30, 2004
  2. KK
    Replies:
    2
    Views:
    590
    Big Brian
    Oct 14, 2003
  3. Greenhorn
    Replies:
    15
    Views:
    827
    Keith Thompson
    Mar 6, 2005
  4. Geoff Robinson

    What Should You Do If You Want to Return a Recordset?

    Geoff Robinson, May 11, 2004, in forum: ASP .Net Web Services
    Replies:
    4
    Views:
    113
    Ahsan Yar Khan
    May 17, 2004
  5. Hung Huynh
    Replies:
    8
    Views:
    308
    Bob Barrows
    Sep 24, 2003
Loading...

Share This Page