Preventing SQL-injection

Conversion

When the Oracle Forms have been converted to Polymer automatically, there still has to be done some final manual work to fine-tune the application. One of the things that still need to be done is that the complex PL/SQL routines within the Forms and Libraries have to be moved to the database. In order to so, they need some adjustments, because they will need to make use of the functionality of other routines within the database - rather than of routines within the forms itself or within the forms libraries.

SQL-injection

Within the PL/SQL routines in the forms, especially in pre-query triggers, there may be made use of dynamic SQL, for instance to filter the outcome of the search-results when in query-mode. When moving these PL/SQL routines to the database, this is where some extra attention is needed, because the dynamic SQL part can introduce the possibility to perform a SQL-injection.

A lot of programmers have chosen for the option to create dynamic SQL using concatenations (in Oracle SQL written as two pipes || ). A complete string is fabricated containing a big part of the query, where variables are concatenated with keywords like “and” and “or”. For instance like in this example:

The variables may come from fields in the screen. And this is where the danger comes from, because instead of filling in in the screen what is expected from me, I can also fill in a SQL-string in the search field. Here is an example of such a SQL-injection string:

So the query of the programmer is ended within the variable, and the statement 1=2 is making sure it does not do anything. The two comment characters at the end make sure that whatever comes behind it is commented out. Then, with a union, a completely other statement is introduced, which was not intended by the programmer. In this case a select from all_tables, but it could also be for example all the names and salaries from an HCM database. Here is a part of a screen from our test-environment where the problem was still unresolved:

In yellow you can see that I filled in SQL-code in the search field (in the blue area) and the results, the tablenames from the database, are in the output on the screen.

How to prevent it

A way to prevent SQL-injection is making use of bind variables. The bind variables are only used for the real variables and are not intermingled with the operators, so that it is not possible to temper with the SQL code from the input in the fields in the screen. In our example we exchanged the information between a PL/SQL-package in the database with the Polymer screen, using a sys_refcursor. So when using dynamic SQL to fill this cursor, I created the following statement to use bind variables instead of a SQL-string with concatenations:

In case that different queries must be used in different occasions, it is better to use different SQL’s for different occations, rather than to try to put a part of the SQL in a string, and concatenate it to the rest of the query.

Over auteur Stefan van Glabbeek

Oracle Fusion Apps HCM functional consultant at Qualogy. Stefan is also a senior functional and technical PeopleSoft HCM consultant with 14 years of experience. Overall, he has over 18 years of experience in IT.

Meer posts van Stefan van Glabbeek