Wednesday, May 4, 2011

Working with SQLite in AIR: Part 11

Today we'll learn how to use variables in SQL statements.

It is often needed to use variable values in SQL statements, for example, if we want to let the user to be able to add new contacts to our "contacts" database by filling out a form and pressing a submit button. Then we would need to apply these text field values to an "INSERT" SQL statement.

It could look something like this:

myStat.text = "INSERT INTO contacts (fname, lname, age) VALUES (" + tf_fname.text + ", " + tf_lname.text + ", " + tf_age.text + ");"

Where tf_fname, tf_lname and tf_age are the text fields that the user can fill.

However, that example is bad, because it makes your code vulnerable. The user will be able to abuse the system by entering his own code into the SQL command. To prevent that, you can parametrize your SQL commands.

Parametrizing your SQL commands means putting parameters as the values for your statement, and AIR will automatically check the values and prevent them from being abused.

To do that, first set the values of your command as parameters by writing @ in front of each parameter keyword:

myStat.text = "INSERT INTO contacts (fname, lname, age) VALUES (@fname, @lname, @age)";

Now you can use the property 'parameters' of your SQLStatement object. This property is now an associative array of the parameters you've specified in the command, and you can set their values like this:

myStat.parameters["@fname"] = tf_fname.text;
myStat.parameters["@lname"] = tf_lname.text;
myStat.parameters["@age"] = tf_age.text;

And you are ready to go.

Alternatively, you can use unnamed parameters, and later refer to them by their indexes in the array. Use the ? symbols to set the parameters in your command:

myStat.text = "INSERT INTO contacts (fname, lname, age) VALUES (?, ?, ?)";

myStat.parameters[0] = tf_fname.text;
myStat.parameters[1] = tf_lname.text;
myStat.parameters[2] = tf_age.text;

That's all for today.

Thanks for reading!

Related:

Working with SQLite in AIR: Part 1
Working with SQLite in AIR: Part 2
Working with SQLite in AIR: Part 3
Working with SQLite in AIR: Part 4
Working with SQLite in AIR: Part 5
Working with SQLite in AIR: Part 6
Working with SQLite in AIR: Part 7
Working with SQLite in AIR: Part 8
Working with SQLite in AIR: Part 9
Working with SQLite in AIR: Part 10
Working with SQLite in AIR: Part 12

No comments:

Post a Comment