REPLACE mySQL not appending/replacing in mySQL table

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 :)

First, you should move your `isset($_POST['submit'])` call to before your HTML output. Then, try adding some checks and breaks in there - for example, just inside the `isset()` call `print_r($_POST)` and then call `exit` to stop the script - this will show you that it is going into the if-then, and what the contents of $_POST is. For MySQL injection, you want to use prepared statements — ivanivan, Jun 12 '18 at 02:15
You're trying to get a value from $_POST['userid'] but it isn't defined. You're setting the value outside the form, if you want to get it using that variable, you have to send it as a hidden input. Also, $_POST get the value from the input's name, not their id's. Your address input doesn't have any name. — César Escudero, Jun 12 '18 at 02:18
On top of all the rest of the good comments, you aren't wrapping your string-type values in quotes. This was clearly shown in the response to your previous question. Also, if you've been warned before about SQL injection, and you're "all ears" about fixing it, why haven't you done so yet? — Greg Schmidt, Jun 12 '18 at 02:50
@ivanivan I did that, and my array returns null... there should at LEAST be userid since that is static and unchanged. - *CésarEscudero* I have ammended that in the current code as a hidden variable - *SloanThrasher* I used replace since I want to call both add to table or replace current values if present. - *GregSchmidt* I am trying to tackle getting this code working, and then mitigating injections is next ... I have wrestled this for a few days! — NRav, Jun 12 '18 at 03:45

Bill Karwin · Answer 1 · 2018-06-12T16:18:26.450

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?

REPLACE mySQL not appending/replacing in mySQL table

1 Answers1