REGEXP_REPLACE - Credit Card (CC) Numbers

I'm starting to rewrite some of our payment processing stuff right now. One thing I want to get a handle on is what is being sent to the payment gateway (we're capturing the response, but the request isn't easily accessible).

So I created a table and I'm writing the REQUEST URL to it. Ran my first test, hey look, there is the credit card number. That won't work.

I then started to think about how to mask just the credit card number...I could look for the key(word) in each URL string, but not all gateways are the same. Regular Expressions!

A nice 5 year old example from Alice Rischert can be found here. Following that it was fairly easy to figure out, specifically the example (REGEXP_INSTR) on finding the Zip code.

[[:digit:]]{13,16}

That's it. That's all there is too it and it's applicable to each and every gateway. You could have some false positives if you're passing through 13 to 16 digit numbers, but this is essentially a logging table, so who cares. If I need to see the exact credit card number, I can look it up through the usual process (access, decrypting, etc).

I ran a bunch of tests to see how it would work:

VAR C VARCHAR2(4000);

SELECT regexp_replace( :c, '[[:digit:]]{13,16}', 'X' ) r 
FROM dual;

I'm starting with the easy cases, numbers, 13 to 16 digits long:

CJUSTICE@TESTING>EXEC :C := 1234123412341234;
CJUSTICE@TESTING>/

R
----------------------------------------------
X

CJUSTICE@TESTING>--15
CJUSTICE@TESTING>EXEC :C := 123412341234123;
CJUSTICE@TESTING>/

R
----------------------------------------------
X

CJUSTICE@TESTING>--14
CJUSTICE@TESTING>EXEC :C := 12341234123412;
CJUSTICE@TESTING>/

R
----------------------------------------------
X

CJUSTICE@TESTING>--13
CJUSTICE@TESTING>EXEC :C := 1234123412341;
CJUSTICE@TESTING>/

R
----------------------------------------------
X

Now some of the negative cases, still just numbers:

CJUSTICE@TESTING>--17
CJUSTICE@TESTING>EXEC :C := 12341234123412341;
CJUSTICE@TESTING>/

R
------------------------------------------------------------
X1

CJUSTICE@TESTING>--18
CJUSTICE@TESTING>EXEC :C := 123412341234123412;
CJUSTICE@TESTING>/

R
------------------------------------------------------------
X12

CJUSTICE@TESTING>--19
CJUSTICE@TESTING>EXEC :C := 1234123412341234123;
CJUSTICE@TESTING>/

R
------------------------------------------------------------
X123

CJUSTICE@TESTING>--20
CJUSTICE@TESTING>EXEC :C := 12341234123412341234;
CJUSTICE@TESTING>/

R
------------------------------------------------------------
X1234

CJUSTICE@TESTING>--30
CJUSTICE@TESTING>EXEC :C := 123412341234123412341234123412;
CJUSTICE@TESTING>/

R
------------------------------------------------------------
XX

So any number over 16 is not masked. For my purposes, I don't much care.

Numbers are easy right? Let's start adding some strings in with the Credit Card number embedded in there.

CJUSTICE@TESTING>SET DEFINE OFF
CJUSTICE@TESTING>EXEC :C := 'card_number=1234123412341234';
CJUSTICE@TESTING>/

R
----------------------------------------------------------------------------
card_number=X

CJUSTICE@TESTING>EXEC :C := 'test=bollocks&card_number=1234123412341234';
CJUSTICE@TESTING>/

R
----------------------------------------------------------------------------
test=bollocks&card_number=X

CJUSTICE@TESTING>EXEC :C := 'card_number=1234123412341234&test=bollocks';
CJUSTICE@TESTING>/

R
----------------------------------------------------------------------------
card_number=X&test=bollocks
CJUSTICE@TESTING>SET DEFINE ON

This is more for me than it is for you (another reason you should blog). I always forget how to use Regular Expressions since I encounter the opportunity to use them so rarely.