?
Solved

SQL Server stored proc encryption  question - complex

Posted on 2009-12-19
7
Medium Priority
?
344 Views
Last Modified: 2012-05-08
I'm trying to create a stored proc that basically does 2 things. It takes a ssn as parameter and does a select query. If the ssn is present, it returns the associated ssnid back. If ssn is not present I need to do an insert row and insert the ssn and also encrypt it. Then I do a select query to return the newly generated ssnid (auto increment) as an output param. I'm having trouble getting the syntax right and I've never done any sql encryption so I'm lost. How do I go about doing this? thnx,

- roger
@ssn char(10)
AS

BEGIN

 

      SET NOCOUNT ON

 

    -- Insert statements for procedure here

      DECLARE @ssnid int --this is the ssnid that needs to be outputted

 

      SELECT @ssnid = ssnid

      from  dbo.SSN

      where ssn = @ssn

END

 

if @ssnid IS NULL

      BEGIN

            insert into dbo.SSN(ssn,date_created,created_by)--this is where I need to encrypt ssn as well as attach zeros to the left if the ssn is less than 9 characters

            values(@ssn,getDate(),'blah')

 

            select @ssnid = ssnid

            from dbo.SSN

            where ssn = @ssn

 

            RETURN @ssnid

      END

else

      BEGIN

            RETURN @ssnid

      END

GO

Open in new window

0
Comment
Question by:roger v
  • 3
5 Comments
 
LVL 11

Accepted Solution

by:
Goodangel Matope earned 1050 total points
ID: 26089677
This is how I understand your question. You want a stored procedure that accepts a parameter (ssn) , and checks if that value exists in  the SSN table or not, and if it does NOT, you want to insert the ssn passed to the procedure into the SSN table, left padding it with Zeroes. You will need to be more specific about what you mean by Encrypting the SSN, but have a look at the code below, it will probably do what you want, with the exception of the encryption. Please explain what you want to achieve with the "ENcryption" part.
CREATE PROCEDURE spInsertSSN
@SSN VARCHAR(10) --varchar because if you use char(10) it will automatically fill with spaces up to 10 characters
AS
BEGIN

DECLARE @SSNID INT; -- the ssnid to be output
DECLARE @LeftPaddedSSN VARCHAR(10);
DECLARE @NumZeros INT;

--We clean up the SSN to make sure there no spaces
SET @SSN = LTRIM(RTRIM(@SSN)); --remove trailind and leading spaces
SET @SSN = REPLACE(@SSN, ' ',''); -- remove any spaces that may be inside the ssn

--We now left pad the SSN to make sure its 9 characters
SET @NumZeros = (9 - DATALENGTH(@SSN))
SET @LeftPaddedSSN = REPLICATE('0', @NumZeros) + @SSN;

IF NOT EXISTS (SELECT ssnid FROM dbo.SSN WHERE ssn = @SSN)
BEGIN --this part only happens if the record does not exist

   INSERT INTO dbo.SSN(ssn,date_created,created_by)--this is where I need to encrypt ssn as well as attach zeros to the left if the ssn is less than 9 characters
   VALUES(@ssn,getDate(),'blah')

   SET @SSNID = SCOPE_IDENTITY(); -- this sets the SSNID to the last inserted ID

END
ELSE BEGIN -- this part happens if the provided ssn already exists in the table
  SELECT @SSNID = [ssnid] FROM dbo.SSN WHERE [ssn] = @ssn
END

RETURN @SSNID

END;

Open in new window

0
 
LVL 1

Author Comment

by:roger v
ID: 26090328
@Goodangel,

O.K. here's what/why I'm talking about "encryption". The legal folks at our company decided that in order to make sure we're adhering to data security policy, we're gonna secure all our data. As part of this, I need to encrypt the ssn when it is stored in a table in the database. I've never done any sql server encryption but if the ssn can be converted into an encrypted form, that can be decrypted anytime I want to display it in a report, that should work.

I have a couple of questions on the above code: What exactly does replicate() do? And, what is scope_identity? Thnx,

- roger
0
 
LVL 1

Author Comment

by:roger v
ID: 26097015
Also, when I  use an ssn that is new and not in the table, the insert statement does not insert the ssn with padded zeros.
0
 
LVL 32

Assisted Solution

by:Brendt Hess
Brendt Hess earned 450 total points
ID: 26097912
Since we are talking SQL Server 2005, you should be considering using the native encryption abilities.  Alternately, you can install some freely available extended stored procedures for encrypting the data.  Since SQL 2005 has native encryption, however, start with that.

To use the native encryption abilities on a given column, you will need to start by setting up native encryption on SQL Server - creating or generating the certificate, etc.  I'd start by reading this article on encrypting a single column in SQL Server, and follow the trail from there:

http://msdn.microsoft.com/en-us/library/ms179331%28SQL.90%29.aspx
0
 
LVL 1

Author Closing Comment

by:roger v
ID: 31668185
My question was only partly answered, the main encryption part was not.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question