Mark Amery Mark Amery - 1 year ago 54
MySQL Question

UPDATE only provided fields in MySQL table using PHP

I have a user table with and

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
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
on the string fields and otherwise sanitizing the others (e.g. by type checking or
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
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
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
etc.), but then there's no way I can see to choose at runtime which of my 10 fields I pass to
(unless I have a
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
? Or is there some other approach I haven't considered that will let me solve this simple problem cleanly and safely?

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download