why my MySQL queries executed before PHP Conditional check?

Question

I'm learning to create conditional event where sql checkout data where is exists before inserting data so they don't conflicted.

i've tried using mysql row check in php then check if query empty before i tried to validate the query executed properly.

also trying to close db connection when conditional satisfied but it worthless anyway.

$user = addslashes(strtolower($usr));
$mail = addslashes(strtolower($mail));
$pass = md5(addslashes($pwd));

$check = $db->query("SELECT EXISTS(SELECT * 
                                   FROM `users`
                                   WHERE LOWER(`username`) = LOWER('$user')
                                      OR LOWER(`email`) = LOWER('$mail'))");

if (!$check) {
    $db->close();
    return false;
} else {
    $sql = "INSERT IGNORE INTO `users` (`username`, `password`, `email`)
                   VALUES ('$user', '$pass', '$mail')";
    $query = $db->query($sql);
    $db->close();
    return true;
}

I'm expecting it execute my queries while data was empty and return false while data has been existed.

var_dump($check) gives what? You should not use addslashes nor md5. Parameterize your query and use password_hash (and password_verify where needed). — user3783243
– user3783243, Commented Jul 15, 2019 at 11:21
also to add to @user3783243 's comment this method/approach is also prone to race conditions.. If you want to prevent duplicates you should be adding a unique key to the table (also).. — Raymond Nijland
– Raymond Nijland, Commented Jul 15, 2019 at 11:22
@user3783243 it look something like this object(mysqli_result)#3 (5) { ["current_field"]=> int(0) ["field_count"]=> int(1) ["lengths"]=> NULL ["num_rows"]=> int(1) ["type"]=> int(0) } — Reyhan Fabiano
– Reyhan Fabiano, Commented Jul 15, 2019 at 11:27
"i think, username and email both fields using as unique values? right, then why are you using OR condition here?" @devpro some applications allow signin on a username OR email.. — Raymond Nijland
– Raymond Nijland, Commented Jul 15, 2019 at 11:49
Depends entirely on the application. Can separate users have the same username or the same email? Or is it just the combination that's unique? I would say the email is at least supposed to be unique, but again - depends on what the application does and what the structure is. — Qirel
– Qirel, Commented Jul 15, 2019 at 12:02

Qirel · Accepted Answer · 2019-07-15 11:35:20Z

3

Your main issue is that $check will always be a truthy value, so long as the query never fails. If the query returns 0 rows, it is still a true object.

You should instead check if there were any values returned. You can also simplify the query quite a bit, given that MySQL is case-insensitive, and you don't need to check if the result exists. Using a prepared statement, the code would look like this

$stmt = $db->prepare("SELECT username FROM users WHERE username = ? OR email = ?");
$stmt->bind_param("ss", $usr, $mail);
$stmt->execute();
$check = $stmt->fetch();
$stmt->close(); 

// True if the user exists 
if ($check) { 
    return false;
} else {
    $stmt = $db->prepare(" INSERT INTO users (username, password, email) VALUES (?, ?, LOWER(?))");
    $stmt->bind_param("sss", $usr, $pass, $mail);
    $stmt->execute();
    $stmt->close();
}

That said, you should not use md5() for passwords - use password_hash() with password_verify() instead.

answered Jul 15, 2019 at 11:35

Qirel

26.6k7 gold badges54 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Raymond Nijland Over a year ago

keep in mind that this is still prone to race conditions, assuming there is non unique key in the table..

Qirel Over a year ago

Fair point - you can use use INSERT INTO ... ON DUPLICATE KEY UPDATE username=username or something along those lines, then check what the affected rows were, or possibly the inserted ID.

Qirel Over a year ago

Indeed it would. Quite assumptious of me really, we haven't seen any tablestructure posted with the question.

Rajkumar Sharma · Accepted Answer · 2019-07-15 15:30:56Z

0

you can change your code like this

$check = $db->query("SELECT EXISTS(SELECT * FROM `users`
    WHERE LOWER(`username`) = LOWER('$user')
    OR LOWER(`email`) = LOWER('$mail'))");


$check = $conn->query($sql); 
$value = $check->fetch_row()[0];
if($value > 0 ){    
    echo "existed".$value; // you can change accordingly
}else{
    echo "doesn't exist"; // this also
}

Because database respond the query in 1 for exist and 0 for non-exist so the num_row will be always 1 thats why we cant determine the existence with num_row so we have to fetch the value.

edited Jul 15, 2019 at 15:30

answered Jul 15, 2019 at 11:33

Rajkumar Sharma

5841 gold badge4 silver badges9 bronze badges

3 Comments

Reyhan Fabiano Over a year ago

it gain error Notice: Undefined property: mysqli_result::$num_row

Nico Haase Over a year ago

Please add some explanation to your answer such that others can learn from it

Rajkumar Sharma Over a year ago

try updated code. I have checked on my database also.It will surely run.

Collectives™ on Stack Overflow

why my MySQL queries executed before PHP Conditional check?

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related