1.42. SQLite injection

If your site allows users to enter through a web page and inserts input into a SQLite database, you face a security problem called SQL injection. This section will show you how to prevent this from happening and make sure that the script and SQLite Security of the statement.

Injection usually occurs when a user is requested for input, such as when the user is required to enter a name, but the user enters a SQLite Statement, which unknowingly runs on the database.

Never trust the data provided by the user, so only deal with validated data, which is done through pattern matching. In the following example, the user name username is limited 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[0]");
}else{
   echo "username not accepted";
}

To demonstrate this problem, consider this excerpt: 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 from the user table name A record whose column matches the name specified by the user. Under normal circumstances $name Contains only alphanumeric characters or spaces, such as strings ilia . But here, to $name An entirely new query has been appended, and this call to the database will cause a catastrophic problem: injected DELETE The query will be deleted users All the records.

Although there are already database interfaces that do not allow queries to stack or execute multiple queries in a single function call, if you try to stack queries, the call will fail, but stacked queries are still performed in SQLite and PostgreSQL, that is, all queries provided in a string are executed, which can lead to serious security problems.

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

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