Fixed error in SQLLite Administration tool

Christophe Coenrates, a man I deeply admire (and whose job I covet) released a sweet SQLite Administration tool. I downloaded it last night and was playing with it. It is a very nice example of an AIR application.

When using the tool, I attempted to execute several sql statements and received an error. The error was caused by single quotes in my sql statement.

view plain print about
1insert into [ContactType]([ContactTypeID], [Type]) values(1, 'Friend');


Thankfully Mr. Coenrates included the source code for the application so I fixed the error.

The problem centered about SQL statements used to manage the cache. The statement below would error if the user created SQL statement contained a single quote. (sql is variable containing the user created SQL Statement) Example:

view plain print about
1var stmt:SQLStatement = new SQLStatement();
2stmt.sqlConnection = sqlConnection;
3stmt.text = "INSERT INTO statements (sql, access) VALUES ('"+sql+"'," + new Date().time + ")";
4stmt.execute();
5return stmt.getResult().rowsAffected;

To fix the error, I converted the SQL statements to prepared statements.

view plain print about
1var stmt:SQLStatement = new SQLStatement();
2stmt.sqlConnection = sqlConnection;
3stmt.text = "INSERT INTO statements (sql, access) VALUES (:sql, :access)";
4stmt.parameters[":sql"] = sql;
5stmt.parameters[":access"] = new Date().time;
6stmt.execute();
7return stmt.getResult().rowsAffected;

There are plenty of reasons to use prepared statements. Escaping is one. SQL Injection is another.

The full Cache.as is attached to this blog post. To fix the application, Click the 'download' link at the bottom of this post. Unzip and extract Cache.as to overwrite the Cache.as file in the original SQLite Administration tool.

Disclaimer: normally, I would contact the author of the original software and explain the bugfix. Mr. Coenrates doesn't seem to have a published email address.

Legalese:This code is licensed under the I Don't Care What You Do With It License. Specifically, Mr. Coenrates and anyone else is free to use this code with or without attribution.

Download Download

6698 Views Print Print Comments (1) Flex, AIR

Related Blog Entries

There are no comments for this entry.

Add Comment Subscribe to Comments

8/8/08 2:37 PM # Posted By Jake Churchill

Excellent post! I wish I had seen this before I wrote mine. This is a lot easier than what I did:

http://reynacho.com/2008/08/08/custom-object-parsi...