Support custom SQL query execution

[kdhrubo]

POST Request :
/query --> QueryController
input payload - use named parameter jdbc template.
named parameters are easy to debug

{
   "sql" : "SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEE WHERE ID = :id",
    "params" : {
        "id" : 1
    },
    "single" : true
}

The parameter single is optional - it indicates if the query returns 1 result when set to true. Otherwise, by default it returns a list.

[thadguidry]

Ensure backtick ` and ' single quote work just fine within the SQL statement there (as well as any other special characters SQL eats without problem). Which component actually does the validation parsing here for what's between the double-quotes? And when does that component actually do the checking, what's the sequence actually look like?

[kdhrubo]

@thadguidry - can you please share some examples

Mandatory attributes can be checked in the controller.

[thadguidry]

Just search for "sql string literals".
https://stackoverflow.com/a/11321508
https://dev.mysql.com/doc/refman/8.2/en/string-literals.html
Where the whole structure for MySQL is here: https://dev.mysql.com/doc/refman/8.2/en/language-structure.html

[thadguidry]

And over the years, as UTF-8 needed to be supported fully...many things changed and were deprecated. For example, in MySQL:

Use of the dollar sign as the first character in the unquoted name of a database, table, view, column, stored program, or alias is deprecated, including such names used with qualifiers (see Section 9.2.2, “Identifier Qualifiers”). An unquoted identifier beginning with a dollar sign cannot contain any additional dollar sign characters. Otherwise, the leading dollar sign is permitted but triggers a deprecation warning.

As a best practice, I'd require having to single quote objects. Tables, etc.

[thadguidry]

There's a Java library that does validation stuff and I think Tony used it for the Database extension in OpenRefine, if I recall. Need me to find it?

[kdhrubo]

Let the database perform these validations, DB2Rest in this case just acts as facilitator.

[souravroy]

@kdhrubo Could you please review following PR ?
#156

[kdhrubo]

@souravroy closing this ticket.