即使在使用 SQLite3::escapeString() 并且不要求用户输入时也存在 SQL 注入漏洞? [英] Vulnerability to SQL injection even when SQLite3::escapeString() is used and no user input is asked?

问题描述

我指的是我对另一个问题的回答，另一位用户批评它因为容易受到 SQL 注入，甚至如果没有请求用户输入并调用转义程序.以下代码用于创建 SQLite 数据库的 .sql 转储，仅使用 PHP 代码，不调用 sqlite3 工具(这是问题作者的原始请求).

I am referring to this answer of mine to another question, which another user criticized because vulnerable to SQL injection, even if no user input is requested and escape procedure is called. The following code is used to create a .sql dump of an SQLite database, using only PHP code with no call to sqlite3 tool (which was the original request of the author of the question).

<?php

$db = new SQLite3(dirname(__FILE__)."/your/db.sqlite");
$db->busyTimeout(5000);

$sql="";

$tables=$db->query("SELECT name FROM sqlite_master WHERE type ='table' AND name NOT LIKE 'sqlite_%';");

while ($table=$tables->fetchArray(SQLITE3_NUM)) {
    $sql.=$db->querySingle("SELECT sql FROM sqlite_master WHERE name = '{$table[0]}'").";\n\n";
    $rows=$db->query("SELECT * FROM {$table[0]}");
    $sql.="INSERT INTO {$table[0]} (";
    $columns=$db->query("PRAGMA table_info({$table[0]})");
    $fieldnames=array();
    while ($column=$columns->fetchArray(SQLITE3_ASSOC)) {
        $fieldnames[]=$column["name"];
    }
    $sql.=implode(",",$fieldnames).") VALUES";
    while ($row=$rows->fetchArray(SQLITE3_ASSOC)) {
        foreach ($row as $k=>$v) {
            $row[$k]="'".SQLite3::escapeString($v)."'";
        }
        $sql.="\n(".implode(",",$row)."),";
    }
    $sql=rtrim($sql,",").";\n\n";
}
file_put_contents("sqlitedump.sql",$sql);

在对此答案的评论中，用户@Dharman 坚称此代码易受攻击，并且在要求提供一个完整示例说明它如何导致问题后，他告诉我就此事提出一个问题.

In the comments to this answer, user @Dharman insisted this code is vulnerable, and after asking to provide a full example of a case of how it could lead to problems, he told me to just open a question regarding the matter.

我个人觉得这段代码不可能爆炸"，因为数据库中已经存在要转储的内容，但我没有权限.所以我问你.

I personally feel there is no way this code could "explode" because of the contents already present inside the database to be dumped, but I'm no authority. So I ask you instead.

推荐答案

在电子商务应用程序中将表命名为 Order 是很合理的，但是如果您运行查询，这会导致语法错误喜欢:

It's pretty reasonable to name a table Order in an e-commerce application, but this causes a syntax error if you run a query like:

SELECT * FROM Order

为什么?因为 Order 是 SQLite 中的保留关键字.它引入了 ORDER BY 子句.以这种方式使用名为 Order 的表只会产生语法错误.

Why? Because Order is a reserved keyword in SQLite. It introduces an ORDER BY clause. Using a table named Order in this way just creates a syntax error.

SQLite 允许您通过分隔表名来以保留字命名表.

SQLite allows you to name tables after reserved words by delimiting the table name.

这是在我链接到的关于保留字的文档中:

This is in the documentation I linked to about reserved words:

'keyword' 单引号中的关键字是字符串字面量.

'keyword' A keyword in single quotes is a string literal.

关键字"双引号中的关键字是标识符.

"keyword" A keyword in double-quotes is an identifier.

[keyword] 用方括号括起来的关键字是一个标识符.这不是标准的 SQL.这种引用机制由 MS Access 和 SQL Server 使用，并包含在 SQLite 中以实现兼容性.

[keyword] A keyword enclosed in square brackets is an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility.

`keyword` 用重音符号(ASCII 代码 96)括起来的关键字是一个标识符.这不是标准的 SQL.这种引用机制由 MySQL 使用，并包含在 SQLite 中以实现兼容性.

`keyword` A keyword enclosed in grave accents (ASCII code 96) is an identifier. This is not standard SQL. This quoting mechanism is used by MySQL and is included in SQLite for compatibility.

因此您可以使用名为 Order 的表而不会出现这样的错误:

So you can use a table named Order without error like this:

SELECT * FROM "Order"

你不应该使用 SQLite3::escapeString($table[0]) 因为那是用于字符串文字的.从上面的列表中记住，单引号字符串是字符串文字.escapeString() 函数只转义 ' 字符，因此您可以将该字符串放在单引号内作为分隔符，因此您可以使用像 'O\'Reilly'.

You shouldn't use SQLite3::escapeString($table[0]) because that's for string literals. Remember from the list above, single-quoted strings are string literals. The escapeString() function only escapes ' characters, so you can put that string inside single-quotes as the delimiter, and thus you can use strings like 'O\'Reilly'.

但escapeString() 对用于分隔表名的双引号没有任何作用.没有为此提供功能.因此，您必须确保没有包含 " 字符的表名，否则使用其他 PHP 函数来转义它们.

But escapeString() doesn't do anything for the double-quotes used for delimiting table names. There isn't a function provided for that. So you either have to be sure you have no table names that contain a " character, or else use other PHP functions to escape them.

$safeTable = str_replace('"', '\\"', $table[0]);
$rows = $db->query("SELECT * FROM \"{$safeTable}\"");

您还应该阅读8.在此页面上接受双引号字符串文字:https://www.sqlite.org/quirks.html

这篇关于即使在使用 SQLite3::escapeString() 并且不要求用户输入时也存在 SQL 注入漏洞?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！