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 ] );
- ENCRYPTBYPASSPHRASE (
- [ ‘passphase’ | @passphrase ] ,
- [ ‘textstring’ | @textstring ]
- );
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
La sintaxis es la siguiente:
DECRYPTBYPASSPHRASE ( [ 'passphase' | @passphrase ] , [ 'encryptedkey' | @encryptedkey ] );
- DECRYPTBYPASSPHRASE (
- [ ‘passphase’ | @passphrase ] ,
- [ ‘encryptedkey’ | @encryptedkey ]
- );
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'
- 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’
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 );
- UPDATE USERS_LOGIN
- SET ENCRYPTED_PWD = ENCRYPTBYPASSPHRASE(
- USERNAME,
- PASSWORD_UNENCRYPTED
- );
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;
- SELECT *,
- CONVERT( VARCHAR(255),
- DECRYPTBYPASSPHRASE(
- USERNAME,
- ENCRYPTED_PWD
- )
- ) AS DECRYPTED_PWD
- 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