Using SQLite Databases with AIR - Part 2 - Synchronous Database Connection

So as we discussed last time, there are two ways to connect to a SQLite database with Adobe AIR. Today we are going to look at how to make a synchronous connection.

Just as a reminder, when we use a synchronous connection to connect to the database, the program will not move forward in processing until it is done processing a statement. With these small statements, that really shouldn't be noticeable.

[More]

Using SQLite Databases with AIR - Part 1 - Synchronous vs. Asynchronous

If you are planning on building an Adobe AIR application, you need to be familiar with the concepts of Synchronous and Asynchronous requests, henceforth referred to as sync and async, respectiviely.

The terms sync and async are used all over the place. There are sync and async transmissions, sync and async learning, sync and async communications, and in Ajax and AIR we have sync and async requests and connections.

[More]

Using SQLite Databases with AIR - Series Introduction

One of the best parts about working with Adobe AIR applications is the ability to have applications that work both online and offline. Since Adobe AIR applications are desktop applications that can run without the browser and without the need for an HTTP server or middleware server (ColdFusion, .NET, PHP, etc), they can be used without a connection to the internet. Of course we still need to write code to handle the "sometimes connected" abilities of our application and sometimes we need to be able to store data that we would normally receive from the server. This data can be stored in an embedded SQLite database.

[More]

Are stored Procedures any more secure than parameterized queries?

I am not going to include this post in my security series, because I am not really sure of the answer. This is more of a thought exercise and a request for input from the community.

So there has been a lot of discussion about best practices for application/database security. One of the "Best Practices" that is mention fairly often is:

Use Stored Procedures for Update/Insert queries.

My question is, is this really necessary to have a secure application.

[More]

Multiple Datasource - Security Series #1

UPDATE: So after reading Ray's Post here and after reading the comments on that post, I think I have to agree with my commenters as well as Ray's that this is not the best idea. The level of complexity that this adds does not make it worth the extra security (perceived or otherwise) that the technique may offer.

For my first security series post, I am going to keep things simple. This one is more of a tip/best practice than an article. At least, that is how I am planning it in my head, we'll see if that changes by the bottom of the page.

Today I want to discuss something that most developers and administrators will find to be very annoying. I know I do.

[More]

I'm starting a new series on Secure Application Development with ColdFusion

Looking around at conference schedules, documentation, and blog postings, there seems (to me) to be a real lack of quality postings and presentations about Application Security using ColdFusion that extend beyond the usual "use <cfqueryparam>" and "Don't log into your dbms as root or SA".

While both of those things are VERY good advice, I fear that many developers think that if they do these few checklist items, that they will be writing completely secure applications.

So, what am I going to do about it?

[More]

A productive couple of days, had I

Well, I have had a couple of very productive days at home. There has been A LOT that I have been wanting to do. Well finally, I just sat down and did a lot of it.  In the last two days I have accomplished setting up my ultimate development environment.

Since Noon yesterday I have:

  • Installed Apache 2.2 (A lot easier than I thought it would be)
  • Configured Apache to work with ColdFusion 8 Dev and PHP5
  • Set up a multi-site Drupal installation so that all of my Drupal sites run off of the same code base
  • Installed and configured SVN Server
  • Configured Apache to work with SVN
  • Set up and loaded repositories for all of my projects
  • Configured Eclipse with Subclipse to access my repositories
  • Set up Virtual Host s in Apache so that each of my development sites can have a local domain

I have to say, most of these things were MUCH easier than I though they would be. All save for Subversion working with Apcahe. That was not easy, and I am still not sure I have it right. It seems to be working. But I did have a few issues with importing my files into the repos and with Subclipse freezing up. I'm not sure, but I think I may also have had an issue with a corrupt repo. Now all seems to be well.

Now I have the really hard part. I have to be consistent with my development processes and make sure that I am properly using all of my tools.  I have also recently installed and configured Apache Ant with Eclipse. I have started watching Jim Priest's presentation on UGTV but I have, so far, only created very simle scripts. I still have a lot to learn about Ant, but it looks like it will help me to maintain good practices.

Oracle IsDate() Function

As promised in a previous post here is my IsDate() function for Oracle

I don't know how great a function it is, since I am a novice to Oracle, but it seems to work pretty well and very quickly. I learned a lot while writing it, especially about basic excpetion handling in PL/SQL. I'm really not a big fan of Oracle, or PL/SQL, but I need to know it for my work, so I hope that my struggles in things like this can help others fuddle through.

Normally, I would use ColdFusions IsDate() function and be VERY happy with it. But in this case Iam importing batches of 1,000,000 records from raw data files and running conversion scripts on them, and ColdFusion just isn't built for that kind of work, so I am doing it directly in Oracle with PL/SQL and then calling that from ColdFusion. So far so good. It is a long road, since Iam new to Oracle there is a large learning curve for me.

This Oracle function is pretty basic. It takes in a varchar2 and returns a varchar2(I would return a Boolean, but for whatever reason, in Oracle you don't get Boolean values for your $65,000 investment, and I cannot convert 1 and 0 to boolean. Stoopid Oracle). The it is the string that we would like to test.


        create or replace
            function IsDate (str varchar2) return varchar2 is
            inDate varchar2(40);
            
    

Next we add a sub-function called dateCheck(). This takes in two parameters and returns one, all are varchar2 (see rant above). The first IN value is the string we passed into the parent function witht he string we want to test. The second IN parameter is the mask of the date format we want to check the string against. The return string will be either 'true' or 'false'.


         FUNCTION dateCheck (
             inputDate varchar2, inputMask varchar2)
             RETURN varchar2
             IS
             dateVar date;
             BEGIN
             dateVar:= to_date(inputDate,inputMask);
             return 'true';
            
             exception
             when others then
             return 'false';
             END;
    

This sub-function uses the to_date() funciton in Oracle. to_date() tries to read in a string and convert it to a date using a user specified input mark. For example, to_date(06-18-2008, 'mm-dd-yyyy') would return a valid date, but to_date(06-18-2008, 'mm.dd.yyyy') would raise an exception. So since Oracle cannot return troe or false, I have create my function to retrun 'true' if the command executes successfully and 'false' if an exception is raised.

Finally, my parent function will call the child function several times using different date pattern masks to see if any of them will work. If none of them evalute to true, the overall function will reutn 'false'. If one of them wrks, then it is a valid date and the function will return 'true'.


             inDate:= trim(str);
             if dateCheck(inDate, 'mm-dd-yyyy') = 'false'
             AND dateCheck(inDate, 'mm-dd-yy') = 'false'
             AND dateCheck(inDate, 'yyyy-mm-dd') = 'false'
             AND dateCheck(inDate, 'yy-mm-dd') = 'false'
             AND dateCheck(inDate, 'yyyy-mon-dd') = 'false'
             AND dateCheck(inDate, 'yy-mon-dd') = 'false'
             AND dateCheck(inDate, 'dd-mon-yyyy') = 'false'
             AND dateCheck(inDate, 'dd-mon-yy') = 'false'
             AND dateCheck(inDate, 'mmddyy') = 'false'
             AND dateCheck(inDate, 'mmddyyyy') = 'false'
             AND dateCheck(inDate, 'yyyymmdd') = 'false'
             AND dateCheck(inDate, 'yymmdd') = 'false'
             AND dateCheck(inDate, 'yymmdd') = 'false'
             AND dateCheck(inDate, 'yymondd') = 'false'
             AND dateCheck(inDate, 'yyyymondd') = 'false'
             AND dateCheck(inDate, 'mm/dd/yyyy') = 'false'
             AND dateCheck(inDate, 'yyyy/mm/dd') = 'false'
             AND dateCheck(inDate, 'mm/dd/yy') = 'false'
             AND dateCheck(inDate, 'yy/mm/dd') = 'false'
             AND dateCheck(inDate, 'mm.dd.yyyy') = 'false'
             AND dateCheck(inDate, 'mm.dd.yy') = 'false'
             AND dateCheck(inDate, 'yyyy.mm.dd') = 'false'
             AND dateCheck(inDate, 'yy.mm.dd') = 'false'
             then
             return 'false';
             else
             return 'true';
             end if;
    

So here is the overall code:


        create or replace
            function IsDate (str varchar2) return varchar2 is
            inDate varchar2(40);
            
             FUNCTION dateCheck (
             inputDate varchar2, inputMask varchar2)
             RETURN varchar2
             IS
             dateVar date;
             BEGIN
             dateVar:= to_date(inputDate,inputMask);
             return 'true';
            
             exception
             when others then
             return 'false';
             END;
            
            BEGIN
             inDate:= trim(str);
             if dateCheck(inDate, 'mm-dd-yyyy') = 'false'
             AND dateCheck(inDate, 'mm-dd-yy') = 'false'
             AND dateCheck(inDate, 'yyyy-mm-dd') = 'false'
             AND dateCheck(inDate, 'yy-mm-dd') = 'false'
             AND dateCheck(inDate, 'yyyy-mon-dd') = 'false'
             AND dateCheck(inDate, 'yy-mon-dd') = 'false'
             AND dateCheck(inDate, 'dd-mon-yyyy') = 'false'
             AND dateCheck(inDate, 'dd-mon-yy') = 'false'
             AND dateCheck(inDate, 'mmddyy') = 'false'
             AND dateCheck(inDate, 'mmddyyyy') = 'false'
             AND dateCheck(inDate, 'yyyymmdd') = 'false'
             AND dateCheck(inDate, 'yymmdd') = 'false'
             AND dateCheck(inDate, 'yymmdd') = 'false'
             AND dateCheck(inDate, 'yymondd') = 'false'
             AND dateCheck(inDate, 'yyyymondd') = 'false'
             AND dateCheck(inDate, 'mm/dd/yyyy') = 'false'
             AND dateCheck(inDate, 'yyyy/mm/dd') = 'false'
             AND dateCheck(inDate, 'mm/dd/yy') = 'false'
             AND dateCheck(inDate, 'yy/mm/dd') = 'false'
             AND dateCheck(inDate, 'mm.dd.yyyy') = 'false'
             AND dateCheck(inDate, 'mm.dd.yy') = 'false'
             AND dateCheck(inDate, 'yyyy.mm.dd') = 'false'
             AND dateCheck(inDate, 'yy.mm.dd') = 'false'
             then
             return 'false';
             else
             return 'true';
             end if;
            
             --exception
             --when others then return 'false';
            END;
    

Oracle IsNumeric Function

So, most people that use Oracle and other RDBMS know that they severly lack some very basic functions that exist in the middle-tier langauges we usually use. But sometimes we can;t use our middle-tier languages.  Right now I am writing a Stored Procedure in Oracle that will import, validate and normalize 1,000,000 records from raw text files.  This is WAY TOO big a job for ColdFusion, so I am using Oracle Stored Procudres and functions.

During my error checking I realized I needed some extra functionality. I needed IsDate() and IsNumeric(). With some Googling and simple Oracle functions, I was able to create both. today I will demostrate IsNumeric(). I am still working on adding more functionality to IsDate() and I will demo that one another day.


UPDATE: I have updated this post to reflect a better way of handling an IsNumeric() function. This is from Alexandre Paiva's comment, thank you Alexandre!

    CREATE OR REPLACE FUNCTION isnumeric (p_string_value IN VARCHAR2)RETURN BOOLEAN
    AS

    test_value NUMERIC;
    BEGIN
        BEGIN
            test_value := TO_NUMBER (p_string_value);
            RETURN (true);

            EXCEPTION
            WHEN OTHERS THEN
            RETURN (false);
        END;
    END isnumeric;

This very simple function takes in a string of characters and passes it in. Then using the Oracle TRANSLATE function, it replaces any characters that are 1-9 or a decimal, plus sign or minus sign with a space (" '). Then it does a trim on the remaining string, if the value is all spaces, the Length() function that wraps the others will return 0 and we know that the string that was passed in is numeric. If it returns 1 or more then we know there were non-numerics and we can return false.

BlogCFC was created by Raymond Camden. This blog is running version 5.9.1. Contact Blog Owner