Error on dynamic query built on array

Question

Alright, So I am trying to make a query that searches the table PRIV for any columns that were selected in the checkboxs that = Yes or No.

Here is the code.

if(isset($_POST['submit']))
    {       
        $fini = $_POST['chk'];
        $fila = $_POST['ends'];
        $qMarks = str_repeat('?,', count($fini) - 1) . '?';
        $stmt = $con->prepare("SELECT * FROM priv WHERE `$qMarks` = `$fila`");
        $stmt->execute($fini);
        while($myR=$stmt->fetch(PDO::FETCH_ASSOC))
        {
            echo $myR['ident'];
            echo "<br>";    
        }
    }

As you can see, $fini represents the checkboxs in an array form that were selected.. the possible numbers in $fini are op1, op2 all the way through op24

$fila represents a simple Yes or No Selector..

For instance.. If I was to select checkbox 2 and 3 then $fini array would be op2, op3 and if I selected enabled on the selector then $fila would be Yes

This is the error I am getting.

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column ''op1'' in 'where clause'' in

It's saying unknown columns op1.. Which makes no sense because I have columns op1 - op24

Could someone please help me with this.

Basically, $fini is telling it what columns to look in to see if it equals $fila which is either Yes or No — Kmiles1990123, Mar 03 '16 at 19:33
You really, **really** need to consider white-listing those two variables. Putting `$_POST` data in a query is extremely dangerous. — tadman, Mar 03 '16 at 19:34
@tadman I know, I was using placeholders at first and I still will. But I couldnt get it to work so I thought it was a issue with my binding.. So I took that out to test.. Now I still cant get it to work — Kmiles1990123, Mar 03 '16 at 19:35
So you think that `SELECT * FROM priv WHERE ? ? ? ? ? = ` is a real __working__ query? — u_mulder, Mar 03 '16 at 19:36
@u_mulder what makes no sense though is, If I only select one checkbox and then select enabled its still saying that column op1 does not exist.. Which it clearly does — Kmiles1990123, Mar 03 '16 at 19:37
[You can't bind column names with PDO](http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter) so you're stuck with white-listing them. Usually this is easiest if you have an array of valid values. — tadman, Mar 03 '16 at 19:44
I do have an array with correct column names.. Yet it is still not working — Kmiles1990123, Mar 03 '16 at 19:45
Could you provide a var_dump output of `$_POST['chk'];` and `$_POST['ends'];` for some example? — trincot, Mar 03 '16 at 20:14

score 0 · Answer 1 · answered Mar 03 '16 at 20:49

The error message shows that the 'op1' column (with quotes) is being looked for, which obviously does not exist. The quotes are there because you passed these values as string parameters.

But that could never work. Parameters are intended to pass literal values, not column names.

The thing you want to do does not go well with parameters. Instead I would suggest the following code. Comments should clarify each step:

// Generate array of valid column names
$columns = array_map(function ($i) { return "op$i"; }, range(1,24));

// Ensure the provided data is acceptable -- remove what is not:
$fila = $_POST['ends'] == 'Yes' ? 'Yes' : 'No'; // only Yes or No are allowed
$fini = array_intersect($columns, $_POST['chk']); // only real column names are allowed

// You need to treat the case when no checkboxes are checked: 
if (count($fini) == 0) die("You need to check at least one checkbox.");

// Make $fila also an array with just as many elements as $fini, and wrap 
// each value (Yes or No) in quotes:
$fila = array_fill(0, count($fini), "'$fila'");

// turn both arrays into comma-separated strings, and compare tuples
$sql = "SELECT * FROM priv 
        WHERE (" . implode(',', $fini) . ") = (" . implode(',', $fila) . ")";

$stmt = $con->prepare($sql);
$stmt->execute(); // no parameters
// ... etc...

Considerations

It is not so good practice to design a database table with 24 columns which serve a similar purpose. Instead you should normalise your database, and put these values in rows instead of in columns.

Error on dynamic query built on array

1 Answers1

Considerations