MicroStrategy ONE

Using Secure Freeform SQL Text Prompts

For security reasons, Freeform SQL reports using the text prompt feature should use parameterized queries (also called prepared statements) to guard against malicious SQL injections. For data sources that do not support parameterized queries, a redesign of the Freeform SQL reports may be necessary.

The following steps provide an overview for configuring secure Freeform SQL text prompts:

Step 1: Identify Database Support for Parameterized Queries

Step 2: Enabling Parameterized Queries (for supported databases)

Step 3: Define Secure Freeform SQL Reports (for supported and unsupported databases)

Note the following regarding parameterized queries:

  • Escaping special characters in input data is not required because SQL instruction and data get separated.
  • Single quotes provided as user input data are always escaped to reduce the risk of an SQL injection attack.

As a result, the following VLDB setting is deprecated:

<PROPERTYDEF NAME="FFSQL Text Prompt Escape Special Characters" TYPE="long" DEFAULT="1" GROUPS="DBRole,DBMS" />

Identify Database Support for Parameterized Queries

  • Supported databases:
    The following databases support parameterized queries without additional Data Source Name (DSN) settings:
    • Actian Vectorwise with native driver
    • Amazon Redshift with PostgreSQL driver
    • Amazon Redshift with MicroStrategy driver
    • Aster nCluster 4.6.x and higher with native driver
    • EnterpriseDB with native driver
    • EXASolution with native driver
    • HP Neoview with native driver
    • IBM DB2 UDB for iSeries with MicroStrategy driver
    • IBM DB2 UDB for iSeries with native driver
    • IBM DB2 UDB for Linux, UNIX & Windows with MicroStrategy driver
    • IBM DB2 UDB for Linux, UNIX & Windows with native driver
    • IBM DB2 UDB for z/OS with MicroStrategy driver
    • IBM DB2 UDB for z/OS with native driver
    • IBM Netezza with native driver
    • Infobright with MicroStrategy driver
    • Informix Dynamic Server with native driver
    • Microsoft SQL Database with MicroStrategy driver
    • Microsoft SQL Database with native driver - Windows
    • Microsoft SQL Server with SQL Server - Windows
    • Microsoft SQL Server with Native Client - Windows
    • Microsoft SQL Server with MicroStrategy driver
    • MySQL Community Server with native driver
    • Oracle with native driver
    • ParAccel with native driver
    • Red Brick with native driver
    • Salesforce with MicroStrategy driver
    • SAP HANA 1.0 Sp2 with native driver
    • Sybase IQ with native driver
    • Teradata with native driver
    • Text files with native driver
    • Vertica with native driver
  • Supported databases after configuration
    The following databases support parameterized queries after being configured to "Enable Describe Parameters":
    • Greenplum with MicroStrategy driver
    • MySQL Enterprise with MicroStrategy driver
    • Oracle with MicroStrategy driver
    • PostgreSQL with MicroStrategy driver
    • Sybase ASE with MicroStrategy driver

    • MicroStrategy ODBC Driver for Oracle Wire Protocol

    • MicroStrategy Driver for Sybase ASE Wire Protocol

      For MicroStrategy Intelligence Server running under UNIX/Linux, the following line should be added to the DSN definitions in odbc.ini, located in the MicroStrategy home directory chosen during installation. This line is valid for the MicroStrategy ODBC drivers for both Oracle and Sybase ASE.

      EnableDescribeParam=1
    • Teradata ODBC Driver version 12.00.00.00 or later, with the "Enable Extended Statement Information" parameter checked.
      • Teradata 12.0

      • Teradata V2R6.2

        For MicroStrategy Intelligence Server running under UNIX/Linux, the following line should be added to the Teradata DSN definition(s) in odbc.ini:

        EnableExtendedStmtInfo=Yes
  • Unsupported databases
    The following database do not support parmameterized queries
    • Apache Hive with MicroStrategy driver
    • Aster nCluster with native driver
    • Composite with native driver
    • Hadoop Amazon EMR Cloud with MicroStrategy driver
    • Hadoop Apache with MicroStrategy driver
    • Hadoop/Hive with Cloudera driver
    • Hadoop MapR M3/M5/M7 with Simba driver
    • Hadoop MapR M3/M5/M7 with MicroStrategy driver
    • Impala with MicroStrategy driver
    • Informix Dynamic Server with MicroStrategy driver
    • Informix Extended Parallel Server with MicroStrategy driver
    • Kognitio WX with native driver
    • SAND CDBMS 6.1 with native driver
    • Text files with MicroStrategy driver

Enabling Parameterized Queries

To use parameterized queries, the MicroStrategy Database Connection object, which holds connection parameters to the database, must be enabled, as described in the following procedure.

Enabling Parameterized Queries

  1. In MicroStrategy Developer, under the Folder List, expand MicroStrategy Analytics Modules (Direct), Administrator, and Configuration Managers.
  2. Select Database Instances, as shown in the following figure.

  3. Choose File > New > Database Instance. The Database Instances dialog box opens.
  4. Under "Database connection (default)", click Modify… The Database Connections dialog box opens.
  5. Click the Advanced tab.
  6. Select the Use parameterized queries check box.

  7. Click OK. The Database Connection dialog box closes.
  8. Click OK. The Database Instances dialog box closes.

Define Secure Freeform SQL Reports

To use parameterized queries, you may have to define the Freeform SQL reports in a certain way. The following sections provide examples of defining Freefrom SQL reports with parameterized queries.

Data Sources Which Support Parameterized Queries

Example: A report designer creates a Freeform SQL report and expects the user to provide one or more prompt answers and run the report .

  1. A single text prompt object is sufficient to allow single/multiple prompt answers. The report definition must not contain single quotes around text prompt object.

  2. When the report is run, the user can provide a single or multiple answers to the text prompt. The following are the requirements for the answer pattern.

    Pattern requirement for single prompt answer: There is no specific requirement for following a pattern while answering the prompt with a single value. However, when you have conflict (see third example below) between pattern and the prompt answer then you may want to use the pattern. Examples of prompt answers and expected results are shown below.

    Pattern requirement for multiple prompt answers: User must follow the specific pattern while answering the prompt with multiple answers. All the answers must be in a pair of single quotes separated by a comma (without white spaces). An example is show below.

    You don't need to escape any special character like single quote if it is part of your answer.

Data Sources Which Do Not Support Parameterized Queries

If the data source does not support parameterized queries, you cannot configure the report to accept multiple answers using the single text prompt due to risk of malicious SQL injection. However, you can redesign the Freeform SQL report to contain multiple text prompts and make them required/optional as needed. See the following example.

A report designer creates a Freeform SQL report and expects the user to provide at least one prompt answer and at most two prompt answers, and the report would display all the provided answers:

  1. Two text prompt objects are needed in the report definition. The report definition must contain single quotes around each text prompt object.

  2. Create the first prompt and make the prompt answer required.

  3. Create a second prompt and make the prompt answer optional.

  4. Since the second prompt is optional, make the related SQL query dependent on the prompt by selecting the dependent query and choosing Prompt-dependent SQL from the context menu.

  5. When the report is run, the user must provide a single answer for each text prompt object. Below are examples: