oracle sql statement to parse string

Question

I have a database field that stores a password with junk characters between each letter in the password. 3 junk chars, then 2 junk chars, then 3 junk chars, etc There will be 3 junk chars to start password and 2 or 3 junk chars at end of password.

So if password is BOB, the db value will be xxxBxxOxxxBxx where x is a random character.

Is there a way to return BOB in an oracle select statement using substrings,etc?

Thanks for anyone up for this challenge

Answer 1

Reusing the idea from here, though in this case the REGEXP_REPLACE() approach from MTO is definitely the best one.

First we generate CTE as a number table with integer from 0 to the maximum of plaintext characters of a password minus 1.

We can get the number of cleartext characters in an obfuscated string as follows:

• We can ignore the first 3 characters, those will always be garbage.

• If the number of plaintext characters is even, there will be 7 characters for each 2 plaintext characters, because the total of the garbage characters is 5 for 2 plaintext characters.

  So we get the number of plaintext characters with FLOOR((LENGTH(PASSWORDS.PASSWORD) - 3) / 7) * 2 if it is even.

  If it is odd, it is one more than an even number of plaintext characters and the length of the string's length minus 3 is no longer divisible by 7, because the last 1 plaintext character will be followed by 2 garbage characters.

  So we can check for the string length minus 3 modulo 7. If it is 0 the number of plaintext characters is even, we don't need to add anything. If it isn't 0, we add 1 and get the total (odd) number of plaintext characters.

  Together that's FLOOR((LENGTH(PASSWORDS.PASSWORD) - 3) / 7) * 2 + DECODE(MOD(LENGTH(PASSWORDS.PASSWORD) - 3, 7), 0, 0, 1).

We left join that CTE to the passwords table, so that for each cleartext character of a password there is a row with the password and a number from 0 to the number of cleartext characters of the password.

We can now use SUBSTR() to get each cleartext character. The base offset is 4, as we can ignore the first three characters. The joined number from the CTE let us calculate the additional offset. We always advance by at least 3 characters, which gives us CTE.I * 3. Additionally every 2 cleartext characters we need to further advance 1 time, so we add FLOOR(CTE.I / 2) giving us SUBSTR(PASSWORDS.PASSWORD, 4 + CTE.I * 3 + FLOOR(CTE.I / 2), 1).

No we have every single cleartext character, but in different rows. To concatenate them back together we group by the obfuscated password (and possibly by an ID too, should there be more then one row in the base table with the same password) and use LISTAGG. Ordering the the number from the CTE makes sure every plaintext character gets the right position.

WITH CTE(I)
AS
(
SELECT 0 I
       FROM DUAL
UNION ALL
SELECT CTE.I + 1
       FROM CTE
       WHERE CTE.I + 1 < (SELECT MAX(FLOOR((LENGTH(PASSWORDS.PASSWORD) - 3) / 7) * 2
                                     + DECODE(MOD(LENGTH(PASSWORDS.PASSWORD) - 3,  7),
                                              0, 0,
                                              1))
                                 FROM PASSWORDS)
)
SELECT PASSWORDS.ID,
       PASSWORDS.PASSWORD PASSWORD_OBFUSACTED,
       LISTAGG(SUBSTR(PASSWORDS.PASSWORD, 4 + CTE.I * 3 + FLOOR(CTE.I / 2), 1))
              WITHIN GROUP (ORDER BY CTE.I) PASSWORD_CLEARTEXT
       FROM PASSWORDS
            LEFT JOIN CTE
                      ON CTE.I < FLOOR((LENGTH(PASSWORDS.PASSWORD) - 3) / 7) * 2
                                 + DECODE(MOD(LENGTH(PASSWORDS.PASSWORD) - 3,  7),
                                          0, 0,
                                          1)
       GROUP BY PASSWORDS.ID,
                PASSWORDS.PASSWORD;

db<>fiddle

Note: This demonstrates, that an attacker must not even guess the random character (or characters, even that wouldn't make a difference), to get the cleartext password. Ergo this is an unsafe method to store passwords! Use hashing instead, that's (most likely, of course depending on the algorithm) irreversible.

Answer 2

Here is a PL/SQL solution that will take a password of any size:

CREATE OR REPLACE FUNCTION decode_password (p_password IN VARCHAR2)
    RETURN VARCHAR2
    DETERMINISTIC
AS
    l_ret   VARCHAR2 (100);
    l_pos      INT := 4;
BEGIN
    WHILE LENGTH (p_password) >= l_pos
    LOOP
        l_ret   := l_ret || SUBSTR (p_password, l_pos, 1);
        l_pos      := l_pos + 3;
        l_ret   := l_ret || SUBSTR (p_password, l_pos, 1);
        l_pos      := l_pos + 4;
    END LOOP;

    RETURN l_ret;
END decode_password;

To test it:

WITH
    aset
    AS
        (SELECT 'BBBBRRRRIIIAAAANNN' pwd
           FROM DUAL)
SELECT pwd, decode_password (pwd) decoded
  FROM aset;

The result is

BBBBRRRRIIIAAAANNN  BRIAN

Answer 3

You can use the regular expression ...((.)..(.)?)? and just keep the 2nd and 3rd capture groups:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( password ) AS
  SELECT 'xxx' FROM DUAL UNION ALL
  SELECT 'xxxBxx' FROM DUAL UNION ALL
  SELECT 'xxxBxxOxxx' FROM DUAL UNION ALL
  SELECT 'xxxBxxOxxxBxx' FROM DUAL UNION ALL
  SELECT 'xxxBxxOxxxBxxBxxx' FROM DUAL UNION ALL
  SELECT 'xxxBxxOxxxBxxBxxxOxx' FROM DUAL UNION ALL
  SELECT 'xxxBxxOxxxBxxBxxxOxxBxxx' FROM DUAL UNION ALL
  SELECT 'xxxBxxOxxxBxxBxxxOxxBxxxBxx' FROM DUAL UNION ALL
  SELECT 'xxxBxxOxxxBxxBxxxOxxBxxxBxxOxxx' FROM DUAL UNION ALL
  SELECT 'xxxBxxOxxxBxxBxxxOxxBxxxBxxOxxxBxx' FROM DUAL;

Query 1:

SELECT REGEXP_REPLACE(
         password,
         '...((.)..(.)?)?',
         '\2\3'
       ) As password
FROM   table_name

Results:

|  PASSWORD |
|-----------|
|    (null) |
|         B |
|        BO |
|       BOB |
|      BOBB |
|     BOBBO |
|    BOBBOB |
|   BOBBOBB |
|  BOBBOBBO |
| BOBBOBBOB |

Answer 4

If you have alphanumeric characters as junk as well and you know your maximum password length then you could do it the dirty way using substr() function. I've generated numbers with 2 and 3 letters gap from 4 to 98 first and cross joined it to table which stores passwords to avoid typing each number by hand. This will cover passwords up to 28 characters. Feel free to play with that.

Test data

create table t(pw varchar(255));
insert into t values ('xxxBxxOxxxBxxFxxxIxxVxxxExx!xxx');
insert into t values ('xxxPxxAxxxSxxSxxx');

Solution

Uses internal table to generate values used as input for substring function, cross join to apply each substring and then listagg to combine it again

with lookup as (
  select column_value as nr 
  from table(sys.odcinumberlist(4,7,11,14,18,21,25,28,32,35,39,42,46,49,53,56,60,63,67,70,74,77,81,84,88,91,95,98))
)
select listagg(substr(t.pw, l.nr, 1), '') within group(ORDER BY l.nr) as password
from lookup l
cross join t
group by t.pw;

Output

password
--------
BOBFIVE!
PASS

Check it out here: Live DEMO

This solution may take a bit of time to process for many rows.