在mySQL表中替换mySQL而不是追加/替换

Question

I have a 3 parameters which should append new/update old entries to a custom mysql table. However, I cannot figure out WHY when I press the submit button ... nothing happens (nor do I get any errors). I am at a loss for what to do. I have asked this question before and have modified my code a bit based on other tutorials thinking that was my issue... no luck :(

I understand that there are concerns for mysql injections - presently I'd just like to see it work and if you have suggestions for mitigating injections I am all ears. I am still a novice at mySQL... but learning slowly and understand (minimally) how string variables can be used to create altered queries.

Here is my code;

echo "<p><h5>Change address:</h5>";

//get user id when the login/visit page
$userid = get_current_user_id();


$loop = new WP_Query( $args );

//form start
echo '<form method = "post" action = "'. $_SERVER['PHP_SELF'] .'">';

//dropdown menu for collecting SKU of product
echo '<br><select name="sku">';
    echo '<option>-- Select product--</option>';
while ( $loop->have_posts() ) : $loop->the_post();
  global $product;
    echo '<option value=' . $product->get_sku() . '>' . $product->get_sku() . ' </option>';
  endwhile;
echo '</select>';

//hidden input for userid 
echo '<input type="hidden" id="userid" name="userid" value="' . $userid . '">'; 

//textbox for address
echo '<br><input type="text" value="Insert new address here" id="address" name="address" size="40" />';

//submit button
echo '<br><input type="submit" name="submit">';
echo '</form>';

//write to database
if(isset($_POST['submit'])) {

  $user = $_POST['userid'];
  $sku = $_POST['sku'];
  $address = $_POST['address'];

  $con2 = mysqli_connect("IP","user","password","wpdb");
  $updateaddress = "REPLACE INTO wp_newaddress(user, sku, address) VALUES($user, $sku, $address)";
  $retval = mysqli_query($con2,$updateaddress);
  if($retval)
   {
       echo 'Data Updated';
   }else{
       echo 'Data Not Updated';
   }
   mysqli_close($con2);
   }

Thanks :)

Answer 1

You need to use prepare and execute with bound parameters to avoid the SQL injection risk.

You need to check for error conditions after every prepare and execute, and output any errors to your error log. You won't see errors if you don't do this.

Of course you should also watch your PHP error log (which is typically the same as your http server error log), but this goes without saying. Every PHP developer should be watching the error log (even though many developers don't know this).

Here's an example:

$user = $_POST['userid'];
$sku = $_POST['sku'];
$address = $_POST['address'];

$con2 = mysqli_connect("IP","user","password","wpdb");

$updateaddress = "REPLACE INTO wp_newaddress (user, sku, address) VALUES (?, ?, ?)";

$stmt = mysqli_prepare($con2,$updateaddress);

if ($stmt) {
  mysqli_stmt_bind_param($stmt, 'sss', $user, $sku, $address);

  $ok = mysqli_stmt_execute($stmt);

  if ($ok) {
    echo 'Data Updated';
  } else {
    echo 'Data Not Updated';
    error_log(mysqli_stmt_error($stmt));
  }

  mysqli_stmt_close($stmt);
} else {
  error_log(mysqli_error($con2));
}

mysqli_close($con2);

Also read answers in How can I prevent SQL injection in PHP?