Visibility SQL

Visibility SQL is a netFORUM convention that is used to conditionally make certain content be visible or not visible based on some condition.

Visibilty SQL Test

Visibility SQL works in the following way. In the visibility sql text area, you will write a SQL command that will return a recordset with zero, one or more rows. If the recordset returns zero rows, then the visibility sql test fails and the element being evaluated will not be visible to the user. If the recordset returns one or more rows, then the content will be visible to the user.

The SQL command may be inline SQL or a stored procedure.

Note that this command:

SELECT visible = 'true' WHERE {skipped}='true']

works like this. If {skipped} evaluates to true then netFORUM will essentially run this SQL command:

SELECT visible = 'true' WHERE 'true'='true'

Running this command above will return 1 row and therefore the button will be visible. If {skipped} evaluated to null or false or batch or anything other than true then you netFORUM will run this:

SELECT visible = 'true' WHERE NULL='true'

or

SELECT visible = 'true' WHERE 'false'='true'

or

SELECT visible = 'true' WHERE 'bach'='true'

and then for all 3 of the examples above you will get ZERO records back, the Visibility SQL test will fail, and the button will be invisible.

Don't be confused or misled or read too much into the command that goes select visible='true' where.... The actuals word v i s i b l e and >y e s are not important; people do this just for clarity and as a general convention to make this abstract feature be more meaningful. Technically, you could type select dog='cat' where... or select [My Favorite Movie]='The Godfather' where... and you would achieve the same effect. Bottom line, if one or more records is returned, regardless of the column names or data in the rows, then the Visibility SQL test passes and the row is visible. If zero rows are returned, then the Visibility SQL test fails and the button is not visible.

Here is a simplified version of how this works from a programming perspective:

/// Simplified pseudo-code illustrates how an element (page, section, page detail, etc.)/// is made visible or non-visible based on Visibility SQLpublic bool IsElementVisible(string sql){// first, parse any values in the SQL command such as {CustomerKey} or anything else  sql = DataUtils.ParseValues(sql)   OleDbDataReader oDr; // second, execute the sql command into a OleDbDataReader// if there is more than 1 row, then return True; else return False  oDr = DataUtils.GetDataReader(sql)if(oDr.Rows.Count > 0){return true;}else{return false;}}

Where Visibility SQL is Used

• Web Page
• Page Detail
• Web Section
• Wizard Form Button
• Child Form - Child Form Visibility SQL is documented on its own page as there are some twists.
• Profile Detail
• Form Control using the InvisibleSQL feature. See Form Designer.
• Profile Tabs
• Form Links

Visibility SQL in eWeb

In eWeb, Visibility SQL is commonly used to restrict a Web Section, Web Page, or even Page Detail to users based on their membership or if they are on a certain committee, mailing list, or any other eligibility factor.

In almost every case, the visibility sql statement will refer to the Customer Key of the logged-in user. This value is accessible by using the {CustomerKey} Parsed Value.

Visibility SQL can also parse other values. If the values are based on an Object, then they will be parsed based on the Object of the Form that is specified for the Page Detail, if any. If no Form is specified on the page detail, then the control will be parsed against a generic Individual object related to the logged in user.

For example, the following visibility sql (right) on a Web Section will limit that section only to members.

The visibility sql:

SELECT cst_key FROM co_customer (NOLOCK)WHERE cst_key = {CustomerKey} AND cst_member_flag = 1

will return a single record if the logged in user is a member, otherwise it will return zero records and the section will not be available to the user; the user will instead be directed to the alternate page which in this case is Access Denied. The access denial page must have the ignore visibility sql checkbox unchecked to make it available as an "access denial notification page" page.

If you wanted to expand the criteria to include people who are members OR people who receive member benefits, then use this command that queries against the vw_customer_member_flag view:

SELECT vst_cst_keyFROM vw_customer_member_flag (nolock)WHERE cst_cst_key = {CustomerKey} AND vst_member_flag = 1

In some cases, you may need a more refined visibility sql command. If, for example, you had a section could be viewed only by those who are an active member of a certain committee, you could expand the query to be:

SELECT cmc_cst_keyFROM mb_committee_x_customer&#40;NOLOCK&#41;WHERE &#40; cmc_cst_key = {CustomerKey} &#41;AND &#40; cmc_cmt_key ='b1377f09-c75c-42b8-81f4-fc4301f0c22c' &#41;AND &#40; cmc_start_date IS NULL OR cmc_start_date < getdate&#40;&#41; &#41;AND &#40; cmc_end_date IS NULL OR cmc_end_date > getdate&#40;&#41; &#41;AND &#40; cmc_delete_flag = 0 &#41;

In the example above, the value of [cmc_cmt_key] is a specific committee's key.

Note that once you have visibility SQL, login is required. The system won't even run the visibility SQL for an anonymous user.

If, for example, you wanted to have a Web Section that appears only to non-members or non-authenticated users, then the solution is two links. One for non-members, and one for anonymous users, which is disabled on login. The link for the anonymous user needs to be a hard coded link, because using an Web Link would tie into the visibility SQL and then wouldn't show up.

Visibility SQL to Prevent URL Tampering

For the CMS pages on which someone 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. See main article for more.

Optimizing SQL

Ensure that the SQL statement will run fast, as this statement will run very often. You may need to tune the SQL, check database indexes, etc.

Additionally, be sure your SQL works the following way:

1. Select only one column. There is no point in selecting more than 1 column since the Visiblity SQL test only cares about how many rows are returned; the number of composition of columns is irrelevant. Never do a "select *" or select more than one column, since you are needlessly wasting resources and slowing down the system.
2. Your SQL should only return at most 1 row. Since the visibility SQL test only cares about whether there are 0 or 1 rows, there is no point in having SQL return 2 or 20 or 200 rows. You are needlessly wasting resources and slowing down the system.
3. As always, use NOLOCK for all tables you select from.

If, despite all your efforts, the SQL is still slow, you may be forced to either simplify your restriction logic, or create a denormalized security table to store various settings that can be accessed faster than going to real-time data, and populate this table nightly with a SQL Job. Although this will speed your page loads, be aware that the data will no longer be real-time and you will now have one more database maintenance job to worry about.

Convert to SP or UDF

If you are running the same visibility SQL statement, or a variation of a SQL statement, in many places, you may want to convert it into a database SP or UDF with parameters for {CustomerKey} and any other necessary parameters. If possible, make it a UDF as this can run faster. This is advantageous both for speed and maintenance, as if you ever need to adjust the logic you can change it in one place in the database and all the pages that use the UDF will not need to be changed.

Example, this:

SELECT cst_key FROM dbo.co_customer (nolock) WHERE cst_key = {CustomerKey}

can be converted to this:

SELECT [visible] = 'yes' WHERE dbo.customer_is_member({CustomerKey}) = 1

where the definition of customer_is_member is:

CREATE FUNCTION dbo.customer_is_member(@cst_key av_key)returns bitASbeginRETURN (SELECT cst_member_flag FROM dbo.co_customer (nolock) WHERE cst_key = @cst_key)endgoGRANT exec ON dbo.customer_is_member TO netForumUser,netForumAdmin,AvectraAdmin,netForumReportgo

FAQ

Visibility SQL with CurrentUserGroups

Q. How can I use the {CurrentUserGroups} parsed value with visibility SQL?

A. Here's an example if you want for an item to be visible only if the logged-in iWeb user belongs to a security Group called EventManager:

SELECT [visible] = 'yes'WHERE charindex(';EventManager;', ';' + {CurrentUserGroups} + ';') > 0

The reason for wrapping the variables with semicolons is because the CurrentUserGroups variable lists the User's Groups, separated by a semicolon delimiter. By enclosing both variables with a semicolon, you ensure that you get an exact match on the Group you want to validate and not false matches with a group named like RegionalEventManager or EventManagerAdmin, for example.