sqlpage.run_sql(file, parameters)

[Aesth]

I'd like to execute a sequence of commands generated in a database server procedure. As far as I understand, this allows you to execute a single SQLPage command or even several of the same type. However, I haven't yet succeeded with different types, essentially generating arbitrary code, since the components have different parameters. An attempt to do this using the dynamic component also failed, although perhaps I simply lacked the understanding.

Basically, I'd like something like sqlpage.run_sql(file, parameters), but not with a file, but with text, like sqlpage.run_sql(text, parameters) ?

[lovasoa]

Hi !
I have been reticent to implement this so far because of how easy it is to introduce security vulnerabilities, bad design, and performance issues with that. It's also usually easy to do using the db itself. I'd be interested in what your use case is (I don't understand what the problem with "different parameters" is).

Here are previous discussions on the same topic:

• custom sql queries #518
• Best way to allow a dynamic (unsafe) "run arbitrary SQL" page? #183

[Aesth]

Overall, I agree with you on all points. Technically, yes, any text insertion can lead to a vulnerability. But in this case, the page code is generated on the fly in a stored procedure on the database server. In fact, there's no user-modifiable data there, only user-specified parameters, which SQLPage passes in strictly normalized form, preventing manipulation and arbitrary code execution.

If we accept that a programmer can make a mistake when generating page code in a stored procedure, then they can equally make a mistake when writing the same code in a file that can be executed when the appropriate option is set. From this perspective, there's no fundamental difference.

Perhaps I didn't get my point across. The issue isn't about query parameters and dynamically executable code on the database server. The intended purpose was for the page code for SQLPage to be generated in a server procedure, similar to the example you showed in the sqlpage.hash_password #1108 thread. It was assumed that it would be possible to return a call to not just one redirect component, but several, and not just redirect. For example:
SELECT 'form' as component;
and then

SELECT 'username1' AS name, 'text' AS type, 'Enter your 1st username' AS placeholder
UNION ALL SELECT 'username2' AS name, 'text' AS type, 'Enter your 2nd username' AS placeholder

And it would certainly be possible to form a chain of calls to different components in the same way, but the problem is that they all have different input parameters, and implementing this using UNION ALL would be extremely difficult. Calling each component via a stored procedure would be expensive due to repeated calls to the database engine. I tried implementing this using a dynamic component, but it didn't work.

Sorry to distract you with my fantasies. It's just that as I explore the capabilities of SQLPage, many questions arise.

[lovasoa]

Oh, ok, I think I understand what you meant. You can do it by passing an array of heterogeneous objects to the dynamic component: https://sql-page.com/component?component=dynamic

select 'dynamic' as component,
  json_build_array(
    json_build_object(
        'component', 'form'
    ),
    json_build_object(
        'name', 'username'
    ),
    json_build_object(
        'name', 'password',
        'type', 'password'
    )
  ) as properties;

[Aesth]

Yes! That's exactly what I meant. It turns out I was heading in the right direction, but stumbled along the way by using the dynamic component incorrectly. Thanks for the tip!