Background: The Payment Card Industry (PCI) Data Security Standard
(PCI DSS) is a standard for financial institutions. It requires
sensitive information, such as credit card numbers, to be "unreadable
anywhere it is stored" using ha****ng, truncation or encryption.
I am looking for a simple truncation function that replaces the last
four digits in the given numeric with four characters (e.g. '*'). As
as Perl programmer (where a simple "s/.{4}$/****/" would suffice), the
resulting SQL/PL code strikes me as unnecessarily complex. Is there a
simpler way?
-- Replace last four characters in decimal number with stars.
-- E.g. 1234567890123456 -> 123456789012**** .
CREATE FUNCTION FUNC_MASK_LAST_4 (cardno decimal(21,0))
RETURNS CHAR(32)
NO EXTERNAL ACTION
DETERMINISTIC
RETURN SUBSTR(STRIP(CAST(cardno AS CHAR(32)), T, ' '),
32 - LENGTH(STRIP(CAST(cardno as char(32)), l,
'0')) + 1,
CAST(CEIL(LOG10(cardno)) AS INT) - 4) || '****'
@[EMAIL PROTECTED]
table largenum (lval decimal(21,0) not null)
@[EMAIL PROTECTED]
into largenum values
(1234567890123456789),
( 123456789012345678),
( 12345678901234567),
( 1234567890123456),
( 123456789012345),
( 12345678901234),
( 1234567890123),
( 123456789012),
( 12345678901),
( 1234567890),
( 123456789),
( 12345678),
( 1234567),
( 123456),
( 12345),
( 1234)
@[EMAIL PROTECTED]
lval, FUNC_MASK_LAST_4(lval) as masked from largenum
@[EMAIL PROTECTED]
MASKED
----------------------- --------------------------------
1234567890123456789. 123456789012345****
123456789012345678. 12345678901234****
12345678901234567. 1234567890123****
1234567890123456. 123456789012****
123456789012345. 12345678901****
12345678901234. 1234567890****
1234567890123. 123456789****
123456789012. 12345678****
12345678901. 1234567****
1234567890. 123456****
123456789. 12345****
12345678. 1234****
1234567. 123****
123456. 12****
12345. 1****
1234. ****
16 record(s) selected.
https://www.pcisecuritystandards.org/pdfs/pci_dss_v1-1.pdf
Regards,
Serman D.
--
|
