UPDATE, field with placeholder query failed to execute

Question

UPDATE, field with placeholder query failed to execute

Navigation

#1 by (2 votes)

2

Follow 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 the line with the arrow, written error:

bind_param () on a non-object

Error must be occurring because the FIELD is with placeholder (?), since I replaced the field in writing and it worked, ie the query is not done 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 do 10 queries (there are 10 fields), and I determine each field in place of the placeholder .

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

php mysql sql sql-update placeholder

asked by anonymous 20.01.2015 / 01:38

1 answer

Vertical-align does not line li's Audit change in Java + Spring + Oracle application

score 2 · Accepted Answer

This is consistent with the operation of the prepared queries. They may have placeholders for values but not for the entire query syntax, otherwise it would not make sense to use a feature that just tries to cache the query (so it needs to be known by complete) and give security (so it can not allow any information to be placed in the query syntax).

I've already warned in another answer for the fact that preprared() is not this whole wonder , although I did not say that I should not use it, after all it has utility. Apparently you liked the answer more that does not question the use of a non-panacea feature.

I'm not saying to give up your use, just say that you will have to create a prepared query for each field that can be used. Obviously in such heterogeneous queries the performance advantage will be less utilized.

If this is too much work you can even opt for query to do what you want in an "easier" way. But if you are not sure what you are doing to ensure that this flexibility is not exploited you will play with the danger.

What you can do to make it easier to use the prepared query is to assemble the query text at hand and use placeholder only where it can be used. It's 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 this:

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

But do not get the $_POST['campo'] field, otherwise you'll be risking, mount an 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.