UPDATE, field with "placeholder query" failed execution

Question

Follows the code:

$campo = $_POST['campo'];
        $valor = $_POST['valor'];
        $id = $_POST['id'];

        $mysqli = new mysqli("localhost","root","","tabela");
        mysqli_set_charset($mysqli,"utf8");
        $consulta = $mysqli -> prepare("UPDATE tabela SET ? = ? WHERE ID = ?");
        $consulta -> bind_param("sss",$campo,$valor,$id); <-------
        $consulta -> execute();

Error occurs on line with the phlegm, error in writing:

Call to a Member Function bind_param() on a non-object

Error must be occurring due to the FIELD is with placeholder(?), for I replaced the Field in writing and it worked, ie query is not made with the field with placeholder.

Only the field of my code will depend on which field the client chose to edit in the table.

Unless I turn 10 darlings (are 10 fields), and determine each field in place of the placeholder.

First, does this really occur? And if so, what can I do as good practice, for this case?

Answer 1

This is consistent with how the prepared queries work. They may have placeholders for values but not for all query syntax, otherwise it would not make sense to use a resource that just tries to cache the query (therefore it needs to be known completely) and give security (therefore it cannot allow any information to be placed in the query syntax).

I had already warned on another answer for the fact that the preprared() It’s not all that wonderful, although I didn’t say I shouldn’t use it, after all it has utility. Apparently you liked the answer better that doesn’t question the use of a resource that isn’t panacea.

I am not saying to abandon its use, just say that you will have to create a ready query for each field that can be used. Obviously in such heterogeneous consultations the performance advantage will be less utilized.

If this is too much work you can even choose query normal to do what you want "easier". But if you don’t know exactly what you’re doing to ensure that this flexibility isn’t exploited you’ll play with danger.

What you can do to facilitate the use of the prepared query is to assemble the text of the query in hand and use placeholder only where it can be used. It is a mix between the two techniques. If the list of fields that can be used is only in your code on the server and does not come from outside you will have security that there will be no code injection. Something like that:

$consulta = $mysqli->prepare("UPDATE tabela SET $campo = ? WHERE ID = ?");
$consulta->bind_param("ss", $valor, $id);

I put in the Github for future reference.

But don’t take the field of $_POST['campo'], otherwise you’ll be taking a chance, mount a array in the code and select an option according to a selection that comes from outside. Giving too much flexibility to the user compromises security.