Variables are used to provide a quick and protected solution to using dynamic parameters within your SQL Statements. Similar to the logical process of using parameters in stored procedures, the query variable provides a mechanism for passing values from nearly any source within your runtime directly into the query or queries. To add a Query Variable to your query, first add the Variable to your configuration by clicking the Varaible button in the Ribbon.
The resulting display provides the ability to specify the variable type from the following list:
<Session> - Identifies a custom session variable value.
<Query String> - Identifies a querystring variable value.
<Form> - Identifies a form variable value.
<ViewState> - Identifies a viewstate variable value.
<Message> - Identifies a Message variable value.
<Custom> - Identifies that custom, ListX based tag structure fulfills the Source value. Place your complex ListX string in the Source area, in place of a variable name.
<Action> - Identifies an action variable value.
ModuleId - Returns the Id of the ListX Module as it exists within the DotNetNuke Portal environment.
Owner - Return the database owner defined in the web.config, followed by a decimal point.
PageNumber - Gets the number of the current data result page.
PageSize - Gets the total number of records to display per page.
PortalAlias - Returns the full domain link to the current portal.
PortalId - Gets the Id of the current Portal.
Qualifier - Returns the database qualifier defined in the web.config, followed by an underscore.
UserId - Fetches the Id of the currently logged in user.
Once the type has been selected, a Source and Target are necessary, and optionally, additional format handling is provided for padding the resulting data, when it is not empty, or provisioning a specific Empty syntax when the result is empty.
Source is necessary when any <Type> is the selected type. Source is used to identify the name of the variable to return from the type entries.
Target is the string representation to locate within the Query and replace with the Source value.
Data Type provides the ability to identify whether the Source parameter meets the data requirement. If so, the value is passed on, if false, the value results as empty.
Target Left is placed to the left of the resulting target replacement, whenever the target result is not an empty string. (Read the following example accompanying the screen shot for a better understanding).
Target Right is placed to the right of the resulting target replacement, whenever the target result is not an empty string. (Read the following example accompanying the screen shot for a better understanding).
Target Empty is used automatically whenever the resulting target value is empty. The Target Left, Target Right and Target Empty parameters are particularly useful when you need to handle NULL values within your database.
As seen in the previous screen shot we are expecting an incoming Form variable, which is named txtDescription. Assuming that we will be using this variable within an Update statement, and that when txtDescription is left within no value we want to store NULL within the target database. The query would need to look something like this:
UPDATE myTable Set Description = @txtDescription
IF txtDescription has the value of “ListX Description Example” the resulting statement would be:
UPDATE myTable Set Description = ‘ListX Description Example’
Otherwise, if the txtDescription has the value of “” (an empty string), the resulting statement would be:
UPDATE myTable Set Description = NULL
There are three possible types of user interaction which could cause “Injection” security issues with the ListX engine: SQL, HTML and SCRIPT
Remember that variables passed from a browser to the web server and then to the database server have the potential of being tampered with, allowing the client user to gain access to some vital database information through a concept known as SQL Injection. To protect against this behavior, you should leave the Protect Against SQL Injection option checked. When checked, all values are automatically replaced with escape characters whenever quotes are contained within the string. This breaks the possibility for SQL Injection Attacks to transpire.
PLEASE READ THE FOLLOWING SO YOU KNOW THE STANDARDS THAT SHOULD BE USED WITHIN OWS.
- ALWAYS use Query Variables.
- ALWAYS check the box to protect against SQL Injection.
- WHEN using SQL Injection protection, ALWAYS place single quotes to the left and right of the consumed value - either IN the Query Variable definition (the main reason for the left and right attributes there), or within the Query itself.This is the default behaviour.
- NEVER place ANY replacement values that could have been touched by a user directly in a QUERY. If you place [x,Querystring] in your Query directly - "x" is something that the user has access to before it goes to the server for processing. So they can place whatever they want in "x" even if you are anticipating an integer (which is the problem most users experience). This goes for Cookie and form variables also. And, sometimes embedded flow values that have been assigned to the session or actions. Meaning, if you assign an Action variable the value from the querystring, you will still have the same problem. Which is why Query variables are so important.
- Whenever the data type of your source is KNOWN (for example, you know your ID is a Number), be sure to select the specific type you desire within the attributes. When this is used, a value not matching the type is automatically assumed as EMPTY.
Since OWS is a powerful runtime script language, you may have users who type syntax, like a SUBQUERY tag directly into a form entry field. It is important to handle these values as hostile, so that you don’t have problems with your data. There are three types of escaping of OWS content, which use the familiar backslash character.
Do Not Escape OWS Tags – means we will leave them intact.
– the default option, escapes OWS tags with one backslash, so that the incoming content is simply stored in the database as it was entered. If you escape only once, the review of the data, as in [VALUE] will result in the execution of the physical tag stored within the content.
– the most secure option, escapes all tags twice, meaning that they are not executed when stored in the database, and whenever they are pulled out of the database they appear exactly as they were typed.