12Robots.com - Jason Dean - Database

Wha sup, yo?

Wed, 01 Sep 2010 08:07:00 -0500

Wow. Things have been busy and I have been neglecting my blog. I feel bad about that. My blog is so important to me, and things have been keeping me away. I am a teacher at heart. I love to teach. That is why I blog, that is why I present at conferences, and that is why I am going to grad school. So the fact that I have been unable to blog for a while upsets me greatly. But I want to tell you a little bit about why. This is not about making excuses. This is about what is keeping me busy and what I am learning about. It will also motivate me to blog about these things, and that's the important part. [More]

Insecure Direct Object Reference - Security Series #15

Tue, 19 Jan 2010 09:07:00 -0500

The first time I looked at the OWASP Top Ten web vulnerabilities, they all made sense to me, save for one. That one was A4 - Insecure Direct Object Reference. At the time I was still pretty new to object-oriented programming and so the first thing I thought was that it was referring to those kinds of objects. But that is not what they are talking about. The are talking about any direct reference to an "implementation object". Meaning objects like files, folders, database records, or other types of "keys". [More]

ORM (Hibernate) SQL Injection - Security Series #14

Thu, 19 Nov 2009 23:18:00 -0500

During Bob Silverberg's awesome ORM presentation today the topic of SQL injection came up. There was a question was about whether or not the Hibernate ORM service built into ColdFusion 9 would prevent SQL injection. On the surface it would seem that it does, but just like everything else, there are exceptions. [More]

Using SQLite Databases with AIR - Part 8 - Encrypted SQLite Databases

Mon, 02 Nov 2009 09:23:00 -0500

Getting back on track with projects I have already started, I want to finish up this series of posts of using SQLite databases with Adobe AIR. This last section will be on using Encrypted SQLite Databases.

Why use Encrypted Databases?

There will likely come a time in your career when you need to deal with sensitive data. When dealing with AIR applications, it may come sooner than you think. With AIR applications, if you need to persist data for use offline, one of the better options to do so is inside of a SQLite database. However, if you do this without any encryption, then the file is (obviously) stored in a clear-text way, meaning that it can be read by anyone who had access to the machine, including other applications like Trojan horses or other AIR applications written by malicious users. [More]

Using SQLite Databases with AIR - Part 7 - Looping over Query results (also with jQuery)

Thu, 27 Aug 2009 08:06:00 -0500

I think that I have neglected to cover one of the most important parts of working with SQLite databases in Adobe AIR, and that is how to get the results out of the query. We talked about making queries, parameterizing queries, using transactions with queries. But I don't think we have covered getting the data out of the queries. So let's do that. [More]

Using SQLite Databases with AIR - Part 6 - Transactions

Mon, 24 Aug 2009 07:48:00 -0500

Transactions is SQL statements are something that I have come to love. I'm sure you have too. But for those that don't know what transactional control in a database management system is, we'll start with a definition. Usually, when doing multiple SQL statements in a row in an application, each SQL statement is handled as a atomic unit and is committed permanently to the database before the next one is run. This can be very problematic when those queries depend on each other to work properly to maintain data integrity. [More]

Hands on Adobe AIR at the next Twin Cities CFUG

Thu, 20 Aug 2009 15:33:00 -0500

On Wednesday, September 2nd, I will be presenting at the Twin Cities ColdFusion User Group meeting. At this meeting we are going to be trying something new (at least for me since I have been going to the CFUG). We are going to do some hands-on work with the technologies we love instead of just doing a lecture-style presentation. The work we will be doing is with Adobe AIR, JavaScript, jQuery, and SQLite. Here is the description for the session: [More]

Using SQLite Databases with AIR - Part 5 - Parameterizing Queries

Wed, 12 Aug 2009 13:49:00 -0500

In a previous post we looked at doing simple CRUD with Adobe AIR and SQLite and doing CREATE TABLE statements. But the examples we've looked at are VERY simple. In fact, we have not looked at any dynamically constructed queries. Today I want to look at properly building dynamic queries in AIR using bind parameters. [More]

Using SQLite Databases with AIR - Part 4 - Simple CRUD

Mon, 03 Aug 2009 15:47:00 -0500

So in case you you've been living under a rock for the last several years, you know that CRUD stands for Create, Read, Update and Delete. Which is what we are going to look at today, doing simple SQL statements with SQLite databases in Adobe AIR using JavaScript. We saw in my last couple posts how do do simple CREATE statements using both synchronous and asynchronous connections. I will paste them here again so that we have the reference all on one page. [More]

Using SQLite Databases with AIR - Part 3 - Asynchronous Database Connection

Tue, 28 Jul 2009 08:55:00 -0500

So in my last AIR and SQLite post we talked about Synchronous Database Connections in AIR. In many cases, synchronous connections may be all you need. If your queries are fast and a slight applicaiton pause is not a concern, or if you have a need for rigid program flow control, then synchronous connections are great. But there may come a time when you do not want the program to pause during a query, or series of queries. You may want the user to be able to continue working while the queries take place in the background. This is where asynchronous queries come in. [More]

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

Tue, 21 Jul 2009 03:23:00 -0500

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

Thu, 16 Jul 2009 09:19:00 -0500

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

Tue, 14 Jul 2009 08:55:00 -0500

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?

Thu, 26 Jun 2008 11:19:00 -0500

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

Tue, 06 May 2008 15:09:00 -0500

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]