Proteger Páginas Web Dinâmicas de acesso errado

Versão em português do meu outro post em inglês.

As vezes queremos proteger o acesso a página web dinâmicas (por exemplo criadas por procedimentos PL/SQL) de acesso errado (não normal) e que não tem relação a permissões de acesso. Uma das razões para o fazer é para evitar tráfego HTTP e carga na base de dados devido a acessos que não foram realmente efectuados pelo normal uso da aplicação web ou sitio web.

Vou usar como exemplo páginas geradas por procedimentos PL/SQL usando o Oracle Web Toolkit e a gateway mod_plsql. Diferentes gateways ou linguagens de programação terão variáveis de sessão web similares.

Mod_plsql's History

In the other day I found out a post in asktom with the history of mod_plsql.
I present here the relevante bits only changing the format, link info and making clear that the "I" in the text is Tom Kyte.

Oracle APEX Listener 1.1 available

Oracle APEX Listener 1.1 is available

New feature: "(...)RESTful Application Programming Interfaces (APIs) are created by configuring resource templates. Each resource template is a configuration file that binds a set of Uniform Resource Identifiers (URIs) to a SQL query or anonymous PL/SQL block."

Actualizações

Tenho ao longo dos tempos actualizados alguns "posts" anteriores. Apresento aqui uma lista das actualizações mais recentes:

Is NVL really slower?
HTML5 onde estás tu?
Uma função muito desprezada...
Valor actual de uma sequência
Como ver resultado de uma execução htp.p na consola SQL?
limites do método GET no mod_plsql
Constraints sem índices? Constraints without indexes?

Mod pl/sql GET limits

A reader asked me for a translation of some part of a previous post that was in Portuguese (very small part but ...:-)):

From the documentation:

"When using the GET method, parameters are passed using a query string. The limitation of this method is that the length of the value in a name-value pair cannot exceed the maximum length for the value of an environment variable, as imposed by the underlying operating system. In addition, operating systems have a limit on how many environment variables you can define.

Protected Dynamic Web Pages from misused access

Sometimes you will want to protected your Dynamic Web Pages from misused (non-normal) access not related with access permissions. You will do it to avoid http traffic and database load for requests that are issued by the use of the web site or web application.

I will use the example of PL/SQL pages using the Oracle Web Toolkit and mod_plsql gateway. Distinct gateways or programming languages will have similar "web session" variables.

RSS and PL/SQL

In answer to a friend request, just two pointers:
Lucas Jellema rss feed reader with several commentaries to improve the code
Tyler Muth rss producer

Do you know of others?

JSON by PL/SQL

JSON (JavaScript Object Notation) is a lightweight data-interchange format.
It's used for communication with javascript (AJAX).
A introduction to the format can be found here.

How to check the pl/sql web output with cookies

In the previous post "How to check the pl/sql web output" I show how you can check the pl/sql web output in code with minimal initialization.
Today I will show how you can make a cookie emulation or put other CGI environment variables in the pl/sql "web environment" variables.

How to check the pl/sql web output

If you use the pl/sql web toolkit sometimes you will want to check the web output that you are creating.
You can use a browse, but maybe you do not have a web server (with the pl/sql gateway) or the EPG configured, or you want to unit test inside PL/SQL or to see the result before pl/sql gateway mess with the http header.

Access security in mod_plsql or EPG

With mod_plsql or EPG (embedded pl/sql gateway) all the procedures (that meet the conditions) are available to be accessed by the web. That could be a security problem if you do not protect your procedure for that.
mod_plsql and EPG (DBMS_EPG) provide two way to deal with that:

Alternatives for PL/SQL Gateway

A attendee of my presentation on OPP 2010 Brussels notice an error in one URL in my presentation and asked me more info in the alternatives for PL/SQL Gateways.
So I provide here my investigation on that (please be free to help me improve this list) with the distintive features of each alternative.

Last day for OPP/APEXposed 2010 in Europe

It's over :-(

Some very interesting sessions and conversations.
All of the attendees tried to get the most of these two days.

OPP/APEXposed 2010 Europe 1st day

Today started the first OPP (oracle pl/sql programming) and APEXposed Conference in Europe.

Less that one week

Less than a week for the 1st Oracle PL/SQL Programming Conference (OPP) in Europe (in Brussels)!
And also the 1st APEXposed in Europe!

I will be presenting a session about "Advanced Web Programming" (outside and beyond APEX)
"In this session we will discuss how to provide security (avoid sql/injection, backlisting and whitelisting program access, etc.. ), to avoid unnecessary DB processing (use of client cache, ...),to add error management to your PL/SQL Web Toolkit programs and to make your website more Google friendly.”

OPP 2010 Oracle PL/SQL Programming Conference in Brussels

As you probably already know this year an Oracle PL/SQL Programming Conference (OPP) will also be holding in Europe (in Brussels). As will be the APEXposed 2010!
The two conferences are simultaneous and you can participate in both!

Sign up for one conference and attend sessions at either conference!

I will be presenting a session about "Advanced Web Programming" (outside APEX)

"Introduced in Oracle 8.1.6. the PL/SQL Web Toolkit provides a way to get build Internet (on Intranet) database-based websites, REST-like webservices and XML and JSON-like output to the Web.

"The year is 2261"

Goodbye 2009!

Quest Error Manager new version

Quest Error Manager (QEM) has a new version that incorporates the fixes and improves I send to Steven
(the fixes for the bugs I found (raise error when only log was expected, the size limit on some variables, the trigger to populate the log table automatically with some debug info and a new procedure to get the log code out).

Two small things I learn today

Today I learned that there is always something that we don't know, even in our main activity area.

1- In Oracle it's possible to make columns unavailable to normal use (SQL statements, etc..) without dropping them! So we could make a column "unavailable"
alter table mytable set unused column mycolumn;
test all the code and only later dropping it
alter table mytable drop unused mycolumn;

2- HTML has a tag button that makes possible to have buttons with images and text (besides the use of CSS classes).
Notice to myself: replace all the forms 'input type="submit" ' buttons to 'button type="submit"'

Of course that there is lots of stuff to learn about ORACLE but I was really surprised to find out that HTML tag!
Ok ...that tag only appeared in HTML 4.0 but that was almost 10 years ago! ops ...i'm getting old!

Como criar Web Services baseados em PL/SQL?

Sem usar o JDeveloper:
Exposing PL/SQL packages as Web services
Build a PL/SQL Web Service

Usando o JDeveloper:
Publishing PL/SQL Based WebServices


Actualização: na versão 11g já é possivel a exposição directa do PL/SQL (ver neste post)