Using SQLite Databases with AIR - Part 5 - Parameterizing Queries

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.

In case you are not familiar with bind parameters, they are used when dynamically constructing SQL statements so that parameters that are passed in to use for the SQL construction are escaped to prevent database errors and treated as a single unit separate form the executing code, preventing SQL injection and improve performance in most cases.

When we build a simple SQL statement in Adobe AIR, we simply create a statement object, specify a connection for it to use, add a SQL string to the text property of the object and then call execute() on it. Like this:

var stmt = new SQLStatement();

stmt.sqlConnection = conn; // I am assuming that this object already exists

stmt.text = "SELECT firstname, lastname FROM users";


Now, what if we want a more advanced SQL statement? Perhaps something with a WHERE clause, like this:

stmt.text = "SELECT firstname, lastname FROM users WHERE userid = 2";

Well this is easy enough, if we are always looking for userid 2. But what if we have a dynamic value to enter in?

var userid = document.getElementById("userid").value;
stmt.text = "SELECT firstname, lastname FROM users WHERE userid = " + userid;

Here we are dynamically building the string by appending the userid onto the end of the string. This would technically work just fine, but it would be vulnerable to SQL injection and it would not be as efficient a query as if we used bind parameters. So let's add bind paramters.

var userid = document.getElementById("userid").value;
stmt.text = "SELECT firstname, lastname FROM users WHERE userid = ?";
stmt.parameters[0] = userid;

The parameters property of a SQLStatement object is an object (associative array) that can be used with either standard array access syntax or with associative keys. In this last example, I am using the "?" token to indicate where the parameter goes, so I am going to use the standard array access syntax ([0]) to access the first element of the parameters property and stick the needed value in there.

When you use the "?" token in your dynamic SQL, you are indicating to AIR that you want the parameters to be inserted in the order of the array. So the first "?" token will be replaced with the value of parameters[0] and the second "?" token will be replace with parameters[1] and so on.

Here is a more complicated statement.

var userid = document.getElementById("userid").value;
var status = document.getElementById("status").value;

stmt.text = "SELECT firstname, lastname FROM users WHERE userid = ? AND userStatus = ?";
stmt.parameters[0] = userid;
stmt.parameters[1] = status;

And that's it. AIR will take care of replacing the values that we inserted into the parameters property.

Now, let's say we don't want to worry about getting the parameter order right, or we want to have SQL that is a little easier to read than this:

INSERT INTO users (userid, firstname, lastname, password, salt, address, city, state, zip) VALUES (?,?,?,?,?,?,?,?);

Well we can use keyed tokens instead of "?".

var userid = document.getElementById("userid").value;
var status = document.getElementById("status").value;

stmt.text = "SELECT firstname, lastname FROM users WHERE userid = :userid AND userStatus = :status";
stmt.parameters[':userid'] = userid;
stmt.parameters[':status'] = status;

Now, we are using keyed, or named, parameters. This allows us to insert the params in any order we want. It also makes our SQL a little more readable. Again, AIR will take care of replacing the tokens with the appropriate value when execute() is called.

That's all I have for parameterizing queries. It is really pretty simple once you get used to it.

freddy's Gravatar Hi Jason,

On the first post of the series you mentioned that you were going to look at using both async and sync modes in the same app. Did you give that a try?

I am currently moving some operations of an application to use the async model and have been trying to use both modes in the app

The app had a connection opened in sync mode, so what I am doing is closing it and opening a new connection in async mode. The async operations after that work as intended. After all async operations have completed, I close the async connection and open a sync one.

The issue I am having, is that after switching back to use sync mode, the operations are being executed in async mode. It would be great to know if that worked for you (using both modes in the same app), as I am now thinking is due to a bug/restriction in adobe air.
# Posted By freddy | 8/13/09 10:23 AM
freddy's Gravatar Got it, it does work i.e. switching back to sync mode behaves appropriately ... there was an app logic bug coming into play :(
# Posted By freddy | 8/13/09 11:02 AM
Jason Dean's Gravatar @freddy, I'm glad to hear you got it working. I did have success with it as well, although I handled it differently by making a separate SQLConnection object for each connection type. Then I just give the SQLStatement object the connection that I want to use.
# Posted By Jason Dean | 8/13/09 11:51 AM
Mark's Gravatar Hi Jason,
I have enjoyed reading your blog about Adobe Air and databases/SQLite. Thank you for the info!

I was wondering if you would help me with something that I can not find the answer to anywhere online.

I am trying to use an UPDATE query in a Flex 4.6 app (local database) in which I need to use a WHERE id IN ( myVariable ). When i try to use a variable, I get an error "data type mismatch" with no details. I have tried it with the var being a string of numbers and an array of numbers. If I hard code the numbers within the parenthesis, everything works. I can get it working with a for each statement, but I have noticed that every so often it's buggy.

The variable is just a list of id numbers that I need to update at a certain point in the app without updating the entire table. They are dependent on user input, so they have to be dynamically sorted.

How can I use a variable in the WHERE id IN (myVariable) clause? Please give an example with your answer as I am still learning AS, Flex, SQLite, etc.
# Posted By Mark | 10/11/14 11:52 PM
Jason Dean's Gravatar @Mark,

In my experience "datatype mismatch" will occur when you try, for example, to compare a string to a date or a number to a string.

In your case, I wonder, are you trying to do something similar.

You said your code was something like:

WHERE id IN ( myVariable )

Could it be that it needs to actually be:

WHERE id IN ( 'myVariable' )

# Posted By Jason Dean | 10/13/14 7:56 AM