Descifrando el enigma. Cifrando y descifrando en SQL Server de forma sencilla

Una forma de las formas que tenemos de proteger nuestra información en nuestras bases de datos es hacerlo a través del cifrado de datos. Todos sabemos que los lenguajes de programación aportan librerías para ello, y que es bastante cómodo gestionarlo desde el código fuente de nuestras aplicaciones. Muchos hemos trabajado con el namespace System.Security.Cryptography de .NET, que nos permite manipular muchos de los detalles de criptografía.

Pero a veces, nos interesa que los algoritmos de criptografía se ejecuten en el SGBD. Yo personalmente prefiero trabajar siempre con procedimientos almacenados, y es aquí donde tengo la necesidad de utilizar toda la tecnología de criptografía de SQL Server.

La forma más simple para cifrar datos en SQL Server, sin la necesidad de contar con las gestiones de un administrador es utilizar una clave secreta. No voy a entrar en si es la mejor o peor forma de cifrado, pues mi intención es que aquellos que aún no se han adentrado en estas técnicas de protección comiencen a utilizarlas.

Sql server nos aporta dos funciones para el cifrado simétrico:

  • EncryptedbyPassphrase: Cifra datos mediante una frase de contraseña
  • DecryptByPassPhrase : Descifra los datos que se cifraron con una frase de contraseña

Vamos a ver un sencillo ejemplo. Como siempre utilizaremos la base de datos de ejemplo Pubs. Creamos una tabla que se va a llamar PruebaCifrado , pero lo vamos a hacer con la instrucción SELECT.. INTO.. de manera que la columna au_fname se cree con los datos encriptados.Utilizamos para ello la función EncryptedbyPassphrase con los siguientes parámetros:

EncryptByPassPhrase(@clavesecreta, au_fname, 1, CONVERT( varbinary,au_id))

  • 1. La clave secreta
  • 2. La columna de la tabla origen que vamos a cifrar (au_lname de authors)
  • 3. 1, porque vamos a agregar un autenticador que nos ayudará a impedir la sustitución del valor de los campos cifrados (Un valor de una fila no se podrá cambiar con otra. El autenticador será la clave primaria)
  • 4. El autenticador: La clave primaria (au_id)

Declaramos la clave secreta y creamos la tabla:

DECLARE @clavesecreta varchar(200);
SET @clavesecreta= ‘U-571 Operacion Enigma’;

Select au_id,EncryptByPassPhrase(@clavesecreta, au_fname, 1,
CONVERT( varbinary, au_id)) as ‘au_fname’
into PruebaCifrado
from authors
order by au_id

El resultado de la tabla sería el siguiente ( solo muestro algunas filas de ejemplo):

au_id

au_fname

172-32-1176

0x010000008AED4E240D1076B874C92253029363AAA6F006007066E57F78BDD4B7601B404F730C5217CE09662F4B608E4947C68458

213-46-8915

0x010000002F5859168EB04C5483C427B5B8B0B059CD9CB976C00E8754FE001A5121EC813ED65EB3A496CBFF7CB7AE679DC4DC3305

238-95-7766

0x010000000319DE1F08E1A4E51CF692BD5C2CB94530A58B56FE4BB19034A9486751791261326FED5AB3A7C14D5CD60CA8B02AAB5C

267-41-2394

0x01000000B896A4CF0D1221BF56837CB347D003D25B7E5A77E02D84E7FCDA2F59395979070B97DAEF62E35DACF1998BF31EEEE4B2

274-80-9391

0x010000002380AC03C124E67196333BFD10E0E2D1D4836C9F69E1CD6B08954EAA64B99016D8B52452020532921F4820E5975E58D0

 

 

Totalmente ilegible, verdad. ¡Me encanta!

Pues bien saquemos la máquina “Enigma” y descifremos. Para ello vamos a utilizar la función DecryptByPassPhrase con los siguientes parámetros:

DecryptByPassphrase(@clavesecreta, au_fname, 1 ,CONVERT(varbinary, au_id))

  • 1. La clave secreta
  • 2. La columna de la tabla que vamos a descifrar
  • 3. 1, porque indicamos que hemos utilizado un autenticador
  • 4. El autenticador: La clave primaria (au_id)

Por lo tanto descifremos:

SELECT au_id, CONVERT(varchar,
DecryptByPassphrase(@clavesecreta, au_fname,
1 ,
CONVERT(varbinary, au_id))) AS ‘au_fname desencriptada’
from PruebaCifrado
order by au_id

Obtendremos el siguiente resultado:

au_id

au_fname desencriptada

172-32-1176

Johnson

213-46-8915

Marjorie

238-95-7766

Cheryl

267-41-2394

Michael

274-80-9391

Dean

341-22-1782

Meander

409-56-7008

Abraham

427-17-2319

Ann

472-27-2349

Burt

486-29-1786

Charlene

527-72-3246

Morningstar

648-92-1872

Reginald

672-71-3249

Akiko

712-45-1867

Innes

722-51-5454

Michel

724-08-9931

Dirk

724-80-9391

Stearns

756-30-7391

Livia

807-91-6654

Sylvia

846-92-7186

Sheryl

893-72-1158

Heather

899-46-2035

Anne

998-72-3567

Albert

¿Y ahora qué? Pues a la espera de mostraros un ejemplo con clave pública, os recomiendo que veáis la película U-571 en la que pretenden capturar la máquina de cifrado alemana Enigma, para mí una de las mejores películas de submarinos, ¿o me recomendáis otra?

 

Naturalmente, solo números, con ROW_NUMBER en T_SQL













Los que hemos trabajado con las distintas versiones de SLQ Server a lo largo de los años (desde la versión 6.5, pasando por la 7 ,2000, 2005 y ahora 2008) hemos visto la buena evolución de este producto, y a decir verdad, no deja de sorprenderme gratamente.

He aquí que cuando hemos tenido la necesidad de añadir un número secuencial al ejecutar una consulta, todos nos las hemos ingeniado con las posibilidades que nos da T-SQL. Quien no ha utilizado alguna vez los alias de tablas, para extraer información fila por fila de datos relacionados con la misma tabla.

En el siguiente ejemplo, vamos a añadir una primera columna que nos va a indicar el número de fila que le corresponde. Utilizaremos para ello la tabla Authors de la base de datos Pubs (sigo añorando la utilización de ésta para pruebas y aprendizaje):

Tabla Author

Select

(Select COUNT(au_lname) from authors b

where a. au_id>=b.au_id

) as secuencia,

au_lname,au_fname, city ,au_id

from authors a

 
 

secuencia

au_lname

au_fname

city

au_id

1

White

Johnson

Menlo Park

172-32-1176

2

Green

Marjorie

Oakland

213-46-8915

3

Carson

Cheryl

Berkeley

238-95-7766

4

O’Leary

Michael

San Jose

267-41-2394

5

Straight

Dean

Oakland

274-80-9391

6

Smith

Meander

Lawrence

341-22-1782

7

Bennet

Abraham

Berkeley

409-56-7008

8

Dull

Ann

Palo Alto

427-17-2319

9

Gringlesby

Burt

Covelo

472-27-2349

10

Locksley

Charlene

San Francisco

486-29-1786

11

Greene

Morningstar

Nashville

527-72-3246

12

Blotchet-Halls

Reginald

Corvallis

648-92-1872

13

Yokomoto

Akiko

Walnut Creek

672-71-3249

Como vemos, hemos conseguido extraer un número secuencial para la tabla Authors. Quizás que para mí una forma útil, pero un tanto rígida, porque nos hemos tenido que basar en el orden de la clave primaria (au_id).

 

En las últimas versiones de SqlServer (estoy hablando de la 2005 y 2008) ha aparecido una función llamada ROW_NUMBER, que devuelve el número secuencial de una fila de un conjunto de resultados, comenzando con 1. En el siguiente ejemplo lo mostraré:

Tabla Author con Row_number ordenado por apellido y nombre
Select ROW_NUMBER() OVER(ORDER BY au_lname,au_fname ) as secuencia,au_lname,au_fname, city ,au_id
from authors

 

 

secuencia

au_lname

au_fname

city

au_id

1

Bennet

Abraham

Berkeley

409-56-7008

2

Blotchet-Halls

Reginald

Corvallis

648-92-1872

3

Carson

Cheryl

Berkeley

238-95-7766

4

DeFrance

Michel

Gary

722-51-5454

5

del Castillo

Innes

Ann Arbor

712-45-1867

6

Dull

Ann

Palo Alto

427-17-2319

7

Green

Marjorie

Oakland

213-46-8915

8

Greene

Morningstar

Nashville

527-72-3246

9

Gringlesby

Burt

Covelo

472-27-2349

10

Hunter

Sheryl

Palo Alto

846-92-7186

11

Karsen

Livia

Oakland

756-30-7391

12

Locksley

Charlene

San Francisco

486-29-1786

 

Pero donde verdaderamente me fascina esta función es a la hora de utilizarla para filtrar datos. Puede parecer simple, pero resulta muy útil en ocasiones en las que la lógica de la consulta se complica. Vamos a utilizar un sencillo ejemplo.

Vanos a extraer las filas con los 5 mejores valores de la columna Quantity (Qty), durante el año 1993.

 

Tabla Sales filtrando 5 valores a elegir (Qty)

WITH Quantity AS
(SELECT ROW_NUMBER() OVER (ORDER BY qty desc) AS ‘RowQty’, stor_id, ord_num, ord_date, qty, payterms, title_id
FROM sales
where YEAR(ord_date) = 1993)

 

SELECT * FROM Quantity
WHERE RowQty BETWEEN 1 AND 5;

 

RowQty

stor_id

ord_num

ord_date

qty

payterms

title_id

1

7066

A2976

1993-05-24 00:00:00.000

50

Net 30

PC8888

2

7896

X999

1993-02-21 00:00:00.000

35

ON invoice

BU2075

3

8042

QA879.1

1993-05-22 00:00:00.000

30

Net 30

PC1035

4

8042

P723

1993-03-11 00:00:00.000

25

Net 30

BU1111

5

7131

P3087a

1993-05-29 00:00:00.000

25

Net 60

PS7777

 

Sé que existen otras maneras de hacerlo, pero al entender este ejemplo veremos su potencial que seguramente nos sacará de un buen apuro.

Aprovecho para mandarte un fuerte abrazo, querido lector en esta mi primera publicación en este blog

Un saludo,

Javier Egea