Mark Amery Mark Amery - 5 months ago 11
MySQL Question

UPDATE only provided fields in MySQL table using PHP

I have a user table with and

id
field and 10 other fields storing user details of various types that the user can change via various web forms. I want to have a PHP script that gets POSTed changed values for some subset of these fields, and UPDATEs only those fields that are received in the POST data. I'm finding this surprisingly difficult to do in a way that doesn't suck. We use mysqli for all database interaction in the rest of this application so mysqli-based solutions are strongly preferred.

The options I've considered and dismissed so far:

1) Run a separate
UPDATE
query for every field provided in the POST data - yuck, I don't want to hit the database up to 10 times for something that could be done in one query.

2) Have a dictionary mapping field names to the fields' data types, and iteratively construct the query by looping through the provided fields, checking whether they are text fields or not, calling
mysqli_real_escape_string
on the string fields and otherwise sanitizing the others (e.g. by type checking or
sprintf
with '%i' placeholders). - Yuck! I could probably safely do things this way if I was careful, but I don't want to make a habit of using this kind of approach because if I'm careless I'll leave myself open to SQL injection. Parameterized queries don't give me the potential to screw up dangerously, but this approach does. My ideal is to never concatenate any data into an SQL query manually and always rely upon parameterized queries; the database libraries of other languages, like Python, let me easily do this.

3) Use a parameterized query - this is my ideal for everything, since as long as I insert all externally-provided data into my query via the
bind_param
method of a mysqli statement object, I'm immune to SQL injection and don't have to worry about sanitization, but using parameterized queries seems to be impossible here. The trouble is that
bind_param
requires that the data be passed as variables, since all arguments after the first are passed by reference. I can reasonably elegantly iteratively construct a query with
?
placeholders, and while I'm at it construct the string of types that gets passed as the first argument to
bind_param
(
'ssiddsi'
etc.), but then there's no way I can see to choose at runtime which of my 10 fields I pass to
bind_params
(unless I have a
switch
statement with 10^2 cases).

Is there some PHP language construct I'm missing (something similar to array unpacking) that will allow me to choose at runtime which variables to pass as arguments to
bind_param
? Or is there some other approach I haven't considered that will let me solve this simple problem cleanly and safely?

Answer

You can easily combine 2 and 3 by means of some helper functions.
The code will look like

$allowed = array('title','url','body','rating','term','type');
$data    = $db->filterArray($_POST,$allowed);
$sql     = "UPDATE table SET ?u WHERE id=?i";
$db->query($sql, $data, $_POST['id']);

(yup, custom placeholder support would be useful)
note that $allowed array doesn't make all these fields necessarily updated - it just filters POST fields out. So, even $_POST with only id and url would be correctly updated.

Nevertheless, using prepared statements, although toilsome, also quite possible.

Comments