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.
insert 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:
var stmt:SQLStatement = new SQLStatement();
stmt.sqlConnection = sqlConnection;
stmt.text = "INSERT INTO statements (sql, access) VALUES ('"+sql+"'," + new Date().time + ")";
stmt.execute();
return stmt.getResult().rowsAffected;
To fix the error, I converted the SQL statements to prepared statements.
var stmt:SQLStatement = new SQLStatement();
stmt.sqlConnection = sqlConnection;
stmt.text = "INSERT INTO statements (sql, access) VALUES (:sql, :access)";
stmt.parameters[":sql"] = sql;
stmt.parameters[":access"] = new Date().time;
stmt.execute();
return 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.