SQLite Injection

If your site allows users to input through web pages and insert the input content into the SQLite database, you will then face a security issue known as SQL injection. This chapter will explain how to prevent this situation from occurring and ensure the security of scripts and SQLite statements.

Injection usually occurs when requesting user input, such as when the user is required to enter a name, but the user enters an SQLite statement, which will run on the database without being noticed.}}

Never trust data provided by users, so only process validated data. This rule is completed through pattern matching. In the following example, the username username is restricted to alphanumeric characters or underscores, and the length must be 8 and 20 characters between - Please 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 the problem, please see the following excerpt-

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

The function call is to retrieve the record from the user table that matches the name column with the name specified by the user. Normally,$name Only contain alphanumeric characters or spaces, such as the string ilia. But here, a new query is appended to $name, and this call to the database will cause catastrophic problems: the injected DELETE query will delete all records from the users table.

Although there are database interfaces that do not allow query stacking or the execution of multiple queries in a single function call, and attempts to stack queries will fail, SQLite and PostgreSQL still perform stacked queries, that is, executing all queries provided in a single string, which can lead to serious security issues.

to prevent SQL injection

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

Note: Use the function sqlite_escape_string() The PHP version required is PHP 5 < 5.4.0.

Higher version PHP 5 >= 5.3.0, PHP 7 Use the following function:

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

Although encoding makes inserting data safe, it presents simple text comparisons in queries, for columns containing binary data,LIKE The clause is not available.

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