replace credit card numbers with *

Question

replace credit card numbers with *

katie1972

SSC-Addicted

Points: 414
More actions
November 17, 2010 at 3:16 am

#388247

Hi
I have been asked to write a script which shows the first 12 numbers of the credit card as * some of the details in the table dont arent all credit cards so if the ccv number is null on the table it doesnt need to be changed can anyone help me fast???
PLEASE! thanks for reading!

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Convert the number into a character data type (if it is not already) and use the STUFF function to replace the first n characters with asterisks. Please try this and post back if there's anything in particualr that you don't understand. It will help us to understand your problem if you post table DDL and sample data in the form of INSERT statements.

John

katie1972 SSC-Addicted Points: 414 More actions · Answer 2

Hi there thanks for your reply but i have never written a script before so i dont understand at all. Its SQL on Java too so i have even less of an idea how to write the script? My boss wants to check it first too any help would be appreciated

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

I think you need to hire a consultant, in that case.

John

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 4

Something like this

select stuff('5490 1234 5678 9128',1,12,'************')

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

katie1972 SSC-Addicted Points: 414 More actions · Answer 5

Thanks very much! If i use that script can i get it to amend all the card details in the table without having to put in the card numbers? Sorry to be so stupid!

WayneS SSC Guru Points: 95460 More actions · Answer 6

Expanding on what has already been provided:

-- test table with data

DECLARE @test-2 TABLE (CCNumber varchar(16), CCV varchar(4) NULL);

INSERT INTO @test-2

SELECT '1234567890123456', NULL UNION ALL -- don't update this row

SELECT '4123456789012345', '111' UNION ALL

SELECT '5987654321098765', '999';

SELECT *

FROM @test-2;

UPDATE @test-2

SET CCNumber = stuff(CCNumber, 1, 12, replicate('*',12))

WHERE CCV IS NOT NULL;

SELECT *

FROM @test-2;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 7

Can I mention that to be PCIDSS compliant you CANNOT store the CV2 number for any purpose after a tranasaction has been complete.

Also, this is how I would have done it. I think it looks simple as it only uses the one function with two arguments. Card numbers would normally be stored as strings anyway as they are not calculated and would take up less space. Depending on the datatype and storage method you may need to RTRIM the column.

UPDATE CardNumber

SET CardNumber = '************' + RIGHT(CardNumber,4)

WHERE CV2 IS NOT NULL

katie1972 SSC-Addicted Points: 414 More actions · Answer 8

Thanks very much for all your help this is what i have gone with...not tested it yet but hoping it will work!

WHILE (SELECT accountNumber FROM payments) Is Not NULL

IF (SELECT LEN(accountNumber) FROM payments) = 16

UPDATE payments SET accountNumber="************" + RIGHT(accountNumber,4)

ELSE

CONTINUE

END

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 9

You should not have a loop here with a WHILE statement. If you run this in the database server, your update statement will update all rows, not just the ones that you think you're selecting.

Ultimately you are not really ready to write this kind of statement, and if you are working with credit card numbers, you could end up costing your company fines or the business if you don't know what you are doing. You should let your boss know that you don't understand how to do this and get someone else to do it AND teach you what should be done for better coding in SQL.

rlwilde SSCommitted Points: 1575 More actions · Answer 10

Did you want something that just DISPLAYS the first twelve characters of the cc number as asterisks, or did you actually want to wipe the first twelve characters out of the database?

Maybe more along the lines of

SELECT '****-****-****-'+CONVERT(VARCHAR,RIGHT(CCNUMBER,4)) AS [CCNUMBER]

WHERE CCV IS NOT NULL

WayneS SSC Guru Points: 95460 More actions · Answer 11

I have to agree with Steve - if this is what you came up with after seeing the solutions posted here, then you need to STOP and get someone that knows what they're doing so that you don't mess up the data. Your update statement is NOT doing what you specified earlier (just those with a CCV value), and you WILL mess up your companies data by proceeding.

Your while and if statements should be incorporated into your update statement.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

katie1972 SSC-Addicted Points: 414 More actions · Answer 12

Thanks very much for all your help, you are right i am not ready or very willing to be writing scripts at my old job we had developers who did all this but thanks to you all for your replies they are really appreciated. Im going to use SSC's suggestion

UPDATE CardNumber

SET CardNumber = '************' + RIGHT(CardNumber,4)

WHERE CV2 IS NOT NULL

Thanks again

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 13

As a note, you comment that you were instructed to write a script that shows the CC numbers with all but the last 4 as *s. Did they intend for this to be permanent? You are modifying the source data, not the display, with the above options, keep that in mind.

I really hope your boss understands you're not ready for this position you've been shoved into and will have to learn as you go. There's bound to be some serious errors along the way.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

WayneS SSC Guru Points: 95460 More actions · Answer 14

Craig Farrell (11/17/2010)
As a note, you comment that you were instructed to write a script that shows the CC numbers with all but the last 4 as *s.

GOOD CATCH!

If this is what you need (not updating the cc #'s, but just displaying them), then:

SELECT CASE WHEN CV2 IS NOT NULL THEN REPLICATE('*',12) + RIGHT(cc_number,4)

ELSE cc_number END

FROM dbo.YourTable

should do the trick for you!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2