question

jhowe avatar image
jhowe asked

sql luhn check

Hi all, i have an ssis package which runs and dumps data in our datawarehouse. After it runs i would like to execute a stored proc or something to check the DW for card information and blank anything it finds, as we have some muppets that put card numbers etc. in phone number fields.. What is the best way of doing this?
sql-server-2008sql-server-2008-r2
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jhowe avatar image
jhowe answered
Update : after doing some research i think the best way of doing this is in SSIS as SQL doesn't natually support REGEX and using LIKE in the way described seems more resource intensive etc. This is what i've come up with in C# using a script component. My programming is crap so any suggestions for refactoring would be greatly appreciated. Hope this helps anyone with a similar issue! /* Microsoft SQL Server Integration Services Script Component * Write scripts using Microsoft Visual C# 2008. * ScriptMain is the entry point class of the script.*/ using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; using System.Text.RegularExpressions; [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { public override void Input0_ProcessInputRow(Input0Buffer Row) { //Replace each \ with \\ so that C# doesn't treat \ as escape character //Pattern: Card matching Switch, Solo, Visa, MasterCard, Discover, American Express string sPattern = "^((67\\d{2})|(4\\d{3})|(5[1-5]\\d{2})|(6011))-?\\s?\\d{4}-?\\s?\\d{4}-?\\s?\\d{4}|3[4,7]\\d{13}$"; string BusinessEmail = Row.BusinessEmail; string BusinessPhone = Row.BusinessPhone; string HomePhone = Row.HomePhone; string MobilePhone = Row.MobilePhone; string PersonalEmail = Row.PersonalEmail; //Find any matches of the pattern in the string Match One = Regex.Match(BusinessEmail, sPattern, RegexOptions.IgnoreCase); Match Two = Regex.Match(BusinessPhone, sPattern, RegexOptions.IgnoreCase); Match Three = Regex.Match(HomePhone, sPattern, RegexOptions.IgnoreCase); Match Four = Regex.Match(MobilePhone, sPattern, RegexOptions.IgnoreCase); Match Five = Regex.Match(PersonalEmail, sPattern, RegexOptions.IgnoreCase); //If a match is found set field to null if (One.Success) Row.BusinessEmail_IsNull = true; if (Two.Success) Row.BusinessPhone_IsNull = true; if (Three.Success) Row.HomePhone_IsNull = true; if (Four.Success) Row.MobilePhone_IsNull = true; if (Five.Success) Row.PersonalEmail_IsNull = true; } }
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can someone help me refactor this so i can get it to search for the pattern which is a regex expression for card numbers?
0 Likes 0 ·
I updated my original answer with the equivalent of a credit card regex. Have a look to see if it's what you are after
0 Likes 0 ·
@KenJ or someone can you have a look at my code and see if this looks ok?
0 Likes 0 ·
KenJ avatar image
KenJ answered
You can add an execute SQL task at the end of the package with a query that deletes any card information it finds (or add a stored procedure to the database that does the delete and call that from the new task). [Update based on further discussion] Although SQL Server doesn't really do regular expressions, you can get creative with the LIKE statement. Here is a user defined function that "validates" many credit cards (based on the regular expressions [here][1] and the test credit card numbers [here][2]. This example runs in tempdb and cleans up after itself. Just graft the function call into your code and you should be close: USE tempdb GO CREATE TABLE #creditcards (cardnumber VARCHAR(50) NOT NULL) INSERT #creditcards VALUES(378282246310005),(371449635398431),(378734493671000),(5610591081018250),(30569309025904),(38520000023237),(6011111111111117),(6011000990139424),(3530111333300000),(3566002020360505),(5555555555554444),(5105105105105100),(4111111111111111),(4012888888881881),(4222222222222), (3782822463100051) IF OBJECT_ID (N'dbo.IsValidCreditCardNumber') IS NOT NULL DROP FUNCTION dbo.IsValidCreditCardNumber GO CREATE FUNCTION dbo.IsValidCreditCardNumber (@cardNumber varchar(max)) RETURNS int WITH EXECUTE AS CALLER AS BEGIN DECLARE @IsValidCreditCardNumber INT = CASE WHEN /*VISA 13 character*/ @cardNumber LIKE '4[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' /*VISA 16 character*/ OR @cardNumber LIKE '4[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' /*MasterCard*/ OR @cardNumber LIKE '51[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR @cardNumber LIKE '52[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR @cardNumber LIKE '53[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR @cardNumber LIKE '54[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR @cardNumber LIKE '55[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' /*American Express*/ OR @cardNumber LIKE '34[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR @cardNumber LIKE '37[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' /*Diners Club*/ OR @cardNumber LIKE '300[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR @cardNumber LIKE '301[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR @cardNumber LIKE '302[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR @cardNumber LIKE '303[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR @cardNumber LIKE '304[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR @cardNumber LIKE '305[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR @cardNumber LIKE '36[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR @cardNumber LIKE '38[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' /*Discover*/ OR @cardNumber LIKE '6011[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR @cardNumber LIKE '65[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' /*JCB*/ OR @cardNumber LIKE '2131[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR @cardNumber LIKE '1800[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR @cardNumber LIKE '35[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN 1 ELSE 0 END Return @IsValidCreditCardNumber END GO SELECT cardNumber AS ValidCardNumber FROM ( SELECT REPLACE(REPLACE(cardnumber, ' ', ''), '-', '') cardNumber FROM #creditcards) AS src WHERE dbo.IsValidCreditCardNumber(cardNumber) = 1 SELECT cardNumber AS NotAValidCardNumber FROM ( SELECT REPLACE(REPLACE(cardnumber, ' ', ''), '-', '') cardNumber FROM #creditcards) AS src WHERE dbo.IsValidCreditCardNumber(cardNumber) = 0 DROP TABLE #creditcards GO IF OBJECT_ID (N'dbo.IsValidCreditCardNumber') IS NOT NULL DROP FUNCTION dbo.IsValidCreditCardNumber GO [1]: http://www.regular-expressions.info/creditcard.html [2]: http://www.paypalobjects.com/en_US/vhelp/paypalmanager_help/credit_card_numbers.htm
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

A - To check the incoming data, you can use a Regular Expression within a script task. The regex site I mentioned above has the credit card regexes pre-built. Here's a quick overview of regex within a script task - http://www.purplefrogsystems.com/blog/2011/07/pattern-matching-in-ssis-using-regular-expressions-and-the-script-component/ or B - To run this at the end of the package, add an Execute SQL task at the end. Just get your entire query working, turn it into a stored procedure and call that from the task (or embed the entire working query within the task, which seems a bit messy).
1 Like 1 ·
Examples? i haven't got a clue how to do this.
0 Likes 0 ·
Execute SQL Task: http://www.sqlis.com/post/The-Execute-SQL-Task.aspx Delete statement (in case "blank anything" means remove the row): http://www.simple-talk.com/sql/learn-sql-server/the-delete-statement-in-sql-server/ Update statement (in case "blank anything" means clear the column): http://www.simple-talk.com/sql/learn-sql-server/update--basics-in-sql-server/
0 Likes 0 ·
thanks i am failry proficient in SQL the bit i am struggling with is how to check for card numbers as well as searching all tables columns etc. i'm thinking of maybe using a search stored proc with a regex search???
0 Likes 0 ·
SQL Server doesn't really do regex. You can create a CLR assembly to do it, though: http://msdn.microsoft.com/en-us/magazine/cc163473.aspx
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.