i am using mysqli statement in procedural php..... data types dont work

Question

The column test_int is an integer however when I execute the below query it shows successful. I wonder how it can be successful because I am entering a string $id = "tett"; I think it should not execute the query because the data type does not match.

<?php


include('db_connect.php');


// Prepare an insert statement
$sql = "INSERT INTO admin (test_int, username, password, maname) VALUES (?, ?, ?, ?)";

if($stmt = mysqli_prepare($con, $sql)){
    // Bind variables to the prepared statement as parameters
    mysqli_stmt_bind_param($stmt, "isss", $id, $username, $password, $maname);

    // Set parameters
    $id = "tett";
    $username = "New username";//$_REQUEST['first_name'];
    $password = "New password";//$_REQUEST['last_name'];
    $maname = "New Name";//$_REQUEST['email'];

    // Attempt to execute the prepared statement
    if(mysqli_stmt_execute($stmt)){
        echo "Records inserted successfully.";
    } else{
        echo "ERROR: Could not execute query: $sql. " . mysqli_error($con);
    }
} else{
    echo "ERROR: Could not prepare query: $sql. " . mysqli_error($con);
}

// Close statement
mysqli_stmt_close($stmt);

// Close connection
mysqli_close($con);

score 1 · Accepted Answer · answered Jun 16 '20 at 07:57

The type you specify in bind_param is telling PHP what it should cast the value as. 99.99% of the time you should cast it as a string, so that you do not run into issues. If you cast it as an integer PHP will do the usual casting. Since tett can't be converted into a proper number, PHP will simply convert it into a 0.

By the way, you must remember to enable error reporting for mysqli. Put this line before mysqli_connect():

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

You can also make your code much simpler if you remove all unnecessary stuff.

<?php

include 'db_connect.php';

// Prepare an insert statement
$sql = "INSERT INTO admin (test_int, username, password, maname) VALUES (?, ?, ?, ?)";

$stmt = $con->prepare($sql);
// Bind variables to the prepared statement as parameters
$stmt->bind_param("ssss", $id, $username, $password, $maname);

// Set parameters
$id = "tett";
$username = "New username"; //$_REQUEST['first_name'];
$password = "New password"; //$_REQUEST['last_name'];
$maname = "New Name"; //$_REQUEST['email'];

// Attempt to execute the prepared statement
$stmt->execute();
echo "Records inserted successfully.";

Never store passwords in clear text or using MD5/SHA1! Only store password hashes created using PHP's password_hash(), which you can then verify using password_verify(). Take a look at this post: How to use password_hash and learn more about bcrypt & password hashing in PHP

What exactly does "ssss" do in this statement? $stmt->bind_param("ssss", $id, $username, $password, $maname); — Clifton Miranda, Jun 16 '20 at 08:50
The `ssss` tells mysqli that the values should be bound as strings. Each s is for a single parameter. — Dharman, Jun 16 '20 at 09:54
@Paedow nobody called error handling unnecessary, it would be just *misplaced* here. Error handling should be centralized and uniform. Please see [this article](https://phpdelusions.net/articles/error_reporting) and also this [answer of mine](https://codereview.stackexchange.com/a/243749/101565) that explains why your mysqli code shouldn't handle its errors to report them. — Your Common Sense, Jun 16 '20 at 11:32

i am using mysqli statement in procedural php..... data types dont work

1 Answers1