Executing a SQL Query

Last Updated: Nov 9, 2021
documentation for the dotCMS Content Management System

The SQL ViewTool ($sql) allows any dotCMS user with proper permissions to run SQL statements from frontend pages. Running the SQL tool allows you to execute a SQL SELECT statement on any datasource defined in your context.xml file (including, but not limited to, the configured dotCMS database).

The SQL Viewtool will only work when used on pages (in either live/preview/edit modes or front-end display). Since Velocity code is not pulled or executed on the content search manager or other portlets, calls to the SQL Viewtool on custom fields, for example, will not return any results on the the dotCMS backend.

Warning: SQL queries through the SQL Tool access the database directly (without any caching or other performance enhancements), and should be avoided for performance reasons. For more information, and for steps to improve query performance, please see Performance Considerations, below.

Requirements

  • This ViewTool can only be executed from Contents or Widgets.
  • The user editing the Content or Widget must have the Scripting User Role to execute this ViewTool.

    Requirements to execute a SQL Query  
  • If the SQL Tool is intended to query a database other than the dotCMS database, the datasource must be defined in the context.xml file (/dotserver/tomcat-X.x.xx/webapps/ROOT/META-INF/context.xml).
    • Note: If you are running dotCMS on an application server other than Tomcat, you must configure a different connection pool directly in the Application Server.
  • If you want to use the SQL Tool for querying the dotCMS database:
    • You can create a different datasource with limited permissions over the database/schema.
    • If it's not possible to create a limited datasource, you can query the dotCMS database with the “default” datasource only if the ALLOW_VELOCITY_SQL_ACCESS_TO_DOTCMS_DB variable is set to true in the dotmarketing-config.properties file:
      ALLOW_VELOCITY_SQL_ACCESS_TO_DOTCMS_DB=true
      
    • Note: Restricted Queries are not allowed even when ALLOW_VELOCITY_SQL_ACCESS_TO_DOTCMS_DB is set to true.

Important: It is strongly recommended that changes to all dotCMS configuration files be made through configuration plugins.

Usage

Methods

MethodReturn ValueDescription
$sql.getSQLResults(datasource, query, startRow, maxRow)List of Query ResultsSubmits the specified query against the specified datasource(#getSQLResults), and returns the the specified rows of results.
$sql.getParameterizedSQLResults(datasource, query, params, startRow, maxRow)List of Query ResultsSubmits the specified query against the specified datasource, as a parameterized query using the specified params, and returns the the specified rows of results.

Parameters:

  • datasource is one of the following (quotes included):
    • “default”: The default datasource specified for accessing the dotCMS database.
    • “custom”: A custom datasource specified for limited access to the dotCMS database or any other database.
  • query is the SQL query you want to run.
  • params is a list of objects that will act as the query's parameters.
    • The size of this List and the amount of allowed parameters in the query must be the same, and set in the same order that they appear in the query.
    • Note: For parameterized queries, the question mark (?) character is required for parameterizing queries and conditions.
  • startRow specifies the starting row of the query results.
    • This allows results pagination. If pagination is not desired, set start_row to 0.
  • maxRow sets the maximum number of results to display from the query's output.

Return Values

The SQL Tool returns results in the following format:

ArrayList<HashMap>String, String<>

Each row of the query results is returned as a separate list item. Each item in the results list can be accessed individually, and the value of each column in that row can be accessed by appending the column name to the object syntax, as in the following psuedo-code:

#set( $results = $sql.getSQLResults($datasource, $query, $startRow, $maxRow) )
#foreach( $row in $results )
    <h3>$row.column1</h3>
    <p>$row.column2</p>
    <p>$row.column3</p>
#end

For a more complete example of accessing query results, please see the Examples, below.

Performance Considerations

As mentioned above, SQL Tool queries access the database directly. SQL queries are performed without any caching or other performance enhancements, and should be avoided. Therefore, for performance reasons please take into account the following considerations:

  • When possible, always perform content pulls thru Widgets and Velocity code instead of the SQL Tool, to take advantage of built-in dotCMS caching and performance features.
  • If it is necessary to use the SQL tool, wrap the code in Block Caching tags to cache the query results and limit the frequency of database access.
  • When possible, use Parameterized queries (getParameterizedSQLResults).
    • Parameterized queries typically have better performance than non-parameterized queries, since the SQL query does not need to be fully parsed and optimized each time the query is run.
    • This is especially true if you have a query which may be run frequently, or which may be run frequently with different values in the query. In this case, use the getParameterizedSQLResults method whenever possible.

Security Considerations

It is important to recognize that SQL statements which are created from user inputs may be susceptible to SQL injection vulnerabilities. However the dotCMS SQL Tool prevents SQL injection vulnerabilities by automatically rejecting any Restricted Queries - queries which include query terms which have the potential to modify the contents or structure of the database, or queries which attempt to access restricted areas of the database.

Restricted Queries

Restricted Terms

The SQL Tool will reject any query which includes certain query terms which could be used to alter the database.

Leading Terms

The following terms are restricted when they occur anywhere in the query string, followed by a space. Thus they will not be rejected if they are at the beginning of a longer variable name, but may be rejected if a variable name ends with them.

  • select
  • insert
  • delete
  • update
  • replace
  • create
  • drop
  • alter
  • truncate
  • declare
  • exec
  • lock
  • unlock
  • write
  • not
  • set
Embedded Terms

The following terms are restricted when they occur anywhere in the query string, with a space both before and after them. This means they will be rejected if they appear as whole words, but will not be rejected if they appear as part of a variable name (as long as the variable name includes some other characters).

  • distinct
  • like
  • and
  • or
  • limit
  • group
  • order
  • as
  • count
  • where
  • procedure
  • engine
  • mode
Total Restrictions

The following terms are restricted anywhere in the query string (with or without preceding or following spaces). This means they can not be used anywhere, including as part of variable names or strings used to match field/column contents.

  • pg_
  • null
  • ;

Important: Queries will be rejected if any of these terms are found anywhere in the query string; therefore it is important that you do not use any of these terms as part of your content being queried.

Restricted Areas

In addition, the SQL tool will reject attempts to access any of the following database areas (even just for read access):

  • Any query that accesses the user_ or cms_role tables from the dotCMS database.
  • Any queries or operations set at database level by the DBA.

Parameterizing Queries

Although the SQL Tool will prevent SQL injection attacks by enforcing the Restricted Queries, you may add an additional layer of security by ensuring that you only use parameterized queries (e.g. use the getParameterizedSQLResults() method instead of getSQLResults()).

Parameter queries prevent SQL injection attacks by treating all user inputs as data only; when user data is added to a query as a parameter, that data is never passed through the SQL parser or compiler, so there is no chance for SQL injection queries. So when performing queries that include user data, you can can use the getParameterizedSQLResults() method to ensure maximum security (and performance).

Examples

The following examples demonstrate using the SQL Tool to access data in several different ways.

Example 1: Query the Employee Content Type Using $sql.getSQLResults

Returns the First Name, Last Name, Title, Email, and Phone number for up to 25 content items of the Employee Content Type. Note that the Employee Content Type is specified by it's inode.

   <h2>Calling the SQL Tool from a Content:</h2>
    <br/>
    #set($query = "SELECT distinct identifier,text1,text3,text4,text6 FROM Contentlet WHERE structure_inode='a1661fbc-9e84-4c00-bd62-76d633170da3'") 
    <strong>Query: </strong> $!{query}
    <br/>
    <br/>
    #set($resultsList = $sql.getSQLResults("default", "$!{query}", 0, 25))

    <table border="1">
        <tr>
            <th><h3>Product Number</h3></th>
            <th><h3>Title</h3></th>
            <th><h3>Retail Price</h3></th>
            <th><h3>Sale Price</h3></th>
        </tr>
        #foreach($product in $resultsList)
            <tr>
            <td>$!{product.text4}</td>
            <td>$!{product.text1}</td>
            <td>$!{product.text3}</td>
            <td>$!{product.text6}</td>
            </tr>
        #end
    </table>

]]#

Output:

The above code produces the following output when run against the dotCMS starter database or dotCMS demo site:

Example 1 Output

Example 2: Query Page Asset Content Using $sql.getSQLResults

Returns the ID, Parent folder path, Page Name, and Asset Type for up to 4 items of “Page Asset” content.

<h2>Calling the SQL Tool from a Content:</h2>
<br/>
#set($query = "select * from identifier where id in (select identifier from contentlet where structure_inode in (select inode from structure where structuretype = 5 AND velocity_var_name = 'htmlpageasset'))")

<b>Query:</b> $!{query}

#set($resultsList = $sql.getSQLResults("default", "$!{query}", 0, 4))
<br/>
<br/>
#if($resultsList.size() > 0)
    #foreach ($res in $resultsList)
        <li><b>ID:</b> $res.get("id")</li>
        <ul>
            <li><b>Parent Path:</b> $res.get("parent_path")</li>
            <li><b>Asset Name:</b> $res.get("asset_name")</li>
            <li><b>Asset Type:</b> $res.get("asset_type")</li>
        </ul>
    #end
    <br />
#end

Output:

The above code produces the following output when run against the dotCMS starter database or dotCMS demo site:

Example 2 Output

Example 3: Query Page Asset Content Using $sql.getParameterizedSQLResults

This example is similar to the previous example, but the structure type and Name are set to 5 and Page Asset, respectively, using parameters.

Returns the ID, Parent folder path, Page Name, and Asset Type for up to 4 items of “Page Asset” content.

<h2>Calling getParameterizedSQLResults Tool from a Content:</h2>
<br/>

#set($paramsList = $contents.getEmptyList())
#set($temp = $paramsList.add(5))
#set($temp = $paramsList.add("htmlpageasset"))

#set($query = "select * from identifier where id in (select identifier from contentlet where structure_inode in (select inode from structure where structuretype = ? AND velocity_var_name = ?))")

<b>Query:</b> $!{query}
<br/>
<b>Params:</b> $!{paramsList}
<br/>
<br/>

#set($resultsList = $sql.getParameterizedSQLResults("default", "$!query", $paramsList, 0, 4))

#if($resultsList.size() > 0)
    #foreach ($res in $resultsList)
        <ul>
            <li><b>ID:</b> $res.get("id")</li>
            <li><b>Parent Path:</b> $res.get("parent_path")</li>
            <li><b>Asset Name:</b> $res.get("asset_name")</li>
            <li><b>Asset Type:</b> $res.get("asset_type")</li>
        </ul>
    #end
    <br />
#end

Output:

The above code produces the following output when run against the dotCMS starter database or dotCMS demo site:

Example 3 Output

Toolbox.xml Configuration

The SQL Tool is enabled by default in dotCMS. To ensure the SQL Tool is enabled, verify that the following lines exist in your toolbox.xml file:

<tool>
    <key>sql</key>
    <scope>request</scope>
    <class>com.dotmarketing.viewtools.SQLResultsViewTool</class>
</tool>

References

For complete documentation on this viewtool, please see the following documentation:

On this page

×

We Dig Feedback

Selected excerpt:

×