Executing a SQL Query - Documentation topics on: executing a sql query,sql query,.

Executing a SQL Query

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).

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.

     
  • 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.

getSQLResults

Usage

#set($resultsList = $sql.getSQLResults(String datasource, String query, int startRow, int maxRow))

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.
  • 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.

getParameterizedSQLResults

Usage

#set($resultsList = $sql.getParameterizedSQLResults(String datasource, String query, ArrayList<Object> params, int startRow, int maxRow))

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.
    • Restricted queries and SQL expressions are:
      • DELETE
      • DROP
      • TRUNCATE
      • ALTER
      • CREATE
      • UPDATE
      • Any query that accesses the user_ or cms_role tables from the dotCMS database.
      • All queries or operations set at database level by the DBA.
  • 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.

Output

The output of this tool takes the following format:

ArrayList<HashMap>String, String<>

Performance Considerations

As mentioned above SQL Tool queries access the database directly, 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.

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

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

The following terms are restricted anywhere in the query string (with or without preceding or following spaces). This means they can not include anywhere as part of variable names or other content.

  • 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/>
    Query: SELECT distinct identifier,text1,text2,text3,text4,text5 FROM Contentlet WHERE structure_inode='ddf29c1e-babd-40a8-bfed-920fc9b8c77f'

    #set($query = "SELECT distinct identifier,text1,text2,text3,text4,text5 FROM Contentlet WHERE structure_inode='ddf29c1e-babd-40a8-bfed-920fc9b8c77f'") 
    #set($resultsList = $sql.getSQLResults("default", "$!{query}", 0, 25))

    <table border="1">
        <tr>
            <th><h3>First Name</h3></th>
            <th><h3>Last Name</h3></th>
            <th><h3>Title</h3></th>
            <th><h3>Email</h3></th>
            <th><h3>Phone</h3></th>
        </tr>
        #foreach($employee in $resultsList)
            <tr>
            <td>
            $!{employee.text1}</td>
            <td>
            $!{employee.text2}</td>
            <td>
            $!{employee.text3}</td>
            <td>
            $!{employee.text4}</td>
            <td>
            $!{employee.text5}</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.

<h3>Calling the SQL Tool from a Content:</h3>
<br/>
<b>Query:</b> select * from identifier where id in (select identifier from contentlet where structure_inode in (select inode from structure where structuretype = 5 AND name = 'Page Asset')));

#set($query = "select * from identifier where id in (select identifier from contentlet where structure_inode in (select inode from structure where structuretype = 5 AND name = 'Page Asset'))")

#set($resultsList = $sql.getSQLResults("default", "$!{query}", 0, 4))
<b>Results:</b> $resultsList
#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.

<h3>Calling getParameterizedSQLResults Tool from a Content:</h3>
<br/>
<b>Query:</b> select * from identifier where id in (select identifier from contentlet where structure_inode in (select inode from structure where structuretype = 5 AND name = 'Page Asset'));
<br/>
<b>Params:</b> 5, Page Asset
<br/>

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

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

#set($resultsList = $sql.getParameterizedSQLResults("default", "$!query", $paramsList, 0, 4))
<b>Results:</b> $resultsList

#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