安全地转义表名/列名 [英] safely escaping table names/column names

问题描述

我在php中使用PDO，因此无法使用准备好的语句对表名或列名进行转义.以下是自己实现的简单方法:

I'm using PDO in php and as such can't escape table names or column names using prepared statements. Would the following be a foolproof way to implement it myself:

$tn = str_replace('`', '', $_REQUEST['tn']);
$column = str_replace('`', '', $_REQUEST['column']);
$sql = "SELECT * FROM `tn ` WHERE `column` = 23";
print_r(
    $pdo->query($sql)->fetchAll()
);

还是还有一些可以被攻击的途径?

Or is there still some avenue that this can be attacked?

推荐答案

您可以通过询问数据库哪些列对于给定的数据库表有效来使用动态白名单.这是一个附加的sql查询，但是安全性很好.

You can use a dynamic white list by asking the database what columns are valid for a given database table. It's an additional sql query, but safety is good.

select COLUMN_NAME 
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_SCHEMA = :databaseName
  and TABLE_NAME = :tableName

获取该结果，然后只需确保所有动态列名称都在结果集中.

Fetch the results of that and then just make sure all the dynamic column names are in the result set.

我认为INFORMATION_SCHEMA.COLUMNS中包含视图，因此应该只是简单的工作.

I believe views are included in INFORMATION_SCHEMA.COLUMNS, so it should all just plain work.

然后，在组装动态sql时，请在经过验证的列名周围使用反引号(我假设您使用的是纯ascii列名，否则可能会有其他注意事项).

Then just use backticks around the validated column names when assembling the dynamic sql(I assume you use purely ascii column names, otherwise you potentially have additional considerations).

这篇关于安全地转义表名/列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！