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?
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; } }
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
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).
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/
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
8 People are following this question.
