Hello,

I hope you are well. I strongly believe in the "FORGE FIRST" philosophy and am a faithful follower of this thinking. However, when it comes to (external) databases, I usually look for solutions outside of Forge, considering that Outsystems itself already has several built-in verification, validation, permissioning, and sanitization functions for handling SQL queries.

After doing some research, I'd like to raise the question: what should I do?

Option 1 - Implement restrictions in the Database (more secure)

The best practice is to create a database user with read-only permissions:

Example:

GRANT SELECT ON table1 TO read_user;

GRANT SELECT ON table2 TO read_user;

REVOKE INSERT, UPDATE, DELETE ON table1 FROM read_user;

REVOKE INSERT, UPDATE, DELETE ON table2 FROM read_user;

Option 2 - Validate SQL before execution

If you have a screen where the user types free SQL and OutSystems executes the query, you can validate the text before sending it to the database. This scenario is a bit more complex (and I even have difficulty imagining an application that needs such a functionality, haha).

Example:

If(

Index(Upper(QuerySQL), "UPDATE") > 0 or

Index(Upper(QuerySQL), "DELETE") > 0 or

Index(Upper(QuerySQL), "INSERT") > 0 or

Index(Upper(QuerySQL), "DROP") > 0,

"Command not allowed",

"OK"

But this alone is not entirely secure, because someone could try to bypass the filter.

Option 3 - Stored Procedures or Views

Instead of allowing free SQL (which I don't imagine is your scenario, but if it is:)

Create Views for the query.

Create specific Stored Procedures.

The user chooses the desired query.

This eliminates the risk of executing inappropriate commands.