Easy Tutorial
Sqlite Alias Sqlite Like Clause Sqlite Syntax Sqlite Php Sqlite Distinct Keyword Sqlite Functions Sqlite Autoincrement Sqlite Detach Database Sqlite Unions Clause Sqlite Attach Database Sqlite Joins Sqlite Pragma Sqlite Group By Sqlite Where Clause Sqlite Injection Sqlite And Or Clauses Sqlite Operators Sqlite Constraints Sqlite Drop Table Sqlite C Cpp Sqlite Order By Sqlite Subquery Sqlite Expressions Sqlite View Sqlite Indexed By Sqlite Alter Command Sqlite Insert Sqlite Select Sqlite Explain Sqlite Python Sqlite Installation Sqlite Transaction Sqlite Update Sqlite Delete Sqlite Commands Sqlite Date Time Sqlite Null Values Sqlite Create Table Sqlite Java Sqlite Data Types Sqlite Trigger Sqlite Create Database Sqlite Limit Clause Sqlite Intro Sqlite Truncate Table Sqlite Vacuum Sqlite Perl Sqlite Glob Clause Sqlite Index Sqlite Tutorial Sqlite Having Clause
❮ Sqlite Where Clause Sqlite And Or Clauses ❯

SQLite Injection

If your site allows users to input data through web pages and inserts that input into an SQLite database, you are faced with a security issue known as SQL injection. This section will explain how to prevent this from happening and ensure the security of your scripts and SQLite statements.

Injection typically occurs when requesting user input, such as asking for a name, but the user inputs an SQLite statement, which then runs unknowingly on the database.

Never trust data provided by users, so only process data that has been validated. This rule is enforced through pattern matching. In the following example, the username is restricted to alphanumeric characters or underscores, with a length between 8 and 20 characters - adjust these rules as needed.

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";
}

To demonstrate the problem, consider this excerpt:

$name = "Qadir'; DELETE FROM users;";
@$db->query("SELECT * FROM users WHERE username='{$name}'");

The function call is intended to retrieve records from the users table where the name column matches the specified name. Normally, $name would only contain alphanumeric characters or spaces, such as the string "ilia". However, here, a new query is appended to $name, which causes catastrophic issues: the injected DELETE query will delete all records in users.

Although there are database interfaces that do not allow query stacking or executing multiple queries in a single function call, attempting to stack queries will result in failure. However, SQLite and PostgreSQL still allow stacked queries, executing all queries provided in a single string, leading to severe security issues.

Preventing SQL Injection

In scripting languages like PERL and PHP, you can handle all escape characters carefully. The PHP programming language provides string functions SQLite3::escapeString($string) and sqliteescapestring() to escape special input characters for SQLite.

Note: The sqliteescapestring() function is required for PHP 5 < 5.4.0.

For newer versions PHP 5 >= 5.3.0, PHP 7, use the following function:

SQLite3::escapeString($string); //$string is the string to be escaped

The following method is not supported in the latest versions of PHP:

if (get_magic_quotes_gpc()) 
{
  $name = sqlite_escape_string($name);
}
$result = @$db->query("SELECT * FROM users WHERE username='{$name}'");

Although encoding makes data insertion safe, it presents simple text comparisons. In queries, the LIKE clause is not usable for columns containing binary data.

Note that addslashes() should not be used to quote strings in SQLite queries, as it can lead to strange results when retrieving data.

❮ Sqlite Where Clause Sqlite And Or Clauses ❯