DotNetcr.com
Si deseas hacer un intercambio de links con DotNetcr, escríbenos aquí
Recibe las actualizaciones vía RSS
Te invitamos a unirte en nuestras redes
   http://www.facebook.com/DotNetcr
   http://www.twitter.com/dotnetcr

Encriptar textos en SQL

Por h@nz | 9/17/2006 | Visitas 21,330 | Voto 4.00
El SQL tiene por defecto diversos encriptadores, también herramientas como .Net y otras más, pero las más seguras no tiene el modo de desencriptar, en este ejemplo vamos a fabricar un encriptador al cual podamos desencriptar.
Categorías : SQL Server
Mucho se ha escrito sobre este tema, tanto que ya no es necesario descubrir la rueda para encriptar sobretodo contraseñas (o cualquier otro dato), desde algoritmos sencillos (como sumar a cada valor ascii de cada caracter un valor cualquiera) hasta los mas complejos (como rijndael y otros) existen en todos lados a nuestra libre disposición.

El propio SQL (en ingles se pronuncia ES-KIU-EL y no SICUEL como dicen algunos) Server propone una forma de encriptar los datos, si usamos PwdEncrypt('Mi contraseña') obtendremos algo como esto:

0x0100A36C176B9D8F41B774C25... este dato de tipo varbynary no esta nada mal, el problema es que ni el propio SQL Server lo puede desencriptar (o al menos asi parece). Si usamos PwdCompare('Mi contraseña', 'el binario con el que comparar') lo único que conseguiremos será comparar ambas cadenas, cosa que tampoco esta mal...

Nunca les ha pasado que un usuario final se olvida su contraseña y llama al Dpto. de Sistemas (Se supone que nuestro Dpto.) pidiendo que les digan su clave????, ah! pero eso si! en sus requerimientos piden que sus claves sean encriptadas para que nadie sepan que son. Bueno, hace poco hice este pequeño
script que permite encriptar una cadena de hasta 20 caracteres en otra de 50 caracteres. A ver si se animan a copiar y pegar... la explicación esta al final.

CREATE PROCEDURE usp_Encriptar(@texto varchar(20), @clave varchar(50)
output)

with encryption

AS

-- Una cadena de texto cualquiera que sirve para cambiar el valor ingresado
declare @key varchar(50)

-- Contadores para las repeticiones
declare @i integer, @j integer

-- Apoyo para conversiones
declare @aux integer

-- Valor Ascii de cada caracter que se extrae del dato ingresado
declare @vAsc integer

-- Apoyo para extraer caracteres de la cadena
declare @posIni integer

-- Contendra el valor binario de cada caracter del texto enviado
declare @chrBin varchar(8)

-- Auxiliar para crear la cadena encriptada
declare @chrAux varchar(8)

-- Contendra la cadena binaria completa del texto enviado
declare @strBin varchar(320)

-- La cadena binaria pero invertida por cada caracter
declare @strInv varchar(320)

-- Para armar el byte de la cadena encriptada (1 caracter = 1 byte = 8 bits)
declare @ci1 char(8), @ci2 char(8)

set @key = 'ColoqueAquiSuPropiaClave'

--1. Convertir el texto enviado en una cadena de binarios
set @i = 1
set @vAsc = 0
set @strBin = ''

while @i <= len(ltrim(rtrim(@texto)))
begin
set @chrBin = ''
set @vAsc = ascii(substring(ltrim(rtrim(@texto)), @i, 1))

while @vAsc >= 2
begin
set @chrBin = ltrim(rtrim(str(@vAsc % 2))) + ltrim(rtrim(@chrBin))
set @vAsc = @vAsc / 2
end

set @chrBin = ltrim(rtrim(str(@vAsc))) + ltrim(rtrim(@chrBin))

while len(ltrim(rtrim(@chrBin))) < 8
set @chrBin = '0' + ltrim(rtrim(@chrBin))

set @strBin = ltrim(rtrim(@strBin)) + ltrim(rtrim(@chrBin))
set @i = @i + 1
end

--2. Invertir la cadena binaria por cada caracter
set @i = 1
set @posIni = 1
set @strInv = ''
set @strBin = reverse(ltrim(rtrim(@strBin)))

while @i <= len(ltrim(rtrim(@texto)))
begin
set @chrBin = ''
set @chrBin = substring(ltrim(rtrim(@strBin)), @posIni, 8)
set @chrBin = reverse(ltrim(rtrim(@chrBin)))
set @strInv = ltrim(rtrim(@strInv)) + ltrim(rtrim(@chrBin))

set @posIni = @posIni + 8
set @i = @i + 1
end

--3. Crear la cadena binaria del encriptado
set @i = 1
set @j = 1
set @posIni = 1
set @vAsc = 0
set @strBin = ''

while @i <= len(ltrim(rtrim(@texto)))
begin
set @chrBin = ''
set @chrBin = substring(ltrim(rtrim(@strInv)), @posIni, 8)

set @ci1 = substring(ltrim(rtrim(@chrBin)), 1, 4)
set @ci2 = substring(ltrim(rtrim(@chrBin)), 5, 4)

--Descomponer en ascii la llave (@key)
set @chrAux = ''
set @vAsc = ascii(substring(ltrim(rtrim(@key)), @j, 1))
while @vAsc >= 2
begin
set @chrAux = ltrim(rtrim(str(@vAsc % 2))) + ltrim(rtrim(@chrAux))
set @vAsc = @vAsc / 2
end

set @chrAux = ltrim(rtrim(str(@vAsc))) + ltrim(rtrim(@chrAux))

while len(ltrim(rtrim(@chrAux))) < 8
set @chrAux = '0' + ltrim(rtrim(@chrAux))
--Fin descomponer la llave

set @ci1 = ltrim(rtrim(@ci1)) + substring(ltrim(rtrim(@chrAux)), 1, 4)
set @ci2 = ltrim(rtrim(@ci2)) + substring(ltrim(rtrim(@chrAux)), 5, 4)

set @strBin = ltrim(rtrim(@strBin)) + ltrim(rtrim(@ci1)) + ltrim(rtrim(@ci2))

set @posIni = @posIni + 8
set @i = @i + 1
end

--4. Convertir la cadena binaria en una cadena de caracteres
set @i = 1
set @posIni = 1
set @chrBin = ''
set @strInv = ''
set @strInv = ltrim(rtrim(@strBin))
set @strBin = ''

while @i <= len(ltrim(rtrim(@strInv))) / 8
begin
set @j = 0
set @aux = 1
set @vAsc = 0
set @chrBin = substring(ltrim(rtrim(@strInv)), @posIni, 8)

while @j <= (len(ltrim(rtrim(@chrBin))) - 1)
begin
set @vAsc = @vAsc + (cast(substring(ltrim(rtrim(@chrBin)), len(ltrim(rtrim(@chrBin))) - @j, 1) as int) * @aux)

set @aux = @aux * 2
set @j = @j + 1
end

set @strBin = ltrim(rtrim(@strBin)) + ltrim(rtrim(char(@vAsc)))
set @posIni = @posIni + 8
set @i = @i + 1
end

--5. Generar la cadena de 40 caracteres
set @i = len(ltrim(rtrim(@strBin)))
set @strInv = ''

while @i < 40
begin
set @vAsc = 0
while (@vAsc < 33) or (@vAsc = 39)
set @vAsc = round(rand() * 255,0)

set @strInv = ltrim(rtrim(@strInv)) + ltrim(rtrim(char(@vAsc)))
set @i = @i + 1
end

set @strBin = ltrim(rtrim(@strInv)) + ltrim(rtrim(@strBin))

--6. Agregar los flags de longitud y completar los 50 caracteres
set @aux = len(ltrim(rtrim(@texto)))
if @aux < 10
begin
set @i = 0
set @j = @aux
end
else
begin
set @i = substring(ltrim(rtrim(@aux)), 1, 1)
set @j = substring(ltrim(rtrim(@aux)), 2, 1)
end

set @aux = 1
set @chrBin = ''
set @chrAux = ''

while @aux <= 5
begin
set @vAsc = 0
while (@vAsc < 33) or (@vAsc = 39)
set @vAsc = round(rand() * 255,0)

if @aux = 3
set @chrBin = ltrim(rtrim(@chrBin)) + ltrim(rtrim(str(@i)))
else
set @chrBin = ltrim(rtrim(@chrBin)) + ltrim(rtrim(char(@vAsc)))

set @vAsc = 0
while (@vAsc < 33) or (@vAsc = 39)
set @vAsc = round(rand() * 255,0)

if @aux = 3
set @chrAux = ltrim(rtrim(@chrAux)) + ltrim(rtrim(str(@j)))
else
set @chrAux = ltrim(rtrim(@chrAux)) + ltrim(rtrim(char(@vAsc)))

set @aux = @aux + 1
end

--7. Devolver el resultado
set @clave = ltrim(rtrim(@chrBin)) + ltrim(rtrim(@strBin)) +
ltrim(rtrim(@chrAux))

return 0

go


Explicación:
Si por ejemplo escribo Hancito, el resultado será una cadena mas o menos como esta:

Œê0ÉâE9¶ÀÉœ_bŒŸRÓuʏtžôû‡uÊAœšdótCd“d3dãdDƒy/7*a

Bien, esa cadena tiene caracteres agregados por las puras... el encriptado original consta del doble de caracteres del texto enviado, osea 14 caracteres en nuestro ejemplo, los otros 36 estan por las puras, solo para despistar al enemigo. El truco esta aquí:

Œê 0 ÉâE9¶ÀÉœ_bŒŸRÓuʏtžôû‡uÊAœš dótCd“d3dãdDƒ y/ 7 *a

El tercer y 48vo. caracter contiene la informacion de la longitud original del texto (Hancito tiene 07 caracteres) y solo la parte que esta en negrita (dótCd“d3dãdDƒ) tiene el encriptado. Si alguien intenta desencriptarlo a la fuerza, empezará por delante y nunca descubrirá el texto (bueno, eso de nunca es un decir), esa cadena delantera es aleatoria.

La instruccion with encryption permite encriptar todo el Stored Procedure en SQL, eso significa que ninguna persona lo podrá leer, ni siquiera quien lo creo, es decir que si pierden las fuentes, ya fueron. No se olviden de cambiar 'ColoqueAquiSuPropiaClave' por una clave de hasta 25 caracteres cualquiera, respetando las reglas para la creación de passwords que todos conocemos (Despues escribiré sobre eso por si acaso alguien no sabe)

Bueno, la discusión sobre este tema es larga, no pretendo que sea el mejor script para encriptar textos, pero me parece una buena alternativa. El script para desencriptar lo colocaré aparte porque juntos ocuparían mas espacio (por cierto ya ocupe bastante).
Area de Comentarios
Por Anónimo - Fecha: 2006/10/16 01:29 PM
Hola me gustaria saber .. como puedo ver el resultado de tu script
Por Anónimo - Fecha: 2006/10/16 01:30 PM
Hola Me gustaria saber como mostar el resultado de tu script porfavor si puedes darme la respuesta al correo roco_666_428@hotmail.com porfavor ..
Por h@nz - Fecha: 2006/10/19 06:39 PM
Hola, para ver el resultado del script en el Analizador de consultas pueden utilizar la siguiente instrucción:

declare @resultado varchar(50)
exec usp_Encriptar ''miPalabraSecreta'', @resultado output
print @resultado

miPalabraSecreta debe ser un texto de no mas de 20 caracteres.

Saludos....
Por h@nz - Fecha: 2006/10/19 06:41 PM
bueno, debe ser solo con una comilla simple, por error me salieron dos en el ejemplo... intentenlo...

nos vemos...
Por Anónimo - Fecha: 2006/11/03 10:44 AM
Excelente trabajo, estaba viendo unos ejemplos de diferentes algoritmos en .Net y esto es lo que buscaba, Danko Sher.
Por Anónimo - Fecha: 2006/11/15 01:08 PM
Y donde se desencripta?
Por Anónimo - Fecha: 2006/11/15 01:18 PM
Se hablo desde el principio de un algoritmo con la propiedad para encriptar y desencriptar, por favor como se haria para desencriptar?

Por h@nz - Fecha: 2006/11/16 08:37 AM
La parte de la desencriptación se encuentra en un artículo aparte, si lo hubiera puesto en uno solo sería muy largo... ahi les va el link para el desencriptado:

http://www.dotnetcr.com/Libreria.aspx?art=102&tag=Encriptar-textos-en-SQL-Segunda-Parte

Saludos...
Por Anónimo - Fecha: 2006/11/16 10:46 AM
Un millon de gracias, este fue un buen trabajo.
Dios los recompensara.

Saludos
Por Anónimo - Fecha: 2006/11/27 08:42 AM
Me parecio bastante interesante tu articulo
Por Anónimo - Fecha: 2007/03/25 11:33 AM
un muy buena explicacion tu si que sabes!!!
Por Anónimo - Fecha: 2007/08/15 12:44 PM
Buen algoritmo... Una pregunta.. Estoy utilizando Visual Basic 6.0.. Esta funcion la hago en vusual basic 6.0 o en SQL.. Y si la hago en SQL Como la utilizo para almacenar la clave desencriptada????
Por Anónimo - Fecha: 2008/03/24 03:38 PM
si eso esta muy bien
Por Anónimo - Fecha: 2010/06/25 10:55 AM
pueden volver a postear la segunda parte de este tema??
Por Anónimo - Fecha: 2010/07/07 12:51 AM
sta chvr tu algoritmo...pero...el script para desencriptar¿?¿?..pueds ponerlo o poner el link d dond sta¿?¿?
Por Anónimo - Fecha: 2012/04/26 05:20 PM
VARCHAR(64))ASBEGIN DECLARE @sql VARCHAR(MAX) DECLARE @TableName VARCHAR(64) DECLARE @ColumnName VARCHAR(64) CREATE TABLE #Results ( TableName VARCHAR(64) , ColumnName VARCHAR(64) ) DECLARE TABLES CURSOR FOR SELECT o.name , c.name FROM syscolumns c INNER JOIN scysbjeots o ON c.id = o.id WHERE o.type = U' AND c.xtype IN (167, 175, 231, 239) ORDER BY o.name , c.name OPEN TABLES FETCH NEXT FROM TABLES INTO @TableName , @ColumnName WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = IF EXISTS(SELECT NULL FROM [' + @TableName + '] SET @sql = @sql + WHERE RTRIM(LTRIM([' + @ColumnName + '])) LIKE %' + @value + % ) SET @sql = @sql + INSERT INTO #Results ( TableName, ColumnName ) VALUES ( ' + @TableName + ', ' SET @sql = @sql + @ColumnName + ')' EXEC(@sql) FETCH NEXT FROM TABLES INTO @TableName , @ColumnName END CLOSE TABLES DEALLOCATE TABLES SELECT * FROM #Results DROP TABLE #ResultsEND
Por Anónimo - Fecha: 2012/04/27 08:36 AM
D1H0YJ , [url=http://mjaagkvftjnr.com/]mjaagkvftjnr[/url], [link=http://cdbiitjvtynl.com/]cdbiitjvtynl[/link], http://dwgnusesdtmk.com/
Por Anónimo - Fecha: 2012/04/29 12:44 AM
XFfl03 , [url=http://iedpujcnrphl.com/]iedpujcnrphl[/url], [link=http://kttnouovrjfk.com/]kttnouovrjfk[/link], http://ogynhuhfxkrb.com/
Por Anónimo - Fecha: 2015/07/18 03:06 AM
Hi Tom,I had try the method and I have two qteiusons about the topic:1. about Execution Plan.if the list contain 1 item ex: 'SYS' and contain many items ,maybe more than 1000,ex:'SYS,SYSTEM,...etc'. But I got the same execute plan ,then I had not good performance for the query. How to let it know that my condition has how many items and get the suitable plan?2. When I use dynamic sql statement, my list length > 4000 bytes , use "USING IN" statement will cause ORA-01461 exception alert. EX: declare l_sql varchar2(10000); l_list varchar2(10000);begin -- length(l_list) > 4000 bytes l_list := 'a,b,c.....etc'; l_sql := ' select * from employees where department_id in ( select trim(trim ('''''''' from substr (txt, instr (txt, '','', 1, level ) + 1, instr (txt, '','', 1, level+1) - instr (txt, '','', 1, level) -1 ) )) as token from (select '',''||:x || '','' txt from dual ) connect by level no rows????? Guessing the extra apostrophes are to blame. I'm going to try and take them out but I don't really understand the solution!!!
Por Anónimo - Fecha: 2015/07/21 01:27 PM
Freemansimple answer: you caonnt, you get the top level unit (package name) and line number of that unit.longer answer: we don't store what function or procedure called you anywhere (and remember with overloading there could be 15 procedures with the same name - the name is not important for LOGGING, the line number is)from the line number YOU can deduce what the name of the procedure is by a quick query on user_source (do it during ANALYSIS of the log, not during logging itself, would take way too long). You would have to read backwards from that line "parsing" the text until you found the procedure or function header (if one exists, nothing to say that a procedure or function in the package was even the a caller - code in a package can run without being in a function/procedure at all) http://yoiqavexoa.com [url=http://egygwfcee.com]egygwfcee[/url] [link=http://amwjrzimlds.com]amwjrzimlds[/link]
Por Anónimo - Fecha: 2015/12/17 01:47 PM
...for I cannot imgaine a human being picking hundreds or thousands of elements from a list - hence I question your "logic" here...Me I can as I have to ;-)Here, they even had to overcome the limit of 1000 elements in an in-list. Of course the performance is way too slow, the CPU consumption is high, a lot of "similar" statements in the shared pool ...We will now go for temporary tables as they still insist, that they need to load all the values when starting up the application (the user might want to scroll down later on). Can I say "Java-developers" here? Yes, this is the people thinking they can do sorting and the like much faster than anybody else ... /rant
Por Anónimo - Fecha: 2015/12/19 02:46 AM
...You would like to retrieve all rows from some table/query such that some comlun is in that string.===There's actually something related to this that I've been wondering about on and off lately that the statement quoted above reminded me of. Is there a way to find out what TABLE_NAME AND COLUMN_NAME a particular value is found in if I know the exact value, but have no idea what table or comlun might contain this information, or if I don't know what they are called? I asked our DBAs and they don't know, and someone that uses SQL Server commented that one could query in the master database. http://reuumea.com [url=http://ptwqbhbajpw.com]ptwqbhbajpw[/url] [link=http://feoxfjesqtd.com]feoxfjesqtd[/link]
Por Anónimo - Fecha: 2015/12/20 02:20 PM
The "in" list is just a relation (temporary or owtsrhiee) which is joined with the rest of the query. Treating "in" list as a relation simplifies a lot. How to optimize joins, for example is well known, versus for "in" list predicate you have to consider all sort of transformations like rewriting the predicate x in (1,2) into x = 1 or x = 2Perhaps it's a good idea to deprecate "in" lists in SQL altogether? http://wjhriqxnf.com [url=http://rdtexr.com]rdtexr[/url] [link=http://iekudqkvfh.com]iekudqkvfh[/link]
Ingrese su Comentario
Comentario
Para poder votar debe estar registrado en DotNetcr.com
Solo queda registrado el primer voto enviado
Voto


Últimos Recursos
ricardo leppe t
pedrojavier
CALIN
willipinru
richard
ragomez
PER 238
MEX 236
CRI 188
COL 118
ESP 105
ARG 88