[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] Creando un resumen de las Tablas de una Base de Datos en SQL 2008/2005.

Saludos amigos en esta ocasión he elaborado un pequeño script en T-SQL 2008 el cual obtiene un Resumen de la estructura de las tablas de una base de datos. Todo esto consiste en consultar las siguientes 3 tablas de sistema:

• sys.tables
• sys.columns
• sys.types

La tabla sys.tables contiene todas las tablas de una base de datos.

La tabla sys.columns contiene todas la columnas que se encuentran en una tabla con información como el el orden de la columna, tipo de dato, si se le puede asignar un valor nulo o no, si es una columna de identidad, etc.

La tabla sys.types contiene los tipos de datos que utilizamos al crear las columnas de una tabla en una base de datos como son: Varchar, Char, Int, Bigint, Bit, datetime, etc.

La consulta:

USE prueba;
GO

SELECT t.name AS Tabla, t.type_desc AS TipoTabla , c.column_id AS Orden,
c.name AS Columna, tp.name AS TipoDato,
c.max_length AS Longitud,
(CASE c.is_nullable WHEN 1 THEN 'Sí'
                   WHEN 0 THEN 'No'    
END) AS ValorNulo,
(CASE c.is_identity WHEN 1 THEN 'Sí'
                   WHEN 0 THEN 'No'    
END) AS LlavePrimaria
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id 
INNER JOIN sys.types tp ON c.system_type_id = tp.system_type_id 
ORDER BY t.name, c.column_id

Etiquetas de Technorati: SQL 2008,SQL 2005. Tablas de Systema de base de datos,Columnas de BD,Tipos de columna,resumen de una BD

Ahora vean el resultado de la consulta anterior y la estructura de la tabla desde el explorador de objetos.

 

espero les sea de ayuda, a la hora de elaborar un resumen de una base de datos o algo que les pida su jefe , con gusto espero sus cometarios.

[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

[ASP .NET] Creando una WebSlice con datos de una BD y un Repeater SQL 2008

Saludos amigos, una novedad de ASP .Net en conjunto con Internet Explorer 8, es que nos permite crear Web Slice, que son pequeños extractos con información de páginas Web, las cuales podemos agregar a nuestra Barra de favoritos de Internet Explorer 8. A continuación les dejo un ejemplo de la creación de una Web Slice, la cual se llena con información de una base de datos en SQL Server 2008 a través de un objeto Repeater.

Nota: Debe configurar la cadena de conexión a la base de datos, vea la entrada anterior: http://sqlpsykrest.wordpress.com/2009/05/14/vs-2008asp-net-crear-una-cadena-de-conexin-en-asp-net-y-ejecutar-un-comando-a-una-bd-de-sql-server-2008/

Paso 1, debemos agregar la Web Slice, esto consiste en insertar un DIV que tenga una clase CSS llamada hslice y ID igual a 1.

Ejemplo:

<div class="hslice" id="1">
            <p class="entry-title">Título de la Web Slice</p>
            <div class="entry-content">
                  <!--Contenido de la Web Slice-->
            </div>
        </div>

Para indicar el título de la Web Slice, se debe colocar una clase CSS aplicada a un elemento XHTML Párrafo <p> llamada “entry-title”, el contenido de la Web Slice se determina agregando un elemento DIV con una clase CSS llamada “entry-content” dentro del DIV principal hslice.

Paso 2, Agregaremos como contenido de la Web Slice un objeto REPEATER, el cual selecciona datos sobre Productos de una base de datos en SQL Server 2008.

Ejemplo:

<h2>Ejemplo de WebSlice</h2>
        <div class="hslice" id="1">
            <p class="entry-title">WebSlice desde SqlPsyKrest Blog.</p>
            <div class="entry-content">
                  <asp:Repeater runat="server" ID="rProductos">
                        <HeaderTemplate>
                            <table class="tabla" cellpadding="3px" cellspacing="0" >
                                <tr>
                                    <td colspan="4" class="titulo" align="center" >Productos</td>
                                </tr>        
                        </HeaderTemplate>
                        <FooterTemplate>
                            </table>
                        </FooterTemplate>
                        <ItemTemplate>
                            <tr>
                                <td class="nombre"><%#DataBinder.Eval(Container.DataItem, "nombre")%></td>
                                <td class="precio"><%#DataBinder.Eval(Container.DataItem, "precio")%></td> 
                            
                        </ItemTemplate>
                        <AlternatingItemTemplate>
                                <td class="nombre"><%#DataBinder.Eval(Container.DataItem, "nombre")%></td>
                                <td class="precio"><%#DataBinder.Eval(Container.DataItem, "precio")%></td> 
                            </tr>                    
                        </AlternatingItemTemplate>
                </asp:Repeater>      
            </div>
        </div>

El DataBinder.Eval obtiene los datos de las columnas del comando que se ejecuta en la base da datos.

Paso3, Agreguemos en el code-behine en el evento Load del formulario Web la ejecución del comando de la base datos, el cual obtendrá la información de los productos que se cargaran en un objeto Repeater llamado repeatProductos que posteriormente se mostrarán en la Web Slice.

Ejemplo:

Imports System.Web
Imports System.Web.Configuration
Imports System.Data
Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not Me.IsPostBack Then

            Dim conn As New SqlConnection(WebConfigurationManager.ConnectionStrings("cnx").ConnectionString)
            Dim comm As New SqlCommand("SELECT nombre, precio FROM DEMO_PRODUCTO", conn)
            comm.CommandType = CommandType.Text

            Dim ds As DataSet = Nothing
            Dim da As SqlDataAdapter = Nothing

            Try
                Using conn
                    conn.Open()
                    ds = New DataSet()
                    da = New SqlDataAdapter()
                    da.SelectCommand = comm
                    da.Fill(ds)
                End Using

                comm.Dispose()

            Catch ex As Exception

            Finally
                If conn.State = ConnectionState.Open Then
                    conn.Close()
                    conn.Dispose()
                End If
            End Try


            Me.repeatProductos.DataSource = ds
            Me.repeatProductos.DataBind()

        End If

    End Sub
End Class

En el ejemplo anterior utilizamos un objeto Dataset y un SqlDataAdapter, los cuales reciben la información de la base da datos y la almacenan para cargarla en el objeto Repeater llamado repeatProductos,  una vez obtenida la información, la Web Slice esta terminada y lista para mostrando datos de los productos al usuario.

Paso 4, Agreguemos la Web Slice al Barra de favoritos de Internet Explorer 8:

Por último observen como se agrego la Web Slice a la Barra de favoritos de Internet Explorer 8,  de esta manera los usuario pueden estar viendo información especifica y actualizada de sus sitios Web con solo dar clic en ella.

Espero les sea de ayuda, me despido de ustedes y quedo en espera de sus comentarios.

Etiquetas de Technorati: Web Slice,ASP Net y Base de datos,Repeater

[VS 2008–ASP NET] Crear una cadena de conexión en ASP .NET y ejecutar un comando a una BD de SQL Server 2008.

Saludos amigos, en esta ocasión les mostrare un ejemplo de como crear una cadena de conexión en ASP .NET con Visual Studio 2008 hacia una base de datos creada en SQL Server 2008.

1. El primer paso es conocer la siguiente información de la base de datos y la instancia de SQL a la cual nos vamos a conectar.

a) Nombre de la instancia.

b) Nombre de la base de datos.

c) Usuario y contraseña del usuario que tiene permisos sobre la base de datos.

Ejemplo:

Instancia: EQUIPO.

BD: prueba.

User: sa.

Pass: sasysadmin.

Nota: Si no conoces el nombre de la instancia del SQL server ejecuta el siguiente comando.

USE master;
GO
SELECT [name], data_source FROM sys.servers;

Resultado:

Nota: SI quieres ver las bases de datos que están en la instancia del SQL server ejecuta el siguiente comando.

USE master;
GO
SELECT [name] FROM sys.databases;

Resultado:

2.  Ahora creamos un nuevo sitio Web en Visual Studio 2008, en el archivo de configuración Web ( web.config ), buscamos el elemento <connectionStrings> contenido dentro del elemento raíz <configuration> y agregamos una cadena de conexión como lo muestra el siguiente ejemplo:

<connectionStrings>
    <add name="cnxCurso"
         connectionString="Data source=EQUIPO; Initial catalog=Prueba; User id=admIvan; Password=123456"
         providerName="System.Data.SqlClient"/>
  </connectionStrings>

Explico brevemente la cadena:

Deben dar un nombre a la cadena de conexión en esta caso la llamaremos CNX .

El atributo connectionString debe tener los siguientes datos:

Data source es la instancia que utilizamos en SQL Server.

Initial catalog es el nombre de la base de datos a utilizar.

User id es el nombre de usuario que se conectara a la base de datos.

Password es la contraseña del usuario de la base de datos.

Por último el providerName que es un espacio de nombres de System.Data.SqlClient.

3. Agregaremos dos cajas de texto, un botón y una etiqueta en un formulario web.

4. En el Code-Behine de nuestro formulario web agregamos los siguientes espacios de nombres necesarios para conectarnos a las base da datos y trabajar de manera efectiva.

Nota: Para ir al code-behine debemos presionar la tecla F7 estando en el formulario Web.

Imports System.Web
Imports System.Web.Configuration

Imports System.Data
Imports System.Data.SqlClient

5. Agregar el siguiente código en el evento clic del botón del formulario web.

Protected Sub btnAceptar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAceptar.Click

        Dim idUsuario As Integer = 0
        Dim msg As String = "Usuario no encontrado en la base de datos."

        Dim conn As New SqlConnection(WebConfigurationManager.ConnectionStrings("cnxCurso").ConnectionString)
        Dim comm As New SqlCommand("SELECT id FROM dbo.PRB_Usuario WHERE Email = @email AND [Password] = @pass", conn)

        Try

            With comm
                .CommandType = CommandType.Text
                .Parameters.AddWithValue("email", Me.txtUser.Text.Trim())
                .Parameters.AddWithValue("pass", Me.txtPass.Text.Trim())
            End With

            Using conn
                conn.Open()

                With comm

                    idUsuario = .ExecuteScalar()
                    .Parameters.Clear()
                    .Dispose()

                End With

                If idUsuario > 0 Then
                    msg = "El usuario esta registrado en la base de datos."
                End If

            End Using

        Catch ex As SqlException
            'Controlar excepción SQL
        Catch ex As Exception
            'Controlar exception
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()
                conn.Dispose()
            End If

        End Try

        Me.lblMsg.Text = msg

    End Sub

 

Es importante poner atención a la hora de crear la cadena de conexión ya que el nombre debe de ser el mismo al que se agrego en el archivo de configuración web (web.config).

El comando al crearlo lleva dos parámetros iniciales, el comando de base de base de datos ( el cual selecciona el id de nuestro usuario) , y la cadena de conexión que utilizara para llevar acabo la ejecución.

Agregamos los parámetros que recibirá el comando para poder consultar los datos, a esto se le llama parametrizar sentencias o comandos SQL, para evitar los ataques de inyección de código.

Para terminar se abre la conexión, y se ejecutamos el comando SQL de manera Scalar (ExecuteScalar), es decir que solo nos regrese el valor de la primer fila y primer columna del conjunto de resultados obtenidos, para este caso siempre nos regresara un valor, este valor se asigna a nuestra etiqueta que mostrara si el usuario se encuentra o no en la base de datos.

Resultado:

 

Bueno amigos, espero les sea de ayuda, próximamente subiré videos.

Etiquetas de Technorati: Cadena de conexión VS,connectionString,sqlCommand,sqlConnection,Visual Studio,Select Command ASP Net,ASP Net y Base de datos.