如何在php中的Postgresql中使用白名单和预准备语句？ [英] How to use whitelists and prepared-statements with Postgresql in php?

问题描述

我了解我需要在我的php代码中实现白名单和预准备语句。但是我不确定如何用Postgresql做到这一点，对我的代码来说真的有必要吗？我正在使用选择列表将用户选择的值传递给查询。

I understand that I need to implement whitelists and prepared-statements into my php code. But I'm not sure how to do this with Postgresql and is it really necessary for my code? I'm using select lists to pass the users selected values to the query.

<?php

include "connect.php";

$table          = $_POST['tableSelected'];
$field          = $_POST['fieldSelected'];
$attribute      = $_POST['attributeSelected'];
$operator       = $_POST['operatorSelected'];
$fieldList      = $_POST['fieldList'];

$fieldstr = $fieldList . ",ST_AsGeoJSON(ST_Transform(l.geom,4326))";


$sql = "SELECT $fieldstr
        FROM $table l";

if (!empty($field) && !empty($operator) && !empty($attribute)) {
    $sql .= " WHERE {$field} {$operator} '{$attribute}'";
}

echo ($sql);

?>

推荐答案

白名单

当前格式的代码非常危险，不仅允许用户决定应选择哪些字段，还允许他决定要查询哪些表。您绝对应该对此进行白名单检查。例如：

White Listing

Your code in it's current form is very dangerous, not only do you allow the user to decide what fields should be selected but you also allow him to decide what tables to query on. You should definitely carry out white list checking on these. eg:

if($_POST['tableSelected'] == 'acceptable_table1' || $_POST['tableSelected'] == 'acceptable_table2) {
    $table = $_POST['tableSelected']
}

类似地，您应该验证字段列表。但是字段列表验证将变得相当复杂，因为您的字段将依赖于表。我建议创建数组并检查选择项。

Similarly you should validate the field lists. But field list validation is going to be rather complicated because your fields are going to be dependent on the table. I suggest creating arrays and checking that the selection is in it.

$table1_fields = array('col1','col2',...)
$table2_fields = array('col1','col2',...)

准备好的语句

您知道，准备好的语句只能用于绑定参数。它们不能用于填写表名和列名。因此，您既需要准备好的陈述，又需要白名单。我建议使用 PDO 。可能看起来像这样

Prepared Statements

As you know prepared statements can only be used to bind parameters. They cannot be used to fill in table names and column names. That's why you need both prepared statements and white listing. I recommend using PDO. It might look something like

$stmt = $dbh->prepare("SELECT {$fieldlist} FROM {$table} where field = ?");
$stmt->execute(array('somevalue'));

这篇关于如何在php中的Postgresql中使用白名单和预准备语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！