Jump to content

No data supplied for parameters in prepared statement


Go to solution Solved by gizmola,

Recommended Posts

Am just a beginner in PHP, am trying to implement user registration using bellow codes. But when am testing to register a user am getting "No data supplied for parameters in prepared statement" error please anyone with an idea please help..

 

 

 

session_start();
require_once ( "../include/config.php" );
require_once ( "../functions/functions.php" );
$message = array();

//check if the form has been submitted
if ( isset($_POST['submitted']) )
{
$firstname = sanitize( $connection, $_POST['firstname'] );
$lastname = sanitize( $connection, $_POST['lastname'] );
$username = sanitize( $connection, $_POST['username'] );
$password = sanitize( $connection, $_POST['password'] );
$confirm_password = sanitize( $connection, $_POST['confirm_password'] );
$status_id = sanitize( $connection, $_POST['status'] );

//check if username exist in the database
$stmt = $connection->prepare( "SELECT * FROM users WHERE username=?" );
$stmt->bind_param( "s",$username );
$stmt->execute();

$results = $stmt->get_result();

if ( $results->num_rows > 0 )
{
$message['error'] = "Username is not available!<br>Choose another and try again";
$_SESSION['error'] = $message;
header( "Location:../../public_html/user_registration.php" );
}

else
{
//check password matching
if ( $password !== $confirm_password )
{
$message['error'] = "Passwords do not match!<br>Make sure password and confirm password fields match";
$_SESSION['error'] = $message;
header( "Location:../../public_html/user_registration.php" );
}

else
{
//encrypt and salt the password
$presalt = "@&^3*!";
$postsalt = "%&*$!";

$salted_password = $presalt.$password.$postsalt;
$hashed_password = hash( 'ripemd128',$salted_password );

//inserting the user
$user_id="";
$date_created = date( 'Y-m-d H:i:s' );
$date_edited ="";

$stmt = $connection->prepare("INSERT INTO users values(?,?,?,?,?,?,?,?)");
$stmt->bind_param("i,s,s,s,s,s,s,s",$user_id,$firstname,$lastname,$username,$hashed_password,$date_created,$date_edited,$status_id);

$result = $stmt->execute();

if ( $result )
{
echo "<i>Successful registered a user..</i>";
header( "refresh:1;url=../../public_html/user_registration.php" );
}

else
{
$message['error'] = "User registration failed!<br>".$stmt->error;
$_SESSION['error'] = $message;
header( "Location:../../public_html/user_registration.php" );
}
}
}


}

Link to post
Share on other sites
  • Solution

Obviously if you don't have all the data required for the insert, you are going to get an error. One issue that you have made for yourself unnecessarily is:
 

$user_id="";
$date_created = date( 'Y-m-d H:i:s' );
$date_edited ="";

So i'm going to assume that you have an auto_increment for user_id, and date_edited can be null, and since this is a new row you want that to be NULL on insert.

In this case, you should not be passing values for these. MySQL figures out how to auto_increment, and it makes no sense to pass a NULL date string for a date parameter that you will never set in this context.


Change the insert so that those are not included:
 

$stmt = $connection->prepare("INSERT INTO users values(?,?,?,?,?,?)");
$stmt->bind_param("s,s,s,s,s,s", $firstname, $lastname, $username, $hashed_password, $date_created, $status_id);
Link to post
Share on other sites

there would be a related and helpful php error, if php's error_reporting/display_errors were set properly, that would help identify that the problem is most likely due to the wrong format for the 1st bind_param() parameter. this should be a string of just the format specifiers, without commas between them.

 

next, whatever your sanitize() function code is doing, it is either one or all of - ineffective, insecure, or redundant and is probably not needed. could you post the code for the sanitize() function.

 

the current code will allow empty value(s) to be used for all the fields (an empty username won't match any existing user and an empty password will be equal to an empty confirmation password.)

 

you should validate all the inputs before using them and validate all the inputs at once. you are only validating some of the data and you are stopping at the first validation error, so it will take multiple form submissions to validate all of the data, but since you are redirecting back to the form, the user must keep filling in all the form values after each error, which will increase the likely-hood of introducing more errors.

 

you should put the form processing code and the form on the same page so that you can display all the validation errors when you re-display the form and you can populate the form fields with the existing values so that the user doesn't need to keep re-tying in all the values when there is a validation error.

 

edit, you should also list the column names in the INSERT query so that your code will be self-documenting and will still work if the columns get rearranged.

Edited by mac_gyver
Link to post
Share on other sites

edit, you should also list the column names in the INSERT query so that your code will be self-documenting and will still work if the columns get rearranged.

 

^^^ and which is required if you leave out a column value.

Link to post
Share on other sites

 

Obviously if you don't have all the data required for the insert, you are going to get an error. One issue that you have made for yourself unnecessarily is:

 

$user_id="";
$date_created = date( 'Y-m-d H:i:s' );
$date_edited ="";

So i'm going to assume that you have an auto_increment for user_id, and date_edited can be null, and since this is a new row you want that to be NULL on insert.

 

In this case, you should not be passing values for these. MySQL figures out how to auto_increment, and it makes no sense to pass a NULL date string for a date parameter that you will never set in this context.

 

 

Change the insert so that those are not included:

 

$stmt = $connection->prepare("INSERT INTO users values(?,?,?,?,?,?)");
$stmt->bind_param("s,s,s,s,s,s", $firstname, $lastname, $username, $hashed_password, $date_created, $status_id);

Thanks for the useful comment, i will revise and remove unnecessary variable; although the problem was due to commas on type specifier on bind param()

 

changed to;

$stmt = $connection->prepare("INSERT INTO users (firstname,lastname,username,password,date_created,status_id) VALUES (?,?,?,?,?,?)");

$stmt->bind_param("sssssi", $firstname, $lastname, $username, $hashed_password, $date_created, $status_id);

 

and it worked!..

Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.