Sqlite 简明教程
SQLite - Injection
如果你通过网页接受用户输入并将其插入到 SQLite 数据库中,那么你很可能会让自己面临一个名为 SQL 注入的安全问题。在本章中,你将学习如何防止这种情况的发生,并帮助你保护你的脚本和 SQLite 语句。
If you take user input through a webpage and insert it into a SQLite database there’s a chance that you have left yourself wide open for a security issue known as SQL Injection. In this chapter, you will learn how to help prevent this from happening and help you secure your scripts and SQLite statements.
注入通常发生在你询问用户信息(例如姓名)时,他们没有提供姓名,而是提供了一个你不知道会在你的数据库上运行的 SQLite 语句。
Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a SQLite statement that you will unknowingly run on your database.
切勿信任用户提供的数据,仅在验证后处理这些数据;通常通过模式匹配来完成此操作。在以下示例中,用户名限制为字母数字字符和下划线,长度在 8 到 20 个字符之间 - 根据需要修改这些规则。
Never trust user provided data, process this data only after validation; as a rule, this is done by pattern matching. In the following example, the username is restricted to alphanumerical chars plus underscore and to a length between 8 and 20 chars - 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}'");
该函数调用应该从用户表中检索一条记录,其中 name 列与用户指定的 name 匹配。在正常情况下, $name 仅包含字母数字字符和空格(例如字符串 ilia)。但是,在这种情况下,通过将一个全新的查询附加到 $name,对数据库的调用就变成了灾难:注入 DELETE 查询会删除 users 中的所有记录。
The function call is supposed to retrieve a record from the users table where the name column matches the name specified by the user. Under normal circumstances, $name would only contain alphanumeric characters and perhaps spaces, such as the string ilia. However in this case, by appending an entirely new query to $name, the call to the database turns into a disaster: the injected DELETE query removes all records from users.
有些数据库接口不允许查询堆叠或在一个函数调用中执行多个查询。如果你尝试堆叠查询,则调用会失败,但 SQLite 和 PostgreSQL 会愉快地执行堆叠查询,执行一个字符串中提供的所有查询,并造成严重的安全问题。
There are databases interfaces which do not permit query stacking or executing multiple queries in a single function call. If you try to stack queries, the call fails but SQLite and PostgreSQL, happily perform stacked queries, executing all of the queries provided in one string and creating a serious security problem.
Preventing SQL Injection
你可以在 PERL 和 PHP 等脚本语言中智能地处理所有转义字符。编程语言 PHP 提供了 string sqlite_escape_string() 函数来转义 SQLite 认为特殊 的输入字符。
You can handle all escape characters smartly in scripting languages like PERL and PHP. Programming language PHP provides the function string 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}'");
尽管编码可以安全地插入数据,但它会使在包含二进制数据的列中进行简单的文本比较和 LIKE 子句变得无法使用。
Although the encoding makes it safe to insert the data, it will render simple text comparisons and LIKE clauses in your queries unusable for the columns that contain the binary data.
Note − addslashes() 不应用于在 SQLite 查询中引用你的字符串;检索数据时会导致奇怪的结果。
Note − addslashes() should NOT be used to quote your strings for SQLite queries; it will lead to strange results when retrieving your data.