Database SQL Query List Action

Introduction

The Database SQL Query List Action allows you to execute SQL queries on one or more records in your datalist, either as a row action or a bulk action. You can specify the database for executing the SQL function, which can be the current Joget database (default datasource) or a custom datasource (external database). This feature is useful for deleting records or performing updates based on user selection in the datalist checkboxes.

When using a Hash Variable that involves URL parameters or user-inputted values in the SQL query, ensure these variables are escaped to prevent SQL injection. Use the ?sql escape keyword for hash variables.

Make use of hash variable escape keywords; see 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

Select Database SQL Query from the Action menu in the lateral menu to add the element Database SQL Query.

When adding Database SQL Query, you will see the following properties from the element available for configuration:

Label: Datalist button label.
Confirmation Message: Before performing an action, for example, Are you sure?
Datasource: Target database to execute SQL statements on.
- Custom Datasource: Requires JDBC Connection Parameters.
- Default Datasource: Points to the current Joget database.
Custom JDBC Driver: JDBC driver name (e.g., com.mysql.jdbc.Driver for MySQL).

This is only applicable to the Custom Datasource option.
Custom JDBC URL: Database connection URL (e.g., jdbc:mysql://localhost:3306/jwdb).
This is only applicable to the Custom Datasource option.
Custom JDBC Username: Database username (e.g., 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 to test your configurations quickly
Query
If a column name contains reserved keywords, ensure it is encapsulated properly.

For example, for MySQL, if the column identifier itself contains a dot symbol ( . ), it should be encapsulated like this:
```
SELECT `myAppName.myColumn` FROM app_fd_myTable;
```
Insert your SQL statement here. Use {id} to inject the selected row key and {uuid} to generate a unique ID.

The special parameters {id} and {uuid} will be replaced with actual values through the use of PreparedStatement. There is no need to encapsulate these special keywords with quotes.
See the following examples:
- Insert
```
INSERT INTO app_fd_sample (id, c_clicked) VALUES ({uuid}, {id})
```
- Update
```
UPDATE app_fd_sample SET c_clicked = CONCAT(c_clicked, ',', {id}) WHERE id = {id}
```
- Delete
```
DELETE FROM app_fd_myTable WHERE id = {id}
```

Table & Column Naming

- For database tables created by Joget Forms, Joget adds a c_ in front of table column names (or t_ if your column name starts with a number) and app_fd_ in front of database table names.
- 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., disables XSS prevention checking. For more information, see Escaping the Resultant Hash Variable.

Introduction

Configure Database SQL Query

Related documentation

Download sample app