| Warning |
|---|
| title | Prevent SQL injection |
|---|
|
When using Hash Variable that uses URL parameter or user-inputted value in the SQL query, ensure that these hash variable(s) are escaped in the query! Make use of hash variable escape keywords, see Hash Variable - Escaping the Resultant Hash Variable.
Example of VULNERABLE query : 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 #'
|
Introduction
JDBC Options Binder allows you to retrieve form option records from Joget or a custom database via user-defined SQL query statements.
JDBC Options Binder Properties
Figure 1: JDBC Option Binder Properties
| Name | Description |
|---|
| Datasource | Target database to execute SQL statements on. Choices:- - Custom Datasource
- JDBC Connection Parameters are needed for this choice.
- Default Datasource
- Points to the current database your copy of Joget currently connects to.
|
| Custom JDBC Driver | JDBC driver name. Example values: com.mysql.jdbc.Driver (MySQL)oracle.jdbc.driver.OracleDriver (Oracle)com.microsoft.sqlserver.jdbc.SQLServerDriver (Microsoft SQL Server)
Only applicable to "Custom Datasource" option. |
| Database connection URL. Example: jdbc:mysql://localhost:3306/jwdb Only applicable to "Custom Datasource" option. |
| Database username. Example: root Only applicable to "Custom Datasource" option. |
| Specified database user's password. Only applicable to "Custom Datasource" option. | Info |
|---|
| title | Test the connection parameters |
|---|
| Click on the "Test Connection" button at the bottom of the page to quickly test out your configurations. |
|
| Use AJAX for cascade options? | When checked, this allows these fields to dynamically load available options based on the other field value (grouping column) when dealing with tremendous amount of selections. Read more at Ajax Cascading Drop-Down List. | Info |
|---|
| Do not forget to configure the dependency field in Field ID to control available option based on Grouping in the Advanced Options tab.  |
| Info |
|---|
| Use question mark (?) in your SQL SELECT Query to represent dependency values. |
|
| Add Empty Option | Click this checkbox if you want an empty option in the selectbox. Clicking this option will display the following field: - Empty Option Label - Enter your "empty" label, for example "Select".
|
| | Note |
|---|
If a column name contains reserved keywords, do ensure it is encapsulated properly. For example for MySQL, if the column identifier itself contains a dot symbol ( . ), it should be encapsulated like this: | Code Block |
|---|
| SELECT `myAppName.myColumn` FROM app_fd_myTable; |
|
To populate a selectbox, for example, you need to return at least 2 columns. The first column is used for Id. The second column is used for Label.
An optional third column can be returned for grouping value if for example, you are using the select box "Field ID to control available options based on Grouping" property field. | Code Block |
|---|
| language | sql |
|---|
| title | Example |
|---|
| linenumbers | true |
|---|
| SELECT
username,
username
FROM
dir_user
ORDER BY
username ASC |
When Use AJAX for cascade options is checked, make sure that a question mark is placed within the query. | Code Block |
|---|
| language | sql |
|---|
| title | Example |
|---|
| linenumbers | true |
|---|
| SELECT
username,
CONCAT(lastName, ' ', firstName)
FROM
dir_user
WHERE
timeZone = (?) |
On multi-select box form element on cascade and when using JDBC, remember to use brackets to enclose the ? parameter in
"WHERE id IN (?)" : | Code Block |
|---|
| language | sql |
|---|
| title | Example |
|---|
| linenumbers | true |
|---|
| SELECT
id,
c_field1
FROM
app_fd_myTable
WHERE
id IN (?) |
| Info |
|---|
| title | 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. Read here for more information..
|
|
Download Demo App