Improving app security

Security is a critical part of building internal applications, especially when these applications access private company information. The following guides offer suggestions and improvements to enhance app security.

Avoiding SQL injections when "Convert SQL queries to prepared statements" is disabled

By default, when connecting a database to UI Bakery, the "Convert SQL queries to prepared statements" option is enabled. This ensures that data source interactions are handled securely and efficiently:

• The SQL query is parameterized and stored on the back-end, never being sent from the client-side. This ensures that the end user cannot alter the query before execution.

• Client-side query parameters are escaped before being added to the query, ensuring protection against SQL injections.

In some use cases, it is required to generate an SQL query on a client, which requires this setting to be disabled and the query to be sent from the client at runtime. In this scenario, the user can alter the query before sending it, which may damage the data source.

In such a scenario, consider taking the following actions to improve security:

• Create a separate data source with limited data/structure access and/or limited privileges. For example, if you need to access specific table data, create a database user restricted to that table and with no or limited permissions for other tables and databases.

• Additionally, you can move SQL-query creation Action to an Automation and call it as a webhook (an http request) from the main application. In this implementation, the query generation is executed on the server, preventing the end-user from altering it.

Managing anonymous access in public applications

By default, data source access is controlled via roles and permissions that restrict access for anonymous users. This method is effective for private apps but unsuitable for public apps that rely on anonymous user access.

To manage access, you can configure data sources to either allow or disallow anonymous access. Disallowing anonymous access completely blocks anonymous users, while enabling it grants them access to a data source.

This means a bad actor can investigate requests made from the application to the data source, adjust parameters, or even execute arbitrary SQL queries if the Convert SQL queries to prepared statements setting is disabled on a data source.

For security reasons, we strongly recommend making your application private and using the UI Bakery authentication layer. If this is not feasible, consider the following recommendations to enhance your app's security:

• Ensure data sources with anonymous access allowed have only the necessary permissions. Create a separate data source with limited data/structure access and/or limited privileges. For example, if you need to access specific table data, create a database user restricted to that table and with no or limited permissions for other tables and databases.

• Relocate essential logic to a custom backend service and manage access with user-specific, time-limited tokens.

Securing current user context

In certain scenarios, securing the current user context is essential, such as when implementing row-level security in your applications.

By default, UI Bakery ensures that variables such as {{user.email}}, {{user.email}}, {{user.role}} are applied on the server side, when used in SQL queries.

In other cases, for example, when {user.email} is used in an HTTP request or a Load Row action step, it is sent from the client side, meaning it can be altered by the end user before sending.

To prevent unexpected behaviors, we suggest moving such actions to an Automation. When automation is initiated from the UI Bakery app, the backend ensures that the {{user}} variable refers to the currently logged-in user.

To do this:

• Create an automation using the variable {{user.email}}:

• Copy the automation webhook URL and create a new HTTP data source:

  • provide URL and key as data source settings

  • and give it a distinctive name

• Use in an HTTP action:

Here you can see the current user email is securely accessed from the user context without being transmitted from the client side.