[SQL] Previniendo la Inyección de código.

Saludos amigos, encontré un ejemplo de una función escalar “Scalar User-Defined Functions”, por @Joseph Sack, la cual verifica si una instrucción TSQL esta intentando ejecutar código malintencionado que puede afectar de manera catastrófica la información y estructura de nuestras bases de datos. Como muchos de ustedes sabrán al tener sentencias TSQL no parametrizadas corremos un enorme riesgo de sufrir ataques de inyección, el script que a continuación les comparto verifica que la sentencia a ejecutar no contenga código que intente alterar los datos o la estructura de los mismos.

1. Creamos una tabla llamada DEMO_ROLES, a la cual insertaremos datos a través de Sentencias dinámicas SQL.

Creando la tabla:

USE prueba;
GO
 
CREATE TABLE dbo.DEMO_ROLES(
    id_rol bigint IDENTITY(1,1) NOT NULL,
    rol varchar(50) NOT NULL,
    CONSTRAINT PK_DEMO_ROLES PRIMARY KEY CLUSTERED 
    ([id_rol] ASC)
);
GO

Insertando datos:

INSERT INTO dbo.DEMO_ROLES 
VALUES ('Administrador'),
       ('Gerente'),
       ('Supervisor'),
       ('Desarrollador');

Seleccionando datos con “sp_executesql”.

DECLARE @SQLtext nvarchar(350);
SET @SQLtext = N'SELECT * FROM dbo.DEMO_ROLES';
EXEC sp_executesql @SQLtext;
GO

Resultado:

2. Simulando la Inyección de código. Para este paso simularemos que la cadena de código que se va a ejecutar fue modificada malintencionadamente y le concatenaron (Inyectaron) una sentencia que borra la tabla DEMO_ROLES.

Ejecutando la sentencia:

DECLARE @SQLtext nvarchar(350);
SET @SQLtext = N'SELECT * FROM dbo.DEMO_ROLES' + ';DROP TABLE dbo.DEMO_ROLES';
EXEC sp_executesql @SQLtext;
GO

Nota: Por el orden en el que se ejecutan las sentencias, primero se seleccionan los datos y después se elimina la tabla, por lo tanto hasta la próxima ejecución de las sentencias se producirá una excepción donde el SQL nos indicara que la tabla DEMO_ROLES no existe, esto a cause de que fue eliminada por la simulación de la inyección de código.

Resultado 1 (Primera ejecución con inyección simulada):

Resultado 2 (Ejecuciones posteriores a la inyección simulada):

Ahora como lo resolvemos:

1. Para esto tenemos que crear la tabla DEMO_ROLES, e insertar los datos (vea la parte inicial del Post).

2. Crear una función escalar la cual se encargara de verificar si las sentencias a ejecutar son maliciosas y contienen código que pueda alterar nuestra base de datos.

USE prueba;
GO

CREATE FUNCTION [dbo].[udf_CheckForSQLInjection]
    (@TSQLString varchar(max))
RETURNS BIT
AS
BEGIN
DECLARE @IsSuspect bit
-- UDF assumes string will be left padded with a single space
SET @TSQLString = ' ' + @TSQLString
    IF (PATINDEX('% xp_%' , @TSQLString ) <> 0 OR
        PATINDEX('% sp_%' , @TSQLString ) <> 0 OR
        PATINDEX('% DROP %' , @TSQLString ) <> 0 OR
        PATINDEX('% GO %' , @TSQLString ) <> 0 OR
        PATINDEX('% INSERT %' , @TSQLString ) <> 0 OR
        PATINDEX('% UPDATE %' , @TSQLString ) <> 0 OR
        PATINDEX('% DBCC %' , @TSQLString ) <> 0 OR
        PATINDEX('% SHUTDOWN %' , @TSQLString )<> 0 OR
        PATINDEX('% ALTER %' , @TSQLString )<> 0 OR
        PATINDEX('% CREATE %' , @TSQLString ) <> 0OR
        PATINDEX('%;%' , @TSQLString )<> 0 OR
        PATINDEX('% EXECUTE %' , @TSQLString )<> 0 OR
        PATINDEX('% BREAK %' , @TSQLString )<> 0 OR
        PATINDEX('% BEGIN %' , @TSQLString )<> 0 OR
        PATINDEX('% CHECKPOINT %' , @TSQLString )<> 0 OR
        PATINDEX('% BREAK %' , @TSQLString )<> 0 OR
        PATINDEX('% COMMIT %' , @TSQLString )<> 0 OR
        PATINDEX('% TRANSACTION %' , @TSQLString )<> 0 OR
        PATINDEX('% CURSOR %' , @TSQLString )<> 0 OR
        PATINDEX('% GRANT %' , @TSQLString )<> 0 OR
        PATINDEX('% DENY %' , @TSQLString )<> 0 OR
        PATINDEX('% ESCAPE %' , @TSQLString )<> 0 OR
        PATINDEX('% WHILE %' , @TSQLString )<> 0 OR
        PATINDEX('% OPENDATASOURCE %' , @TSQLString )<> 0 OR
        PATINDEX('% OPENQUERY %' , @TSQLString )<> 0 OR
        PATINDEX('% OPENROWSET %' , @TSQLString )<> 0 OR
        PATINDEX('% EXEC %' , @TSQLString )<> 0)
    BEGIN
        SELECT @IsSuspect = 1
    END
    ELSE
    BEGIN
        SELECT @IsSuspect = 0
    END

RETURN (@IsSuspect)
END;
GO

3. Ejecutando las sentencias TSQL con la ayuda de la función para verificar la inyección de código.

DECLARE @SQLtext nvarchar(350);
SET @SQLtext = N'SELECT * FROM dbo.DEMO_ROLES' + ';DROP TABLE dbo.DEMO_ROLES';

IF dbo.udf_CheckForSQLInjection(@SQLtext) = 1
BEGIN
    PRINT ('Advertencia: El código es sospecho de contener código malicioso (TSQL Inyection).')
END
ELSE
BEGIN
    EXEC sp_executesql @SQLtext;
END

Resultado 1 con inyección de código:

Resultado 2 sin inyección de código:

Pueden hacer la modificaciones que crean necesarias a la función de inyección de código, ya que el funcionamiento y validación depende de las necesidades que deseemos cubrir.

Bueno amigos me despido y con gusto espero comentarios, por favor apóyenme votando por mi blog en http://blogit.ms en la categoría de SQL Server.

Etiquetas de Technorati: TSQL Injection,Inyección de código SQL,SQL

[SQL] Cifrando un Procedimiento almacenado en MS SQL Server 2008

Saludos amigos, he preparado un breve ejemplo de como Cifrar “Encriptar” un procedimiento almacenado desde MS SQL Server 2008.

Para empezar crearemos un procedimiento almacenado básico, este procedimiento ejecutara una consulta a una tabla llamada “DEMO_USUARIOS” de la base de datos “PRUEBA”.

1.Insertando los datos:

USE prueba;
GO

INSERT INTO DEMO_USUARIOS VALUES
('Ivan','Ragel','ivan.rangel.c@gmail.com','trew',GETDATE()),
('Claudia','Pérez','clau@gmail.com','456789',GETDATE()),
('Mariana','Ragel','mariana@gmail.com','123456',GETDATE()),
('Noriga','Rojas','negro@gmail.com','963852',GETDATE()),
('Pedro','Infante','infante@gmail.com','789456',GETDATE());
GO

SELECT * FROM DEMO_USUARIOS;
GO

Resultado:

2. Transformando la consulta a Procedimiento Almacenado básico:

--Creando el Procediemiento almacenado.
CREATE PROCEDURE dbo.ObteniendoUsuariosDemo
AS
SELECT * FROM DEMO_USUARIOS ORDER BY nombre,apellidos
GO
--Ejecutando el Procedimiento almacenado.
EXEC dbo.ObteniendoUsuariosDemo;
GO
--Mostrando la definición del Procedimiento almacenado.
EXEC sp_helptext ObteniendoUsuariosDemo;
GO

En el batch de T-SQL anterior podemos observar que:

a. Estamos creando un procedimiento almacenado.

b. Ejecutando el procedimiento almacenado.

c. Mostrando la definición del procedimiento almacenado.

 

Ahora vamos a CIFRAR (Encriptar) el procedimiento almacenado aplicando un ALTER PROCEDURE en combinación con la clausula WITH ENCRYPTION como se muestra en el siguiente ejemplo:

ALTER PROCEDURE dbo.ObteniendoUsuariosDemo
WITH ENCRYPTION
AS
SELECT * FROM DEMO_USUARIOS ORDER BY nombre,apellidos
GO

d. Modificando el procedimiento almacenado para el cifrado de seguridad.

 

Es importante mencionar que al aplicar “WITH ENCRYPTION” no podrá tener acceso a la definición del procedimiento almacenado, ni podrá modificar al código del mismo, así que cuando aplique el cifrado debe de estar completamente seguro de tener guardado el código con el que creo dicho procedimiento almacenado.

e. Ejecutando el procedimiento almacenado CIFRADO (funciona de manera similar al creado de manera no cifrada).

f. Consultando la definición del procedimiento almacenado cifrado.

g. Tratando de modificar el procedimiento cifrado.

Bueno, espero les sea de ayuda, espero sus comentarios, dudas o sugerencias, por favor apóyenme botando por mi blog en la categoría de SQL Server en www.blogit.ms

Etiquetas de Technorati: WITH ENCRYPTION,CIFRADO DE SP,SQL,Procedimiento almacenados

[SQL] Como planear un Procedimiento almacenado en SQL Server 2008 (Creación y ejecución).

Saludos amigos, no se si sea realmente el nombre correcto para este post, le idea es que a partir de una sentencia SQL (Select, Insert, Update, Delete, Etc) puedan lograr parametrizar la consulta  y posteriormente elaborar su procedimiento almacenado.

Paso 1. Sentencia SQL a ejecutar.

USE prueba;
GO

INSERT INTO dbo.DEMO_PRODUCTO (nombre, precio, descripcion, fecha)
VALUES('Whisky', 275.50,'12 Años de añejamiento, promoción 2x1',GETDATE());
GO

SELECT TOP 1 * FROM dbo.DEMO_PRODUCTO ORDER BY id DESC;
GO

Nota: deben de asegurarse que la sentencia SQL que ejecuten se realice correctamente.

Paso 2. Parametrizar la Sentencia SQL.

Por cada uno de los campos que se requieren al insertar (para el caso de este ejemplo), deben de convertirse en variables T-SQL con el tipo de dato correspondiente, asignarles el valor del campo a Insertar y sustituirlos en la sentencia SQL.

USE prueba;
GO

DECLARE @producto varchar(100), 
        @precio smallmoney, 
        @descripcion varchar(750);
        
SET @producto = 'Agua mineral';
SET @precio = 12.50;
SET @descripcion = 'Agua mineral de 2 litros';

INSERT INTO dbo.DEMO_PRODUCTO (nombre, precio, descripcion, fecha)
VALUES(@producto, @precio,@descripcion,GETDATE());
GO

SELECT TOP 2 * FROM dbo.DEMO_PRODUCTO ORDER BY id DESC;
GO    

Paso 3. Convertir el Bacth T-SQL a Procedimiento almacenado.

Ya que tenemos la sentencia parametrizada, el paso final es convertirla en procedimiento almacenado, para esto tenemos que agregar la estructura de un “STORE PROCEDURE – SP”  de SQL Server 2008 como lo muestra el siguiente ejemplo:

CREATE PROCEDURE <Procedure_Name, sysname, Procedure_Name>
    <@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>, 
    <@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0>
AS
    <Instrucciones T-SQL (Insert, Select, Update, Delete, ETC)>
GO

La primera parte es asignarle un nombre al SP.

La Segunda parte es crear los parámetros que recibirá el SP.

Como tercera parte es crear el Batch de T-SQL que se ejecutara al llamar a nuestro SP.

Ejemplo:

 

El código:

CREATE PROCEDURE InsertaProductoSP
    @producto varchar(100), 
    @precio smallmoney, 
    @descripcion varchar(750)
AS
    INSERT INTO dbo.DEMO_PRODUCTO (nombre, precio, descripcion, fecha)
    VALUES(@producto, @precio,@descripcion,GETDATE());
GO

La ejecución:

USE prueba;
GO

EXEC InsertaProductoSP 'Hielos', 12.90, 'Bolsa de hielos de 3 kilos';
GO

SELECT TOP 3 * FROM dbo.DEMO_PRODUCTO ORDER BY id DESC;
GO

Resultado:

Espero les sea de ayuda, para algunos usuarios el ejemplo les puede parecer sumamente sencillo (y lo es), pero también creo que a los usuarios que inician los ubicara un poco más de como desarrollar en Transact SQL 2008, espero sus comentarios.

Etiquetas de Technorati: Store Procedure,SQL 2008,Crear Procedieminto almacenado,SQL,EXEC SP

[SQL-Select] Porque no utilizar SELECT *.

Saludos amigos leyendo un libro de SQL 2008, me encontré con un párrafo muy interesante que creo les será de ayuda a las personas que empiezan con el desarrollo en TSQL, considero que no importa si es SQL 2000, 2005 o 2008.

El símbolo asterisco (*) devuelve todas las columnas para cada fila de tabla o vista en la que se consulta.

Recordemos que, como buena práctica, es mejor hacer referencia explícita a las columnas que deseemos recuperar en lugar de utilizar “SELECT *”.

Referencia explícita.

SELECT Id, Nombre, Apellidos FROM PRB_Usuario

Si desarrolla una aplicación que usa “SELECT *”, la aplicación pueden esperar las mismas columnas (en el mismo orden) de la consulta. Si más adelante se agrega una nueva columna a la tabla o vista, o si se reordenan las columnas de la tabla, se puede romper el llamando a la aplicación, ya que la nueva columna puede provocar un resultado inesperado.

Usar “SELECT *” también puede repercutir negativamente en:

1. El rendimiento, ya que la consulta puede devolver más datos de los que se necesitan.
2. El aumento del tamaño del conjunto de resultados y operaciones de recuperación de datos en la instancia de SQL Server.
3. Para aplicaciones que requieren miles de transacciones por segundo, el número de columnas devueltas en el resultado puede tener un fuerte impacto.

En resumen use mejor: SELECT campo1, campo2, campo3 FROM TUTABLA

Evite usar: SELECT * FROM TUTABLA.

Espero les sea de ayuda, dejen comentarios.

Etiquetas de Technorati: SQL,Select *

 

[SQL-LOG] – Reducir el LOG de una Base de Datos.

Saludos amigos después de un tiempo de no postear nada, les dejo un script que reduce el log de una base de datos.

Supongamos que el siguiente archivo LOG pesa demasiado y lo queremos reducir.

Como primer paso y previniendo cualquier error que se pueda generar durante la ejecución de la reducción del LOG tendremos que hacer un respaldo de la BD.

 

USE master
GO

BACKUP DATABASE prueba
TO DISK = N'C:\RespaldosBD\Prueba_Full.BAK'
GO

BEGIN TRY

    DECLARE @backupSetId AS INT
    
    SELECT @backupSetId = position
    FROM msdb..backupset
    WHERE database_name=N'Prueba'
       AND backup_set_id=(SELECT MAX(backup_set_id)
                          FROM msdb..backupset
                          WHERE database_name=N'Prueba' )

    IF @backupSetId IS NULL
    BEGIN
       RAISERROR(N'Fallo la verificación. La información de reapaldo para la base de datos, no se encontro.', 16, 1)
    END    
    
    RESTORE VERIFYONLY
    FROM  DISK = N'C:\RespaldosBD\Prueba_Full.BAK'
    WITH  FILE = @backupSetId    

    PRINT 'La verificación se realizo correctamente, el respaldo de la base de datos esta listo para utilizarse.'

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
    PRINT ERROR_LINE()
    PRINT ERROR_NUMBER()
END CATCH
GO

Si todo salió bien tendremos nuestro respaldo en la ubicación indicada en el script anterior.

 

Ahora, respaldamos el archivo de LOG y le aplicamos un SHRINKFILE.

El SHRINKFILE Reduce el tamaño del archivo de datos o de registro para la base de datos actual, o vacía un archivo moviendo los datos del archivo especificado a otros archivos del mismo grupo de archivos, permitiendo quitar el archivo de la base de datos. Puede reducir un archivo a un tamaño menor que el tamaño especificado cuando se creó. Así se restablece el tamaño mínimo de archivo al valor nuevo.

@http://msdn.microsoft.com/es-es/library/ms189493.aspx

El CHECKPOINT Escribe en disco todas las páginas desfasadas de la base de datos actual. Las páginas desfasadas son páginas de datos que se han incluido en el caché la memoria y se han modificado, pero todavía no se han guardado en el disco. Los puntos de comprobación permiten ahorrar tiempo en una recuperación posterior al crear un punto en el que se garantiza que todas las páginas desfasadas se hayan escrito en el disco.

El script final:

USE Prueba
CHECKPOINT
BACKUP LOG Prueba TO DISK = 'C:\RespaldosBD\Prueba_Log.trn'
DBCC SHRINKFILE (N'Prueba_Log' , 0, TRUNCATEONLY)
GO

Si todo se ejecuto correctamente tendremos el archivo LOG reducido ‘”Truncado” (para este caso).

Resultado:

 

Me hubiera gustado hacer el ejemplo con LOG más grande, pero al momento solo cuento con este, aun así debe funcionar de la misma manera.

Nota: Me paso que al ejecutar el SHRINKFILE a una base de datos desde un acceso remoto, me decía que el archivo LOG no existía aunque físicamente ahí estuviera, después de probar un rato, logre que funcionara, en lugar de utilizar el nombre del archivo LOG, utilice el file_id y listo todo se resolvió.

¿Como saber el File_id?

Si ejecutamos el siguiente query obtendremos los siguientes resultados.

USE Prueba
SELECT * FROM SYS.DATABASE_FILES

Ahora que ya sabes el file_id lo usamos en el SHRINKFILE:

USE Prueba
CHECKPOINT
BACKUP LOG Prueba TO DISK = 'C:\RespaldosBD\Prueba_Log.trn'
DBCC SHRINKFILE (2 , 0, TRUNCATEONLY)
GO

Listo, con eso se debe reducir el archivo LOG de una BD.

Espero les sea de ayuda, espero comentarios!!!.

Etiquetas de Technorati: SQL,SHRINKFILE,CHECKPOINT,SQL LOG,REDUCIR LOG,TRUNCATE LOG