A case AGAINST using Stored Procedures (and Triggers)

Many programmers are taught (and blindly follow) the "Stored Procedure" principle: If you have to insert/update/delete in a database table from a Java or .NET application, it is desirable to write stored procedures that do the insert/update/delete. The reasons usually given are:

1. Better Performance. The database server parses and compiles the stored procedure text once and re-uses the compiled string for later uses. This applies of course if you are using parametarized sql and you are not (god forbid!) using string concatenation.
2. By using a stored procedure, we allow the database server to also handle business logic, thus relieving the client site from processing.

This above reasons hold water for any RDBMS, albeit Oracle, MS SQL Server, Sybase, etc.

I would argue against using Stored Procedures. The main problems I have with the approach are:

1. The performance argument does not hold water at all. The database server will take the same resources and time to compile the stored procedure sql text, or the plain insert/update/delete sql text. The trick here is to use parameterized sql and not string concatenation.
2. The database is supposed to handle storage, not business processing. It's just not meant for programming logic. That is why we have the "Middleware", to handle our business logic.
3. By moving business logic to a stored procedure, then you are tying your business logic to procedural languages, and you are not taking advantage of Object Oriented programming concepts and techniques.
   The desired approach is to have all your business logic processing in the middle tier, or the client if you are working on a 2-tier system.
4. What about triggers? These are even worse. In my opinion triggers should NOT be used at all in an application. The only reason to use triggers is to get a new sequence number, and this applies only to Oracle. By having your logic dispersed in stored procedures, triggers and client code then you are decreasing the maintainability of your application.

To summarize, my advice is:

• Use plain insert/update/delete statements to update your database from your middleware/client code.
  Do not even create plain stored procedures that just do the insert/update/delete. In the future someone will have the bright idea to start writing business logic code in them. If they are there, they will use them.
• Use Java or any other Object Oriented language to encapsulate and capture ALL of your business logic.

By following the above, you increase the maintainability and extensibility of your application.

Configuring ESAPI for use with a Java Web Application (Java 1.4)

First thing's first: I would strongly advice against applying ESAPI to your web application if you are in production or even at the final stages of testing. Doing so will render all tests mute and you will have to re test the application from the ground up. Securing a web application should be one of the first things to consider, not the last.

ESAPI (The OWASP Enterprise Security API) is a free, open source, web application security control library that makes it easier for programmers to write lower-risk applications. The ESAPI libraries are designed to make it easier for programmers to retrofit security into existing applications or build a solid foundation for new development. If you can figure out these libraries that is :-). If you Google ESAPI Sample Code you wont find much and the documentation provided in ESAPI for Java is incomplete. They do have some general guidelines here: ESAPI Secure Coding Guidelines
So the best way to go is to download the source code from http://owasp-esapi-java.googlecode.com/svn/ and try to figure out from the very thin documentation and some reference implementation classes what to do. Here are the steps that I took to apply the ESAPI libraries to an **existing** web application.

1. For Java 1.4, download code (checkout from svn) from http://owasp-esapi-java.googlecode.com/svn/tags/releases/1.4.0
2. Add owasp-esapi-full-java-1.4.jar and antisamy-bin.1.2.jar to your project classpath.
   Note: From http://code.google.com/p/owaspantisamy/: AntiSamy is a collection of APIs for safely allowing users to supply their own HTML and CSS without exposing the site to XSS vulnerabilities.
3. Create a ESAPI.properties file in the root source directory of your web application. Do not place it in a package inside the root source directory because the DefaultSecurityConfiguration will not find it.
4. Download/Copy the antisamy-esapi.xml file in the root source directory of your web application.
5. Next step is to implement interfaces org.owasp.esapi.User and org.owasp.esapi.Authenticator This is because the default reference implementations use a File as the user database. So you will need to create your own, unless of course you store users in a text file. For our purposes, we store user information in a database table.

   Note that this is not necessary, unless you want to use the ESAPI API for user authentication and authorization. In addition, the org.owasp.esapi.User object carries with it some interesting methods to set and verify CSRF tokens.

6. In the context initializer of your web app, call

   ESAPI.setAuthenticator( new MyUserAuthenticator() );
   

   This will load the properties and print them in the console output, and then set the system Authenticator.
7. Next step is to write and configure an http filter class for your application. Good reference implementation are the java classes in the org.owasp.esapi.filters package. Here's an example that implements the doFilter method of the javax.servlet.Filter:

   public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) 
                     throws IOException, ServletException {
   
           if (!(request instanceof HttpServletRequest)) {
               chain.doFilter(request, response);
               return;
           }
   
           HttpServletRequest hrequest = (HttpServletRequest)request;
           HttpServletResponse hresponse = (HttpServletResponse)response;
   
           // this is necessary on every call
           ESAPI.httpUtilities().setCurrentHTTP(hrequest, hresponse);
           
           // doFilter by wrapping the request and the response to the 
           // ESAPI safe HttpServletRequest and  HttpServletResponse  
           chain.doFilter(new SafeRequest(hrequest), new SafeResponse(hresponse));
   
   }
   
   

   Another good example of an ESAPI filter is  org.owasp.esapi.filters.ESAPIFilter.java
   You need to be careful with the url-pattern of the ESAPIFilter filter. If you use /* then all http requests, including requests for images, css files and JavaScript files included on a page will pass through the filter and probably fail if you have a call to
   ESAPI.authenticator().login(request, response);. The recommendation here is to create a separate directory for your protected JSPs and put them in that directory. Anything else, like JavaScript files, css files and images should go into another folder and not have their requests pass through the filter. Don't forget to also specify url patters of protected servlets that include the secured directory.
8. Deploy and run or debug your web application. You should see the ESAPI properties printed on the console. If you see an error that ESAPI cannot load properties, make sure that the ESAPI properties file resides in the source of your web application code (i.e., in WEB-INF/classes)
9. You may need to modify the ESAPI validator properties. For example, if your application user interface is non-English, then the Validator.HTTPParameterValue pattern will not do:

   Validator.HTTPParameterValue=^[a-zA-Z0-9.\\-\\/+=_ ]*$

   The above will cause ESAPI to throw an IntrusionDetector exception if any of your html fields contain non-English characters. We had to change the above to:

   Validator.HTTPParameterValue=^[\p{L}\p{Nd}0-9.\\-\\/+=_ ]$

   to allow Unicode characters. See below for a list of all ESAPI.properties we had to change and why.
Depending on your application needs, you may need to modify other validation patterns in the ESAPI.properties file. But keep these changes to a minimum. The guys that wrote this code knew what they were doing.
10. And lastly, the hard part: Test, test and then test again. As mentioned above, the Validator.* regular expression patterns defined in ESAPI.properties may cause validation exceptions to be thrown. For example, the default cookie name validation pattern does not allow for dots in the cookie name, but in our case the application server was actually setting a cookie with a dot in the name.

List of ESAPI.properties that you may need to change if running on Oracle Application Server 10g

• Validator.HTTPCookieName: OAS 10g sets a cookie with name "oracle.uix" even if you do not use Oracle UIX. The HTTPCookieName pattern was changed to

  Validator.HTTPCookieName=^[a-zA-Z0-9.\\-_]{0,32}$ 

• Validator.HTTPCookieValue: The "oracle.uix" cookie value in our case was 0^^GMT+2:00 The pattern was changed to

  Validator.HTTPCookieValue=^[a-zA-Z0-9:.\\^\\-\\/+=_ ]*$ 

• Validator.HTTPParameterName: The default ESAPI.properties file allows for a maximum of 32 characters. We changed this to allow for 50:

  Validator.HTTPParameterName=^[a-zA-Z0-9_]{0,50}$ 

Changing the Oracle Character Set after installation

Sometimes when creating an Oracle Database, we forget to choose the correct character set on the relevant dbca screen. Then, when we then go to import data or create data we discover that the character set is not correct. With Oracle 11g, you can actually change it after the database creation. Here is sql plus commands to make this happen:

conn / as sysdba
-------
SHUTDOWN IMMEDIATE;
STARTUP RESTRICT;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
-------
ALTER DATABASE CHARACTER SET EL8ISO8859P7;
--- EL8ISO8859P7 is greek

-- if the above fails:
ALTER DATABASE CHARACTER SET INTERNAL_USE EL8ISO8859P7;
SHUTDOWN IMMEDIATE;
STARTUP;
 
---if all this is not working run the following command to reload the stylesheet
dbms_metadata_util.load_stylesheets