We all know that collecting information from users can be potentially dangerous. Not because the user means to be malicious, but because you may not have a procedure/function in place to handle single quotes or double quotes. This can cause issues when the data is passed to the database. In most cases a SQL Stored Procedure can handle “SQL Injection” with single quotes and double quotes. However with Dynamic Forms or Dynamic Registration tokens should be encapsulated inside of '$(Token)'. So if you’re passing a Token into a stored procedure call:

exec MyProcedure '$(Token)'

You can see how collecting a ' in a textbox and passing to a stored procedure or inserting directly into a database table can become an issue. For instance, let’s say that the value I provided for a textbox in Dynamic Forms was:

When using the $(FirstName) token from this form in a SQL Completion Event the value would render as:

'John O'Neal'

You can see how handling this on the client side instead of Server side can be beneficial within Dynamic Forms or Dynamic Registration.

Add this JavaScript function to your Dynamic Form or Dynamic Registration Custom JavaScript file:

-------------------------------------------------------------------------------------------------------------------------

function Replace_Single_Double_Quotes(DF_QuestionID)
{
    //Assigning passed in parameter to variable
    var QuestionValue = document.getElementById(DF_QuestionID).value;
   
    //This field will assist us in knowing whether to replace " with a left or right double quote
    var NeedRightQuote = 'False';

    //Loop that checks each character in the QuestionValue variable
    for ( var i = 0; i < QuestionValue.length; i++ )
    {
        //Is this character a '?
        if(QuestionValue.charAt(i) == "'")
        {
            //Replace ' with an apostrophe
  &

Dynamic Forms & Dynamic Registration require that you return at least two columns for a SQL Driven questions: QuestionOption and QuestionOptionValue

QuestionOption = Text User will see

QuestionOptionValue = Value behind the Item that User will see

For Dynamic Views, you can have SQL Driven Combo Boxes and Radio Buttons for Search Filters. This requires that you return at least two columns named SearchOption and SearchOptionValue.

SearchOption = Text User will see

SearchOptionValue = Value behind the Item that User will see

This means that you can use the queries below in Dynamic Forms / Dynamic Registration or Dynamic Views by simply changing the alias of the columns being returned.

Example:

Query for Dynamic Forms / Dynamic Registration:

SELECT RoleName AS QuestionOption, RoleId AS QuestionOptionValue FROM Roles 

Query for Dynamic Views:

SELECT RoleName AS SearchOption, RoleId AS SearchOptionValue FROM Roles 

Below are queries that I use often:

1.) Get States
---------------------------------------------------------------------------------------------------

SELECT '-- Select State --' AS QuestionOption, '-1' AS QuestionOptionValue, '0' AS SortOrder
UNION ALL
SELECT Text AS QuestionOption, Value AS QuestionOptionValue, Text AS SortOrder
FROM Lists
WHERE ListName = 'Region' AND ParentID = 221
ORDER BY SortOrder

---------------------------------------------------------------------------------------------------

2.) Get States and Territories:
---------------------------------------------------------------------------------------------------

SELECT '-- Select State --' AS QuestionOption, '-1' AS QuestionOptionValue, '0' AS SortOrder
UNION ALL
SELECT Text AS QuestionOption, Value AS QuestionOptionValue, Text AS SortOrder
FROM Lists
WHERE ListName = 'Region'
ORDER BY SortOrder

---------------------------------------------------------------------------------------------------

3.) Get Countries with United States at the top:

---------------------------------------------------------------------------------------------------

SELECT '-- Select Country --' AS QuestionOption, '-1' AS QuestionOptionValue
UNION ALL
SELECT Text AS QuestionOption, Value AS QuestionOptionValue
FROM Lists
WHERE ListName = 'Country' AND Text = 'United States'
UNION ALL
SELECT Text AS QuestionOption, Value AS QuestionOptionValue
FROM Lists
WHERE Lis