custom sql queries

[isriam]

I'm trying to make an interactive front end to my sql database so users can get information. I have standard sql queries for all information in my tables but was looking for a way to allow users to write their own sql queries?

i'm currently using a form post method with a $query variable but it doesn't seem to work with a table.

[lovasoa]

Hello @isriam , and welcome to SQLPage !

What are you building exactly, and which database are you using ?

On all databases, you can write to the sqlpage_files table to create new sql files that will then be interpreted by sqlpage. There is an example covering that in the official example folder on this repo: https://github.com/lovasoa/SQLpage/tree/main/examples/SQLPage%20developer%20user%20interface

On some databases, you can define a function to dynamically execute a sql string.

1. Mysql

   DELIMITER $$
   
   CREATE PROCEDURE ExecuteSQL(IN sql_string TEXT)
   BEGIN
       SET @stmt = sql_string;
       PREPARE stmt FROM @stmt;
       EXECUTE stmt;
       DEALLOCATE PREPARE stmt;
   END $$
   
   DELIMITER ;

2. Postgres

    CREATE OR REPLACE FUNCTION ExecuteSQL(sql_string TEXT)
    RETURNS VOID AS $$
    BEGIN
        EXECUTE sql_string;
    END;
    $$ LANGUAGE plpgsql;

3. TSQL (microsoft sql server)

    CREATE PROCEDURE ExecuteSQL
        @sql_string NVARCHAR(MAX)
    AS
    BEGIN
        EXEC sp_executesql @sql_string;
    END

In any case, please think carefully about the security impact of letting your users execute arbitrary sql on your database.

[isriam]

Oh thank you! I will play around with that some. My access to mysql is actually read only, which is why I was looking for something like a custom mysql query. Thank you for the great product, if I have more questions I will ask.

mysql 9.0

My concept is mostly to expose my database for view/sort/filter to friends. I have 3 generic tables I was hoping they could view called castles, teams, and players. While the table option is great, I would like to allow them to sort the sql query better themselves to do their own searches through my database.

I did end up creating the routine and can run it manually but how would I execute that from say custom.sql formatted like this:

custom.sql

SELECT 'text' as component;

SELECT 'Back...' as contents,
'index.sql' as link;

SELECT 'form' as component,
'create_custom.sql' as action,
'SQL Query' as title,
'GET' as method;

select 'Fields' as label,
'sqlfields' as name,
'text' as type,
TRUE as required;

select 'Table' as label,
'sqltable' as name,
'text' as type,
TRUE as required;

create_custom.sql

SELECT 'text' as component;

SELECT 'Back...' as contents,
'index.sql' as link;

SELECT 'text' AS component,
$sqlfields AS contents_md;

select 'text' as component,
$sqltable as contents_md;

select 'text' as component,
$sqlfields as contents_md,
$sqltable as contents_md;

select 'text' as component,
sqlpage.variables('GET') as contents_md;

SELECT 'table' as component, 
'Castles' as title,
TRUE as sort,
TRUE as search,
TRUE as striped_rows,
TRUE as hover,
TRUE as small,
TRUE as overflow;

SELECT $sqlfields FROM $sqltable;

[lovasoa]

SQLPage itself currently doesn't have an option to run arbitrary sql defined by the end user. I am reluctant to adding something like that, since this feature has a great potential for misuse and would probably most of the time result in vulnerabilities in the application.

If you really want to, you can save the user query in a file and run with run_sql. But be very careful and please think twice about the security implications if you do that.

query.sql

set Query = COALESCE($Query, 'SELECT * from todo')

select 'form' as component;
select 'Query' as name, $Query as value;

set tmp_sql_file = sqlpage.exec('./query_write_tmp_sql.sh', $Query);

select 'table' as component, true as search, true as sort;
select 'dynamic' as component, sqlpage.run_sql($tmp_sql_file) as properties; -- this allows anyone able to send a query to your app to run any sql queries on your database. This is generally a very bad idea and a serious security risk.

and the simple supporting shell script:

query_write_tmp_sql.sh

#!/bin/sh

temp_file=$(mktemp)
echo "$@" > "$temp_file"
echo -n "$temp_file"

https://replit.com/@pimaj62145/SQLPage-custom-user-defined-sort-and-filtering?v=1

Things you should think about if you do that, even with a read-only access to the database:

• access to the event scheduler
• access to local files on the database server
• access to Database Structure Information
• access to the server's configuration option
• voluntary or involuntary denial of service on the database server by running expensive queries

in addition to these general risks, there are risks specific to SQLPage, especially if you return their data directly to the page

• ability to "break out" of the table and use any component
• denial of service on the sqlpage server
• ability to run javascript on the client

[isriam]

Thanks! I run this on an isolated cloud VM, and appreciate your concerns. I will probably write my own queries versus let anyone have public access but I was just curious your thoughts based on what I was envisioning. The risk is very great as you outlined above.

[lovasoa]

You may be interested in #536 (comment) : an example custom data filtering form.