Άραγε πόσες φορές όταν γράφουμε ένα Delete Query μπορεί νά κάνουμε λάθος καί νά σβήσουμε στην βάση περισσότερες γραμμές από αυτές που θέλουμε.

Φαντάζομαι ότι αυτό μπορεί νά έχει συμβεί σέ όλους μας.Παρακάτω παρθέτω μία Stored Procedure μέσω της οποίας μπορούμε νά εκτελούμε το Delete Statment μας καί άν τυχόν έχουμε κάνει κάποιο λάθος η διαγγεγραμμένες εγγραφές έχουν αποθηκευτεί σέ ένα Deleted αντίγραφο του πίνακα που κάνουμε διαγραφή.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Cmd_Delete_sp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Cmd_Delete_sp]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Cmd_Delete_sp
  (
  @DeleteStatment AS VARCHAR(2000),   --Πέρασμα του Delete Statment σαν παράμετρο
  @HasIdentity smallint,                           --1 Αν ο πίνακας διαθέτει identity
  @Apply_Comment AS VARCHAR(1000),   --Εισαγωγή Σχολίου
  @ReturnErrMsg AS VARCHAR(2000) --Μύνημα λάθους κατά την εκτέλεση της procedure
  )

AS


--ΔΗΛΩΣΗ ΜΕΤΑΒΛΗΤΩΝ

DECLARE @VarStr AS VARCHAR(2000),     --Μεταβλητή για κατασκευή του Sql Statement
@VarSelect AS VARCHAR(2000),    --Μεταβλητή για την δημιουργία του Select Statement
@VarTable AS VARCHAR(2000),     --Μεταβλητή για την ονομασία του πίνακα των διαγγεγραμμένων εγγραφών
@VarPos AS INT                  --Μεταβλητή γιά την θέση των αποθχαρακτήρων


--Εδώ κάνουμε trim τα δεδομένα

SET @DeleteStatment = ' '+ LTRIM(RTRIM(@DeleteStatment)) + ' '

--Δημιουργούμε αντίγραφο της παραμέτρου @DeleteStatment για να εκτελέσουμε το 'SELECT' SQL STATEMENT

SET @VarSelect = @DeleteStatment
--Δημιουργία του  'SELECT' SQL από την παράμετρο @DeleteStatment
--έτσι δημιουργούμε άνα ακριβές αντίγραφο τών Record που πρόκειται νά διαγραφούν
SET @VarPos = CHARINDEX(' DELETE ', @VarSelect)

IF @VarPos > 0

BEGIN
      IF CHARINDEX(' FROM ', @VarSelect, @VarPos) = 0
      BEGIN

      --Εδώ δημιουργούμε το αντίγραφο άν στην παράμετρο  @DeleteStatment δέν έχουμε δηλώσει FROM π.χ. DELETE customers
         IF CHARINDEX(' WHERE ',LTRIM(RTRIM(@VarSelect)))=0
          BEGIN 

            SET @VarTable = LTRIM(RTRIM(SUBSTRING(@VarSelect, @VarPos + 7, 2000)))
            SET @VarSelect = ' SELECT * FROM ' + @VarTable
            SELECT @VarTable
          END
          ELSE
          BEGIN
           SET @VarStr = RTRIM(LTRIM(SUBSTRING(@VarSelect, CHARINDEX(' WHERE ', LTRIM(RTRIM(@VarSelect))) + 7, 2000)))
           SET @VarTable = LTRIM(RTRIM( SUBSTRING(@VarSelect, @VarPos + 7, CHARINDEX(' WHERE ', LTRIM(RTRIM(@VarSelect)))- 7)))
           SET @VarSelect = ' SELECT * FROM ' + @VarTable +' WHERE '+@VarStr

          END 

      END
      ELSE
      BEGIN
      --Εδώ δημιουργούμε το αντίγραφο άν στην παράμετρο  @DeleteStatment  έχουμε δηλώσει FROM π.χ. DELETE FROM customers
            SET @VarStr = RTRIM(LTRIM(SUBSTRING(@VarSelect, CHARINDEX(' FROM ', @VarSelect) + 6, 2000)))
           
            IF CHARINDEX(' ', @VarStr) = 0
            BEGIN
                  SET @VarSelect = ' SELECT * FROM ' + @VarStr
                  SET @VarTable = @VarStr
            END
            ELSE
            BEGIN
                  SELECT SUBSTRING(@VarStr, 1, CHARINDEX(' ', @VarStr))   ,@VarStr
                  SET @VarTable = SUBSTRING(@VarStr, 1, CHARINDEX(' ', @VarStr))
                  SET @VarSelect = REPLACE(@VarSelect, ' DELETE ', ' SELECT * ')
            END
      END
END
ELSE
BEGIN
     --Επιστρέφουμε μύνημα σφάλματος εφόσον δέν έχει δηλωθεί η σύνταξη DELETE
      SET @ReturnErrMsg = 'Δέν υπάρχει η έκφραση DELETE στην δήλωση της αρχικής παραμέτρου: Συντακτικό σφάλμα'
      RETURN -1
END


--Παρακάτω εκτελούμε το δημιουργηθέν Select Statement για νά εισάγουμε τις εγγραφές που πρόκειται νά διαγραφούν σέ ένα αντίγραφο του πίνακα
IF CHARINDEX('[',@VarTable)=0
BEGIN
SET @VarTable = 'DELETEDROWS_' + @VarTable
END
ELSE
BEGIN
SET @VarTable = '[DELETEDROWS_' + REPLACE(@VarTable,'[','')
END

--Έλεγχος ύπαρξης αντιγράφου του πίνακα από τον οποίο πρόκειται να κάνουμε διαγραφή

IF (SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @VarTable ) > 0
BEGIN      

      --Ο πίνακας ήδει υπάρχει
      --Δημιουργία του Insert Statment
   IF @HasIdentity=1
    BEGIN
      DECLARE @columnList varchar(1000),@columnName varchar(255),@execSql nvarchar(1000)
      SET @columnList=''
      DECLARE cur1 CURSOR FOR
      SELECT b.name from sysobjects a,syscolumns b WHERE a.id=b.id and a.type='u' and <A href="mailto:a.name=@VarTable">a.name=@VarTable</A>
      OPEN cur1
      FETCH NEXT FROM cur1 INTO  @columnName
      WHILE @@fetch_status<>-1
       BEGIN
         SELECT @columnList=@columnList+','+@columnName

      FETCH NEXT FROM cur1 INTO  @columnName
      END
      CLOSE cur1
      DEALLOCATE cur1
      SET @columnList=substring(@columnList,2,len(@columnList))
 

      SET @VarSelect = REPLACE (@VarSelect, ' SELECT * ', ' INSERT INTO ' + @VarTable + ' (<A href="mailto:'+@columnList+'">'+@columnList+'</A>)  SELECT *, (SELECT MAX(SEQUENCE_ID)+1 FROM ' + @VarTable + '), ''' +  @Apply_Comment + ''' ')

   END

   ELSE
   BEGIN     
      SET @VarSelect = REPLACE (@VarSelect, ' SELECT * ', ' INSERT INTO ' + @VarTable + ' SELECT *, (SELECT MAX(SEQUENCE_ID)+1 FROM ' + @VarTable + '), ''' +  @Apply_Comment + ''' ')
   END
END

ELSE  --Όταν ο πίνακας δέν υπάρχει

BEGIN
      --Δημιουργούμε το Create αυτού
      SET @VarSelect = REPLACE (@VarSelect, ' SELECT * ', ' SELECT *, 1 AS SEQUENCE_ID, ''' + @Apply_Comment + ''' AS COMMENT INTO ' + @VarTable + ' '  )
END
 

BEGIN TRANSACTION DELETE_TRANSACTION
--Εκτέλεση του δυναμικού SELECT SQL το οποίο Δημιουργεί και εισάγει τις εγγραφές που προκειται να διαγραφούν στο αντίγραφο του πίνακα

EXEC(@VarSelect)

--Έλεγχος για λάθη
IF @@ERROR > 0
BEGIN
      SET @ReturnErrMsg = 'Λάθος κατά την δημιουργία ή εισαγωγή στο αντίγραφο'
      ROLLBACK TRANSACTION DELETE_TRANSACTION
      RETURN -1
END
--Εκτέλεση της εντολής διαγραφής
EXEC (@DeleteStatment)
--Έλεγχος για λάθη
IF @@ERROR > 0
BEGIN
      SET @ReturnErrMsg = 'Λάθος κατά την εκτέλεση της διαγραφής'
      ROLLBACK TRANSACTION DELETE_TRANSACTION
      RETURN -1
END
ELSE
BEGIN
      --Επιτυχής εκτέλεση

      COMMIT TRANSACTION DELETE_TRANSACTION
      RETURN 0
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Παράδειγμα σε Northind.

'Οταν ο πίνακας που θα γίνει διαγραφή έχει IDENTITY πρέπει νά τον εκτελέσουμε όπως παρακάτω

'DELETEDROWS_  πάντα το πρόθεμα του νέου πίνακα
SET IDENTITY_INSERT [DELETEDROWS_Order details] ON
go
Cmd_Delete_sp 'DELETE [Order details] WHERE OrderID>=1 AND OrderID<=1000',1,' ',' '
go
SET IDENTITY_INSERT [DELETEDROWS_Order details] OFF

όταν δέν έχει εκτελείται

Cmd_Delete_sp 'DELETE [Order details] WHERE OrderID>=1 AND OrderID<=1000',1,' ',' '

Σκοπός αυτού του άρθρου είναι να μας παρουσιάσει την υλοποίηση του RSA .

Δημιουργούμε πρώτα μία κλάση όπως παρακάτω.

Public Class CryptographyClass

Public vPbKey As String

Public vPrKey As String

Public EncryptedByteArray() As Byte

Public DecryptedByteArray() As Byte

Public myKeySize As Integer

Public Function Encrypt(ByVal varData() As Byte, ByVal vPbKeyIn As String) As Boolean

Try

Dim Params As CspParameters = New CspParameters()

Params.Flags = CspProviderFlags.UseMachineKeyStore

Dim RSA As RSACryptoServiceProvider = New RSACryptoServiceProvider(Params)

RSA.FromXmlString(vPbKeyIn)

EncryptedByteArray = RSA.Encrypt(varData, False)

Return True

Catch ex as Exception

'Δέν αποκρυπτογραφήθηκε σωστά

Return False

End Try

End Function

Public Function Decrypt(ByVal varData() As Byte, ByVal vPrKeyIn As String) As Boolean

Try

Dim Params As CspParameters = New CspParameters()

Params.Flags = CspProviderFlags.UseMachineKeyStore

Dim RSA As RSACryptoServiceProvider = New RSACryptoServiceProvider(Params)

RSA.FromXmlString(vPrKeyIn)

DecryptedByteArray = RSA.Decrypt(varData, False)

Return True

Catch ex as Exception

'Δέν κρυπτογραφήθηκε σωστά

Return False

End Try

End Function

Public Function GetNewKey() As Boolean

Try

Dim Params As CspParameters = New CspParameters()

Params.Flags = CspProviderFlags.UseMachineKeyStore

Dim RSA As System.Security.Cryptography.RSACryptoServiceProvider = New RSACryptoServiceProvider(Params)

Me.vPbKey = RSA.ToXmlString(False)

Me.vPrKey = RSA.ToXmlString(True)

myKeySize = RSA.KeySize

Return True

Catch

Return False

End Try

End Function

End Class

Κατόπιν δημιουργούμε μία φόρμα ή οποία νά περιέχει ένα Button (Name=btnGenKey) και πέντε textbox

Name=txtPbKey, Name=txtPrKey,Name=txtToEncrypt,Name=EncryptedTxt,Name=DecryptedTxt

και ακόμα δύο Buttons  Name=btnEncrypt,Name=btnDecrypt

Public Class Form1

Ελπίζω το παραπάνω παράδειγμα νά σας φανεί χρήσιμο.

Την παρακάτω procedure την χρησιμοποιώ όταν είμαι στο στάδιο ανάπτυξης για να βρίσκω εύκολα από ποιές sp εξαρτώνται οι πίνακες της βάσης μου.

IF EXISTS (select * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Table_onProc_Depends_sp]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Table_onProc_Depends_sp]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE Table_onProc_Depends_sp
AS
 SELECT    a.name as tableName, a.id as tableId, b.name as ProcName,b.id as ProcId
 FROM      sysobjects a
 LEFT OUTER JOIN (sysobjects b LEFT OUTER JOIN sysdepends on b.id = sysdepends.id) on sysdepends.depid = a.id
 WHERE      a.xtype = 'u'
 AND          b.xtype = 'p'
 AND NOT   a.name = 'dtproperties'
 GROUP BY  a.name, a.id, b.name,b.id
 ORDER BY  a.name
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Δεν είναι κάτι φοβερό αλλά πιστεύω οτι είναι εύχρηστο.

Η συνάρτηση επιστρέφει ένα Encrypted varchar στοιχείο .Για νά κάνετε un-encrypt το στοιχείο απλά περάστε στην παράμετρο το Encrypted varchar στοιχείο.

CREATE FUNCTION PwdEncrypt
  (
   @Pwd as varchar(255)
  )
RETURNS varchar(255)
AS

    BEGIN

     DECLARE @PwdEncrypted as varchar(255)
     DECLARE @PwdLength as int
     DECLARE @iPOS AS int
     DECLARE @XOR AS int
     --Το Encryption γίνεται μόνο όταν η παράμετρος δέν είναι null
     IF @Pwd IS NOT NULL
     BEGIN
      SET @PwdLength = len(@Pwd)
      -- Δημιουργία κλειδιού σέ συνδυασμό μέ το μέγεθος του pwd και της θέσης του πρώτου 'α' που βρίσκεται στο pwd
      -- (Το γράμμα 'α' είναι ένα απο τα πιό κοινά γράμματα του αλφαβήτου)
      -- (Βέβαια μπορεί νά τροποποιηθεί ανάλογα όπως θέλετε)
      SET @XOR = @PwdLength + charindex(@Pwd,'α')
      SET @iPOS = 1
      SET @PwdEncrypted = ''
      WHILE @iPOS <= @PwdLength
      BEGIN 
       SET @PwdEncrypted = @PwdEncrypted + char(Ascii(substring(@Pwd, @iPOS, 1)) ^ @XOR)

       SET @iPOS = @iPOS + 1
      END
     END
     --Εάν η τιμή της παραμέτρου @Pwd είναι null τότε δέν γίνεται καμμία ενέργεια
     IF @Pwd IS NULL
     BEGIN
      SET @PwdEncrypted = NULL
     END
     RETURN @PwdEncrypted
END

GO
GRANT EXECUTE ON PwdEncrypt TO PUBLIC

Θά μου πείτε γιατί να το κάνω από των SQL SERVER και όχι μέσα από τον Client όπου μόνο αυτός θά ξέρει το κλειδί και δέν θα μπορεί να το κάνει όποιος ξέρει SQL και την συγκεκριμμένη λειτουργία.

Σίγουρα το ερώτημα θά είναι εύλογο .Απλά είναι ένας εύκολος τρόπος να αποθηκεύεται κάποια δεδομένα μέ ένα σχετικό encryption.