Blog de Javier Egea

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?

 

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