This can be done using a dynamic workflow where you use a schema feature to set the schema in the writer at runtime. This schema schema feature can be generated using a SchemaScanner transformer.

For more info on this, check Tutorial: Dynamic Workflows or more specific, Dynamic Workflows: Destination Schema is Derived from a Schema Feature and How to Use the SchemaScanner Transformer.

If you know the attribute names, you can forcefully input the field names or alias names in the text box of the SQL executor or SQL creator, or you can give aliases for each field in a SQL query.

Additionally, these attributes will be automatically filled up while the query is running.

also

For the first time, when building FME, you can hard code the SQL and generate all possible attributes.

Here I would need to generate the dataset from the SQL query that is passed by the user, and each time the query would be different.

So the reader would be executing the query and writer will write the query results to the database.

I have used SQL creator, but it is not able to fetch the attributes on the go, any alternate solution for this would be appreciated.

Your SQL statement is executing correctly, it is getting the attributes, but the attributes aren't Exposed. That's not a problem though - attributes don't need to be exposed when outputting with a writer set to Dynamic, the writer just needs to receive a special schema feature along with the data features to tell it what the attributes are to write out.

Since FME 2022, the SQL Creator has a <Schema> port which should make this easier. The links in Niels' reply above describe all you need for dynamic writing.

One other thing, SQL injection is problem to consider with this approach. You'll want to make sure the SQL query is safe to execute, to prevent misuse.