Visibility SQL in eWeb to Prevent URL Tampering

For the CMS pages on which someone views or edits their own information, you could add Visibility SQL to validate that the record being edited "belongs" to the logged in user. This can prevent URL querystring parameter tampering. This can also prevent an accidental or unintended "back door" entry into a record by an unwitting user.

What is URL Querystring Tampering

URL querystring tampering is when a user sees a URL like this:

http://www.zzz.com/UpdateInfo.aspx?id=389

and the user thinks, "Hmm, what if I change the id to 390?" After doing this, the user sees another record. A web application must protect itself from this sort of tampering. A lot of times, the developer of a web application just assumes that a user will never do this, but users do. Many applications will encrypt querystring values to thwart these users, or avoid the user of querystrings altogether.

The recommended approach to thwart URL querystring tampering in NetForum is explained below.

URL Querystring Tampering in NetForum

NetForum does not have any automatic protection about URL querystring tampering. NetForum is fairly, but not completely, safe from querstring tampering because most URL querystrings in eWeb have GUID values (instead of integer values) which are virtually unguessable.

The one exception to this rule is when you have pages that do expose certain keys (most commonly the CustomerKey) such as in a member directory with a summary search results page that has a drill-down to a detail page, with the detail page being driven by a cst_key value in the querystring.

For example, if the user were to view source on a member directory, you might see this:

<a href="DynamicPage.aspx?WebCode=MemberDetail&ind_cst_key=abc-123">Smith, Jeff</a><br /><a href="DynamicPage.aspx?WebCode=MemberDetail&ind_cst_key=def-398">Smith, Karen</a><br /><a href="DynamicPage.aspx?WebCode=MemberDetail&ind_cst_key=asd-787">Smith, Steve</a><br />

A clever user can "scrape" these ind_cst_key values, and then replace them on a "Edit My Information" page and see what happens. The user might actually be able to load up someone else's record.

Preventing URL Tampering

You cannot stop a user from URL tampering, but you can defend your site from these attempts by embedding Visibility SQL on your pages to validate that every record being loaded on a page is "allowed" to be loaded by the logged in user, whose identity is based on the CustomerKey session variable.

In the Visibility SQL on the page, add:

SELECT visible = 'yes' WHERE {session:CustomerKey} = {zzz_cst_key}

where zzz_cst_key is the parsed value of the control on that web page's form that corresponds to the Customer.

If the CustomerKey is in the URL querystring as "Key", then this can work as well:

SELECT visible = 'yes' WHERE {request:KEY} = {session:CustomerKey}

If a user sneaks into some other customer's page, they will not be able to edit the page because {session:CustomerKey} will be different from {zzz_cst_key}, and the Visibility SQL test above will return 0 records, which renders the page NOT visible, and kicks the user over to the Access Denied Page.

Access Denied Page

Your web site can have a single access denied page. If a page fails the Visibility SQL test, then NetForum will redirect the user to the access denied page. This page might have text saying, "We are sorry but we are unable to process your request."

Optionally, you can create another access denied page and send users to that page. You will need to select this separate "access denied" page for each particular page you want.

To figure out which users are messing around with your site, you could even add a List SQL web page detail to insert a record in a table (such as md_page_access to audit this activity.

Keep in mind that these access denials might not always be caused by malicious or mischievous users. You might also have a flaw in your Visibility SQL, or you might have some faulty application code that allows a user to get to a record they should not be seeing. So don't jump to conclusions until you investigate.

When Do I Need to do this?

If the data on the page loads based on some value in the querystring, then to be safe you should add Visibility SQL to the page.

For example, suppose you have a page that lists a customer's invoices. Each invoice has a hyperlink to a drill-down detail page to see more about that invoice. The URL querystring for this link contains the primary key of the Invoice, which enables the drill down detail page to "know" which invoice to load.

Do you need Visibility SQL on this page?

Probably not. Why? Because you are probably never going to publish any page where Invoice Keys are exposed to an end user. So, if I am a sneaky user, and I see this querystring:

http://www.netforum.org/eweb/DynamicPage.aspx?WebCode=InvoiceDetailView&Key=66d89b0b-eaae-4853-90c3-238d4531bd1a

I could try all day to pop in other values for the Key parameter and I'll never get a match.

Another way, absent visibility SQL, to make your display-only pages safer is to always use the CustomerKey in your List SQL or XslGenerator page SQL. This way you will not need Visibility SQL and you avoid one extra SQL call.

For example, on this Invoice Detail drill down page, you might normally write this sort of SQL:

SELECT * FROM vw_ac_invoice_detail WHERE inv_key = {KEY}

To safeguard against visibility SQL, you could add the following additional filter to the WHERE clause on the last line. It is not necessary from a SQL perspective, in fact is actually slower from a purely SQL perspective, but from a data safety perspective it's better:

SELECT * FROM vw_ac_invoice_detail WHERE inv_key = {KEY}AND inv_cst_key = {session:CustomerKey}

This SQL command above protects you from any querystring tampering, because even if somehow a user can get someone else's Invoice Key, they cannot get the page to load that record because the value of inv_cst_key will not be their own Customer Key.

More Detailed Example

Here's another example. Suppose you have an Awards site. Users can view award submissions from other members, as well as edit their own submission. The page to view other award submissions uses the primary key of the award submission in the querystring. Here, you definitely need Visibility SQL because users can see the keys, and they could substitute a key from another user's submission into the "edit my own submission" page, since this page will load based on a querystring parameter.

To lock down this page, your visibility SQL is:

SELECT [visible] = 'yes'FROM aw_award_entry (nolock)WHERE awe_key = {request:KEY}AND awe_entry_cst_key = {session:CustomerKey}

This line is what loads up the specific Award Entry record, based on the querystring parameter:

WHERE awe_key = {request:KEY}

This line ensures that the specific record being loaded really belongs to the logged-in customer:

AND awe_entry_cst_key = {session:CustomerKey}

Depending on your scenario, you might enlarge the pool of users who are allowed to edit a particular record. For example, if you allow the primary contact and the submitted by customer to also edit submissions, then you might expand the where clause to:

SELECT [visible] = 'yes'FROM aw_award_entry (nolock)WHERE awe_key = {request:KEY}AND (awe_entry_cst_key = {session:CustomerKey}OR awe_submit_cst_key = {session:CustomerKey}OR awe_aec_cst_key = {session:CustomerKey})

More Complicated Example

A more complicated example may be where a user can access a page only if they belong to a certain committee. The Visibility SQL on that page could take this general form:

SELECT visible = 'yes' WHERE {chp_cst_key} IN  (SELECT chp_cst_key FROM co_chapter (nolock)WHERE << {session:CustomerKey} IS the key_contact >> )

In the "Access Denial Page" of the web page, you could have it go to some "Sorry, your request could not be processed" type of page.

Distinction Between View and Edit Pages

View-only pages using List SQL and XslGenerator can be locked down more easily, without using Visibility SQL, by always considering the CustomerKey session variable in the WHERE clause of the SQL that loads the data.

Edit pages, by contract, that use the Wizard content type are more problematic. The NetForum engine demands that you have the primary key in the querystring, so to be safe you should add Visibility SQL on the page.