$smt->get_result() gives empty result after MySQL (only) INSERT

Question

I'm doing a user registration in PHP and I'm trying to check, if user account after INSERT was actually created. What am I doing wrong? $dataR variable returns nothing, so after every registration, account is created, but the script still returns "Sorry, your registration failed. Please go back and try again."

Thanks for response, feel free to ask!

Connection:

$this->db_connection = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

Register.php:

$username = $this->db_connection->real_escape_string(strip_tags($_POST['username'], ENT_QUOTES));
$useremail = $this->db_connection->real_escape_string(strip_tags($_POST['email'], ENT_QUOTES));
$password = $_POST['password_new'];
$options = [
    'cost' => 10,
];
$salt = $this->random_str(64);
$salted_password = $password . $salt;
$password_hash = password_hash($salted_password, PASSWORD_BCRYPT, $options);

$query = $this->db_connection->prepare("SELECT name, mail FROM users WHERE name = ? OR mail = ?");
$query->bind_param('ss', $username, $useremail);
$query->execute();
$results = $query->get_result(); // this works fine

if($results->num_rows == 1) {
    $row = $results->fetch_object();
    
    if($username == $row->name) {
        $this->errors[] = "This username is already taken!";
    } elseif($useremail == $row->mail) {
        $this->errors[] = "This email address is already taken!";
    } else {
        $this->errors[] = "This username / email address is already taken.";
    }
} else {
    $SIS = new SnowflakeIdService;
    $snowflakeID = $SIS->CreateSnowflakeID();
    $sql = $this->db_connection->prepare("INSERT INTO users (snowflake, name, salt, hash, mail) VALUES (?, ?, ?, ?, ?)");
    $sql->bind_param("issss", $snowflakeID, $username, $salt, $password_hash, $useremail);
    $sql->execute();
    $dataR = $sql->get_result(); // this not

    if($dataR) {
        $this->messages[] = "Your account has been created successfully. You can now log in.";
    } else {
        $this->errors[] = "Sorry, your registration failed. Please go back and try again.";
    }
}

Because there IS NO RESULT from an insert only a status True/False — RiggsFolly, Nov 25 '21 at 14:45
If you want to know if anything was inserted, use `$stmt->affected_rows`. — Barmar, Nov 25 '21 at 14:46
___BIG NOTE___ `password_hash()` generates its own SALT, better than anything you are likely to come up with. If you look at the manual page is says `Warning The salt option is deprecated. It is now preferred to simply use the salt that is generated by default. As of PHP 8.0.0, an explicitly given salt is ignored. ` — RiggsFolly, Nov 25 '21 at 14:48
_Another Note:_ If you are using prepared, bound, parameteterised queries (like you are) you do not need to `real_escape_string()` the user inputs — RiggsFolly, Nov 25 '21 at 14:52
So try `$dataR = $sql->execute(); if ( $didIt ) { // inserted ok` and remove the `$dataR = $sql->get_result(); ` — RiggsFolly, Nov 25 '21 at 14:53
Thanks! `$sql->affected_rows` works great! Also, you wrote that this code can be injected, how? And how can I use prepare statements, I use them already, don't I? — DaRealAdalbertBro, Nov 25 '21 at 14:54
SOrry, my mistake, saw the `real_escape_string()` and made an assumption. I should know better :) — RiggsFolly, Nov 25 '21 at 14:54
Alright, no problem. Thanks for help! I published the solution — DaRealAdalbertBro, Nov 25 '21 at 15:00
Do not use `$this->db_connection->real_escape_string(strip_tags` It will damage your data. — Dharman, Nov 25 '21 at 15:16

score 0 · Accepted Answer · edited Nov 25 '21 at 15:17

0

I changed $dataR = $sql->get_result(); to $dataR = $sql->affected_rows;, so the final code looks like:

$sql = $this->db_connection->prepare("INSERT INTO users (snowflake, name, salt, hash, mail) VALUES (?, ?, ?, ?, ?)");
$sql->bind_param("issss", $snowflakeID, $username, $salt, $password_hash, $useremail);
$sql->execute();
$dataR = $sql->affected_rows;
if($dataR > 0) {
     $this->messages[] = "Your account has been created successfully. You can now log in.";
} else {
     $this->errors[] = "Sorry, your registration failed. Please go back and try again.";
}

edited Nov 25 '21 at 15:17

Dharman

30,962
25
85
135

answered Nov 25 '21 at 14:59

DaRealAdalbertBro

124
1
10

Checking for `$sql->affected_rows` sounds dubious. Why do you think that the account would not get created? Make sure you have error reporting enabled [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) – Dharman Nov 25 '21 at 15:19

$smt->get_result() gives empty result after MySQL (only) INSERT

1 Answers1