I am baffled, I've been trying to get this piece of code to work for the past 30 minutes but with no luck. It should in theory be a simple registration script to check the user's input and then add it to the database however it won't add it to the table for some reason.

<?php

    include_once($_SERVER['DOCUMENT_ROOT'].'/PHP/Services/MySQLi_Connect.php');

    $Username = mysqli_real_escape_string($Connect, $_POST['Username']);
    $Password = mysqli_real_escape_string($Connect, $_POST['Password']);
    $RPassword = mysqli_real_escape_string($Connect, $_POST['RPassword']);
    $SecurityQ = mysqli_real_escape_string($Connect, $_POST['securityQuestion']);
    $SecurityA = mysqli_real_escape_string($Connect, $_POST['securityAnswer']);
    $CheckDigit = mysqli_real_escape_string($Connect, $_POST['checkDigit']);
    $Date = date('Y/m/d H:i:s');

    if($CheckDigit){

        die ("You have been denied access for the following reason: Suspected Bot");

    }

    if($Username && $Password && $RPassword && $SecurityQ && $SecurityA){

        $Username = preg_replace('/\s+/', '', $Username);
        $Username = strtolower($Username);

        $SecurityQ = preg_replace('/\s+/', ' ', $SecurityQ);
        $SecurityA = preg_replace('/\s+/', ' ', $SecurityA);
        $SecurityA = strtolower($SecurityA);

        if($Password != $RPassword){

            die (Header ('Location: /HTML/Errors/Register/Password.php'));

        }

        if(strlen($Password) < 5){

            die (Header ('Location: /HTML/Errors/Register/PasswordLength.php'));

        }

        if(strlen($Username) < 3 || strlen($Username) > 25){

            die (Header ('Location: /HTML/Errors/Register/UsernameLength.php'));

        }

        if(strlen($SecurityQ) < 5 || strlen($SecurityQ) > 100){

            die (Header ('Location: /HTML/Errors/Register/SecurityQ.php'));

        }

        if(strlen($SecurityA) < 5 || strlen($SecurityA) > 100){

            die (Header ('Location: /HTML/Errors/Register/SecurityA.php'));

        }

        if ($stmt = mysqli_prepare($Connect, "SELECT Username FROM MemberData WHERE Username = ?"))
        {

            mysqli_stmt_bind_param($stmt, 's', $Username);
            mysqli_stmt_execute($stmt);
            mysqli_stmt_store_result($stmt);

            $NumRows = mysqli_stmt_num_rows($stmt);

            if($NumRows != 0)
            {
                die (Header('Location: /HTML/Errors/Register/Taken.php'));
            }

        }

        $Password = hash('sha512', $Password);

        if ($stmt = mysqli_prepare($Connect, "INSERT INTO MemberData (Username, Password, SecurityQuestion, SecurityAnswer, DateRegistered) VALUES (?, ?, ?, ?, ?)"))
        {
            mysqli_stmt_bind_param($stmt, 'sssss', $Username, $Password, $SecurityQuestion, $SecurityAnswer, $Date);
            mysqli_stmt_execute($stmt);

            mysqli_close($Connect);

            die (Header('Location: /HTML/Errors/Register/Complete.php'));
        }

    }

    else{

        die (Header('Location: /HTML/Errors/Register/MissingValues.php'));

    }

?>

The script shall run through as it should do, and shall end up on the Complete.php page, however it doesn't add anything to the database.
If I intentionally break the script such as by asking it to add to a fake column then it shall spit out errors as expected, so it certainly is running over the INSERT query however it just won't add anything normally.

This is my table dumped directly from MySQL:

CREATE TABLE IF NOT EXISTS `MemberData` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Username` varchar(25) NOT NULL,
  `Password` varchar(150) NOT NULL,
  `SecurityQuestion` varchar(100) NOT NULL,
  `SecurityAnswer` varchar(100) NOT NULL,
  `DateRegistered` datetime NOT NULL,
  `AccountStatus` int(11) NOT NULL DEFAULT '1',
  `AccountComments` longtext NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Any suggestions?

*Note: I know I still need to salt the passwords, and the Security Answers need hashing and salting. *

Thank you

Member Avatar for diafol

Use parameterized queries - GOOD - don't mysqli_real... the values too.

Thanks for the advice, looking back at it I realise there isn't any need to do it as well as parametised queries?

Do you have any ideas about it not submitting to the database?

Thanks

Member Avatar for diafol

Can't see anything obvious. Try running a hard-coded mysqli insert query just before your real one and see if that works.

Try to alert your variables.
and your query parameter is five while your trying to enter six data.

Member Avatar for diafol

and your query parameter is five while your trying to enter six data.

If you use the procedural mysqli_stmt_bind_param() function, then your 2nd parameter will be setting datatypes: 'sssss' in this case. So he's still binding 5 values.

try adding:

error_reporting(E_ALL);
ini_set('display_errors', 1);

at top of you script.
also add: mysqli_error($conn) to check the error msg when the query fails.

Fixed it, was a simple (and stupid) mistake.
Thanks for the suggestion dorco however I tried adding that and it returned nothing.

The problem is with this line here:
mysqli_stmt_bind_param($stmt, 'sssss', $Username, $Password, $SecurityQuestion, $SecurityAnswer, $Date);

$SecurityQuestion and $SecurityAnswer are not defined, it should be $SecurityQ and $SecurityA and this is causing the entire thing to break.

Thanks for all the help guys!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.