SQLite Injection | Tutorial

Tutorial -- Learning is not just about technology, but also about dreams!

SQLite Injection

If your site allows users to enter content through a webpage and inserts that content into an SQLite database, you are facing a security issue known as SQL injection. This chapter will explain how to prevent this from happening and ensure the security of your scripts and SQLite statements.

Injection typically occurs when user input is requested, for example, when a user is asked for their name, but they enter an SQLite statement instead, which then runs unknowingly on the database.

Never trust user-provided data, so only process data that has been validated. This rule is accomplished through pattern matching. In the following example, the username is restricted to alphanumeric characters or underscores, and the length must be between 8 and 20 characters - 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");
}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 name specified by the user. Normally, $name would only contain alphanumeric characters or spaces, like the string "ilia". However, in this case, a completely new query has been appended to $name. This call to the database will cause a catastrophic problem: the injected DELETE query will delete all records from the users table.

While there are database interfaces that do not allow query stacking or executing multiple queries in a single function call (which will fail if stacking is attempted), SQLite and PostgreSQL still allow query stacking, meaning all queries provided in a string are executed. This leads to serious security issues.

Preventing SQL Injection

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

Note: The function sqlite_escape_string() requires PHP version PHP 5 < 5.4.0.

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

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

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 inserting data safe, it results in simple text comparisons. In queries, 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, as it will cause strange results when retrieving data.

SQLite Autoincrement

SQLite Explain

iFlytek Xingchen Coding Plan includes free model call quotas, DeepSeek, GLM, Kimi, MiniMax, one-stop experience and deployment platform. Configuration Guide ¥3.9/month Subscribe Now