Export to Excel and firewalls

[Marja Ribbers-de Vroed]

Hi,

One of my clients has a strange problem with my webapplication which I think may be related to some firewall setting on his computer.

On several screens in the application, it is possible to click on an icon to start an export of data to Excel.
The click on the icon triggers a form submit (a post, not a get) to a blank page, thereby passing the SQL select statement to use for the Excel export.

I've never had any problem reports about this Excel export feature, but now one of my clients claims that the export doesn't work for him on his computer.
He gets an error that indicates that the SQL query is somehow not passed to the blank page.

He has tried the same thing on another computer as well, and then there is no problem whatsoever.

Can this issue indeed be related to some firewall setting on his own computer?
And if so, what would he have to do to prevent this problem?
And if not, what else could be the cause of this Excel export problem?

Regards,
--
Marja Ribbers-de Vroed

[Larry Bud]

Marja Ribbers-de Vroed wrote:

Hi,

One of my clients has a strange problem with my webapplication which I think may be related to some firewall setting on his computer.

On several screens in the application, it is possible to click on an icon to start an export of data to Excel.
The click on the icon triggers a form submit (a post, not a get) to a blank page, thereby passing the SQL select statement to use for the Excel export.

I've never had any problem reports about this Excel export feature, but now one of my clients claims that the export doesn't work for him on his computer.
He gets an error that indicates that the SQL query is somehow not passed to the blank page.

Since ASP is all server side, whatever you're outputting back to the
client is on port 80. Since you have port 80 open (by the fact that
the client can get to your webpage) I don't see how this could be a
firewall issue at all.

You need to post the code along with the SQL error to get any more
help.

[Marja Ribbers-de Vroed]

> Since ASP is all server side, whatever you're outputting back to the

client is on port 80. Since you have port 80 open (by the fact that
the client can get to your webpage) I don't see how this could be a
firewall issue at all.
Sound reasonable, so I guess this isn't a firewall issue.
You need to post the code along with the SQL error to get any more
help.

I use Response.Conten tType = "applicatio n/vnd.ms-excel" to output to Excel.

The full SQL query is in a hidden field of the form that is being posted to the page that outputs to Excel.
The receiving ASP page uses a custom datagrid control to create the datatable to export:

Function wsShowGrid()
Dim l_oDataGrid, l_sSql, l_sPattern, l_oRegExp, l_aMatch, l_sTable
l_sSql = Request("hidden GridSql")
If (l_sSql = "") Then
l_sSql = Request("sql")
End If
l_sTable = Request("hidden GridTable")
If (l_sTable = "") Then
l_sTable = Request("table" )
End If
Set l_oDataGrid = New wsDataGrid
With l_oDataGrid
.wsCommand = l_sSql
.wsTableName = l_sTable
.wsPrimaryKeyCo lumn = "ID"
.wsPageResults = False
.wsAllowView = False
.wsAllowFilter = False
.wsAllowSort = False
Call .wsSetTableOpti ons(null,null,n ull,1)
wsShowGrid = .wsBind()
End With
Set l_oDataGrid = Nothing
End Function

As you can probably see the SQL select query should be passed through Request("hidden GridSql"), which is successful in all cases except for this one user.

The error this one user receives has to do with the wsBind() call.
The webapplication complains that the SQL command has not been set yet.
Very, very strange!

Any insights would be grately appreciated.

Regards, Marja

[Bob Barrows [MVP]]

Marja Ribbers-de Vroed wrote:

Since ASP is all server side, whatever you're outputting back to the
client is on port 80. Since you have port 80 open (by the fact that
the client can get to your webpage) I don't see how this could be a
firewall issue at all.
Sound reasonable, so I guess this isn't a firewall issue.

You need to post the code along with the SQL error to get any more
help.

I use Response.Conten tType = "applicatio n/vnd.ms-excel" to output to
Excel.

The full SQL query is in a hidden field of the form that is being
posted to the page that outputs to Excel.

<gasp> Never put SQL where it is visible to users! Oh! You think that
because the field is "hidden" that hackers won't be able to see it? Load
your page in a browser and View Source to disabuse yourself of that notion.
Once you give a hacker some knowledge of your database structure, as well
as the knowledge that all he has to do is post some sql to your asp page to
have it run, your server is "owned"! Using your current code, think of the
consequesnces if a hacker loads this url:

yourpage.asp?sq l=truncate%20so me_important_ta ble

Best Security Practice:
Pass values via form fields, never executable statements! Either use the
values passed via the form fields to construct sql statements (not
recommended), or better yet, pass the values as parameters to stored
procedures (http://tinyurl.com/jyy0) or strings containing parameter markers
(http://groups-beta.google.com/group/...er.asp.db/msg/
72e36562fee7804 e).
The receiving ASP page uses a custom datagrid control to create the
datatable to export:
Datagrid? Is this classic ASP or ASP.Net?
Function wsShowGrid()
Dim l_oDataGrid, l_sSql, l_sPattern, l_oRegExp, l_aMatch, l_sTable
l_sSql = Request("hidden GridSql")
http://www.aspfaq.com/show.asp?id=2111

As you can probably see the SQL select query should be passed through
Request("hidden GridSql"), which is successful in all cases except for
this one user.

Are all the other form values passed? You should verify this.

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.