Uso de funciones PL/SQL en postgreSQL

Una función (function) en PostgreSQL son sentencias SQL agrupadas y precompiladas para ejecutarse en bloque dentro del servidor, a diferencia de las consultas SQL donde cada consulta es procesada en tiempo de ejecución por el servidor , las funciones procedurales son compilados cuando son creados, ya que el servidor asume que serán ejecutados más de una vez, un función ofrece las siguientes ventajas:

  • No sobrecarga la comunicación cliente/servidor al evitar enviar una consulta tras otra, en su lugar procesa una consulta tras otra y envía únicamente el resultado.
  • Cuando y se ejecuta la primera vez se crea un plan preparado de ejecución, las siguientes ejecuciones reutilizan el plan preparado.
  • Agrega estructuras de control y capacidad de calculo al lenguaje SQL.
  • Las mismas consultas están disponibles para varias aplicaciones.
  • Seguridad los datos solo están accesibles mediante las funciones y evita el uso de SQL injection.

De los lenguajes más utilizados para crear funciones en postgreSQL, se encuentra PL/pgSQL, el cual se distribuye como un módulo cargable junto con postgreSQL, para emplearlo en nuestra base de datos es necesario darlo de alta, de la siguiente manera.

Fig 1 Agregando el soporte PL/SQL

Revisamos si ya lo tenemos disponible en nuestra base de datos para utilizarlo
La sintaxis de PL/pgSQL (similar al lenguaje PL/SQL de Oracle)

Fig 2 Comprobando la instalación del lenguaje PL/SQL

Utilizando PL/pgSQL con C#

En este ejemplo usaremos PL/pgSQL y C# para resolver un requerimiento practico como seria relacionar la columna city de nuestra tabla authors en nuestra base de datos con una tabla llamada cities donde se encontrará la información de la columna ciudad mas un identificador.

Fig 3 La tabla autores

La relación deberá de quedar de la siguiente manera, donde la columna city se debe cambiar por la clave primaria de la tabla cities que tendrá como clave primaria la clave de la ciudad y una columna adicional llamada city que contendrá el nombre de la ciudad.

Ahora usamos la siguiente función AddCities(varchar) para tomar los valores de la columna city en la tabla authors, crear un identificador único para la llave primaria, insertar ese valor de clave primaria junto con el nombre de la ciudad y por último sustituir los valores en la columna city y reemplazarlos con el valor de la llave primaria en la tabla cities.

using System;
using Gtk;
using System.Text;
using System.Data;
using Npgsql;
using NpgsqlTypes;

namespace PgForm {
class PgForm : Window {
Label lbMsg = new Label("DEBUG: ");
Entry txtNameFunction = new Entry();
Entry txtArg = new Entry();

public PgForm() : base("Ejecutar funciones"){
BorderWidth = 8;
SetDefaultSize(208,220);
this.DeleteEvent += new DeleteEventHandler(OnWindowDelete);
Frame frame = new Frame ("Ejecutar funciones");
Add (frame);
VBox MainPanel = new VBox (false, 8);
MainPanel.BorderWidth = 8;
frame.Add (MainPanel);
MainPanel.PackStart(new Label("Nombre de la funcion"),false,false,0);
MainPanel.PackStart(txtNameFunction,false,false,0);
MainPanel.PackStart(new Label("Si recibe un argumento de tipo varchar")
                    ,false,false,0);
MainPanel.PackStart(txtArg,false,false,0);
Button btnSubmit = new Button("Ejecutar funcion");
btnSubmit.Clicked += new EventHandler(btnSubmitClicked);
MainPanel.PackStart(btnSubmit,false,false,0);
MainPanel.PackStart(lbMsg,false,false,0);
lbMsg.LineWrap = true;
ShowAll();
}

public void OnWindowDelete(object o, DeleteEventArgs args) {
Application.Quit(); 
}

void btnSubmitClicked(object o, EventArgs args){
string cStr = "Server=127.0.0.1;Port=5432;User Id=postgres;" + 
 "Password=postgres;Database=pubs;";
try{
using(NpgsqlConnection conn = new NpgsqlConnection(cStr))
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand(txtNameFunction.Text, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//en caso de tener un parametro como en nuestra funcion de ejemplo
if(txtArg.Text.Length > 0){
cmd.Parameters.Add(new NpgsqlParameter());
cmd.Parameters[0].NpgsqlDbType = NpgsqlDbType.Varchar;
cmd.Parameters[0].Value = txtArg.Text;
}
lbMsg.Text += cmd.ExecuteScalar().ToString();
}}catch(Exception e){
lbMsg.Text += e.Message;
}
}

static void Main(string[] args) {
Application.Init();
new PgForm();
Application.Run();
}}  
}

Lo compilamos y lo ejecutamos

    mcs /t:winexe -r:System.Data,Npgsql -pkg:gtk-sharp-2.0 ExecutePgFunction.cs
Fig 5 Compilación del programa

Podemos probar nuestro programa invocando la funcion version() predeterminada de PostgreSQL.

Fig 6 Ejecutando la función version() de PostgreSQL

Al ejecutar la función sin argumentos desde el formulario se vera el mismo resultado.

Fig 7 El programa ejecutando función version().

Aquí el driver de PostgreSQL para .NET ejecuta la función usando la clase NpgsqlCommand la cual recibe como argumento el nombre de la función y la conexión al servidor donde se encuentra.

    NpgsqlCommand cmd = new NpgsqlCommand(“version”, conn);

Si la función recibe parámetros, debemos de crear una instancia de la clase NpgsqlParameter() por cada uno de los parametros que reciba, es muy importante no olvidar indicarle a la clase NpgsqlCommand que el comando que ejecutaremos es un stored procedure o una función pl/sql, esto lo hacemos mediante la instrucción:

    cmd.CommandType = System.Data.CommandType.StoredProcedure;

Para mayor referencia no olvidar leer la documentación del data provider para PostgreSQL.
Si todo es ejecutado correctamente, ya podemos probar la función AddCities con el argumento del nombre de la ciudad y debe devolvernos la clave primaria de la tabla cities.

Fig 8 El programa ejecutando la función AddCities

 Descarga el código fuente en Plpgsql (Download Plpgsql source code)

 Descarga el código fuente en C# (Download C# source code)

Code Access Security CAS, la seguridad en el código

CAS (Code Access Security), es la forma en que los ensamblados solicitan permisos para acceder a ciertos recursos para su ejecución en el CLR, esto proporciona seguridad a nivel código,como una forma complementaria y adicional a la seguridad del sistema operativo.

Esta seguridad debe estar presente sobre todo si de desarrollan componentes, plugins, add-ons o juegos que serán ejecutados en diferentes ambientes que no conocemos ni controlamos.
Señalo que esta seguridad es complementaria y en ningún momento deberá reemplazar la seguridad del sistema operativo.
Para explicar voy a usar un ejemplo, un banco nos solicito realizar un aplicación donde el usuario genera unos archivos de estados de cuenta esta aplicación en un diseño inicial esta compuesta por un formulario y un ensamblado que se encarga de generar el estado de cuenta en un archivo XML.
Obviamente una aplicación de esta naturaleza, necesitaría una disciplina de análisis y diseño, pero para fines ilustrativos, logramos llegar a la etapa de construcción sin la utilización del CAS.

La aplicación se mostraría más o menos así,

La operación de la aplicación consiste que cuando el usuario presione el botón de generar, se generaría un archivo XML en una carpeta restringida para ciertos usuarios, el código del método del ensamblado es el siguiente:

El método de la aplicación para invocar el método del ensamblado es el siguiente:

La aplicación es utilizada sin ningún problema hasta que varios archivos de estados de cuenta no coinciden al utilizarlos en la contabilidad, se encuentran localizados en una ubicación que no es la ruta predeterminada y el sistema operativo no indica ninguna excepción en las bitácoras de seguridad.
Supongamos que un usuario autenticado construye un programa malintencionado que ejecuta el método de nuestro ensamblado para escribir los estados de cuenta afuera de la carpeta predeterminada un código como el siguiente:

Para compilar este código usamos:
csc /r:Ensamblado1.dll SinPermiso.cs

Con la opción /r le indicamos al compilador, que haga referencia al metadata del ensamblado indicado, en este caso al Ensamblado1 donde se encuentra el método para generar estados de cuenta.
Al revisar los códigos observamos que el método esta implementado para generar los archivos debajo de una carpeta llamada [Banks] en la partición [C:\\] y que la protección de los valores del ensamblado dependerá de las validaciones que implementemos en el formulario de la aplicación, mas no del ensamblado mismo, por eso al invocar el método y pasar le la cadena con una secuencia ..\\ indicamos al ensamblado que escriba el archivo un nivel arriba.

Más que buscar un culpable a nivel usuario, la pregunta se plantea a nivel código:
¿como podemos evitar que el código realice operaciones para las que no fue diseñado?
La respuesta sin duda alguna es uno de los objetivos del CAS.
Usando CAS podemos evitar que un usuario o un proceso autenticado utilice los métodos del ensamblado para realizar acciones que no están consideradas en la lógica del programa.
Para lograr ese objetivo CAS utiliza permisos encapsulados como objetos que pueden ser usados a nivel de métodos o ensamblados, estos permisos pueden ser de manera declarativa usados como o como atributos dentro del código, en este ejemplo usaremos un permiso de forma declarativa para nuestro método.

Los permisos representan acciones que son revisadas cada vez que el CLR carga el ensamblado para ejecutarse, estos permisos son controlados por el CAS.

UIPermission Controla la manipulación a la interfaz de usuario y el portapapeles.
ResourcePermissionBase Controla el acceso a los recursos de Windows
RegistryPermission Controla el acceso al Registro de Windows
ReflectionPermission Controla el acceso a la funcionalidad de la reflexión proporcionada por el CLR
IsolatedStoragePermission Controla el acceso a isolated storage
FileIOPermission Controla el acceso de escritura o lectura al disco duro
FileDialogPermission Controla el acceso a archivos o carpetas.
EnviromentPermission Controla el acceso a leer, modificar y crear variables de ambiente.

Si ejecutamos nuevamente el código para escribir los estados de cuenta fuera de la carpeta predeterminada con el ensamblado modificado, se mostrará el siguiente error.

Ahora nuestro ensamblado esta protegido por la seguridad del CAS y no depende de la validación que implementen las clases que invoquen sus métodos.

Para habilitar o des habilitar la seguridad del CAS se utiliza la herramienta caspol con el siguiente argumento:
caspol -s on
Siempre es recomendable habilitar la seguridad del CAS.

Para compilar la aplicación:
csc /r:Ensamblado1.dll EstadosCuenta.cs EstadosCuenta.Designer.cs

 Descarga el código fuente

Usando los controles de validación ASP .NET III (RegularExpressionValidator)

Este control es verdaderamente útil, si necesitamos comparar el valor de un campo con una expresión regular , este control aplica esencialmente si queremos comparar el formato de una dirección de correo electrónico,una fecha o un código postal entre o bien para buscar un patrón dentro del texto.


Veamos el siguiente formulario como ejemplo:

<%@Page Language="C#" AutoEventWireup="false" 
CodeBehind="RegularExpression.aspx.cs"
Inherits="blog.listings.RegularExpression"
%>
<html>
<head>
<title>Expresiones Regulares</title>
</head>
<body>
<form id="form1" runat="server">
<table>
<tr>
<td>Fecha (dd/mm/yyyy)</td>
<td><asp:TextBox ID="txtDate" runat="server"></asp:TextBox></td>
<td>
<asp:RequiredFieldValidator ID="reqvtxtDate" runat="server"
ErrorMessage="* Obligatorio" ControlToValidate="txtDate">

</asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID="rexpvtxtDate" runat="server"
ErrorMessage="* Invalida"
ValidationExpression="(0[1-9]|[12][0-9]|3[01])[- /.]
(0[1-9]|1[012])[- /.](19|20)\d\d" ControlToValidate="txtDate">

</asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td>Teclea un URL</td>
<td><asp:TextBox ID="txtUrl" runat="server"></asp:TextBox></td>
<td>
<asp:RequiredFieldValidator ID="reqvtxtUrl" runat="server"
ErrorMessage="* Obligatorio" ControlToValidate="txtUrl">

</asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID="rexpvtxtUrl" runat="server"
ErrorMessage="* Invalida"
ValidationExpression="http://([\w-]+\.)+[\w-]+(/[\w- ./?%&amp;=]*)?"
ControlToValidate="txtUrl">

</asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td>Teclea un código postal</td>
<td><asp:TextBox ID="txtCp" runat="server"></asp:TextBox></td>
<td>
<asp:RequiredFieldValidator ID="reqvtxtCp" runat="server"
ErrorMessage="*Obligatorio" ControlToValidate="txtCp">

</asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID="rexptxtCp" runat="server"
ErrorMessage="* Invalido"
ValidationExpression="\d{5}(-\d{4})?"
ControlToValidate="txtCp">

</asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td>Correo Electrónico</td>
<td><asp:TextBox ID="txtEmail" runat="server"></asp:TextBox></td>
<td>
<asp:RequiredFieldValidator ID="reqvtxtEmail" runat="server"
ControlToValidate="txtEmail" ErrorMessage="* Obligatorio">

</asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID="rexptxtEmail" runat="server"
ErrorMessage="* Invalido" ControlToValidate="txtEmail" ValidationExpression="\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*">

</asp:RegularExpressionValidator>
</td>
</tr>
</table>
<p><asp:Button ID="btnSubmit" runat="server" Text="Enviar"></asp:Button></p>
<asp:Label ID="lbMsg" runat="server"></asp:Label>
</form>
</body>
</html>

y su correspondiente código de clase:

Lo compilamos:

  • (.NET)csc /t:library -r:System.Web RegularExpression.aspx.cs
  • (mono) mcs /t:library -r:System.Web RegularExpression.aspx.cs

Lo instalamos: copiamos el ensamblado a la carpeta bin, ejecutamos xsp y abrimos el navegador con la dirección http://localhost:8080/RegularExpression.aspx.
Al ejecutar el programa se mostrará como en la siguiente imagen.

Propiedades del control RegularExpressionValidator


  1. display Esta propiedad puede tener 3 valores: Static es la propiedad predeterminada, reserva un espacio suficiente en la página para mostrar el mensaje de error.Dynamic el espacio para mostrar el mensaje no se reserva, cuando el mensaje se despliega se desplaza el contenido existente en la página. None el mensaje no será desplegado en el lugar del control sino en el control ValidationSummary si se localiza en la misma página.


  2. ValidatorExpression El valor de la expresión regular con la que se compara el valor del control a validar.


  3. controlToValidate El identificador del control donde obtenemos el valor para validar.


  4. ErrorMessage El texto del mensaje de error a desplegar

 Descarga el código fuente

Usando los controles de validación ASP .NET II (RangeValidator)

Si necesitamos asegurarnos que el valor de un campo se encuentre dentro de unos limites es decir dentro de un rango especifico, el control RangeValidator se asegura que el valor de un campo sea del tipo que necesitemos y se encuentre dentro de los valores iniciales y finales que necesitemos, el código del formulario es el siguiente:

<%@Page language="C#" AutoEventWireUp="false" 
CodeBehind="ValidarRango"
Inherits="blog.listings.ValidarRango"
%>
<html>
<head><title>Validar Rango</title></head>
<body>
<p>Fecha de nacimiento</p>
<form id="frmRange" runat="server">
<table>
<tr>
<td>Dia</td>
<td>
<asp:TextBox id="txtDay" Runat="server" Maxlength="2"
Columns="3"></asp:TextBox>

<asp:RangeValidator id="rngvtxtDay" Runat="server"
Display="Dynamic" ErrorMessage="* Fuera de rango"
ControlToValidate="txtDay" Type="Integer" MinimumValue="1"
MaximumValue="31"></asp:RangeValidator>

<asp:RequiredFieldValidator id="reqvtxtDay" Runat="server"
ControlToValidate="txtDay" ErrorMessage="* Obligatorio">

</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>Mes</td>
<td>
<asp:TextBox id="txtMonth" Runat="server" Maxlength="2"
Columns="3"></asp:TextBox>

<asp:RangeValidator id="rngvtxtMonth" Runat="server"
Display="Dynamic" ErrorMessage="* Fuera de rango"
ControlToValidate="txtMonth" Type="Integer" MinimumValue="1"
MaximumValue="12"></asp:RangeValidator>

<asp:RequiredFieldValidator id="reqvtxtMonth" Runat="server"
ControlToValidate="txtMonth" ErrorMessage="* Obligatorio">

</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>Año (entre 1950 y 1989)</td>
<td>
<asp:TextBox id="txtYear" Runat="server" MaxLength="4"
Columns="6"></asp:TextBox>

<asp:RangeValidator id="rngvtxtYear" Runat="server" Display="Dynamic"
ErrorMessage="* Fuera de rango" ControlToValidate="txtYear" Type="Integer"
MinimumValue="1950" MaximumValue="1989"></asp:RangeValidator>

<asp:RequiredFieldValidator id="reqvtxtYear" Runat="server"
ControlToValidate="txtYear" ErrorMessage="* Obligatorio">

</asp:RequiredFieldValidator>
</td>
</tr>
</table>
<br>
<asp:Button id="btnSubmit" Runat="server" Text="Validar"></asp:Button>
<br>
<asp:Label id="lbMsg" Runat="server"></asp:Label>
</form>
</body></html>

y el código de la clase es:

Lo compilamos:

  • (.NET)csc /t:library -r:System.Web ValidarRango.aspx.cs
  • (mono) mcs /t:library -r:System.Web ValidarRango.aspx.cs

Lo instalamos: copiamos el ensamblado a la carpeta bin ejecutamos xsp y abrimos el navegador con la dirección http://localhost:8080/ValidarRango.aspx.
Si todo es correcto se mostrará la ejecucción como en la siguiente imagen:

Propiedades del control RangeValidator



  1. display Esta propiedad puede tener 3 valores: Static es la propiedad predeterminada, reserva un espacio suficiente en la página para mostrar el mensaje de error.Dynamic el espacio para mostrar el mensaje no se reserva, cuando el mensaje se despliega se desplaza el contenido existente en la página. None el mensaje no será desplegado en el lugar del control sino en el control ValidationSummary si se localiza en la misma página.


  2. type El tipo de datos de los valores a comparar, los tipos de datos disponibles para este control son: Currency (moneda), Date (fecha), Double (valor de punto flotante), Integer (Entero sin punto decimal), String (Cadena).

  3. controlToValidate El identificador del control donde obtenemos el valor para validar.

  4. minimumValue El valor mínimo del rango.


  5. maximumValue El valor máximo del rango.



 Descarga el código fuente

Usando los controles de validación ASP .NET (RequiredFieldValidator)

Verificando la información de los formularios con los controles de validación

Algo indispensable en el desarrollo de formularios que trabajan con bases de datos, es la validación de los datos que solicitamos, acciones que son repetitivas e importantes ya que están relacionadas con la integridad y la seguridad de nuestra aplicación, una mala validación de los formularios puede convertirse en un problema que va desde un formato inadecuado o ataques con sentencias SQL (SQL Injection).
NET provee de controles web (Web Controls) de validación que nos ayudan a realizar este tipo de tareas, tareas como: verificar que los datos que necesitemos estén completos en el formulario, comparar que el tipo de datos que solicitemos coincida con el tipo de datos donde se va a almacenar en la base de datos, que los datos se encuentren en el formato que necesitamos, etc, estos controles no solo nos ahorran tiempo de codificación sino que también están diseñados para detectar la versión del navegador (browser) y así presentar el mejor HTML para ese navegador.

Validando los campos obligatorios con RequiredFieldValidator

Una de las primeras tareas que se necesitan cuando se desarrolla una aplicación es verificar que antes de que la información sea devuelta con los cambios hacie el servidor la información cumpla con los
criterios obligatorios para continuar, incluso antes de la tarea de validar el formato de los campos, debemos asegurarnos que eses campos tienen información y los campos necesarios no estan sin información, el control RequiredFieldValidator nos ayuda a esa tarea, el código del formulario es el siguiente:

y el código de la clase es:

Lo compilamos:

(.NET) csc /t:library CampoRequerido.aspx.cs
(mono) mcs /t:library CampoRequerido.aspx.cs

Lo instalamos: copiamos el ensamblado a la carpeta bin
ejecutamos xsp y abrimos el navegador con la dirección http://localhost:8080/CampoRequerido.aspx

Al presionar el botón para enviar los datos al servidor se verifica que el atributo de la página Page.IsValid regrese un valor verdadero, si es falso desplegará el mensaje de error de lo contrario desplegará el texto en el control etiqueta Label. En la siguiente imagen se muestra la ejecucción del programa.

Propiedades del control RequiredFieldValidator


  1. controlToValidate El control de donde obtendremos el valor para evaluar

  2. errorMessage El texto del mensaje que se desplegara si no se cumplen las condiciones

  3. display Esta propiedad puede tener 3 valores: Static es la propiedad predeterminada, reserva un espacio suficiente en la página para mostrar el mensaje de error.Dynamic el espacio para mostrar el mensaje no se reserva, cuando el mensaje se despliega se desplaza el contenido existente en la página. None el mensaje no será desplegado en el lugar del control sino en el control ValidationSummary si se localiza en la misma página.

 Descarga el código fuente

Entendiendo los ensamblados (assemblies)

Una de las caracteristicas más atractivas que ofrece la programación con .NET es la posibilidad de poder construir aplicaciones con diferentes lenguajes, cada compilador para .NET genera un módulo ejecutable conocido como assembly (ensamblado) que contiene instrucciones en un lenguaje llamado CIL (Common Intermediate Language) que es ejecutado por el CLR (Common Language Runtime).
Examinemos pues el siguiente programa en código C#

Lo compilamos:

$ mcs ProgramCS.cs

Esto nos genera un archivo ProgramCS.exe que es el ensamblado ejecutable.
Ahora examinemos el mismo programa en código Visual Basic.

Lo compilamos:

$ vbnc ProgramVB.vb

Igualmente el compilador de VB nos genera un ensamblado ejecutable ProgramVB.exe.
Al ejecutar cualquiera de los dos ensamblados se mostrará el siguiente resultado:

Los ensamblados se clasifican según su uso:

Privados (Private Assembly) son usados solamente por una aplicación, se instalan dentro del mismo directorio o subdirectorio que la aplicación y opcionalmente pueden o no ser firmados con un Strong name.

Compartido (Shared Assembly) Se usan por dos o más aplicaciones en una misma máquina, se instalan dentro del GAC (Global Assembly Cache) y deben ser obligatoriamente firmados por un Strong name.
o según el contenido:
Archivo único (Single-File Assembly) Todo el contenido del ensamblado es empacado en un solo archivo con extensión (.dll) en caso de ser una biblioteca o con extensión (.exe) en caso de ser un ejecutable.
Multiples archivos (Multifile Assembly)Aqui el contenido del ensamblado esta dividido en multiples archivos, siendo estos de códido IL o bien de otros recursos como imágenes o archivos XML.
Los ensamblados únicamente pueden ser ejecutados por el CLR de .NET, por lo que si queremos que este ensamblado funcione en otra máquina esa máquina debe tener instalado Mono en el caso de GNU/Linux o bien Microsoft .NET en el caso de Windows.

En el caso de los códigos anteriores se genero para cada código un ensamblado privado (Private Assembly) y de archivo único (Single File Assembly).

 Descarga el código fuente