Columns and Values

By Kevin M Schreiner

June 03, 2009

Obtaining values from your Form, Querystring, Cookies, Viewstate, Session, Actions,Subquery, and other Database functionality is easy and well defined within Open Web Studio. The source of the value, referred to as the Collection is identified as an element contained in the previously stated list. The Name refers to the specific key within the collection (like the Column Name within the table, or the Parameter Key within the Querystring). Finally some specific validations are provided that are referred to as the type casting.

Syntax

[ Name,Collection:Type list]
[ Name,Collection ]
[ Name ]

Name


The Name is the key used to reference the value within the collection. For example - given the following URL "http://www.openwebstudio.com/default.aspx?test=xyz", "test" would be considered the name.

 

Collection


The list containing the key referenced by the Name parameter. There are a variety of collections for the system: Form, Querystring, Cookies, Viewstate, Session, Actions. Database columns are referenced with the collection as the name of the Subquery or Action:Query]. For retrieving columns out of the [[Action:Template#Query Template|Query Template no collection is provided.

Type


The Type identifies how the value should be casted or verified. This is a combination of validation of values, as well as protection against SQL Injection attacks. For example, if the "test" parameter from the url (querystring) should always be a number it can be cast as such. When the value is NOT a number it will simply be left empty. The following options are available for the Type attribute:

 

  • None - This means NO assumed casting is in place, and all values should be excluded from SQL Injection detection and filtering. This case is the default for ALL collections OTHER THAN QUERYSTRING. Querystrings default to "Any", which means that they will always be checked for SQL Injection and filtered automatically, unless "None" is explicitly used as the type.
  • Any - This means that the value is assumed to be of any variant type like a string. The value will be automatically filtered for SQL Injection, but otherwise unaltered.
  • Boolean - This checks the value to determine if it is True/False. If the value fails to meet the requirement it is returned as an empty value.
  • Date - This checks the value to determine if it is in an appropriate date format. If the value fails to meet the requirement it is returned as an empty value.
  • Guid - This checks the value to determine if it is in a format appropriate for globally unique identifiers. If the value fails to meet the requirement it is returned as an empty value.
  • Number - This checks the value to determine if it is Numeric. If the value is a NOT a number an empty value is the result.
  • String - (see Any).

Values that are found to contain SQL Injection will result in missing symbolic characters in the value. SQL Injection is detected as the value containing any of the following terms:insert, delete, select, union, where, declare, exec. When identified, the value is then filtered to remove any of the standard SQL delimiters or symbolic characters: ("*^';&<>)

Using these SQL Injection check rules, the following example would be the result:

url:  default.aspx?test=123;DECLARE+@S+VARCHAR(4000);SET+@S=CAST(0x4445F43415445205461626C655F437572736F7220+AS+VARCHAR(4000));EXEC(@S);
token: [test,querystring]
output: 123DECLARE @S VARCHAR4000SET @S=CAST0x4445F43415445205461626C655F437572736F7220 AS VARCHAR4000EXEC@S

As the example shows, the result would be a harmless string that would never pass into SQL with an appropriate structure to cause any execution.

List


In coordination with the Type checking, a List check will automatically split the value on "," and verify each element contained within the resulting list. For example - given the following value:

url:  default.aspx?test=,,,1,2,abc,45,adsce,6sdwqfqw
token: [test,querystring:number list]
output: 1,2,45

Column Values


Replacing cell values from your columns with the actual value from the cell during the output generation is made simple by using the built in syntax. Two styles of handling are provided for column data output. The basic output is made simplistic, it simply requires you to place your column name in square brackets - [...].

Formatting Values


An advanced, yet extremely useful feature provides the ability to format you values using either date, or numeric formatters supplied by the .Net Framework environment. Formatting is done by using the [$...] syntax, with the Column Name as the first variable, and a second variable containing the format attributes. Follow the syntax definitions provided to assist in building the appropriate output block. While there are three possible ways to format your values from the system, it is always important to understand that you should use COALESCE in nearly all situations when formatting is required. This is because COALESCE has far superior capabilities, and is more generally accepted as the standard format technique that both pre "[$...]" and post "[FORMAT,...]" formatting.

System Values and Parameters


A variety of system values are available directly within the standard token structure. When the General_Settings dictate that the System parameters must be followed by ",System" like "[ModuleID,System]" that must be followed. This is the default methodology and all examples will do it using that pattern.

[ALIAS,System]
[LOCALHOSTALIAS,System] – ie: localhost/Demo32 would be /Demo32, www.openwebstudio.com/Demo32 remains.
[MODULEID,System]
[TABID,System]
[TABMODULEID,System]
[CLIENTID,System]
[ID,System]
[MODULEPATH,System]
[PAGENUMBER,System]
[TOTALPAGES,System]
[TOTALRECORDS,System]
[USERNAME,System]
[FIRSTNAME,System]
[LASTNAME,System]
[FULLNAME,System]
[USERID,System]
[DATE,System]
[LOCALE,System] - returns the locale in lowercase. eg. en-us, en-gb, pl-pl.
[URLREFERRER,System] – returns the “previous” link, or the url that drove the user to this page.
[FORM,System] – returns the entire form, in textual format
[RESPONSESTATUS,System]  - returns the response status value (from the browser)
[RESPONSETEXT,System] – returns the physical response in textual format
[OBJECTQUALIFIER,System] – returns the configured objectQualifier from DotNetNuke database setting
[DATABASEOWNER,System] – returns the configured databaseOwner from the Dotnetnuke settings

Advanced System Values


Along with the provided system variables, you may also pull from many runtime and environmental values which are available. These variables may be contained within the requesting Form, QueryString, ViewState, Cookies, Session and ModuleSettings using the following structures.

[ItemName,Session]
[ItemName,QueryString]
[ItemName,Form]
[ItemName,ViewState]
[ItemName,Cookie]
[ItemName,ModuleSetting]
[MessageType,Message]
[ItemName,Action]


Input File (Form) Values


[frmFile.Path,Form] – returns the full directory name of the file that is received through the form, from the source machine.
[frmFile.Name,Form] – returns the full file name, including the extension of the file.
[frmFile.NameOnly,Form] – returns the name, without the extension for the file.
[frmFile.Extension,Form] – returns the extensions of the file.
[frmFile.Type,Form] – returns the MIME type (discussed in the appendix)
[frmFile.Length,Form] – returns the physical byte length of the incoming file.
[frmFile.Height,Form] – Returns the vertical height of the file if the file is an Image
[frmFile.Width,Form] – Returns the horizontal width of the file if the file is an Image


File (CSV/SQL) Import Process Values


[processName.Complete,Action] – returns true or false
[processName.Status,Action] – returns the number of records imported so far.
[processName.Percent,Action] – returns the overall percentage of completion for the import.
Localization/Resource File Values
Many times you may desire to pull values from the standard Localization files, or from custom Localization resource files. The [LOCALE] tag has been extended to allow just this behavior. To use Locale, you must specify either the [LOCALE] tag, which will return the current Language selections - ex: en-US. Or, to load key information from a specific resource file, you would need to provide the path of the file, as well as the key - like this:

[LOCALE,Session/App_GlobalResources/GlobalResources.resx,MESSAGE_PORTAL_TERMS.Text]


Runtime Values (Reflection)


The physical runtime has now been exposed within Open Web Studio, so you can gain access directly to any of the properties, functions and variables which require no parameters. This means, that you can get access to all the deeper knowledge of the runtime system, without getting your code hands dirty. An example of this is provided  in as simple a logic as the [Request.UserAgent,System] information, which will return the users browser information. Or, as conceptual as [UserInfo.Membership.LastLoginDate,System]

The engine is completely intelligent in the parsing, so you can easily access any level of depth within the current architecture or may expand to any depth required, simply by using the tag [NAME.NAME.NAME,System] – The following list is a representation of most of the runtime values you currently have access to, based on the DotNetNuke runtime which you are running.

  • Context  - includes all aspects from the httpContext model provided by ASP.NET, please refer to the HttpContext documentation.
    • Context.AllErrors
    • Context.Application
    • Context.ApplicationInstance
    • Context.Cache
    • Context.Current
    • Context.Error
    • Context.Handler
    • Context.IsCustomErrorEnabled
    • Context.IsDebuggingEnabled
    • Context.Items
    • Context.Request
    • Context.Response
    • Context.Server
    • Context.Session
      • Context.Session.SessionID
    • Context.SkipAuthorization
    • Context.Timestamp
    • Context.Trace
    • Context.User
  • PortalSettings – Provides all the DotNetNuke runtime Portal Settings, as they are defined within the DotNetNuke architecture.
    • PortalSettings.ActiveTab
      • PortalSettings.ActiveTab.AdministratorRoles
      • PortalSettings.ActiveTab.AuthorizedRoles
      • PortalSettings.ActiveTab.BreadCrumbs
      • PortalSettings.ActiveTab.ContainerPath
      • PortalSettings.ActiveTab.ContainerSrc
      • PortalSettings.ActiveTab.Description
      • PortalSettings.ActiveTab.DisableLink
      • PortalSettings.ActiveTab.EndDate
      • PortalSettings.ActiveTab.FullUrl
      • PortalSettings.ActiveTab.HasChildren
      • PortalSettings.ActiveTab.IconFile
      • PortalSettings.ActiveTab.IsAdminTab
      • PortalSettings.ActiveTab.IsDeleted
      • PortalSettings.ActiveTab.IsSuperTab
      • PortalSettings.ActiveTab.IsVisible
      • PortalSettings.ActiveTab.KeyWords
      • PortalSettings.ActiveTab.Level
      • PortalSettings.ActiveTab.Modules
      • PortalSettings.ActiveTab.Panes
      • PortalSettings.ActiveTab.PortalID
      • PortalSettings.ActiveTab.SkinPath
      • PortalSettings.ActiveTab.SkinSrc
      • PortalSettings.ActiveTab.StartDate
      • PortalSettings.ActiveTab.TabID
      • PortalSettings.ActiveTab.TabName
      • PortalSettings.ActiveTab.TabOrder
      • PortalSettings.ActiveTab.TabPath
      • PortalSettings.ActiveTab.TabPermissions
      • PortalSettings.ActiveTab.TabType
      • PortalSettings.ActiveTab.Title
      • PortalSettings.ActiveTab.Url
    • PortalSettings.AdministratorId
    • PortalSettings.AdministratorRoleId
    • PortalSettings.AdministratorRoleName
    • PortalSettings.AdminTabId
    • PortalSettings.BackgroundFile
    • PortalSettings.BannerAdvertising
    • PortalSettings.Currency
    • PortalSettings.DefaultLanguage
    • PortalSettings.Description
    • PortalSettings.DesktopTabs
    • PortalSettings.Email
    • PortalSettings.ExpiryDate
    • PortalSettings.FooterText
    • PortalSettings.HomeDirectory
    • PortalSettings.HomeDirectoryMapPath
    • PortalSettings.HomeTabId
    • PortalSettings.HostFee
    • PortalSettings.HostSpace
    • PortalSettings.KeyWords
    • PortalSettings.LoginTabId
    • PortalSettings.LogoFile
    • PortalSettings.PortalAlias
    • PortalSettings.PortalId
    • PortalSettings.PortalName
    • PortalSettings.RegisteredRoleId
    • PortalSettings.RegisteredRoleName
    • PortalSettings.SiteLogHistory
    • PortalSettings.SplashTabId
    • PortalSettings.SuperTabId
    • PortalSettings.TimeZoneOffset
    • PortalSettings.UserRegistration
    • PortalSettings.UserTabId
    • PortalSettings.Version
  • UserInfo
    • UserInfo.AffiliateID
    • UserInfo.FirstName
    • UserInfo.FullName
    • UserInfo.IsSuperUser
    • UserInfo.LastName
    • UserInfo.Membership.Approved
    • UserInfo.Membership.CreatedDate
    • UserInfo.Membership.Email
    • UserInfo.Membership.LastLockoutDate
    • UserInfo.Membership.LastLoginDate
    • UserInfo.Membership.LockedOut
    • UserInfo.Membership.Password – Supported only in versions of DNN preceeding 4.3x and 3.3x
    • UserInfo.Membership.Username
    • UserInfo.PortalID
    • UserInfo.Profile.Cell
    • UserInfo.Profile.City
    • UserInfo.Profile.Country
    • UserInfo.Profile.Fax
    • UserInfo.Profile.FirstName
    • UserInfo.Profile.FullName
    • UserInfo.Profile.IM
    • UserInfo.Profile.LastName
    • UserInfo.Profile.PostalCode
    • UserInfo.Profile.PreferredLocale
    • UserInfo.Profile.Region
    • UserInfo.Profile.Street
    • UserInfo.Profile.Telephone
    • UserInfo.Profile.TimeZone
    • UserInfo.Profile.Unit
    • UserInfo.Profile.Website
    • UserInfo.UserID
    • UserInfo.Username
  • Request
    • Request.AcceptTypes
    • Request.ApplicationPath
    • Request.Browser
    • Request.ClientCertificate
    • Request.ContentEncoding
    • Request.ContentLength
    • Request.ContentType
    • Request.Cookies
    • Request.CurrentExecutionFilePath
    • Request.FilePath
    • Request.Files
    • Request.Filter
    • Request.Form
    • Request.Headers
    • Request.HttpMethod
    • Request.InputStream
    • Request.IsAuthenticated
    • Request.IsSecureConnection
    • Request.Item
    • Request.Params
    • Request.Path
    • Request.PathInfo
    • Request.PhysicalApplicationPath
    • Request.PhysicalPath
    • Request.QueryString
    • Request.RawUrl
    • Request.RequestType
    • Request.ServerVariables
    • Request.TotalBytes
    • Request.Url
    • Request.UrlReferrer
    • Request.UserAgent
    • Request.UserHostAddress
    • Request.UserHostName
    • Request.UserLanguages
  • Response
    • Response.Buffer
    • Response.BufferOutput
    • Response.CacheControl
    • Response.Charset
    • Response.Clear
    • Response.ClearContent
    • Response.ClearHeaders
    • Response.Close
    • Response.ContentType
    • Response.Expires
    • Response.ExpiresAbsolute
    • Response.IsClientConnected
    • Response.RedirectLocation
    • Response.Status
    • Response.StatusCode
    • Response.StatusDescription
    • Response.SuppressContent
  • Session
    • Session.Abandon
    • Session.Clear
    • Session.CodePage
    • Session.Count
    • Session.IsCookieless
    • Session.IsNewSession
    • Session.IsSynchronized
    • Session.LCID
    • Session.Mode
    • Session.SessionID
    • Session.Timeout
  • ViewState
    • ViewState.Clear
    • ViewState.Count
  • Engine – The basis class that physically renders the Open Web Studio output, there are a few properties which can be utilized to a better advantage when necessary
  • Me.ClearCache – clears the web and module cache when executed
  • Me.GetRequestForm – returns a string representation of the entire incoming form request object.

 

Average (3 Ratings):
 
Want to help out?
 
 

New York, NY • Baltimore, MD • Vienna, VA • St. Louis, MO • Seattle, WA • info@openwebstudio.com

Bookmark & Share Bookmark and Share