SQLite injection

If your site allows users to input through web pages and insert the input content into a SQLite database, you will face a security issue called SQL injection. This section will show you how to prevent this from happening and ensure the security of your scripts and SQLite statements.

Injection usually occurs when user input is requested, such as requiring the user to enter a name, but the user enters a SQLite statement, and this statement will be run on the database unknowingly.

Never trust user-provided data, so only process data that passes validation. This rule is accomplished through pattern matching. In the example below, the username username is restricted to alphanumeric characters or underscores, and must be between 8 and 20 characters in length - 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, consider this excerpt:

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

The function call is to retrieve the name column from the user table corresponding to the name specified by the user. Matching records. Normally, $name contains only alphanumeric characters or spaces, such as the string ilia. But here, a brand new query is appended to $name, and this call to the database will cause catastrophic problems: the injected DELETE query will delete all records for users.

Although there are already database interfaces that do not allow query stacking or executing multiple queries in a single function call. If you try to stack a query, the call will fail, but SQLite and PostgreSQL still perform stacked queries, that is, execute Provide all queries in a string, which causes serious security issues.

Prevent SQL Injection

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

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 renders simple text comparisons in queries where the LIKE clause is not available for columns containing binary data.

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