SQLite is a mostly ACID-compliant relational database management system contained in a relatively small (~500kB) C programming library. The Adobe AIR runtime includes the SQLite embedded database for use by Adobe AIR applications. This allows applications to run and store data locally and or synchronise the datastore with online repositories.
Applications that depend on user input to create a SQL statement — concatenating the user input to the SQL query — can become vulnerable to SQL Injection attacks, much like those common to web applications.
SQL Injection is a technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed.
Fortunately, there is a simple solution to the problem: use parameterised SQL Statements. Parameterised statements not only make your applications more secure and run more efficiently, but they also enable you to use objects, rather than literal values, in your queries. SQL injection can’t happen because the parameter values are treated explicitly as substituted values, rather than becoming part of the literal statement text.
Parameters in a SQL statement can be either named or unnamed. Below are examples of both types of statement in ActionScript and JavaScript.
Named Parameters
A named parameter has a specific name that is used to match the parameter value to its placeholder location in the SQL statement text. A parameter name consists of the colon (:) or an at (@) character followed by the variable’s name:
ActionScript 3
var conn:SQLConnection = new SQLConnection(); var stmt:SQLStatement = new SQLStatement(); stmt.sqlConnection = conn; stmt.text = "INSERT INTO user VALUES(@title, @firstname, @lastname)"; stmt.parameters["@title"] = "Mr"; stmt.parameters["@firstname"] = "Simon"; stmt.parameters["@lastname"] = "Whatley"; stmt.execute()
JavaScript
var conn = new air.SQLConnection(); var stmt = new air.SQLStatement(); stmt.sqlConnection = conn; stmt.text = "INSERT INTO user VALUES(@title, @firstname, @lastname)"; stmt.parameters["@title"] = "Mr"; stmt.parameters["@firstname"] = "Simon"; stmt.parameters["@lastname"] = "Whatley"; stmt.execute()
Unnamed Parameters
As an alternative to using explicit named parameters, you can also use implicit unnamed parameters. To use an unnamed parameter you simply designate a parameter in the SQL statement using a question mark (?) character. Each parameter is assigned a numeric index, according to the order in which the parameters appear in the SQL statement, starting with index 0 (zero) for the first parameter.
ActionScript 3
var conn:SQLConnection = new SQLConnection(); var stmt:SQLStatement = new SQLStatement(); stmt.sqlConnection = conn; stmt.text = "INSERT INTO address VALUES(?, ?, ?, ?)"; stmt.parameters[0] = "123 Main Street"; stmt.parameters[1] = "Sometown"; stmt.parameters[2] = "12345"; stmt.parameters[3] = "USA"; stmt.execute()
JavaScript
var conn = new air.SQLConnection(); var stmt = new air.SQLStatement(); stmt.sqlConnection = conn; stmt.text = "INSERT INTO address VALUES(?, ?, ?, ?)"; stmt.parameters[0] = "123 Main Street"; stmt.parameters[1] = "Sometown"; stmt.parameters[2] = "12345"; stmt.parameters[3] = "USA"; stmt.execute()
Note: Use clearParameters() to empty the statement parameters array; e.g. stmt.clearParameters().
Advantages
- Performance - A SQL statement that uses parameters can execute more efficiently compared to one that dynamically creates the SQL text each time it executes. The performance improvement is because the statement is prepared once and then executed multiple times using different parameter values, without needing to recompile the SQL statement. A comparison can be draw with database stored procedures.
- Data Typing - Parameters are used to allow for typed-substitution of values that are unknown at the time the SQL statement is constructed. The use of parameters is the only way to guarantee the type (storage class) for a value passed to the database. Using paramters therefore, implies better performance and security. When parameters are not used, the runtime attempts to convert all values from their text representation to a type based on the associated column’s type.
- Security - The AIR application is not vulnerable to SQL injections so common to web applications.
Recent Comments