"SQL query" module: running long queries from the Admin panel return "HTTP Error 404.15 - Not Found"

[MarGraz]

Describe the bug

When the "SQL Query" feature is active in Orchard Core 1.8.2, creating and attempting to run a long SQL query through "Search" > "Queries" > "All queries" results in an error when using the Run button:

HTTP Error 404.15 - Not Found. The request filtering module is configured to deny a request where the query string is too long

Instead, if a shorter query is first opened and then replaced by pasting the long query into the editor, it runs successfully.

As discussed in this conversation, the problem seems to be related on how the query is sent to the server. It is likely more appropriate to send the query as a POST request with the query in the body, rather than as a GET request with the query in the URL.

To Reproduce

Steps to reproduce the behavior:

1. Run the project using Visual Studio with a PostgreSQL database;
2. Enable the "SQL Query" feature;
3. Navigate to "Search" > "Queries" and create a short SQL query;
4. Create and save a longer SQL query;
5. Attempt to run the long query from the Run button under "Search" > "Queries" > "All queries";
6. Observe the error 404.15;
7. Go back and copy the long query;
8. Open and edit the short query;
9. Paste the long query into the editor and run it by clicking the Query button;
10. The long query executes without error.

Long query example:
I used a CAST() function named cast_text_to_jsonb because PostgreSQL does not support the default CAST(), as discussed here.

This query retrieves additional data from User using a CustomUserSettings content type, as detailed in the documentation. The data is stored in the Document DB table.

You can reproduce this issue with any long query. This is the one I used:

SELECT 
    UI."DocumentId", 
    UI."UserId", 
    UI."NormalizedUserName", 
    UI."NormalizedEmail", 
    STRING_AGG(UBRNI.RoleName, ',') AS RoleNames,
    STRING_AGG(casttotext(UBRNID.UserByRoleNameIndexId), ',') AS RoleIds,
    jsonb_extract_path_text(cast_text_to_jsonb(D."Content"), 'Properties', 'UserDetails', 'UserDetails', 'Name', 'Text') AS FirstName,
    jsonb_extract_path_text(cast_text_to_jsonb(D."Content"), 'Properties', 'UserDetails', 'UserDetails', 'Surname', 'Text') AS LastName,
    jsonb_extract_path_text(cast_text_to_jsonb(D."Content"), 'Properties', 'UserDetails', 'UserDetails', 'Phone', 'Text') AS Phone,
    jsonb_extract_path_text(cast_text_to_jsonb(D."Content"), 'Properties', 'UserDetails', 'UserDetails', 'WhatsApp', 'Text') AS WhatsApp,
    jsonb_extract_path_text(cast_text_to_jsonb(D."Content"), 'Properties', 'UserDetails', 'UserDetails', 'Street', 'Text') AS Street,
    jsonb_extract_path_text(cast_text_to_jsonb(D."Content"), 'Properties', 'UserDetails', 'UserDetails', 'StreetNumber', 'Text') AS StreetNumber,
    jsonb_extract_path_text(cast_text_to_jsonb(D."Content"), 'Properties', 'UserDetails', 'UserDetails', 'City', 'Text') AS City,
    jsonb_extract_path_text(cast_text_to_jsonb(D."Content"), 'Properties', 'UserDetails', 'UserDetails', 'ParamOne', 'Text') AS ParamOne,
    jsonb_extract_path_text(cast_text_to_jsonb(D."Content"), 'Properties', 'UserDetails', 'UserDetails', 'ParamTwo', 'Url') AS ParamTwo,
    jsonb_extract_path_text(cast_text_to_jsonb(D."Content"), 'Properties', 'UserDetails', 'UserDetails', 'ParamThree', 'Url') AS ParamThree,
    jsonb_extract_path_text(cast_text_to_jsonb(D."Content"), 'Properties', 'UserDetails', 'UserDetails', 'ParamFour', 'Url') AS ParamFour,
    jsonb_extract_path_text(cast_text_to_jsonb(D."Content"), 'Properties', 'UserDetails', 'UserDetails', 'Description', 'Text') AS Description,
    jsonb_extract_path_text(cast_text_to_jsonb(D."Content"), 'Properties', 'UserDetails', 'UserDetails', 'Logo', 'Paths') AS LogoPaths
FROM 
    "UserIndex" UI
JOIN 
    "Document" D ON UI."DocumentId" = D."Id"
JOIN 
    "UserByRoleNameIndex_Document" UBRNID ON UI."DocumentId" = UBRNID."DocumentId"
JOIN 
    "UserByRoleNameIndex" UBRNI ON UBRNID."UserByRoleNameIndexId" = UBRNI."Id"
GROUP BY 
    UI."DocumentId", 
    UI."UserId", 
    UI."NormalizedUserName", 
    UI."NormalizedEmail",
    D."Content"

Expected behavior

Click on Run should not return a 404.15 error. As demonstrated when editing an existing query or via the page "Search" > "Query" > "Run SQL Query".

Screenshots

This is the request URL that you can see in the screenshot:

https://localhost:44300/Admin/Queries/Sql/Query?query=U0VMRUNUIA0KICAgIFVJLiJEb2N1bWVudElkIiwgDQogICAgVUkuIlVzZXJJZCIsIA0KICAgIFVJLiJOb3JtYWxpemVkVXNlck5hbWUiLCANCiAgICBVSS4iTm9ybWFsaXplZEVtYWlsIiwgDQogICAgU1RSSU5HX0FHRyhVQlJOSS5Sb2xlTmFtZSwgJywnKSBBUyBSb2xlTmFtZXMsDQogICAgU1RSSU5HX0FHRyhjYXN0dG90ZXh0KFVCUk5JRC5Vc2VyQnlSb2xlTmFtZUluZGV4SWQpLCAnLCcpIEFTIFJvbGVJZHMsDQogICAganNvbmJfZXh0cmFjdF9wYXRoX3RleHQoY2FzdF90ZXh0X3RvX2pzb25iKEQuIkNvbnRlbnQiKSwgJ1Byb3BlcnRpZXMnLCAnVXNlckRldGFpbHMnLCAnVXNlckRldGFpbHMnLCAnTmFtZScsICdUZXh0JykgQVMgRmlyc3ROYW1lLA0KICAgIGpzb25iX2V4dHJhY3RfcGF0aF90ZXh0KGNhc3RfdGV4dF90b19qc29uYihELiJDb250ZW50IiksICdQcm9wZXJ0aWVzJywgJ1VzZXJEZXRhaWxzJywgJ1VzZXJEZXRhaWxzJywgJ1N1cm5hbWUnLCAnVGV4dCcpIEFTIExhc3ROYW1lLA0KICAgIGpzb25iX2V4dHJhY3RfcGF0aF90ZXh0KGNhc3RfdGV4dF90b19qc29uYihELiJDb250ZW50IiksICdQcm9wZXJ0aWVzJywgJ1VzZXJEZXRhaWxzJywgJ1VzZXJEZXRhaWxzJywgJ1Bob25lJywgJ1RleHQnKSBBUyBQaG9uZSwNCiAgICBqc29uYl9leHRyYWN0X3BhdGhfdGV4dChjYXN0X3RleHRfdG9fanNvbmIoRC4iQ29udGVudCIpLCAnUHJvcGVydGllcycsICdVc2VyRGV0YWlscycsICdVc2VyRGV0YWlscycsICdXaGF0c0FwcCcsICdUZXh0JykgQVMgV2hhdHNBcHAsDQogICAganNvbmJfZXh0cmFjdF9wYXRoX3RleHQoY2FzdF90ZXh0X3RvX2pzb25iKEQuIkNvbnRlbnQiKSwgJ1Byb3BlcnRpZXMnLCAnVXNlckRldGFpbHMnLCAnVXNlckRldGFpbHMnLCAnU3RyZWV0JywgJ1RleHQnKSBBUyBTdHJlZXQsDQogICAganNvbmJfZXh0cmFjdF9wYXRoX3RleHQoY2FzdF90ZXh0X3RvX2pzb25iKEQuIkNvbnRlbnQiKSwgJ1Byb3BlcnRpZXMnLCAnVXNlckRldGFpbHMnLCAnVXNlckRldGFpbHMnLCAnU3RyZWV0TnVtYmVyJywgJ1RleHQnKSBBUyBTdHJlZXROdW1iZXIsDQoJanNvbmJfZXh0cmFjdF9wYXRoX3RleHQoY2FzdF90ZXh0X3RvX2pzb25iKEQuIkNvbnRlbnQiKSwgJ1Byb3BlcnRpZXMnLCAnVXNlckRldGFpbHMnLCAnVXNlckRldGFpbHMnLCAnQ2l0eScsICdUZXh0JykgQVMgQ2l0eSwNCiAgICBqc29uYl9leHRyYWN0X3BhdGhfdGV4dChjYXN0X3RleHRfdG9fanNvbmIoRC4iQ29udGVudCIpLCAnUHJvcGVydGllcycsICdVc2VyRGV0YWlscycsICdVc2VyRGV0YWlscycsICdaaXBDb2RlJywgJ1RleHQnKSBBUyBaaXBDb2RlLA0KICAgIGpzb25iX2V4dHJhY3RfcGF0aF90ZXh0KGNhc3RfdGV4dF90b19qc29uYihELiJDb250ZW50IiksICdQcm9wZXJ0aWVzJywgJ1VzZXJEZXRhaWxzJywgJ1VzZXJEZXRhaWxzJywgJ1dlYnNpdGUnLCAnVXJsJykgQVMgV2Vic2l0ZVVSTCwNCiAgICBqc29uYl9leHRyYWN0X3BhdGhfdGV4dChjYXN0X3RleHRfdG9fanNvbmIoRC4iQ29udGVudCIpLCAnUHJvcGVydGllcycsICdVc2VyRGV0YWlscycsICdVc2VyRGV0YWlscycsICdGYWNlYm9vaycsICdVcmwnKSBBUyBGYWNlYm9va1VSTCwNCiAgICBqc29uYl9leHRyYWN0X3BhdGhfdGV4dChjYXN0X3RleHRfdG9fanNvbmIoRC4iQ29udGVudCIpLCAnUHJvcGVydGllcycsICdVc2VyRGV0YWlscycsICdVc2VyRGV0YWlscycsICdJbnN0YWdyYW0nLCAnVXJsJykgQVMgSW5zdGFncmFtVVJMLA0KICAgIGpzb25iX2V4dHJhY3RfcGF0aF90ZXh0KGNhc3RfdGV4dF90b19qc29uYihELiJDb250ZW50IiksICdQcm9wZXJ0aWVzJywgJ1VzZXJEZXRhaWxzJywgJ1VzZXJEZXRhaWxzJywgJ0Rlc2NyaXB0aW9uJywgJ1RleHQnKSBBUyBEZXNjcmlwdGlvbiwNCiAgICBqc29uYl9leHRyYWN0X3BhdGhfdGV4dChjYXN0X3RleHRfdG9fanNvbmIoRC4iQ29udGVudCIpLCAnUHJvcGVydGllcycsICdVc2VyRGV0YWlscycsICdVc2VyRGV0YWlscycsICdMb2dvJywgJ1BhdGhzJykgQVMgTG9nb1BhdGhzDQpGUk9NIA0KICAgICJVc2VySW5kZXgiIFVJDQpKT0lOIA0KICAgICJEb2N1bWVudCIgRCBPTiBVSS4iRG9jdW1lbnRJZCIgPSBELiJJZCINCkpPSU4gDQogICAgIlVzZXJCeVJvbGVOYW1lSW5kZXhfRG9jdW1lbnQiIFVCUk5JRCBPTiBVSS4iRG9jdW1lbnRJZCIgPSBVQlJOSUQuIkRvY3VtZW50SWQiDQpKT0lOIA0KICAgICJVc2VyQnlSb2xlTmFtZUluZGV4IiBVQlJOSSBPTiBVQlJOSUQuIlVzZXJCeVJvbGVOYW1lSW5kZXhJZCIgPSBVQlJOSS4iSWQiDQpHUk9VUCBCWSANCiAgICBVSS4iRG9jdW1lbnRJZCIsIA0KICAgIFVJLiJVc2VySWQiLCANCiAgICBVSS4iTm9ybWFsaXplZFVzZXJOYW1lIiwgDQogICAgVUkuIk5vcm1hbGl6ZWRFbWFpbCIsDQogICAgRC4iQ29udGVudCI%3D

[Piedone]

While you can configure IIS and Kestrel to accept long URLs, yes, a better solution (and otherwise a more appropriate use of HTTP verbs) would be to send a POST request.

Is this something you'd perhaps be interested in contributing?

[MarGraz]

@Piedone it will be my first code contribution, so please guide me 😊

[Piedone]

Sure, and great! Start here: https://docs.orchardcore.net/en/latest/guides/contributing/contributing-code/. Then let me know if you have questions.