PHP code for inserting non-duplicate values into MySQL

Question

I have basic PHP/MySQL experience, having taken an introductory class. My knowledge is literally limited to the following PHP codes:

if(!($stmt = $mysqli->prepare...)
if(!($stmt->bind_param...)
if(!$stmt->execute...)

I'm currently trying to write a program that allows a user to enter a new password, and checks the password against existing passwords in the database.

Here is what I have:

<?php
foreach($Password){
    $dupesql = "SELECT PasswordID Passwords WHERE (Password = '$Password')";
    $duperaw = mysql_query($dupesql);
    if(mysql_num_rows($duperaw){
        echo nl2br("$Password has already been used \n");
    }
    else{
        echo "Password added \n";
    }
}
?>

I got the code from this post: Check for duplicates before inserting

I'm not sure if the code itself has problems or if I need to add anything else to my PHP code to get this working, as it's currently producing an "Error 500".

Please use `password_hash()` for storing passwords, judging from the code you've provided you store your passwords as clear text which you should never ever do. — mazedlx, Nov 20 '16 at 18:49
you're not mixing mysql_ and mysqli_ together are you? You going to check comments/answers given or should we stand around here till there's some activity on your part? 500 error is a server error; check your logs. Post & go/leave answers aren't well-accepted here. — Funk Forty Niner, Nov 20 '16 at 19:53
and that `foreach($Password)` of yours; doesn't do much really. Have you RTM on that? http://php.net/manual/en/control-structures.foreach.php I take it not. — Funk Forty Niner, Nov 20 '16 at 19:55
Thank you for all the comments, I didn't realize I had to sit online and respond to comments right away. It's only been 2 hours, and I am returning to my post to respond to comments. First, this is just a small prototype and it's only a piece of a larger project, so I'm not storing actual passwords and clear text should work. Second, I'm not sure what RTM means, as I explained before, I copied this code from another post's accepted answer. After reading the answer below, I did change all mysql commands to mysqli commands. — Aisha Ashwal, Nov 20 '16 at 20:59

score 1 · Answer 1 · edited May 23 '17 at 11:45

1

MySQL extension is deprecated and probably you have PHP 7.0 from where it is removed. Rewrite your code to MySQLi or PDO. Check this question on how to convert to MySQLi: How could I change this mysql to mysqli?

Also, your code just doesn't add a password (never). Probably you expect to add it before the "Password Added" message, but be aware: the solution you want to use is not ideal, because there is a risk of race condition between checking the password for existence and adding it. This means that it is possible to add a password twice.

To solve this problem, you might want to use transactions. More details are covered in this question: PHP + MySQL transactions examples

edited May 23 '17 at 11:45

Community

1
1

answered Nov 20 '16 at 18:41

Denis V

3,290
1
28
40

1

I fear you may have fallen into a rather deep rabbit hole on this question, one that doesn't make much sense neither, especially that `foreach` of theirs. See the comments I left under their question also. – Funk Forty Niner Nov 20 '16 at 19:56
@Fred-ii- Well, yes, you are probably right. But I'll keep my answer here, since it might be useful for others. At least in terms of mentioning race condition problem. – Denis V Nov 20 '16 at 20:08
*\*sigh\** - yeah I'm afraid so there Denis. Oh well, they have enough to go on with what's been given in your answer and comments. – Funk Forty Niner Nov 20 '16 at 20:12
I read the links you provided. Essentially, I should just changed all codes involving "sql" to "sqli" right? – Aisha Ashwal Nov 20 '16 at 21:02
@AishaAshwal your code contains other issues. It was already mentioned that foreach is written syntactically incorrectly + the password is actually never saved. Also, error 500 can mean anything - check your server logs for the exact problem. – Denis V Nov 20 '16 at 21:33

score 0 · Accepted Answer · answered Nov 20 '16 at 22:01

I decided to go an entirely different route, which is to set the Password column as unique.

Then I did a simple INSERT that would prompt an error if the user attempts to add a duplicate:

<?php
        if(!($stmt = $mysqli->prepare("INSERT INTO Heroes(HeroName, FirstName, LastName, Age, HomeCountry, RoleID) VALUES (?,?,?,?,?,?)"))){
            echo "Prepare failed: "  . $stmt->errno . " " . $stmt->error;
        }
        if(!($stmt->bind_param("sssisi",$_POST['HeroName'],$_POST['FirstName'],$_POST['LastName'],$_POST['Age'],$_POST['HomeCountry'],$_POST['RoleID']))){
            echo "Bind failed: "  . $stmt->errno . " " . $stmt->error;
        }
        if(!$stmt->execute()){
            echo "Execute failed: "  . $stmt->errno . " " . $stmt->error;
        } else {
            echo "Added " . $stmt->affected_rows . " row to Heroes.";
        }
?>

PHP code for inserting non-duplicate values into MySQL

2 Answers2