Hi all,
Hope everyone is doing good. I came across a scenario of late, where I needed to perform an SQL validation. And I have to validate the SQL provided by the user in a free text field. The problem compounds when I have to do it with the schema he provides. And the schema name is captured in a separate variable. And also, the table name is captured in a separate variable. Only a single table in the FROM clause. So, if he just provides 'select * from tab' and the schema name is 's1', then the query should be validated like 'select * from s1.tab'. But below are the constraints:
1. I can't connect to s1 schema as I don't know the password but I can execute any SQL statement.
2. I can't just replace 'tab' with schema_name.table_name like 's1.tab' as this might be risky since we are changing SQL user provided.
3. Only allowed data types are char, varchar2, number and date.
Solution so far:
I'm creating a synonym for the table he provides. And checking the validity like select * from (whatever_user_provides) where 1=2. This way, I'm not changing the SQL text he provided.
And then I create a view and query the data dictionary for columns to see if he has used any other datatype other than what is allowed.
Finally synonym and view are dropped.
The return type is a collection of errors if SQL is invalid or a null collection if SQL is valid.
Question:
Is there any way, I can validate the SQL provided by the user without creating a synonym ? Or totally a new way to do it ?
Oracle version: 19c
Regards,
Ranagal