Script isn't inserting data [MySQL/PHP]

Question

I have a little question.

I made a PHP script which should have to insert data into my Database, but sadly enough.. It doesn't work, it has worked before though. I'm running my script on a host now, which might be the problem.. But I don't think it is.

The code:

<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">

<?php
$usernameErr = $emailErr = $passwordErr = $password_valErr = "";
$username = $email = $password = $password_val = "";

if($_SERVER['REQUEST_METHOD'] == 'POST') {
    if(empty($_POST['username'])) {
        $usernameErr = "Name is required";
    } else {
        $username = validate_input($_POST['username']);
        if(strlen($username) <= 3) {
            $usernameErr = "Username must be 4 characters or longer.";
        }
        if(strlen($username) > 26) {
            $usernameErr = "Username can't be longer as 26 characters.";
        }
        if(!preg_match("/^[a-zA-Z ]*$/", $username)) {
            $usernameErr = "Only letters and white space allowed.";
        }       
    }

    if(empty($_POST['email'])) {
        $emailErr = "Email is required";
    } else {
        $email = validate_input($_POST['email']);
        if(!filter_var($email, FILTER_VALIDATE_EMAIL)) {
            $emailErr = "Invalid email format.";
        }           
    }

    if(empty($_POST['password'])) {
        $passwordErr = "Password is required";
    } else {
        $password = validate_input($_POST['password']);
        if(strlen($password) <= 5)  {
            $passwordErr = "Password must be 6 characters or longer.";
        }
        if(strlen($password) > 26) {
            $passwordErr = "Password can't be longer as 26 characters.";
        }
        if(!preg_match("#[0-9]+#", $password)) {
            $passwordErr = "Password must contain atleast 1 number.";
        }
    }

    if(empty($_POST['password_val'])) {
        $password_valErr = "Password_val is required";
    } else {
        $password_val = validate_input($_POST['password_val']);
        if($password_val != $password) {
            $password_valErr = "Password_val must be equal to password.";
        }       
    }

    if($usernameErr == '' && $emailErr == '' && $passwordErr == '' && $password_valErr == '') {

        $check_user = mysqli_query($conn, "SELECT * FROM users WHERE username='".trim($username)."'");
        $check_mail = mysqli_query($conn, "SELECT * FROM users WHERE email='".trim($email)."'");

        if(mysqli_num_rows($check_user) > 0) {
            echo 'This username allready exists';
        } elseif(mysqli_num_rows($check_mail) > 0) {
            echo 'This email address is already registered.';
        } else {
            $username   = mysql_real_escape_string(trim($username));
            $email      = mysql_real_escape_string(trim($email));
            $password   = mysql_real_escape_string(trim($password));
            $rand_salt  = randString();

            /*$final_pass = password_hash($password_val, PASSWORD_DEFAULT)."\n";*/
            $final_pass = sha1($password.PASSWORD_SALT.$rand_salt);
            $privileges = 0;

            $sql = "INSERT INTO users (username,password,salt,email)
                    VALUES ('".$username."','".$final_pass."','".$rand_salt."','".$email."')";

            if($conn->query($sql) === TRUE) {
                echo "User registered.";

            } else {
                echo 'Error: ' . $sql . '<br>' . $conn->error;
            }
        }
    }
}
?>

<table border="1">
    <tr>
        <td><label>Username</label><?=' <b>' . $usernameErr . '</b>';?></td>
        <td><input type="text" name="username" value="<?=$username;?>" placeholder="Enter your desired username..." /></td>
    </tr>
    <tr>
        <td><label>E-mail</label><?=' <b>' . $emailErr . '</b>';?></td>
        <td><input type="text" name="email" value="<?=$email;?>" placeholder="Enter your email address..." /></td>
    </tr>
    <tr>
        <td><label>Password<?=' <b>' . $passwordErr . '</b>';?></label></td>
        <td><input type="password" name="password" placeholder="Enter your desired password..." /></td>
    </tr>
    <tr>
        <td><label>Repeat Password<?=' <b>' . $password_valErr . '</b>';?></label></td>
        <td><input type="password" name="password_val" placeholder="Repeat your chosen password.." /></td>
    </tr>
    <tr>
        <td><input type="submit" name="register" value="Register" /></td>
    </tr>
</table>
</form>

This code should work, but the first problem is:

action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>"

This code would have to send me to the same page, register.php in this case, but somehow it sends me to the index.php page. So, if I delete this code and leave the action empty or just enter register.php in there, the data gets inserted except for the Username and Email which are both Varchars..

I hope someone can help me,

thank you!

You are vulnerable to [sql injection attacks](http://bobby-tables.com) as well. — Marc B, May 19 '15 at 14:41
@Fred-ii- If I use the `action=""` only the password, salt and id get inserted.. leaving the username and email empty. — peer, May 19 '15 at 14:44
am sure it worked before you started using `mysql_real_escape_string()` as per my first comment ;-) — Funk Forty Niner, May 19 '15 at 14:44

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

You're using an mysql_ function along with connecting with an mysqli_ API.

You can't mix those functions. You must use the same MySQL API from connection to query.

You need to change all instances of mysql_real_escape_string to mysqli_real_escape_string($conn, $_POST['variable'])
variable being your POST arrays.

Plus, make sure you are indeed connecting with mysqli_ and not mysql_ or PDO for that matter.

It is uncertain as to which MySQL API you are connecting with, even though your code does contain multiple mysqli_ functions.

Add error reporting to the top of your file(s) right after your opening PHP tag.

For example <?php error_reporting(E_ALL); ini_set('display_errors', 1); then the rest of your code, to see if it yields anything, as well as or die(mysqli_error($conn)) to mysqli_query().

Sidenote: sha1() isn't considered the safest method for storing hashes.

Pulled from ircmaxell's answer https://stackoverflow.com/a/29778421/ and using PDO with prepared statements and password_hash().

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
}

Wouldn't I do the variables? Since I already declared those and gave those the $_POST? — peer, May 19 '15 at 14:51
@Peurr you could, but then you'd leave yourself open to SQL injection. — Funk Forty Niner, May 19 '15 at 14:52
But, what would happen? Since I already declared the variables? Like this: $email = example@example.com, and if I'd do the mysqli_real_escape_string after that.. would it work? — peer, May 19 '15 at 14:56
`$email = "example@example.com";` then `$email = mysqli_real_escape_string($conn, $email);` or from the POST array itself and all in one go `$email = mysqli_real_escape_string($conn, $_POST['email']);` etc. @Peurr — Funk Forty Niner, May 19 '15 at 14:59
So `$email = mysqli_real_escape_string($conn, $email);` wouldn't make me opened to SQL injection? — peer, May 19 '15 at 15:00
@Peurr you're pretty good to go, plus you're using `FILTER_VALIDATE_EMAIL`. However, consider looking into using [**`mysqli` with prepared statements**](http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php), or [**PDO with prepared statements**](http://php.net/pdo.prepared-statements), *they're much safer*. — Funk Forty Niner, May 19 '15 at 15:03
@Peurr You're welcome. Also, reload my answer. I've made an additional edit in regards to using `sha1()`. Look near the bottom under **Sidenote**. — Funk Forty Niner, May 19 '15 at 15:11

Script isn't inserting data [MySQL/PHP]

1 Answers1