Some values do not submit: two different SQL's

Question

In my last question people said that I need to use prepared statements to avoid SQL injection.

I'm changing the previous SQL's now to prepared statements, as y'all wanted.

The thing is, it submits the settings, this part:

$stmt_setsettings = mysqli_stmt_init($conn);
if (!mysqli_stmt_prepare($stmt_setsettings, $usersettings_sql)) {
  array_push($errors, "Safe SQL failed, could not insert settings. Contact the helpdesk.");
} else {
  mysqli_stmt_bind_param($stmt_setsettings, "sssss", $email_show, $fname_show, $lname_show, $private_account, $profile_style);
  mysqli_stmt_execute($stmt_setsettings);
}

But it submits none of the actual info I need (like the username, firstname, ...)

Also, at the end of the code below it should redirect to the new profile, normally if this feels it should display "Something went wrong, refer to the helpcenter. (SE100)" but it like refreshes the sign up page and throws no error, while there is an error: the not submitting info!

I tried searching up similar questions or fixes but nothing useful found.

Can you check out the following code and let me know what is the deal with the not submitting values? Thanks!

  // Finally, register user if there are no errors in the form
  if (count($errors) == 0) {
    $password = md5($password_1); // Encrypt the password before saving in the database
    
    $user_ip = $_SERVER['REMOTE_ADDR'];   // Getting the IP of the user
    $bio = $config['default-bio'];   // Setting default biography
    $profileimg = $config['default-profileimg'];   // Setting default profile image
    $timestamp = date('d.m.Y');  // Defining the current date
    $activity = "on"; // Defining which state the user profile is in, online

    $userdata_sql = "INSERT INTO users (username, bio, activity, profileimg, regdate, email, password, firstname, lastname, gender, birthday, country, ip) 
                     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        $usersettings_sql = "INSERT INTO usersettings (show_email, show_fname, show_lname, private_acc, profile_style)
    VALUES (?, ?, ?, ?, ?)";

    $stmt_signup = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt_signup, $userdata_sql)) {
      array_push($errors, "Safe SQL failed, could not sign up. Contact the helpdesk.");
    } else {
      mysqli_stmt_bind_param($stmt_signup, "sssssssssssss", $username, $bio, $activity, $profileimg, $regdate, $email, $password, $fname, $lname, $sex, $bday, $country, $user_ip);
      mysqli_stmt_execute($stmt_signup);
    }

    $stmt_setsettings = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt_setsettings, $usersettings_sql)) {
      array_push($errors, "Safe SQL failed, could not insert settings. Contact the helpdesk.");
    } else {
      mysqli_stmt_bind_param($stmt_setsettings, "sssss", $email_show, $fname_show, $lname_show, $private_account, $profile_style);
      mysqli_stmt_execute($stmt_setsettings);
    }

    session_regenerate_id();
    $_SESSION['username'] = $username;
    $_SESSION['loggedin'] = true;

        // Generate user id
        $generateid_sql = "SELECT id FROM users WHERE username=? ORDER BY id";
        $stmt_generateid = mysqli_stmt_init($conn);
        
        if (!mysqli_stmt_prepare($stmt_generateid, $generateid_sql)) {
          array_push($errors, "Safe SQL failed, could not generate a new ID. Contact the helpdesk.");
        } else {
          mysqli_stmt_bind_param($stmt_generateid, "s", $username);
          mysqli_stmt_execute($stmt_generateid);

          $generateid_result = mysqli_stmt_get_result($stmt_generateid);
        }

        while ($id = mysqli_fetch_assoc($generateid_result)) {

        if ($id['username'] <= 0) {   // Checking if the user id is a valid id (not below or equal to 0), and if not, displaying a critical error
          array_push($errors, "Something went wrong whilst signing up, please refer to the helpcenter. (SE100)");
        }
    
        if ($id['username'] > 0) {          // Redirecting the user to his or her profile if it is a valid id
          header('location: /content/users/profile?id=' . $id['username'] . '');
        }
     } 
  }
}

Answer 1

First off, PLEASE don't ever store passwords like this:

$password = md5($password_1); // <-- Totally insecure

Instead use the built-in password_hash() and password_verify() functions. See https://www.php.net/manual/en/faq.passwords.php for a good overview of why md5() is not secure and examples how to handle password storage correctly.

Also, I'd recommend pulling the user out of the database and validating the password, BEFORE setting $_SESSION['loggedin'] = true.

Regarding your question, I'd recommend adding some additional error handling and result checking around your calls to $conn->prepare() and $stmt->bind_param. See mysqli_stmt_execute() does not execute the prepared query for examples of how to check $stmt->errors.

Another general recommendation is checking $stmt->affected_rows to see if your insert statements are actually being executed as you expect. Your inserts should each be affecting 1 row.

Lastly, turning on the MySQL query log can be a great troubleshooting tool: How to show the last queries executed on MySQL? . Are all the SQL queries in your code showing up in the log? Try running the queries manually and see if the results look right.

Answer 2

// Finally, register user if there are no errors in the form
if (count($errors) == 0) {
    $password = md5($password_1); // Encrypt the password before saving in the database

    $user_ip = $_SERVER['REMOTE_ADDR'];   // Getting the IP of the user
    $bio = $config['default-bio'];   // Setting default biography
    $profileimg = $config['default-profileimg'];   // Setting default profile image
    $timestamp = date('d.m.Y');  // Defining the current date
    $activity = "on"; // Defening wich state the user profile is in, online

    $userdata_sql = "INSERT INTO users (`username`, `bio`, `activity`, `profileimg`, `regdate`, `email`, `password`, `firstname`, `lastname`, `gender`, `birthday`, `country`, `ip`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    $usersettings_sql = "INSERT INTO usersettings (`show_email`, `show_fname`, `show_lname`, `private_acc`, `profile_style`)
VALUES (?, ?, ?, ?, ?)";

    $stmt_signup = $conn->prepare($userdata_sql);
    $stmt_signup->bind_param("sssssssssssss", $username, $bio, $activity, $profileimg, $timestamp, $email, $password, $fname, $lname, $sex, $bday, $country, $user_ip);
    if(!$stmt_signup->execute()){
        array_push($errors,mysqli_error($conn));
    }

    $stmt_setsettings=$conn->prepare($usersettings_sql);
    $stmt_setsettings->bind_param("sssss", $email_show, $fname_show, $lname_show, $private_account, $profile_style);
    if(!$stmt_setsettings->execute()){
        array_push($errors,mysqli_error($conn));
    }

    session_regenerate_id();
    $_SESSION['username'] = $username;
    $_SESSION['loggedin'] = true;

    // Generate user id
    $generateid_sql = "SELECT `id`,`username` FROM `users` WHERE `username`=? ORDER BY `id` limit 1";

    $stmt_generateid=$conn->prepare($generateid_sql);
    $stmt->generateid->bind_param("s", $username);
    if(!$stmt_generateid->execute()){
        array_push($errors,mysqli_error($conn));
    }else{    
        $generateid_result = $stmt_generateid->get_result();
    }

    $username_assoc = mysqli_fetch_assoc($generateid_result);    
    if ($username_assoc['id'] > 0) {
        // Redirecting the user to his or her profile if it is a valid id
        header('location: /content/users/profile?id=' . $username_assoc['username'] . '');
    }else{
        array_push($errors, "Something went wrong whilst signing up, please refer to the helpcenter. (SE100)");
    }

}