SQLite Injection

If your site allows users to input data through web pages and inserts that input into an SQLite database, then you face a security issue known as SQL injection. This chapter will explain how to prevent this from happening and keep your scripts and SQLite statements secure.

Injection typically occurs when user input is requested, such as when a user is asked to enter their name, but instead enters an SQLite statement, which unknowingly runs on the database.

Never trust data provided by users, so only process data that has been validated. This rule is accomplished through pattern matching. In the example below, the username is restricted to alphanumeric characters or underscores, and its length must be between 8 and 20 characters - modify 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 this 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 name specified by the user. Under normal circumstances,$nameit would only contain alphanumeric characters or spaces, such as the string ilia. But here, an entirely new query is appended to $name. This database call will cause a disastrous problem: the injected DELETE query will delete all records from users.

Although there are already database interfaces that do not allow query stacking or executing multiple queries in a single function call, and the call will fail if you attempt to stack queries, SQLite and PostgreSQL still allow stacked queries, meaning all queries provided in a single string are executed, which can lead to serious security issues.

Preventing SQL Injection

In scripting languages such as PERL and PHP, you can cleverly handle all escape characters. The programming language PHP provides a string functionSQLite3::escapeString($string)andsqlite_escape_string()to escape input characters that are special for SQLite.

Note: The PHP version required to use the functionsqlite_escape_string()isPHP 5 < 5.4.0。

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

SQLite3::escapeString($string);//$string为要转义的字符串

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 inserted data safe, it presents simple text comparison. In queries, for columns containing binary data,LIKEthe clause is not usable.

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