PHP - Multiple bind_params Failing

Question

I'm trying to execute delete SQL statement upon button press, which was working if I binded 1 parameter, but I want to make the delete.php generic, as not to have multiple of them just for referencing different tables:

<?php
include "header.php";
include "db.php";

$_POST['table'] = "customer";
$_POST['column'] = "cID";
$_POST['del_id'] = 26;

if(isset($_POST['del_id']))
{
    if ($stmt = $conn->prepare("DELETE FROM customer WHERE ? = ?"))
    {
        $stmt->bind_param('si', $_POST['column'], $_POST['del_id']);
        $stmt->execute();
    }
    else echo("Oops");
}

This binding executes but doesn't do anything to the table, only binding the final value 'del_id', executes correctly, and binding 3 arguments including the table name, just causes prepare() to fail.

I am setting the _POST vars in other places from AJAX POSTs, above is just for testing that this bit works or not. I also haven't gotten round to doing validation yet before that comes up.

Very PHP nooby, likely a simple mistake, or just something I'm not aware of, in which case I'd be rather curious as to why the table/column names can't be parameterised, as it's been eluding me for some time. As a workaround, would some form of concatenation work instead, to be able to drop dynamic names into this query from multiple different places?

You cannot bind column names and table names too. – u_mulder Apr 04 '17 at 21:26 — u_mulder, Apr 04 '17 at 21:26

score 0 · Answer 1 · answered Apr 04 '17 at 21:32

Yes, to achieve this you will need to have a combination of concatenation and parameters for the Prepared Statement:

if ($stmt = $conn->prepare("DELETE FROM " . $_POST['table'] . " WHERE " . $_POST['column'] . " = ?"))
    {
        $stmt->bind_param('si', $_POST['del_id']);
        $stmt->execute();
    }

Make sure you pay attention on additional validations you will need on the table and column names. This should be validated against your data model and not just making sure its a valid identifier. Further, take a look at some ORMs/Query Builders to learn how to elaborate upon this idea. It's a good learning exercise.

score 0 · Accepted Answer · answered Apr 04 '17 at 21:33

You should validate both the table name and column name before running the delete.

Since you can't prepare either the table or column names, just put them in the sql statement before the prepare.

<?php
    include "header.php";
    include "db.php";

    $_POST['table'] = "customer";
    $_POST['column'] = "cID";
    $_POST['del_id'] = 26;

    //  Add code to prevent SQL injection
    $table = $_POST['table'] == 'customer' ? $_POST['table'] : '';
    $column = $_POST['column'] == 'customer' ? $_POST['column'] : '';

    if(isset($_POST['del_id']) && $table != '' && $column != '') {
        if ($stmt = $conn->prepare("DELETE FROM `".$table."` WHERE `".$column."` = ?"))
        {
            $stmt->bind_param('i', $_POST['del_id']);
            $stmt->execute();
        }
        else echo("Oops");
    }

Thanks! Didn't know you couldn't prepare table/column names. — kingtaco, Apr 07 '17 at 11:30

PHP - Multiple bind_params Failing

2 Answers2