Cómo encriptar y desencriptar contraseñas usando frases de contraseña en SQL Server – Querychat

En este post aprenderemos a cifrar y descifrar contraseñas con una frase de contraseña en SQL Server junto con ejemplos de almacenamiento de las contraseñas cifradas y descifradas en una tabla.

Cuando creamos una contraseña, puede ser almacenada de forma encriptada con una serie de caracteres o palabras únicas y más tarde los resultados encriptados también pueden ser recuperados desencriptando la misma. En SQL Server, podemos usar la función ENCRYPTBYPASSPHRASE para encriptar las contraseñas con una frase y la función DECRYPTBYPASSPHRASE para restaurar una contraseña encriptada.

Sintaxis de cifrado con una frase de contraseña


SQL Server proporciona la función ENCRYPTBYPASSPHRASE para cifrar la contraseña deseada o cualquier cadena que devuelva un valor de tipo VARBINARY con una longitud máxima de 8000 bytes. Tiene 2 parámetros que consisten en una frase de contraseña o una variable que contiene una frase de contraseña y el otro es una cadena de texto o una variable que contiene una cadena de texto.

La sintaxis es la siguiente:
 
ENCRYPTBYPASSPHRASE (

   [ 'passphase' | @passphrase ] ,

   [ 'textstring' | @textstring ]

);
  1. ENCRYPTBYPASSPHRASE (
  2.  
  3. [ ‘passphase’ | @passphrase ] ,
  4.  
  5. [ ‘textstring’ | @textstring ]
  6.  
  7. );
ENCRYPTBYPASSPHRASE (

   [ 'passphase' | @passphrase ] ,

   [ 'textstring' | @textstring ]

);

Nota: Esta función devolverá una combinación de valores que siempre es diferente cada vez que la ejecutamos según la tecla introducida.


La sintaxis de desencriptación con una frase clave

 
Para la función de desencriptación, podemos usar el comando DECRYPTBYPASSPHRASE para desencriptar una contraseña encriptada con una frase clave. También tiene 2 parámetros, una frase o variable que contiene una frase para generar la clave de descifrado y una clave cifrada o variable que contiene los datos que se cifran con la clave en un tipo VARBINARY.

La sintaxis es la siguiente:
 
DECRYPTBYPASSPHRASE (

   [ 'passphase' | @passphrase ] ,

   [ 'encryptedkey' | @encryptedkey ]

);
  1. DECRYPTBYPASSPHRASE (
  2.  
  3. [ ‘passphase’ | @passphrase ] ,
  4.  
  5. [ ‘encryptedkey’ | @encryptedkey ]
  6.  
  7. );
DECRYPTBYPASSPHRASE (

   [ 'passphase' | @passphrase ] ,

   [ 'encryptedkey' | @encryptedkey ]

);

Nuestro ejemplo

Para nuestro propósito de demostración, usamos una base de datos MYDB de muestra y una tabla USERS_LOGIN. La tabla USERS_LOGIN contiene IDs, nombres de usuario y contraseñas no encriptadas y encriptadas. Comencemos siguiendo las siguientes secciones.

#1 Preparación

Primero, creamos la base de datos MYDB y la tabla USERS_LOGIN con columnas específicas para la columna de contraseña cifrada que utilizará el tipo VARBINARY y 255 bytes de longitud. Y luego rellenamos las filas de datos en la tabla.

La siguiente es la sintaxis para crearlos a través de Transact-SQL:

CREATE DATABASE MYDB

GO

 

USE MYDB

GO


CREATE TABLE USERS_LOGIN (

ID INT, 

USERNAME VARCHAR(50), 

PASSWORD_UNENCRYPTED VARCHAR(50), 

ENCRYPTED_PWD VARBINARY(255)

)

GO

 

INSERT INTO USERS_LOGIN (ID, USERNAME, PASSWORD_UNENCRYPTED)

SELECT 1, 'user1','secretpwd1' UNION

SELECT 2, 'user2','secretpwd2'
  1. CREATE DATABASE MYDB
  2.  
  3. GO
  4.  
  5.  
  6.  
  7. USE MYDB
  8.  
  9. GO
  10.  
  11.  
  12. CREATE TABLE USERS_LOGIN (
  13.  
  14. ID INT,
  15.  
  16. USERNAME VARCHAR(50),
  17.  
  18. PASSWORD_UNENCRYPTED VARCHAR(50),
  19.  
  20. ENCRYPTED_PWD VARBINARY(255)
  21.  
  22. )
  23.  
  24. GO
  25.  
  26.  
  27.  
  28. INSERT INTO USERS_LOGIN (ID, USERNAME, PASSWORD_UNENCRYPTED)
  29.  
  30. SELECT 1, ‘user1’,‘secretpwd1’ UNION
  31.  
  32. SELECT 2, ‘user2’,‘secretpwd2’
CREATE DATABASE MYDB

GO

 

USE MYDB

GO


CREATE TABLE USERS_LOGIN (

ID INT, 

USERNAME VARCHAR(50), 

PASSWORD_UNENCRYPTED VARCHAR(50), 

ENCRYPTED_PWD VARBINARY(255)

)

GO

 

INSERT INTO USERS_LOGIN (ID, USERNAME, PASSWORD_UNENCRYPTED)

SELECT 1, 'user1','secretpwd1' UNION

SELECT 2, 'user2','secretpwd2'

Echemos un vistazo a los datos de la tabla USERS_LOGIN ejecutando el comando “SELECT * FROM USERS_LOGIN” y el resultado se mostrara así:

ID USERNAME PASSWORD_UNENCRYPTED ENCRYPTED_PWD
1 user1 secretpwd1 NULL
2 user2 secretpwd2 NULL

Tabla 1. Un ejemplo de la tabla USERS_LOGIN

#2 Cifrar contraseñas

Para cifrar las contraseñas en la tabla USER_LOGIN, ponemos la columna USERNAME como parámetro de la frase y la columna PASSWORD_UNENCRYPTED como clave de cadena en la función ENCRYPTBYPASSSPHRASE. La siguiente sintaxis se utiliza para cifrar las contraseñas y almacenarlas en la tabla mediante la sentencia UPDATE:

UPDATE USERS_LOGIN

SET ENCRYPTED_PWD = ENCRYPTBYPASSPHRASE(

   USERNAME,

   PASSWORD_UNENCRYPTED

);
  1. UPDATE USERS_LOGIN
  2.  
  3. SET ENCRYPTED_PWD = ENCRYPTBYPASSPHRASE(
  4.  
  5. USERNAME,
  6.  
  7. PASSWORD_UNENCRYPTED
  8.  
  9. );
UPDATE USERS_LOGIN

SET ENCRYPTED_PWD = ENCRYPTBYPASSPHRASE(

   USERNAME,

   PASSWORD_UNENCRYPTED

);

Las contraseñas encriptadas en la tabla USERS_LOGIN se mostraran así:

ID USERNAME PASSWORD_UNENCRYPTED ENCRYPTED_PWD
1 user1 secretpwd1 0x02000000F7D70C6611D2654319B92E02D3F1C213B7005AC6EFDE7CC56D8291F0767D31E6678373DF30E114765489E2A2E805FBA7
2 user2 secretpwd2 0x02000000420846AC667EB6814D4AB7F4DD0957F45D58C8A994D74D5AB7709D9C2870007A48A7EDC928D5557A035A3A81EA327D6B

Las contraseñas encriptadas en la tabla USERS_LOGIN se mostraran así.

 

#3 Descifrar las contraseñas

La forma en que desencriptamos las contraseñas encriptadas en la tabla USER_LOGIN es usando la función DECRYPTBYPASSPHRASE. Debemos recordar que esta función también devuelve un valor en el mismo tipo de VARBINARY que la función de encriptación. Para devolver la contraseña al original, debe ser convertida a un tipo VARCHAR u otro tipo de cadena.
La siguiente sintaxis nos muestra cómo descifrar las contraseñas cifradas usando la sentencia SELECT:

SELECT *,

CONVERT( VARCHAR(255),

DECRYPTBYPASSPHRASE(

USERNAME,

ENCRYPTED_PWD

)

) AS  DECRYPTED_PWD

FROM USERS_LOGIN;

  1. SELECT *,
  2.  
  3. CONVERT( VARCHAR(255),
  4.  
  5. DECRYPTBYPASSPHRASE(
  6.  
  7. USERNAME,
  8.  
  9. ENCRYPTED_PWD
  10.  
  11. )
  12.  
  13. ) AS DECRYPTED_PWD
  14.  
  15. FROM USERS_LOGIN;
SELECT *,

CONVERT( VARCHAR(255),

DECRYPTBYPASSPHRASE(

USERNAME,

ENCRYPTED_PWD

)

) AS  DECRYPTED_PWD

FROM USERS_LOGIN;

El resultado es el siguiente:

ID USERNAME PASSWORD_UNENCRYPTED ENCRYPTED_PWD DECRYPTED_PWD
1 user1 secretpwd1 0x02000000F7D70C6611D2654319B92E02D3F1C213B7005AC6EFDE7CC56D8291F0767D31E6678373DF30E114765489E2A2E805FBA7 secretpwd1
2 user2 secretpwd2 0x02000000420846AC667EB6814D4AB7F4DD0957F45D58C8A994D74D5AB7709D9C2870007A48A7EDC928D5557A035A3A81EA327D6B secretpwd2

Tabla 3. La tabla USERS_LOGIN con contraseñas desencriptadas