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

jhowe gravatar image

jhowe
1.1k 51 57 61

(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

jhowe gravatar image

jhowe
1.1k 51 57 61

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

KenJ gravatar image

KenJ
20k 1 3 11

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1840
x587

asked: Jul 03, 2012 at 10:43 AM

Seen: 1525 times

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