PHP, MySql. Requiring people to sign up with a key

Question

So I've created a registration page and I've attempted to try and make it require an "alpha" key for the game but I cannot get it working. I've got all the information in my database and it's just coming out with my error "ERR 02: Failed to register!"

    <!doctype html>
<html>
<head>  
<title>Register</title>
</head>
<body>

<h3>Registration Form</h3>
<form action="" method="POST">
Alpha Key: <input type=text name='alphakey'><br/>
Username: <input type=text name='user'><br/>
Password: <input type=password name='pass'><br/>    

<input type=submit value='Register' name='submit'>
</form>
<?php 
if (isset($_POST['submit'])){

if(!empty($_POST['user']) && !empty($_POST['pass'])) {

//mysql_real_escape_string() escapes special characters in a string for use                 in an SQL statement
$user=mysql_real_escape_string($_POST['user']); 
$pass=mysql_real_escape_string($_POST['pass']);
$alphakey=mysql_real_escape_string($_POST['alphakey']);

$con=mysql_connect('localhost','<my_user>','<my_password>') or die(mysql_error());
mysql_select_db('user') or die("cannot select DB");

$query=mysql_query("SELECT * FROM login WHERE user='".$user."'");
//$query.=mysql_query("SELECT * FROM regkey WHERE     alphakey='".$_POST["alphakey"]."'");
$numrows=mysql_num_rows($query);
if($numrows==0)
{
//md5() calculates the MD5 hash of a string
//$encrypt_password=password_hash($pass, PASSWORD_DEFAULT);
$encrypt_password=md5($_POST["pass"]);

$sql="INSERT INTO login(user,pass)    VALUES('".$_POST["user"]."','$encrypt_password')";
$sql.="SELECT * FROM regkey WHERE alphakey='".$_POST["alphakey"]."'";

$result=mysql_query($sql);


if($result!=1) 
{
echo "ERR 02: Failed to register";
}
else{
echo "Account Successfully Created";
}
} else {
echo "That username already exists! Please try again with another.";
}

} else {
echo "All fields are required!";
}
}
?>
<p><a href="register.php">Register</a> | <a href="login.php">Login</a></p>
</body>
</html>

echo your $sql query and try and run it directly in your DB handler (phpmyadmin or w.e you use) and see if it gives you any errors. - Secondly, mysql is deprecated and should not be used, consider using mysqli or pdo instead. — Epodax, Apr 29 '15 at 11:21
use $result = mysql_num_rows($sql); and check if($result!=1) { — Saty, Apr 29 '15 at 11:23
try splitting your queries. you're concatenating 2 different types of queries and asking MySQL if it were successful. do `$result=mysql_query($sql) or die(mysql_error());` see if it returns syntax errors. comment out the `select` see if it enters it in db — Funk Forty Niner, Apr 29 '15 at 11:28
When trying Honza fix, it says it creates the account but nothing is inserted into the database. @Epodax it returns no errors. — user3765978, Apr 29 '15 at 11:28
I wouldn't do that. curious to know why you're using select after that; can you tell me why you're using select after wanting to insert? — Funk Forty Niner, Apr 29 '15 at 11:33
I'm still new to mysql so please go easy and explain it throughly. :) — user3765978, Apr 29 '15 at 11:34
you can use `mysql_affected_rows())` instead http://php.net/manual/en/function.mysql-affected-rows.php if that's the goal to see if rows were successful and affected. — Funk Forty Niner, Apr 29 '15 at 11:36
@Fred-ii- okay so basically I've smash some numbers into my database and made it a key and if you type in that key that i put into the database then you're able to create an account. — user3765978, Apr 29 '15 at 11:36
you would need to use a different query for that. either use `COUNT()` or `mysql_num_rows()` on select to match up data from user input. But usually, confirmation codes (if sent via mail), use a GET method. — Funk Forty Niner, Apr 29 '15 at 11:37
so how would I write out that line? sorry about that @Fred-ii- still a newbie — user3765978, Apr 29 '15 at 11:39
I'm not at my coding machine right now, as I don't have all my snippets. I'd have to get to the question once I'm on it. If nobody puts in an answer, then I'll be able to once I get on my coding machine which will be in about 20-30 mins. — Funk Forty Niner, Apr 29 '15 at 11:40
Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Apr 29 '15 at 12:14

score 1 · Accepted Answer · edited May 23 '17 at 12:17

I would use the following method.

Foreword: Consult my footnotes about the use of insecure functions.

First check if the reg key exists, then insert into DB.

$query = mysql_query("SELECT * FROM login WHERE user='".$user."'");

$numrows = mysql_num_rows($query);

// if user doesn't exist...
if($numrows==0) {

    $encrypt_password = md5($_POST["pass"]);

$query_key = mysql_query("SELECT * FROM regkey WHERE alphakey='".$_POST["alphakey"]."'") 
             or die(mysql_error());

$check_key = mysql_num_rows($query_key);

if($check_key >0){

    $sql = mysql_query("INSERT INTO login (user,pass) 
                        VALUES ('".$_POST["user"]."','$encrypt_password')") 

          or die(mysql_error());
    }

} // brace for if($numrows==0)

if($sql){
echo "Success.";
}

Give that a go. If you have any problems or that I may have misunderstood the question, let me know and I will be glad to adjust my answer accordingly.

Footnotes:

Your present code is open to SQL injection. Use prepared statements, or PDO with prepared statements, they're much safer.

Password storage:

You are using MD5 which is an old and considered broken method of hashing and is no longer considered safe to use.

I recommend you use CRYPT_BLOWFISH or PHP 5.5's password_hash() function.

For PHP < 5.5 use the password_hash() compatibility pack.

PDO with prepared statements example, including using password_hash().

Pulled from ircmaxell's answer https://stackoverflow.com/a/29778421/

Just use a library. Seriously. They exist for a reason.

PHP 5.5+: use password_hash()
PHP 5.3.7+: use password-compat (a compatibility pack for above
All others: use phpass

Don't do it yourself. If you're creating your own salt, YOU'RE DOING IT WRONG. You should be using a library that handles that for you.

$dbh = new PDO(...);

$username = $_POST["username"];
$email = $_POST["email"];
$password = $_POST["password"];
$hash = password_hash($password, PASSWORD_DEFAULT);

$stmt = $dbh->prepare("insert into users set username=?, email=?, password=?");
$stmt->execute([$username, $email, $hash]);

And on login:

$sql = "SELECT * FROM users WHERE username = ?";
$stmt = $dbh->prepare($sql);
$result = $stmt->execute([$_POST['username']]);
$users = $result->fetchAll();
if (isset($users[0]) {
    if (password_verify($_POST['password'], $users[0]->password) {
        // valid login
    } else {
        // invalid password
    }
} else {
    // invalid username
}

So how would I replace it with the higher security reason as you said about password_hash() — user3765978, Apr 29 '15 at 12:48
@user3765978 Just follow the example in the PDO code I've pulled from ircmaxell's answer. Once you've gotten your present code to work, make up a new file (never overwrite your originals) and then implement the `password_hash()` function. Please note that `password_hash()` is only available for PHP 5.5> otherwise, you will need to use the compatibility pack. — Funk Forty Niner, Apr 29 '15 at 12:50
Alright, I'll get back to you. EDIT: It says $dbh = new PDO(...); what do I need to put in here? — user3765978, Apr 29 '15 at 12:55
@user3765978 see the manual on how to connect using PDO. http://php.net/manual/en/pdo.connections.php however and remember; you cannot mix any other MySQL API with PDO, such as `mysqli_` or `mysql_` functions. — Funk Forty Niner, Apr 29 '15 at 21:48
It solves it but it still injects the user and password into the database even when it displays the error of "Invalid Alpha Key". And it just keep inserting the same username into the database [EDIT: Don't worry fixed the issues! thank you again.] — user3765978, Apr 30 '15 at 06:37

PHP, MySql. Requiring people to sign up with a key

1 Answers1