动态(列和表)PHP选择查询安全吗? [英] is this dynamic (column & table) PHP select query safe?

问题描述

表和列的名称不能使用PDO-> bindParam()进行绑定，但是我敢肯定不止一个.有点晚了，但是我写得更早了，到目前为止它是可行的.我是php的新手，想知道您的想法以及它是否安全.

Tables and Columns names cannot be bind using PDO ->bindParam(), but I am sure more than one would love to be able to. It is a little late, but I wrote this earlier and so far it works. I am kind of new to php, and would like to know what you think and if it is safe.

$type = "defaultTableName";
$sortBy = "defaultColumnName";
$orderBy = "ASC";

//whitelisting unsafe input
if(isset($_GET['orderBy'])&&($_GET['orderBy']=="ASC"||$_GET['orderBy']=="DESC"))
    $orderBy = $_GET['orderBy'];
$tableNames = array("defaultTableName", "tableName2", "tableName3");
$unsafeType= $_GET['type']; <---unsafe input
$unsafeSortBy = $_GET['sortBy']; <---unsafe input

try {
    $pdo = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    //if input is not valid this will use default table to render a table in html.
$stmt = $pdo->prepare("DESCRIBE $type");
$stmt->execute();
$table_fields = $stmt->fetchAll(PDO::FETCH_COLUMN);

 //Whitelisting user input against table names (will require table names updates)
    if (in_array($unsafeType, $tableNames)) {
    $stmt = $pdo->prepare("DESCRIBE $unsafeType");
    $stmt->execute();
    $table_fields = $stmt->fetchAll(PDO::FETCH_COLUMN);

 ///Whitelisting the column name to sort by against the description of the table.
        if (in_array($unsafeSortBy, $table_fields)) {
        $stmt = $pdo->prepare("SELECT * FROM $unsafeType ORDER BY $unsafeSortBy $orderBy");
    }
    else    {
        $stmt = $pdo->prepare("SELECT * FROM $type ORDER BY $sortBy $orderBy");
    }
} else {
    $stmt = $pdo->prepare("SELECT * FROM $type ORDER BY $sortBy $orderBy");
}
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
 }

我看到的唯一问题是，更改表时需要添加/删除/更改表名数组.我想到的是中小型应用程序，不是很复杂.

Only problem I see is that you will need to add/delete/change the table name array when you change the tables. I have a small/medium application in mind, not very complex.

注意:在stackoverflow中，我的编辑也很糟糕，因此，如果您知道一种使其变得更好的方法，请继续编辑或让我知道.

Note: I am also terrible editing in stackoverflow, so if you know a way to make it nicer go ahead and edit or let me know.

推荐答案

否.不安全您将用户提交的数据直接放入查询字符串中. 随时，您这样做很容易受到sql注入攻击.

No. It's not safe. You're directly placing user-submitted data into the query string. ANYTIME you do that you're vulnerable to sql injection attacks.

但是，由于您不能将占位符用于这些特定值，因此您必须使用pdo::quote自己对数据进行转义，例如

However, since you can't use placeholders for those particular values, you'll have to escape the data yourself with pdo::quote, e.g.

$safeType = $pdo->quote($_GET['type']);

仅因为它是表名或sort-by子句值并不意味着它不能被注入.进入未经引号/转义或未通过占位符插入的字符串的任何用户数据都是攻击向量.

just because it's a table name or a sort-by clause value doesn't mean it can't be injected. ANY user data going into a string that's not been quoted/escaped or inserted via placeholders is an attack vector.

这篇关于动态(列和表)PHP选择查询安全吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！