I'm creating a user registration system for my website. I had this working code:
$sqlQuery = "INSERT INTO GH_users (firstname, surname, email, accountConfirmed, username, passwordHash)
VALUES ('$firstname', '$surname', '$email', 0, '$usernameSignup', '$passwordHash')";
$execute = $dbConn->exec($sqlQuery);
However, I found out that this invites the risk of SQL injection. Therefore, I have tried to use a prepared statement to prevent this but I am unable to get it to work. Codes that I've tried:
$sqlQuery = "INSERT INTO GH_users (firstname, surname, email, accountConfirmed, username, passwordHash)
VALUES (?, ?, ?, ?, ?, ?)";
$stmt = $dbConn->prepare($sqlQuery);
$stmt->bindParam($firstname, $surname, $email, 0, $usernameSignup, $passwordHash);
$stmt->execute();
$sqlQuery = "INSERT INTO GH_users (firstname, surname, email, accountConfirmed, username, passwordHash)
VALUES (?, ?, ?, 0, ?, ?)";
$stmt = $dbConn->prepare($sqlQuery);
$stmt->bindParam("sssss", $firstname, $surname, $email, $usernameSignup, $passwordHash);
$stmt->execute();
// These give the following error: PDOStatement::bindParam() expects at most 5 parameters, 6 given
// So I tried this:
$sqlQuery = "INSERT INTO GH_users (firstname, surname, email, accountConfirmed, username, passwordHash)
VALUES (?, ?, ?, 0, ?, ?)";
$stmt = $dbConn->prepare($sqlQuery);
$stmt->bindParam($firstname, $surname, $email, $usernameSignup, $passwordHash);
$stmt->execute();
// But this throws: PDOStatement::bindParam() expects parameter 3 to be long, string given
I'm not sure why these are throwing the errors given, especially the "expects parameter 3 to be long" as the email field is a string (varchar) data type. Can anyone help with this and explain what is wrong(accountConfirmed is a bit if it helps)?
UPDATE
I realised that I was receiving these errors because I was not using prepared statements and bound parameters in PDO. Thanks to @tadman and @user3783243 in the comment section, I was able to shorten my code by adding my parameters to execute() to do the binding instead of using bindParam() for each of the parameters.
SOLUTION
$sqlQuery = "INSERT INTO GH_users (firstname, surname, email, accountConfirmed, username, passwordHash)
VALUES (?, ?, ?, 0, ?, ?)";
$stmt = $dbConn->prepare($sqlQuery);
$stmt->execute(array($firstname, $surname, $email, $usernameSignup, $passwordHash));
