Cobol connect to oracle failed when password field is defined longer than real password value

Question

My COBOL program cannot connect to oracle when the password field is defined longer than actual password length for a user. i.e, if the password value is 'mypasswd', the host variable to keep password must be defined with "PIC X(8)", otherwise, connection will fail; for example:

 1         IDENTIFICATION DIVISION.
 2         PROGRAM-ID. SAMPLE.
 3         ENVIRONMENT DIVISION.
 4         DATA DIVISION.
 5         WORKING-STORAGE SECTION.
 6             EXEC SQL BEGIN DECLARE SECTION END-EXEC.
 7         01 USERNAME      PIC         X(010).
 8         01 PASSWD        PIC         X(010).
 9         01 DBSTRING      PIC         X(020).
10             EXEC SQL END DECLARE SECTION END-EXEC.
11             EXEC SQL INCLUDE SQLCA END-EXEC.
12
13         PROCEDURE DIVISION.
14         BEGIN-PGM.
15             EXEC SQL WHENEVER SQLERROR
16              DO PERFORM SQL-ERROR
17             END-EXEC.
18         LOGON.
19             MOVE "myuser" TO USERNAME.
20             MOVE "mypasswd" TO PASSWD.
21             MOVE "mydb" TO DBSTRING.
22             EXEC SQL
23              CONNECT :USERNAME IDENTIFIED BY :PASSWD USING :DBSTRING
24             END-EXEC.
25         LOGOUT.
26             DISPLAY "HAVE A GOOD DAY.".
27             EXEC SQL COMMIT WORK RELEASE END-EXEC.
28             STOP RUN.
29         SQL-ERROR.
30             EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
31             DISPLAY "ORACLE ERROR DETECTED:".
32             DISPLAY SQLERRMC.
33             EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
34             STOP RUN.

I must get a connect failure: ORACLE ERROR DETECTED: ORA-01017: invalid username/password; logon denied

But when I change the password field definition to: 8 01 PASSWD PIC X(008). i.e. length is the same length of real password value (length("mypasswd")=8), the program can connect to Oracle successfully.

My situation is that we need users to be able to provide their own username and password, so we must firstly define a username and password fields long enough to keep the maximum length we allow. However, as stated above, all connection requests will be failed if a user chooses a shorter password than the maximum.

The program is migrated from an old version of Oracle 11.2.0.1.0, where we don't have this issue, the program was working fine, the connect operation was successful. But the problem occurred after we migrate to Oracle 12.1.0.1.0.

Have you used your favourite search engine? Asked Oracle? Looked in the documentation? — Bill Woodger, Apr 11 '14 at 13:17

Bill Woodger · Accepted Answer · 2014-04-14T04:38:48.467

If you are using Pro*COBOL, then this link is for you: http://docs.oracle.com/cd/A57673_01/DOC/api/doc/PCO18/ch1.htm#toc024

It shows how to define your username and password fields as VARYING.

 WORKING STORAGE SECTION. 

     ... 

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 

         01  USERNAME  PIC X(10) VARYING. 

         01  PASSWD    PIC X(10) VARYING. 

         ... 

     EXEC SQL END DECLARE SECTION END-EXEC. 

     ... 

 PROCEDURE DIVISION. 

 LOGON. 

     MOVE "SCOTT" TO USERNAME-ARR. 

     MOVE 5 TO USERNAME-LEN. 

     MOVE "TIGER" TO PASSWD-ARR. 

     MOVE 5 TO PASSWD-LEN. 

     EXEC SQL WHENEVER SQLERROR GOTO LOGON-ERROR END-EXEC. 

     EXEC SQL 

         CONNECT :USERNAME IDENTIFIED BY :PASSWD 

     END-EXEC.

As it turns out, the example quoted is not directly useful to you (from Comments) because your passwords may not be five in length.

This really is no problem. You can calculate the length of the password for a given user, and then, instead of using the literal 5, use the value that you have calculated.

@NealB has shown in his answer a simple way to do this (if you can have no leading or embedded blanks in a password).

INSPECT PASSWD TALLYING PSSWDLEN FOR ALL SPACE
COMPUTE PSSWDLEN = LENGTH OF PASSWD - PSSWDLEN

If you are unable to use that method, a simple loop-construct of your choice starting from the last byte of the password field and continuing whilst a space is encountered. Watch out for the entirely-space possibility.

You may want to use the same technique for username anyway, as it would be more transportable amongst different flavours of Oracle/OS (depending on what it is that is allowing it to work for you). I'd do that, unless it it absolutely impossible that it is ever required.

You do mention a move to a new Oracle version. This behaviour should be documented in the Summary of Changes, or similar, section of the documentation. If you cannot find reference to it, contact Oracle and find out what is going on.

If you are not using Pro*COBOL, you may be able to emulate the effect of VARYING.

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 

         01  USERNAME.
             05  USERNAME-LEN BINARY PIC 9(4).
             05  USERNAME-VALUE      PIC X(10). 

         01  PASSWD.
             05  PASSWD-LEN BINARY PIC 9(4).
             05  PASSWD-VALUE      PIC X(10). 

     END-EXEC.

Then:

 LOGON. 

     MOVE "SCOTT" TO USERNAME-VALUE. 

     MOVE 5 TO USERNAME-LEN. 

     MOVE "TIGER" TO PASSWD-VALUE. 

     MOVE 5 TO PASSWD-LEN. 

     EXEC SQL WHENEVER SQLERROR GOTO LOGON-ERROR END-EXEC. 

     EXEC SQL 

         CONNECT :USERNAME IDENTIFIED BY :PASSWD 

     END-EXEC.

You may have to try:

         01  USERNAME.
             05  USERNAME-LEN BINARY PIC 9(4).
             05  USERNAME-VALUE.
                 10  FILLER OCCURS 1 TO 10 TIMES
                     DEPENDING ON USERNAME-LEN.
                     15  FILLER      PIC X. 

         01  PASSWD.
             05  PASSWD-LEN BINARY PIC 9(4).
             05  PASSWD-VALUE.
                 10  FILLER OCCURS 1 TO 10 TIMES
                     DEPENDING ON PASSWD-LEN.
                     15  FILLER      PIC X. 

     END-EXEC.

If getting nowhere with suggestion, you need to supply more information, like OS, version of COBOL, version of Oracle, and what you have tried and what results you got with those attempts.

Thank you, Bill. I am using Pro*COBOL, you solution is workable as tested, but in my case i don't know the exact password value, so the "MOVE 5 TO PASSD-LEN' does not work, i cannot get the password length. — HiHui, Apr 14 '14 at 01:54
And more, this issue occurs only with 'password' field, it does not affect 'username' field, i.e, the definition of 'username' field can be longer than the real username value. — HiHui, Apr 14 '14 at 01:58
Thank you Bill. I am using Pro*COBOL; and this solution is worked:01 USERNAME. 05 USERNAME-LEN BINARY PIC 9(4). 05 USERNAME-VALUE PIC X(10). but i cannot make the assumption that customers won't use a whitespace in their password. And i am suspecting the oracle had make some changes when upgrade from 11.2.0.1.0 to 12.1.0.1.0, i will investigate more. — HiHui, Apr 14 '14 at 06:15
OK, so loop from the back to count trailing spaces, as I suggested in the answer. Use LENGTH OF/FUNCTION LENGTH to get the length of the field, start there and work backwards until you find a non-blank. Test the field first for all blank to make termination of the loop easy. Let us know if you find something from Oracle (will help others with same problem). — Bill Woodger, Apr 14 '14 at 06:45
Yes, don't we all? For your documentation for the new version there should be something which relates to this, with an explanation of how you have to deal with it. That may require code changes, it may not. If there is no reference to the problem, you need to take it up with Oracle. They will then tell you what needs to be done. That may require code changes, it may not. In the short-term, to get it going, you may need to make a change which you can later back out. Depends on what you find out from Oracle. It is still possible someone who has faced this will see the question, but hit Oracle. — Bill Woodger, Apr 14 '14 at 06:54

score 1 · Answer 2 · edited Apr 11 '14 at 19:40

1

Have you tried using reference modification to adjust the length of the username/password on the connect request?

I am not an Oracle kind-of-guy, but something like this might work:

22             EXEC SQL
23              CONNECT :USERNAME(1:UNAMELEN) IDENTIFIED BY :PASSWD(1:PSSWDLEN) USING :DBSTRING
24             END-EXEC.

where the UNAMELEN and PSSWDLEN are numeric variables (e.g. PIC S9(4) BINARY) containing the actual lengths of the user name and password.

Determining the values for UNAMELEN and PSSWDLEN can be done using the INSPECT verb something like this:

INSPECT PASSWD TALLYING PSSWDLEN FOR ALL SPACE
COMPUTE PSSWDLEN = LENGTH OF PASSWD - PSSWDLEN

This will work provided that passwords and user names do not contain internal blank spaces. If they do you will have to compute the actual lengths differently.

edited Apr 11 '14 at 19:40

Bill Woodger

12,968
4
38
47

answered Apr 11 '14 at 19:32

NealB

16,670
2
39
60

The other thing I was thinking of was a delimiting binary zero, but a search led to something else as well. All of these should have been attempted already, but there we go. – Bill Woodger Apr 11 '14 at 20:33
Hi NealB, "CONNECT :USERNAME(1:UNAMELEN) IDENTIFIED BY ..." does not work with Pro*COBOL, although "USERNAME(1:UNAMELEN)" is valid COBOL literal, Pro*COBOL cannot understand it. – HiHui Apr 14 '14 at 02:00
INSPECT PASSWD to tail all space is reasonable, but i cannot make sure whether user have a whitespace in the password content. – HiHui Apr 14 '14 at 03:08

Cobol connect to oracle failed when password field is defined longer than real password value

2 Answers2