我有两个问题与PHP相同的pgsql查询

Question

I am using drop down menu to create a user generate pgsql query. It works perfectly, however, I am now trying to convert it to work with multiple selections. I have run into 2 separate issues. When I add multiple to it is no longer a drop down list, it behaves more like a scrolling wheel. As a note, the options are populated by an initial db query.

<select multiple name="userSite" class="form-dropdown validate[required]">

-While I am able to select multiple options, the query is only returning the first selected, rather than the results for all of the selected options.

ini_set('error_reporting', E_ALL);
ini_set("display_errors", 1);

$site= $_POST["userSite"];
$datea= $_POST["userDatea"];
$table= $_POST["userTable"];
$datez= $_POST["userDatez"];

// You need to do all of this if and only if this is a post request
// Also this method of detecting a post request is more consistent
if( !empty($_SERVER['REQUEST_METHOD']) && (strcasecmp($_SERVER['REQUEST_METHOD'], 'post')===0)  ) {
    // Create connection
    $conn = pg_connect("host=xxxxxxxxxx port=xxxx dbname=db user=xxx password=mypassword");

    // Check connection
    if (!$conn) {
        echo "Did not connect.
";
        exit;
    }

    $result = pg_query($conn,
        "SELECT *
        FROM 
        db.$table
        WHERE 
        $table.site_id = '$site' AND
        $table.created_on BETWEEN '$datea' AND '$datez' AND
        $table.soft_delete_id = '0';");

if (!$result) {
echo "Query failed.
";
exit;
}
$num_fields = pg_num_fields($result);
$headers = array();

for ($i = 0; $i < $num_fields; $i++) 
{
    $headers[] = pg_field_name($result , $i);
}

$fp = fopen('php://output', 'w');
if ($fp && $result)
{
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="customreport.csv"');
        header('Pragma: no-cache');
        header('Expires: 0');
        fputcsv($fp, $headers);

        while ($row = pg_fetch_row($result)) 
        {
             fputcsv($fp, array_values($row));
        }
        die;

}
    exit('It works');
}

Answer 1

As humbolight suggested, you need to set your name attribute to name="userSite[]". But, that is only half the problem. The other issue is with your database query. You can't search for multiple sites with your query and it opens your code up to SQL injection.

Try this instead:

$site= $_POST["userSite"]; // this will be an array.
$datea= $_POST["userDatea"];
$datez= $_POST["userDatez"];
$table= $_POST["userTable"];

// verify that $table is a valid name since it can't be parameterized in the query.

if( $table !== 'my_table'
    || $table !== 'my_other_table' ){
    // exit due to possible sql injection.
    exit();
}

$params = array();
$params[] = $datea; // $1
$params[] = $datez; // $2

// generate the site query params
// we need a string like this: $3, $4, $5, etc. (one for each site selected)
$site_param_ids = array();
foreach($site as $s){
    $params[] = $s;
    $site_param_ids[] = '$' . count($params);
}
$site_param_ids = implode(', ', $site_param_ids);

$sql = "SELECT
            *
        FROM db.$table t
        WHERE t.soft_delete_id = '0'
            AND t.created_on BETWEEN $1 AND $2
            AND t.site_id in ($site_param_ids);";
//

$result = pg_query_params($conn, $sql, $params);