x

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?

more ▼

asked Jul 03, 2012 at 10:43 AM in Default

avatar image

jhowe
1.1k 56 60 66

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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;
     }
 }
more ▼

answered Jul 03, 2012 at 12:23 PM

avatar image

jhowe
1.1k 56 60 66

Can someone help me refactor this so i can get it to search for the pattern which is a regex expression for card numbers?

Jul 03, 2012 at 12:24 PM jhowe

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

Jul 04, 2012 at 02:25 AM KenJ

@KenJ or someone can you have a look at my code and see if this looks ok?

Jul 23, 2012 at 04:35 PM jhowe
(comments are locked)
10|1200 characters needed characters left

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 and the test credit card numbers here.

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


more ▼

answered Jul 03, 2012 at 11:06 AM

avatar image

KenJ
24.8k 3 10 19

Examples? i haven't got a clue how to do this.

Jul 03, 2012 at 11:25 AM jhowe

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/

Jul 03, 2012 at 12:02 PM KenJ

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???

Jul 03, 2012 at 12:21 PM jhowe

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

Jul 03, 2012 at 04:10 PM KenJ

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).

Jul 05, 2012 at 04:18 PM KenJ
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2091
x748

asked: Jul 03, 2012 at 10:43 AM

Seen: 2152 times

Last Updated: Aug 08, 2012 at 10:55 AM

Copyright 2016 Redgate Software. Privacy Policy