Transactions

A database is transactional if it has all the changes and queries to be Atomic, Constant, Isolated and Durable (ACID). SQLite implements serializable transactions even if it is interrupted by system crash or failures.

This means that in SQLite whatever transactions or changes occurred are atomic. In SQLite database, the query or changes within a single transaction occurs completely or not at all. Even if there is any interruption caused by the following –

  • A power failure
  • Operating system crashes down, or
  • The program crash or failure.


Injections

An injection is a way in which a user may run some SQL statements which runs unknowingly on the database. For example, suppose there is an input field which takes user’s name and a user inserts some SQL statements which runs unknowingly.

To stop these injections the validation of the fields must be done which can be done by pattern matching. The following line of restricts the username to alphanumeric characters and underscores with the length between 8 and 20 characters.


if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)){

   $db = new SQLiteDatabase('filename');
   $result = @$db->query("SELECT * FROM users WHERE username = $matches[0]");
} else {
   echo "username not accepted";
}

There are various databases interfaces which do not permit query stacking or in easy language executing multiple queries in a single function call. If you try to stack queries, the call fails but SQLite perform stacked queries, executing all of the queries provided in one string and creating a serious security problem.


Leave Comment