Improving app security

Security is a critical part of building internal applications, especially when these applications access private company information. In this article, you'll find suggestions and improvements how to enhance your 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 this case, 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/limited permissions to other tables and databases.

• Additionally, you can move the 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/limited permissions to 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, for example when implementing row-level security in your applications.

By default, UI Bakery ensures that such variables as {{user.email}} and {{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 in 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. Check out the instruction below how you can do that👇

1. Create an automation using the {{user.email}} variable. In our example, we used UI Bakery Postgres data source and a Load Row action.

1. Copy the automation webhook URL (right side panel) and create a new HTTP API data source:

   1. Give the data source a distinctive name, for example, Load current user.

   2. Provide the URL and key from the copied webhook in the data source settings.

1. Use this data source in an HTTP Request action step.

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