[SQL] CIFRAR(encriptar) / DECIFRAR(desencriptar) Campos en SQL2005


Saludos amigos, en esta ocasión prepare un ejemplo de como en cifrar un campo en una tabla en SQL 2005. Para hacer esto es usando la función:

ENCRYPTBYPASSPHRASE: Para cifrar y

DECRYPTBYPASSPHRASE: Para descifrar.

Como primer paso tienen que preparar el campo de la base de datos para que sea de tipo VARBINARY.

image

Como segundo paso creamos una función escalar en SQL 2005 para poder colocar el valor cifrado. Hagan clic derecho sobre la opción “Funciones con valores escalares” y seleccionen “Nueva función con valores escalares”.

 

image

 

La función quedaría de la siguiente manera.

CREATE FUNCTION [dbo].[fnColocaClave] 
(
    @clave VARCHAR(25)
)
RETURNS VarBinary(8000)
AS
BEGIN
    
    
    DECLARE @pass AS VarBinary(8000)
    ------------------------------------
    ------------------------------------
    SET @pass = ENCRYPTBYPASSPHRASE('dbCurso09',@clave)--dbCurso09 es la llave para cifrar el campo.
    ------------------------------------
    ------------------------------------    
    RETURN @pass

END

 

Ahora ejecutamos la función al insertar un nuevo campo a una tabla.

--El campo a cifrar es contrasena y se guarda el valor sqlPsyKrest.
INSERT INTO USUARIO_CUR (nombre, apellidos, email, contrasena, activo, fecha_add)
VALUES('Ivan','Rangel','ir@email.com',dbo.fnColocaClave('sqlPsyKrest'),1,GETDATE())

Si consultamos el valor de nuestro campo seria:

SELECT id, nombre, apellidos, email, contrasena
FROM USUARIO_CUR

Resultado:

image

Listo ya nos devuelve el valor d nuestro campo de manera cifrada, ahora el siguiente paso es crear la función para leer el campo cifrado.

CREATE FUNCTION fnLeeClave 
(
    @clave VARBINARY(8000)
)
RETURNS VARCHAR(25)
AS
BEGIN
    
    
    DECLARE @pass AS VARCHAR(25)
    ------------------------------------
    ------------------------------------
    --Se descifra el campo aplicandole la misma llave con la que se cifro dbCurso09
    SET @pass = DECRYPTBYPASSPHRASE('dbCurso09',@clave)
    ------------------------------------
    ------------------------------------    
    RETURN @pass

END
GO

Por ultimo consultamos la tabla aplicándole al campo cifrado la función para leer el campo cifrado.

SELECT id, nombre, apellidos, email, dbo.fnLeeClave(contrasena) 
FROM USUARIO_CUR

Y el resultado seria el siguiente:

image

Espero les sea de ayuda!!!.

 

votaPorMiBlogIvan

Apóyame votando aquí: http://blogit.ms en la categoría SQL Server.

Los pasos para votar están aquí te llevara 3 min: Clic Aquí. 

    • David
    • 19/02/09

    Que tal amigo!!
    Que chido que postes este tipo de código para comenzar a recordar la gran importancia de codificar datos para una mayor seguridad y así poder utilizarlos en cualquier consulta o aplicación que requiera hacer consultas a una base de datos.

    Saludos!!!
    P.D. ya me acordaba de esto jajaja!!!

  1. El tipo de dato del campo que voy a cifrar lo debo de declarar varbinary(8000), sea este numerico, entero o cadena (el campo original me refiero) u para devolverlo a su valor original solo con un convert lo haria o como funciona.

    Otra duda, cuando encripto campos en sql la informacion a travez de internet viaja encriptada o no? o cuando hago una consulta como la que nos presentas cuando llega a la computadora cliente esa cadena que viaja por la red va encriptada o desencriptada?

    Saludos y espero me puedas ayudar.

    • psykrest
    • 23/04/09

    Hola Roberto, efectivamente no importa el tipo de dato que requieras usar en tu columna, al final para leerlo deberás aplicar un CONVERT, ahora la “des encriptación” se ejecuta del lado del Servidor de BD, y no si la envías por medio de un Servicio Web o Página ASPX tú decides si va encriptado o no, en el caso de que lo envíes encriptado, tendrás que aplicarle un método para leerlo del lado de la aplicación, por lo general la encriptación se usa en este caso para proteger la información del lado de la base de datos y descifrarla a la hora de hacer consultas, si tienes dudas no dudes en escribir, saludos Ivan.

  2. Gracias por tu respuesta.

    Una pregunta haber si te entendi, cuando encripto campos de SQL al momento de hacer la consulta y desencriptarla con un procedimiento almacenado ejecutado en codigo de C# este se ejecuta en el Servidor de SQL aunque la este llamando en codigo de programacion C# y cuando viaja por la red esta ya va desencriptada?

    Espero y me halla explicado saludos.

  3. Hola, respecto al anterior comentario que opinas.

    SAludos

    • psykrest
    • 2/05/09

    Hola roberto, perdón por la tardanza pero he estado un poco enfermo y no habia revisado el blog, mira aunque lo ejecutes desde c#, al final la instrucción SQL, se esta interprentando y compilando desde el servidor de base de datos, como mencione anteriormente la decodificación se ejecuta en el servidor de BD y al final se proyecta sobre tu aplicación ya sea WEB o Window application; Puedes hacer la consulta y ejecutarla y tu decides si muestras informaci+ón cifrada o no?, ojala te sirva mi opinion, estamos en contacto. Saludos

    • qko
    • 3/11/09

    grax men… x vos voy a pasar una materia..
    ggg…
    ta bueno la info!!!
    salu2..

    • Gabo
    • 5/11/09

    Mi duda es si se podria hacer la encriptacion sin importar el tipo de dato. Segun leí comentan que con un Convert, el problema es que no se como hacerlo no se si me puedes ayudar en eso? graciasss

    • Otoniel
    • 29/11/09

    Hey men esta con todo esto de encryptar, men segui con estas colaboraciones te hara aun mas , mejor profesional men…..

    si lo pruebo y esta con todo donde puedo publicar mi pequeño aporte…..

    • Andrè
    • 2/12/09

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘[dbo].[fnColocaClave]
    (
    @clave varchar(25)
    )
    returns varbinary(8000)
    ‘ at line 1

    me salio esto >> que hice mal?

    ♠Andrè♠

    • psykrest
    • 4/12/09

    Amigo, mencionas algo de tu versión de MySQl, este script es para SQL Server 2005 o 2008, por favor verifica en que manejador de BD estas ejecutando el script, si es SQL Server entonces lo revisamos. Saludos.

    • psykrest
    • 4/12/09

    Hola Otoniel, cuando quieras colaborar solo enviame un mail “ivan.rangel.c@gmail.com” y vemos la manera de publicarlo, gracias por intentar participar conmigo y con la gente interesada en aprender más… saludos.

    • daniel
    • 25/01/10

    Genial! solo que me aparece “tipo de datos binario” y no me lo encripta.

    Que puedo hacer si ya tengo establecido el tipo de datos como nvarchar(MAX), Como puedo desencriptar?

    Estoy usando SQL2008

    Saludos

    • Carlos
    • 16/09/10

    ¿ Tienes alguna función para desencriptar en el cliente?, estoy encriptando con ENCRYPTBYPASSPHRASE en el lado servidor pero necesito desencriptar en el cliente.

    Saludos,

    Carlos.

    • lsegura
    • 7/11/10

    Gracias amigo, este fue un buen post!!!!

    • Orlando
    • 10/04/11

    por que dbCurso09 que es nombre de la base de datos una columna o que?

    pues he seguido los pasos pero no me sale

    SELECT Usuario, dbo.fnLeeClave(Contrasena) —
    FROM Usuario
    me sale

    Usuario Contrasena
    ———|————
    Jpardo | Contrasena

    por que me devuelve el nombre de la columna

    • Orlando
    • 10/04/11

    Disculpa ya vi el problema
    es que lo vi en el store

    Muy bueno y gracias por todos sigue adelante amigo……

    • yoo
    • 26/08/11

    Men sos un groso sabelo😀

    • psykrest
    • 12/10/11

    Claro, no importa el tipo de dato que quieras encriptar, pero recuerda que el TIPO de dato en la table debe ser varbinary, esto es independiente a lo que quieras cifrar en el (strings, int’s, xml’s).

    Ivan

    • Vicente
    • 26/10/11

    Gracias…. men fue de gran ayuda… exelente mis respetos para voz

    • nainzp
    • 20/01/12

    Funciona la perfección. Muchas gracias. Saludos desde Santa Marta, Colombia.

    • Delybeth
    • 4/05/12

    holaaa, alguien sabe como encriptar en Oracle

    • Antonio
    • 17/07/12

    Excelente
    Gracias un mundo en 5 minutos instalado e implementado

  4. muchas gracia por compartir tus conocimientos con todos nosotros me fue de mucha ayuda sigue así….

  5. Buen aporte !!!!

    • yalefon
    • 23/10/12

    muy…bueno el aporte….

    • rq5
    • 20/02/13

    gracias compadre me sirvió mucho.

    • SaVaGe
    • 6/03/13

    Podrias poner un ejemplo usando EncryptByKey con las llaves simetricas y certificado?

    • tigre
    • 15/04/13

    Excelente aporte.. te sacaste un diez!!!
    lo he aplicado en sql2008 sin problemas..
    lo único que le agregué fue el tan odiado WITH ENCRYPTION y lo que conlleva..

    Tan sencillo de aplicar y tan enoooooorme beneficio.
    saludos desde monterrey, mx.

  1. No trackbacks yet.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: