Tuesday, January 08, 2008

FRM-40367 Query/Where function



An old and almost forgotten option in Oracle forms is the query/where function. This allows you to give your own “where” or “where and” clauses in your form. In basics you also could escape your select statement and do a SQL injection.

The intended functionality of the query/where is that you can write your own query for the form where you will not be limited to the fields that are represented in the form itself. You are basically limited to the columns of the tables that are noted down in the hardcoded part of the query. For example you could query for a user in the “system administrators” “users” screen by user_id which is standard not an option.

After you invoked the query/where screen and enter the following extension to the query: “user_id = 2071” if you want to view the information from the user with id 2071.

Because of the potential danger of SQL injection Oracle has removed this functionality, however as we are used to with Oracle it is not really removed only turned off. Meaning we can turn it on again which can be a big relieve when trying to figure something out in a test environment. Oracle has disabled this option by setting in $APPL_TOP/PUBTST21.env the following line:

FORMS60_RESTRICT_ENTER_QUERY="TRUE"


When you change the TRUE value to FALSE and restart forms you will have the possibility to use the query/where option. You can use this by going to a forms screen and go to query mode (F11) now place a ‘:’ or ‘&’ character into one of the fields and execute the query (Ctrl + F11). Now you will be represented by a free query field as shown in the screenshot below.

An indication that the value of FORMS60_RESTRICT_ENTER_QUERY is still set to TRUE is that if you try to execute a search with one of the mentioned characters you will get a FRM-40367 error. FRM-40367: Invalid criteria in field XXX in example record.





No comments: