Database SQL Query Options

Database SQL Query Options allows you to retrieve form option records from Joget or a custom database via user-defined SQL query statements.

When using a Hash Variable incorporating URL parameters or user-input values in the SQL query, ensure these hash variables are escaped to prevent SQL injection. Utilize hash variable escape keywords described in Hash Variable - Escaping the Resultant Hash Variable.

Vulnerable Query Example:

SELECT * FROM app_fd_sample_table WHERE  c_value = '#requestParam.id#'

To fix this, use ?sql Hash variable escape:

SELECT * FROM app_fd_sample_table WHERE c_value = '#requestParam.id?sql#'

Configure Database SQL Query Options

Database SQL Query Options configuration allows you to specify the datasource and various JDBC connection parameters to customize your SQL queries and their behavior.

Datasource: Specify the target database to execute SQL statements on.
- Custom Datasource: Requires JDBC Connection Parameters.
- Default Datasource: Points to the current database to which your Joget instance is connected.
Custom JDBC Driver: Specify the JDBC driver name.
- com.mysql.jdbc.Driver (MySQL)
- oracle.jdbc.driver.OracleDriver (Oracle)
- com.microsoft.sqlserver.jdbc.SQLServerDriver (Microsoft SQL Server)
  This is only applicable to the Custom Datasource option.
Custom JDBC URL: Database connection URL, such as jdbc:mysql://localhost:3306/jwdb.
This is only applicable to the Custom Datasource option.
Custom JDBC Username: Database username, such as root
This is only applicable to the Custom Datasource option.
Custom JDBC Password: Specified database user's password.
This is only applicable to the Custom Datasource option.

Click the Test Connection button at the bottom of the page to test your configurations quickly.
Use AJAX for Cascade Options?: Allows fields to dynamically load available options based on another field value (grouping column) when dealing with many selections. See Dynamic Cascade Drop-Downs.

Do not forget to configure the dependency field in Field ID to control available options based on Grouping in the Advanced Options tab.

Use a question mark ? in your SQL SELECT Query to represent dependency values.
Add Empty Option: Click this checkbox to include an empty option in the select box.
- Empty Option Label: Enter your empty label, for example, Select.
SQL SELECT Query:
If a column name contains reserved keywords, ensure it is encapsulated properly. For MySQL, if the column identifier contains a dot (.), it should be encapsulated like this:
```
SELECT `myAppName.myColumn` FROM app_fd_myTable;
```
To populate a select box, you must return at least two columns. The first column is used for the ID, and the second is used for the label. An optional third column can be returned for the grouping value.

For example, when using the Field ID to control available options based on Grouping property field, you can pass more than one parameter to your SQL query WHERE clause by separating the parameter values with a semicolon. For instance, if you need to perform two WHERE conditional queries, you can enter location;username in the Field ID to control available options based on Grouping property field.
```
SELECT
   username,
   username
FROM
   dir_user
ORDER BY
   username ASC
```
When Use AJAX for cascade options is checked, ensure a question mark is placed within the query.
```
SELECT
   username,
   CONCAT(lastName, ' ', firstName)
FROM
   dir_user
WHERE
   timeZone = (?)
```
Multi-select Box with JDBC
```
SELECT
   id,
   c_field1
FROM
   app_fd_myTable
WHERE
   id IN (?)
```
Table & Column Naming
- For database tables created by Joget Forms, Joget prefixes c_ to table column names (or t_ if the column name starts with a number) and app_fd_ to database table names.
Environment Hash Variables
- If you use environment hash variables to store SQL query strings, use ?noescape to escape SQL query strings in JDBC binders to prevent the <> (not equal) operator from being converted, i.e., disabling XSS prevention checking. For more information, see Escaping the Resultant Hash Variable.