[SQL] – Encriptar y Desencriptar una cadena en SQL

 

Buen día a todos!. En esta oportunidad se les mostrará el método para encriptar y desencriptar un cadena en SQL con los métodos ENCRYPTBYPASSPHRASE y DECRYPTBYPASSPHRASE. Estos tienen la utilidad de poder encriptar por ejemplo el password de un usuario en nuestra base de datos de manera que al hacerle select * from no se mostrará el password. Para comprobarlo por ustedes mismo pasamos a detallarlo :

• Creamos una tabla llamada Customer :

   1: CREATE TABLE CUSTOMER(

   2: ID           INT IDENTITY,

   3: NOMBRE       NVARCHAR(50),

   4: APELLIDO     NVARCHAR(50),

   5: NICKNAME     NVARCHAR(50),

   6: PWD          NVARCHAR(MAX),

   7: PRIMARY KEY(ID))

• En este paso pueden crear un Store Procedure o una simple insercion, pero lo haré mediante un Store Procedure. Y aquí se asignará el método de SQL llamado ENCRYPTBYPASSPHRASE al campo PWD, y verán que coloco una cadena entre comillas simples, eso es una clave que servirá para encriptar, eso lo pueden modificar sin problemas y colocar lo que gusten, pero OJO esa clave si le asignamos al insertar… también deberá ser la misma al momento de desencriptar.

   1: CREATE PROCEDURE SP_SAVE_CUSTOMER

   2: @NOMBRE NVARCHAR(50),

   3: @APELLIDO NVARCHAR(50),

   4: @NICKNAME NVARCHAR(50),

   5: @PWD    NVARCHAR(MAX)

   6: AS

   7: INSERT INTO CUSTOMER(NOMBRE,APELLIDO,NICKNAME,PWD) VALUES (@NOMBRE,@APELLIDO,@NICKNAME,ENCRYPTBYPASSPHRASE('PWD_CUSTOMER',@PWD))

• Insertamos un dato de prueba mediante el Store Procedure :

   1: SP_SAVE_CUSTOMER 'Ejemplo de Encrypt y Decrypt','En SQL','ejemplosdotnet.blogspot.com','aprendiendo'

• Hacemos la consulta a la base de datos para comprobar con un simple consulta de selección :

   1: SELECT*FROM CUSTOMER 

• Y Obtenemos el siguiente resultado :

vemos que en el campo PWD aparece un [], eso significa que esta compuesto por un array de bytes, lo cual nos impide ver el password. Para poder verificar los datos por ejemplo en caso de un logueo necesitamos verificar que el usuario y el password que se ingresa deben ser iguales. Para eso usaremos el método DECRYPTBYPASSPHRASE.

• Creamos un Store Procedure que muestre el password, aquí usaremos el  DECRYPTBYPASSPHRASE :

   1: CREATE PROCEDURE SP_SHOW_CUSTOMER 

   2: @NICKNAME    NVARCHAR(50),

   3: @PWD        NVARCHAR(MAX)

   4: AS

   5: SELECT ID,NOMBRE,APELLIDO,NICKNAME,CONVERT(NVARCHAR(MAX),DECRYPTBYPASSPHRASE('PWD_CUSTOMER',PWD)) AS PWD FROM CUSTOMER

   6: WHERE NICKNAME = @NICKNAME AND CONVERT(NVARCHAR(MAX),DECRYPTBYPASSPHRASE('PWD_CUSTOMER',PWD)) = @PWD

• Ejecutamos el Store Procedure :

   1: SP_SHOW_CUSTOMER 'ejemplosdotnet.blogspot.com','aprendiendo'

• Y obtendremos el siguiente resultado :

Espero les haya sido de mucha utilidad. Saludos!.

[Tips] - Formatos de Datetime a String()

 

Buen día! Para obtener una Fecha en Cadena solíamos hacer Datetime.Now.ToString() que botaba la fecha según el tipo de Cultura que se define por la Globalización, pero que pasaba si necesitabamos darle otro tipo de formato por ejemplo día/mes/año o mes/dia/año? eh aquí unas muestras de los tipos de formatos que podemos obtener en base al formateo por medio del ToString() :

Formato	Resultado
MM/dd/yyyy	08/22/2006
dddd, dd MMMM yyyy	Tuesday, 22 August 2006
dddd, dd MMMM yyyy HH:mm	Tuesday, 22 August 2006 06:30
dddd, dd MMMM yyyy hh:mm tt	Tuesday, 22 August 2006 06:30 AM
dddd, dd MMMM yyyy H:mm	Tuesday, 22 August 2006 6:30
dddd, dd MMMM yyyy h:mm tt	Tuesday, 22 August 2006 6:30 AM
dddd, dd MMMM yyyy HH:mm:ss	Tuesday, 22 August 2006 06:30:07
MM/dd/yyyy HH:mm	08/22/2006 06:30
MM/dd/yyyy hh:mm tt	08/22/2006 06:30 AM
MM/dd/yyyy H:mm	08/22/2006 6:30
MM/dd/yyyy h:mm tt	08/22/2006 6:30 AM
MM/dd/yyyy HH:mm:ss	08/22/2006 06:30:07
MMMM dd	August 22
yyyy'-'MM'-'dd'T'HH':'mm':'ss.fffffffK	2006-08-22T06:30:07.7199222-04:00
ddd, dd MMM yyyy HH':'mm':'ss 'GMT'	Tue, 22 Aug 2006 06:30:07 GMT
yyyy'-'MM'-'dd'T'HH':'mm':'ss	2006-08-22T06:30:07
HH:mm	06:30
hh:mm tt	06:30 AM
H:mm	6:30
h:mm tt	6:30 AM
HH:mm:ss	06:30:07
yyyy'-'MM'-'dd HH':'mm':'ss'Z'	2006-08-22 06:30:07Z
dddd, dd MMMM yyyy HH:mm:ss	Tuesday, 22 August 2006 06:30:07
yyyy MMMM	2006 August

DESCRIPCION  :

Se muestra en detalle los conceptos de cada variable :

d	Represents the day of the month as a number from 1 through 31. A single-digit day is formatted without a leading zero
dd	Represents the day of the month as a number from 01 through 31. A single-digit day is formatted with a leading zero
ddd	Represents the abbreviated name of the day of the week (Mon, Tues, Wed etc)
dddd	Represents the full name of the day of the week (Monday, Tuesday etc)
h	12-hour clock hour (e.g. 7)
hh	12-hour clock, with a leading 0 (e.g. 07)
H	24-hour clock hour (e.g. 19)
HH	24-hour clock hour, with a leading 0 (e.g. 19)
m	Minutes
mm	Minutes with a leading zero
M	Month number
MM	Month number with leading zero
MMM	Abbreviated Month Name (e.g. Dec)
MMMM	Full month name (e.g. December)
s	Seconds
ss	Seconds with leading zero
t	Abbreviated AM / PM (e.g. A or P)
tt	AM / PM (e.g. AM or PM)
y	Year, no leading zero (e.g. 2001 would be 1)
yy	Year, leadin zero (e.g. 2001 would be 01)
yyy	Year, (e.g. 2001 would be 2001)
yyyy	Year, (e.g. 2001 would be 2001)
K	Represents the time zone information of a date and time value (e.g. +05:00)
z	With DateTime values, represents the signed offset of the local operating system's time zone from Coordinated Universal Time (UTC), measured in hours. (e.g. +6)
zz	As z but with leadin zero (e.g. +06)
zzz	With DateTime values, represents the signed offset of the local operating system's time zone from UTC, measured in hours and minutes. (e.g. +06:00)
f	Represents the most significant digit of the seconds fraction; that is, it represents the tenths of a second in a date and time value.
ff	Represents the two most significant digits of the seconds fraction; that is, it represents the hundredths of a second in a date and time value.
fff	Represents the three most significant digits of the seconds fraction; that is, it represents the milliseconds in a date and time value.
ffff	Represents the four most significant digits of the seconds fraction; that is, it represents the ten thousandths of a second in a date and time value. While it is possible to display the ten thousandths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock's resolution is approximately 10-15 milliseconds.
fffff	Represents the five most significant digits of the seconds fraction; that is, it represents the hundred thousandths of a second in a date and time value. While it is possible to display the hundred thousandths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock's resolution is approximately 10-15 milliseconds.
ffffff	Represents the six most significant digits of the seconds fraction; that is, it represents the millionths of a second in a date and time value. While it is possible to display the millionths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock's resolution is approximately 10-15 milliseconds.
fffffff	Represents the seven most significant digits of the seconds fraction; that is, it represents the ten millionths of a second in a date and time value. While it is possible to display the ten millionths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock's resolution is approximately 10-15 milliseconds.
F	Represents the most significant digit of the seconds fraction; that is, it represents the tenths of a second in a date and time value. Nothing is displayed if the digit is zero.
:	Represents the time separator defined in the current DateTimeFormatInfo..::.TimeSeparator property. This separator is used to differentiate hours, minutes, and seconds.
/	Represents the date separator defined in the current DateTimeFormatInfo..::.DateSeparator property. This separator is used to differentiate years, months, and days.
"	Represents a quoted string (quotation mark). Displays the literal value of any string between two quotation marks ("). Your application should precede each quotation mark with an escape character (\).
'	Represents a quoted string (apostrophe). Displays the literal value of any string between two apostrophe (') characters.
%c	Represents the result associated with a c custom format specifier, when the custom date and time format string consists solely of that custom format specifier. That is, to use the d, f, F, h, m, s, t, y, z, H, or M custom format specifier by itself, the application should specify %d, %f, %F, %h, %m, %s, %t, %y, %z, %H, or %M. For more information about using a single format specifier, see Using Single Custom Format Specifiers.