Securing and optimizing SQLite statements in AIR 1.5 using named or unnamed parameters

This is an excerpt from the AIR 1.5 Cookbook that you can finally buy on any bookstore:

Solution 10.11 Using Parameters in Queries

Problem

You want to use parameters in queries to create a reusable SQL statement and prevent the risk of SQL injection.

Solution

Use the parameters property to specify named or unnamed parameters in SQL queries and to create reusable SQL statements.

Discussion

Parameters enable you to create reusable SQL statements to work with the same SQLStatement instance and carry out multiple SQL operations. For example, you can  use an INSERT statement several times during the life cycle of the application to allow the user to insert multiple values in the database that will populate the database with data. This is why it is compulsory to use the parameters approach in SQL statements for the performance of the application itself. Parameters can be declared as named or unnamed parameters.
Named parameters are declared with a specific name, which the database uses as a placeholder in the SQL statement. They can be specified by using the : or @ character.
Here’s an example where :name and :surname are two parameters that are inserted in the SQL text statement:

var statementInstance:SQLStatement = new SQLStatement();

var sqlText:String = "INSERT INTO Students (firstName, lastName) VALUES (:name,
:surname)";
statementInstance.parameters[":name"] = "Marco";
statementInstance.parameters[":surname "] = "Casario";
SQL_String = "INSERT INTO Students (firstName, lastName) VALUES (:name, :surname)"
statementIstance.parameters[":name"] = "Marco";
statementIstance.parameters[":surname "] = "Casario";

Unnamed parameters, on the other hand, are specified with the ? character in the SQL statement, and they are set by using a numerical index in the same order they are written in the SQL statement:

var statementInstance:SQLStatement = new SQLStatement();
var sqlText:String = "INSERT INTO Students (firstName, lastName) VALUES (?, ?)";
statementInstance.parameters[0] = "Marco";
statementInstance.parameters[1] = "Casario";
SQL_String = "INSERT INTO Students (firstName, lastName) VALUES (?, ?)"
statementIstance.parameters[0] = "Marco";
statementIstance.parameters[1] = "Casario";

The parameters property is an associative array, and the indices are zero-index based.
Using parameters doesn’t enable you only to reuse the same SQL statement; it also makes the application more robust and secure. It’s more robust because the parameters are typed substitutions of values and they guarantee the storage class for a value passed into the database. It’s more secure because the parameters aren’t written in the SQL text and they don’t link the user input to the SQL text. Therefore, this prevents possible SQL injection attacks. In fact, when you use parameters, the values are treated as substituted values instead of being part of the SQL text.
It will become necessary to use parameters in SQL statements in most AIR applications.
To use parameters, you need to have an instance of the SQLStatement class where you can define the parameters property as an associative array. The SQL text will also have to be changed by defining the placeholder values that will be associated to the parameters of the SQLStatement instance.

See how to use parameters in your SQLite statements in Adobe AIR using Flex as well as JavaScript.

Using parameters in your SQLite statements in Adobe AIR using  ActionScript/Flex

Use the ActionScript class created in Recipe 10.7 to create an InsertParam.as class,
making the following changes:

1. Add a private String property called sqlAdd'sqlAdd'.

2. Change the constructor by adding a SQL statement that will use parameters:

private var sqlAdd:String;
//...

public function InsertParam()
{
this.createLocalDB();
sqlString = "CREATE TABLE IF NOT EXISTS Students(" +
"stuId INTEGER PRIMARY KEY AUTOINCREMENT, " +
"firstName TEXT, " +
"lastName TEXT" + ")";
sqlInsert = "INSERT INTO Students (firstName, lastName) " +
"VALUES ('Marco', 'Casario')";
sqlSelect = "SELECT * FROM Students";
sqlAdd = "INSERT INTO Students (firstName, lastName)" +
"VALUES (:name, :surname)";
}

3. Write a new public method that will be invoked by the application and that will
be responsible for executing the SQL statement and associating the parameters to
the SQL text:

public function insertParameters (paramName:String, paramLast:String):void
{
var paramStmt:SQLStatement = new SQLStatement();
paramStmt.sqlConnection = _dbConn;
paramStmt.text = sqlAdd;
paramStmt.parameters[":name"] = paramName;
paramStmt.parameters[":surname"] = paramLast;
paramStmt.execute();
paramStmt.addEventListener(SQLEvent.RESULT, paramAddHandler);
paramStmt.addEventListener(SQLErrorEvent.ERROR, errorHandler);
}
private function paramAddHandler(event:SQLEvent):void
{
trace("Data added using parameters");
}

The insertParam method accepts two parameters: paramName:String and
paramLast:String. These are used by the parameters property of the SQLStatement
instance:

paramStmt.parameters[":name"] = paramName;
paramStmt.parameters[":surname"] = paramLast;

The MXML page that imports the new ActionScript class has a Form container with two
TextInput controls. The text you will insert into these two controls will be passed onto
the insertParam method, which will use them as parameters in the SQL statement:

<?xml version="1.0" encoding="utf-8"?>
<mx:WindowedApplication
xmlns:mx="http://www.adobe.com/2006/mxml"
layout="vertical"
initialize="init()">
<mx:Script>
<![CDATA[
import com.oreilly.aircookbook.ch10.InsertParam;
private var myDB:File;
[Bindable]
private var myDBclass:InsertParam;

private function init():void
{
createBtn.addEventListener(MouseEvent.CLICK, onClick);
openBtn.addEventListener(MouseEvent.CLICK, onClickOpen);
insertBtn.addEventListener(MouseEvent.CLICK, onClickInsert);
selectBtn.addEventListener(MouseEvent.CLICK, onClickSelect);
addBtn.addEventListener(MouseEvent.CLICK, onClickAdd);
}

private function onClick(evt:MouseEvent):void
{
myDBclass = new InsertParam();
myDB = myDBclass.myDB;
openBtn.enabled = true;
mx.controls.Alert.show("Database File Was Created : \n" + myDB.nativePath );
}

private function onClickOpen(evt:MouseEvent):void
{
insertBtn.enabled = true;
selectBtn.enabled = true;
}

private function onClickInsert(evt:MouseEvent):void
{
myDBclass.insertData(myDB);
mx.controls.Alert.show("Data was inserted into the database : \n" +
myDB.nativePath );
}

private function onClickSelect(evt:MouseEvent):void
{
myDBclass.selectData(myDB);

myDG.dataProvider = myDBclass.myResultAC;
}

private function onClickAdd(evt:MouseEvent):void
{
myDBclass.insertParameters(nameTxt.text, lastTxt.text);
}

]]>
</mx:Script>

<mx:VDividedBox>
<mx:HBox>
<mx:Button id="createBtn" label="Create DB" />
<mx:Button label="Open DataBase" id="openBtn" enabled="false" />
<mx:Button label="Insert Data Asynchronously" enabled="false" id="insertBtn" />
<mx:Button label="Show Data" id="selectBtn" enabled="false" />
</mx:HBox>
<mx:DataGrid id="myDG" width="100%" height="60%"/>
<mx:VBox width="100%">
<mx:Label text="Insert Values into the Database" />
<mx:Form width="100%">
<mx:FormHeading label="Heading"/>
<mx:FormItem label="Name">
<mx:TextInput id="nameTxt"/>
</mx:FormItem>
<mx:FormItem label="Surname">
<mx:TextInput id="lastTxt"/>
</mx:FormItem>
<mx:FormItem >
<mx:Button label="Insert Values" id="addBtn"/>
</mx:FormItem>
</mx:Form>
</mx:VBox>
</mx:VDividedBox>
</mx:WindowedApplication>

In the event handler that is triggered with the click of the button, the insertParam
method of the ActionScript class is invoked, and the values inserted in the two
TextInput controls are passed onto it.
You can test it by launching the AIR application, inserting values in the text fields, and
clicking the button to send the data. This data will be inserted in the database and

shown in the DataGrid control that is associated with the ArrayCollection that contains
the SELECT SQL statement.

Using parameters in your SQLite statements in Adobe AIR using   JavaScript

As far as the JavaScript and HTML version is concerned, you can also add a few finishing
touches to the JavaScript and HTML files you created in the previous solution to add
parameters to a SQL operation. Create a new file called InsertParam.js based on the
RetrieveDataAsynch.js file, and make the following changes:
1. Insert two new variables in the JavaScript file; one will contain the SQL text with
specified named parameters, and the other will be the instance of the SQLState
ment with the following parameters:

var SQL_ADD = "INSERT INTO Students (firstName, lastName)" +
"VALUES (:firstName, :lastName)";
// ...
var dbStmAddParam;
// ...

function addDataParam(name, last)
{
dbStmAddParam = new air.SQLStatement();
dbStmAddParam.sqlConnection = dbConn;
dbStmAddParam.text = SQL_ADD;
dbStmAddParam.parameters[":firstName"] = name;
dbStmAddParam.parameters[":lastName"] = last;
dbStmAddParam.addEventListener(air.SQLErrorEvent. RESULT, onStatementResult);
dbStmAddParam.addEventListener(air.SQLErrorEvent.ERROR, onStatementError);
dbStmAddParam.execute();
}

function onSelectResult(event)
{
selectData();
}

The addDataParam function accepts two parameters, which it will pass onto the
parameters property of the SQLStatement instance. You launch the selectData function
in the event handler of the air.SQLEvent.RESULT event, which has the role of
writing all the records returned by a SELECT statement in a ul list.
2. Add the selectData function.
This is the content of the selectData function and the RESULT event handler:

function selectData()
{
dbStmSelect = new air.SQLStatement();
dbStmSelect.sqlConnection = dbConn;
dbStmSelect.text = SQL_SELECT;
dbStmSelect.execute();
dbStmSelect.addEventListener(air.SQLEvent.RESULT, onSelectResult);
dbStmSelect.addEventListener(air.SQLErrorEvent.ERROR, onStatementError);
}

function onSelectResult(event)
{
var result = dbStmSelect.getResult();
var numResults = result.data.length;
var ul = document.createElement('ul');
for (i = 0; i < numResults; i++)
{
var row = result.data[i];
var x = document.createElement('li');
x.appendChild(document.createTextNode("Student #"+ row.stuId + ": " +
row.firstName + " " + row.lastName));
ul.appendChild(x);
var output = "ID: " + row.stuId;
output += "; NAME: " + row.firstName;
output += "; LAST NAME: " + row.lastName;
air.Introspector.Console.log(output);
}
document.getElementById('resultDiv').appendChild(ul);
}

The HTML page will contain a Form with two text input controls. The values inserted
by the user will be used as parameters of the addDataParam function that is defined in
the JavaScript file:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<script type="text/javascript" src="frameworks/AIRAliases.js"></script>
<script type="text/javascript" src="frameworks/AIRIntrospector.js"></script>
<script type="text/javascript" src="InsertParam.js"></script>

<script language="javascript" type="text/javascript">
<!--

function sendParam()
{
var name = document.simpleForm.firstName.value;
var last = document.simpleForm.lastName.value;
addDataParam(name,last);
}

//-->

</script>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>AIR Cookbook: 10.8 Using Parameters in Queries (JavaScript)</title>
</head>
<body onload="createDB()">
<h1>AIR Cookbook: 10.8 Using Parameters in Queries (JavaScript)</h1>
<p>
<label>Create a Table in a Database File
<input type="button" name="openDB" id="openDB" value="Insert Data" accesskey="o"
tabindex="1" onclick="populateDB();document.getElementById('resultDiv').innerText =
'Data was added to the database';" />
</label>
</p>
<p>
<label>Select Data in the Database
<input type="button" name="openDB2" id="openDB2" value="Select Data" accesskey="o"
tabindex="1" onclick="selectData();document.getElementById('resultDiv').innerText =
'Data selected';" />
</label>
</p>
<p>div>
<form name="simpleForm">
Insert Name: <input type="text" name="firstName" size="20" /><br />
Insert Surname:<input type="text" name="lastName" size="20" />
<br />
<input type="button" value="Insert Values" onclick="sendParam()" />
</form>
</divp>
<p><div id="resultDiv"></div></p>
</body>
</html>

Comments

[Quentin]

Nice article, but that'd be even better with a monospaced font and syntax coloring!

[mario fernandes]

hello!!

Flex solutions help me a lot.

Hope this to. My book already dispatch from amazon store, will arrive very soon.
thanks for the great work.

[M]

Hi Marco, sorry for using your blog the way one would use an answering machine. I sent you a private mail some days ago, asking you about a Flash Player licencing issue. Just two lines to tell you that I solved anything. Thanks (and sorry for disturbing).

[Mariano KIWO Carrizo]

Hi Marco! I'm working with AIR to create a large scale application and I'm planning to place the database file directly with AIR application without using SQL code from AS. Do you think it is a good practice? My database have about 10 tables and 1600 rows in one of them... By copying database file I don't need a lot of code to recreate it.
Thank you for this post! You are great!

[lieben]

Interessante Informationen.