0

I'm upgrading some old legacy mysql scripts and upgrading to mysqli.

I have a table with editable form fields in each cell and passing those variables (onBlur) via AJAX to the following page:

$stmt = mysqli_prepare($conn, "UPDATE CONTACTS SET ? = ? WHERE CONTACTID = ? LIMIT 1");
mysqli_stmt_bind_param($stmt , "ssi", $_GET['column'], $_GET['editval'], $_GET['id']);
mysqli_stmt_execute($stmt);

After reading up, I find you can't bind a parameter to define a column within the statement so this gives me a problem.

How do I use AJAX $_GET variables to identify which column needs updating? I don't particularly want to have to write individual AJAX scripts for each column.

Richard Owens
  • 155
  • 16
  • 2
    You use a whitelist to define possible columns. – user3783243 Oct 06 '21 at 11:29
  • 2
    (I would use `in_array` rather than long block of conditions) – user3783243 Oct 06 '21 at 11:31
  • 1
    @user3783243 Thanks - just found this based on your comment. https://stackoverflow.com/questions/41598080/mysqli-prepared-statement-column-with-variable. Array route seems neater. I'll add full script when I've tested it. – Richard Owens Oct 06 '21 at 11:33
  • Final code as follows: - $column = $_GET['column']; // define "white list" $allowed = ['COMPANY', 'ACCMAN', 'FORENAME', 'SURNAME', 'DEAR', 'EMAIL', 'COMPTYPE', 'PRE', 'OPTOUT', 'OPTIN', 'OPTINDATE']; // Check the input variable against it if (!in_array($column, $allowed)) { throw new Exception("Invalid column name"); } $rs_update = mysqli_prepare($conn, "UPDATE CONTACTS SET $column = ? WHERE CONTACTID = ? LIMIT 1"); mysqli_stmt_bind_param($rs_update, "si", $_GET['editval'], $_GET['id']);// bind parameters mysqli_stmt_execute($rs_update);// execute query – Richard Owens Oct 06 '21 at 11:52

0 Answers0