Finally, dangerous Oracle bug fixed after several years in the wild

Almost three years ago a serious bug was found in Oracle 19c by one of my colleagues, who is a brilliant COBOL programmer.

Due to this bug, it happens that bytes not representing numeric values are stored in number columns. In some cases these bytes are converted to bytes representing another valid number . 

Let me give you an example: Your COBOL code intends to store the digit nine in a number columm. By accident (bug in your code / unexpected data pattern) a non numeric value is sent to the database and digit three gets stored. 

Now you might think COBOL? What do I have to do with COBOL? Before you quit reading : COBOL RUNS THE WORLD! 

Many people are not aware of that fact that billions of code lines are still in productive use every day. Here is a link to an interesting article about that topic: COBOL in 2023

As soon as we discovered the bug, we reported it to Oracle. Finally this month patches were announced. 

Before we delve into the specifics of the problem, let me provide a brief introduction to COBOL numeric variables. This will help everyone understand the significance of the issue, especially if you work in the finance industry, where your company may be using COBOL code without you even realizing it.

This is an numeric COBOL variable : SALGRADE  PIC 9(01).

This variable can store one byte, representing a single digit. The number inside the brackets indicates how many bytes this variable can store. Unlike in Java, a variable that is never initialized in COBOL does not have a default value and instead points to encoded byte 00 (X'00').

To make use of the variable you have to initialize the variable or assign a value 

INITIALIZE SALGRADE  --> value 0 is assigend to the variable
MOVE 1 TO SALGRADE.  --> value 1 is assigend to the variable 

COBOL permits the use of uninitialized or unassigned variables, which can occur when a program contains bugs or has not undergone comprehensive testing. Additionally, numeric variables can inadvertently receive non-numeric byte values, resulting in invalid calculations. However, COBOL prohibits the use of such invalid bytes as numbers or in calculations.

Example:

* SALGRADE is not initialized 
ADD 1 TO SALGRADE

* If you try to add one to SALGRADE then you get the following error: 

COBOL ERROR
Object Code error : file 'ora_num_bug'
error code: 163, pc=0, call=1, seg=0
163     Illegal character in numeric field (in ./ora_num_bug.cbl, line 72)

Accidentally storing non-numeric values in a numeric variable is a relatively common error in COBOL systems, and accessing such a variable results in the error mentioned above.

My colleague found that on the other hand Oracle 19c accepts and stores these "non-numeric bytes" in number columns without raising an error. This can lead to significant problems down the line.

Similar to prepared statements in Java, COBOL variables are also used as bind variables to prevent a hard parse for every new value in a SQL statement.

The following section is a demonstration of what happens when the Oracle bug and the described COBOL behavior coincide. The salgrade table from the Oracle demo schema scott/tiger is being utilized. Although we cannot recall the specific version of Oracle 19c from three years ago, we have replicated the issue this month using Oracle 19.18.

The  table salgrade has three columns without given precision and scale    

desc salgrade

Name  Null? Typ    
----- ----- ------ 
GRADE       NUMBER 
LOSAL       NUMBER 
HISAL       NUMBER

Now onto the COBOL part:

1. define some numeric variables: 
     EXEC SQL
         BEGIN DECLARE SECTION
     END-EXEC

     01  SALGRADE    PIC 9(01).
     01  LOWSAL      PIC 9(04).
     01  HIGHSAL     PIC 9(04).

     EXEC SQL
         END DECLARE SECTION
     END-EXEC 
     
2. asign a value to lowsal and hisal and "accidently" forget to assign a value to SALGRADE, so that SALGRADE is byte 00
  
     MOVE 1 TO LOWSAL
     MOVE 1000 TO HIGHSAL
     
3. now do an insert using the three variables 
     EXEC SQL
         INSERT INTO SALGRADE (GRADE, LOSAL, HISAL)
         VALUES (:SALGRADE,:LOWSAL,:HIGHSAL)
     END-EXEC

4. test the return code from the database. If 0 everything is fine...    
     IF SQLCODE NOT = 0
         DISPLAY "STATEMENT FAILD WITH ORA"SQLCODE
     END-IF  
     
5. commit     
     EXEC SQL
         COMMIT
     END-EXEC 

As mentioned earlier in the text we would have anticipated the database to reject the newly inserted bytes, as it's unclear how they could be interpreted as a number.

What is returned, when you query the inserted data using SQL Developer script runner?

select * 
from salgrade;
    
    
     GRADE      LOSAL      HISAL
---------- ---------- ----------
       3.2          1       1000

-- Is it a display bug or the actual number? 


select grade, rawtohex(grade) 
from salgrade ;

     GRADE RAWTOHEX(GRADE)                             
---------- ---------------
       3.2 C10415    
       

select * 
from salgrade
where grade = 3.2;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
       3.2          1       1000

The actual number 3.2 is stored into the column, when the database gets send  byte 00! 

It's bad when it's possible to store corrupt data in the database. It's worse when this corrupt data is interpreted as a valid number. And it's a disaster when, as in this case, these numbers can be read back via a COBOL program or any other database driver and are used in further business process as if they were intended. You have no chance of noticing that!

What happens if two bytes 00 are written ? To check that lets change the variable  SALGRADE  to PIC 9(02) , set other values for the other two variables and rerun the program. 

salgrade  again not initialized 

lowsal = 1001

highsal = 2000

Result using SQL Developers script Runner: 

select grade, rawtohex(grade) , losal, hisal 
from salgrade;

     GRADE RAWTOHEX(GRADE)                            LOSAL      HISAL
---------- ------------------------------------- ---------- ----------
       3.2 C10415                                         1       1000
       
It simply doesn't output the newly inserted row!

Lets try sqlplus

     GRADE RAWTOHEX(GRADE)                         LOSAL    HISAL
---------- --------------------------------------- ---------- --------
       3.2 C10415                                         1       1000
           C1F1                                        1001       2000           

Grade is shown as null but cannot be queried with the filter predicate "is null"  because it is not null (rawtohex)

If the column grade for the second row is read by the COBOL driver ORA-1438 is raised which is actually a good thing because the program knows something is wrong with the data. 

In COBOL variables have fixed length and most of the time when data is stored in the database their is a column with corresponding precision.   

So lets do some further experiments:  Add nine columns to our test table with precision one to nine and nine corresponding numeric variables c1 to c9 to the program. 

ALTER TABLE SALGRADE ADD (
  C1 NUMBER(1),
  C2 NUMBER(2),
  C3 NUMBER(3),
  ...
); 

01  C1    PIC 9(01).
01  C2    PIC 9(02).
01  C3    PIC 9(03).
...

EXEC SQL
	 INSERT INTO SALGRADE (C1)
	 VALUES (:C1)
END-EXEC
...


Results: 

c1 -->         3 rawtohex --> C104  -- WORST CASE!
c2 --> (null)	 rawtohex --> C1F1
c3 --> STATEMENT FAILD WITH ORA-1438
c4 --> (null)	 rawtohex -->C2F1F1
c5 --> STATEMENT FAILD WITH ORA-1438
c6 --> (null)	 rawtohex -->C3F1F1F1
c7 --> STATEMENT FAILD WITH ORA-1438
c8 --> (null)	 rawtohex --> C4F1F1F1F1
c9 --> STATEMENT FAILD WITH ORA-1438

So the results are changing depending on the COBOL variable and column definition. 

The bug is first fixed in version 23.1 but Oracle provides backport patches. 

Bug 32563824 - INSERT OF LOW-VALUES FROM PRO*COBOL SHOWS UNEXPECTED RESULT

After installing the patch and the actions described above are attempted, a new ORA message is raised: "ORA-24352 :conversion of data from COBOL display data type to Oracle number type failed."

If someone is able to test if all of this can also happen using Pro*C  i would love to hear the results.