Example - SQL Data with Form

XX Sources with Form

The Site Database with Form and Any Database with Form Content Sources are further sources in the XX Sources extension addon package for Universal Content Puller. The sources in this extension are XX because they are the kind of source you may not want to let just anyone loose with, hence a separate package so they don't have to be installed with less sensitive sources.

Site Database with Form

A content source that makes SQL queries on the site database could break a page, be used to pull data you do not want exposed, or even to subvert the site in more malign ways. The Site Database with Form extends the Site Database content source to include parameters from a front end form in the SQL query.

Queries with placeholders

To illustrate the use of this Site Database with Form content source we have modified the previous example SQL Data to accept an additional GET or POST parameters to filter the pages returned.

The query used is shown below. The tokens {{limit}} and {{offset}} are inserted to facilitate pagination in the Content Source with an additional '?' query placeholder parameter for the value from the form. 

SELECT 
PagePaths.cID AS 'cID', 
PagePaths.cPath AS 'Path', 
CollectionVersions.cvName AS 'Name',
CollectionVersions.cvDescription AS 'Description' 
FROM PagePaths JOIN CollectionVersions ON PagePaths.cID = CollectionVersions.cID 
WHERE CollectionVersions.cvIsApproved = 1
AND PagePaths.ppIsCanonical = 1
AND PagePaths.cPath LIKE '%/addons%' 
AND PagePaths.cPath LIKE CONCAT('%' ,?,'%')
ORDER BY PagePaths.cPath 
ASC LIMIT {{limit}} OFFSET {{offset}}

Note how a '?' placeholder cannot be within a string, so CONCAT is used to build the LIKE string. The optional COUNT() query is similarly modified to accept the same placeholder. You can add as many placeholders as you like, but they have to be in the same sequence because the query parameters used to fill them when the query is executed are strictly in this sequence.

SELECT 
COUNT(PagePaths.cID) AS 'total_cID'
FROM PagePaths JOIN CollectionVersions ON PagePaths.cID = CollectionVersions.cID 
WHERE CollectionVersions.cvIsApproved = 1
AND PagePaths.ppIsCanonical = 1
AND PagePaths.cPath LIKE '%/addons%' 
AND PagePaths.cPath LIKE CONCAT('%' ,?,'%')

When UCP runs the database queries, {{limit}} and {{offset}} are automatically filled to facilitate pagination. The '?' placeholders are then mapped to query parameters as configured in the edit dialog. Internally, the database API escapes these '?' placeholders to secure the query against SQL injection.

In the block edit dialog, a section for placeholder mapping is automatically created for each  '?' placeholder in the SQL query.

The Content Transform and Content Display are the same as before. This will work equally well directly or with ajax pulled content.

You can experiment with the example shown below to see how the results displayed are filtered by page path, then read on for some more details. Try some entries like 'tabs', 'universal', 'magic', button'.

In more detail

Despite its name, this content source does not provide the actual form. Providing the form is entirely up to you and in its simplest guise could be just an HTML block. This content source responds to parameters from the form you provide.

Above is a UCP block and immediately preceding it an HTML block to provide the form.

<form class="ucp-attach-form form-inline">
    <input type="text" name="including_path" class="form-control">
    <input type="submit" value="Go" class="btn btn-primary">
</form>

The class 'ucp-attach-form' is required for UCP to locate the form and maintain it through pagination. Other classes in the form html are purely for Bootstrap styling. 

With all this complexity added to the URL parameters, you are recommended to only use one UCP block that responds to form parameters on any one page.

Use of any of the forms blocks or packages to create a form that is used by this content source may also be feasible, but you will need to disconnect the form display from actually submitting to the form controller. The form simply needs to refresh the page without actually doing anything, so allowing UCP to use the form data without interference.

Furthermore, you don't actually need to have a form on the page with UCP! UCP reads the URL query string data, so you could have a form on another page or simply a hard coded link with a query embedded to navigate to the page with the UCP block using Site Database with Form or Any Database with Form.

As well as enabling form or url parameter values to be used in a query, special key names can be used to embed page or user attribute values. See the source section in Content Sources for details.

More about placeholders

In addition to the pagination placeholders 'LIMIT {{limit}} OFFSET {{offset}}', and thee '?' placeholders for parameters to the query, the query string also supports some special special parameters placeholders within {{CCM_...}}:

• CCM_CID - The cID of the current page
• CCM_URL - The URL of the current page
• CCM_CATTR_attribute_handle - A page attribute that must evaluate to a scalar value
• CCM_UID - The uID of the current user
• CCM_EMAIL - The email address of the current user
• CCM_UATTR_attribute_handle - A user attribute that must evaluate to a scalar value