Olamilekan Oshodi Olamilekan Oshodi - 3 months ago 19
MySQL Question

Trouble sanitizing form with prepared statement

Hello i was hoping could share light. I believe am make progress so far. i have a registration here:

What i wanted to do is sanitized my data before sending it database.

<?php


session_start();
if(is_file('include/connection.php'))
include_once('include/connection.php');
else
exit('Database FILES MISSING:(');


if(isset($_POST['submit'])) {
$errors = array();
$data = array();
$name = mysqli_real_escape_string($_POST['name']);
$last_name = $_POST['last_name'];
$user_name = $_POST['user_name'];
$user_type = $_POST['user_type'];
$email = $_POST['email'];
$password = $_POST['password'];
$confirm_password = $_POST['confirm_password'];
// $created_at = $_POST['created_at'];
$password_hash = password_hash($password, PASSWORD_DEFAULT);

//$created_at = date('Y-m-d');
if(!($stmt = $mysqli->prepare("INSERT INTO user (name, last_name, user_name, user_type, email, password)
VALUES (?,?,?,?,?,?)"))){
echo "Prepare failed: (" . $mysqli->errno . ")" . $mysqli->error;
}

if(!$stmt->bind_param('ssssss', $name, $last_name, $user_name, $user_type, $email, $password_hash)){
echo "Binding paramaters failed:(" . $stmt->errno . ")" . $stmt->error;
}

if(!$stmt->execute()){
echo "Execute failed: (" . $stmt->errno .")" . $stmt->error;
}

if($stmt) {
$_SESSION['main_notice'] = "Successfully registered, login here!";
header('Location: index.php');

}
else{
echo "Registration failed";
}

}

$mysqli->close();

?>



Execute failed: (1048)Column 'name' cannot be null


Above is the error am getting when I submit the data.

Have tried using
mysqli_real_escape_string
AND
mysqli->real_escape_string


Can some advice if i still need to satinized even tho have bind the data.




Also in my database i have created_at field. see below code.

$created_at = $_POST['created_at'];
$password_hash = password_hash($password, PASSWORD_DEFAULT);

$created_at = date('Y-m-d');
if(!($stmt = $mysqli->prepare("INSERT INTO user (name, last_name, user_name, user_type, email, password, created_at)
VALUES (?,?,?,?,?,?)"))){
echo "Prepare failed: (" . $mysqli->errno . ")" . $mysqli->error;
}

if(!$stmt->bind_param('ssssss', $name, $last_name, $user_name, $user_type, $email, $password_hash, $created_at)){
echo "Binding paramaters failed:(" . $stmt->errno . ")" . $stmt->error;
}


Am getting this error:


Prepare failed: (1136)Column count doesn't match value count at row 1
Fatal error: Call to a member function bind_param() on a non-object in /home/olami560/public_html/project/allocation/progress/register.php on line 30


Your help will be appreciated. thanks

Answer

Issue #1, mysqli_real_escape_string requires a connection link when used procedurally. You don't need to escape when binding. If you were to escape it would be:

$name = mysqli_real_escape_string($mysqli, $_POST['name']);

or

$name = $mysqli->real_escape_string($_POST['name']);

Issue #2, you have 7 columns listed and 7 values trying to be written but only have 6 placeholders and 6 string types.

So update to:

 if(!($stmt = $mysqli->prepare("INSERT INTO user (name, last_name, user_name, user_type, email, password, created_at) 
            VALUES (?,?,?,?,?,?,?)"))){
            echo "Prepare failed: (" . $mysqli->errno . ")" . $mysqli->error;
        }

        if(!$stmt->bind_param('sssssss', $name, $last_name, $user_name, $user_type, $email, $password_hash, $created_at)){
         echo "Binding paramaters failed:(" . $stmt->errno . ")" . $stmt->error;
        }

Note the number of ?s, those are placeholders, and the number of ss in the bind_param. If the missing value is an integer, not a string change the last s to an i or whatever data type it is.

Comments