Dynamic SQL query

Wappler General
1

Hi,
I’m trying to build a complex, dynamic SQL query, in which the tables, attributes to return, and where clauses would be defined based on choices that the user makes in the front end. Among other things, the user will be able to narrow the search by creating nested AND and OR choices.

I thought of doing the following:
a) Assemble a SQL query string in the front end, based on the choices made by the user
b) Pass that SQL query string to the back end, using a server connect
c) In the Wappler API called by the server connect,

  • Receive the SQL query string from the front end as a parameter
  • Use a SET VALUE step to assign the parameter to a variable called MyQuery
  • Use a Database Custom Query step and in the space to build the query, simply type {{MyQuery}}

I tested the back end part (point c) and I can see that it works.

I have two questions:

  1. Is what I described an appropriate way to address this issue or am I exploiting a feature that will be removed in the future?

  2. Is there a better way to create this, knowing that there is no way at the start to know which table, attributes, and nested and/or conditions the user will impose as constraints?

Many thanks!

2

This would be a huge security concern, allowing the execution of SQL queries supplied by user input. A slightly better alternative is to construct the query string in the back-end (server action) instead of the front-end

You still need to ensure you strip away weird characters on user input in the server action, to hopefully prevent SQL injection as you’re using an undocumented (unsupported) feature vulnerable to it :wink:

3

I totally agree with you. Done as described it would invite SQI.
That said, I was not planning to build steps a and b exactly as written, I just made a simplification to describe the problem, because my questions are really about point c. But I should have been a bit more careful when formulating those steps. Thank you for pointing that out.

I know that there are many things in Wappler that are undocumented, so my real question was, and maybe this s/b directed to @Teodor, if I am on the wrong path constructing the query for the Database Custom Query in a variable and passing the variable to that step.

Again, thank you!

1 Like
4

When you hit Wappler limitations, you do what you gotta do to get the job done :wink:

Some people write extensions, other people use “undisclosed” functionality :wink:

If it breaks, it can be solved with a custom extension to run raw SQL queries.

The other alternative would be doing a series of Condition steps (for table selection) and “Toggle condition” for database query conditions:

Screenshot
Screenshot712×274 13.6 KB

And even then it’s not guaranteed you’d have the same level of flexibility as constructing a raw query

If it puts your mind at ease, you’re not the first person to come with this solution, I think @ sid mentioned it once - it’s been a while, and the method is still working as you verified, so I think you’d be fine :wink:

Of course, I take a @ JonL stance of not actively recommending such approach to other users. Exception made here because it seems you know what you’re doing :+1: