MySQLi insert, successful database connection but not successfully inserted

Question

I'm attempting to insert some data into a table using mysqli functions.

My connection works fine using the following:

function connectDB(){
    // configuration
    $dbuser     = "root";
    $dbpass     = "";

    // Create connection
    $con=mysqli_connect("localhost",$dbuser,$dbpass,"my_db");

    // Check connection
    if (mysqli_connect_errno()) {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
        return false;
    }else{
        echo '<br />successfully connected<br />';
        return $con;
    }
}

But when I attempt to run my insert function I get nothing in the database.

function newUserInsertDB($name,$email,$password){
    $con = connectDB();

    // Prepare password
    $password = hashEncrypt($password);
    echo $password . "<br />";

    // Perform queries 
    mysqli_query($con,"SELECT * FROM users");
    mysqli_query($con,"INSERT INTO users (name,email,password,isActivated) VALUES ($name,$email,$password,0)");

    // insert
    mysqli_close($con); 
}

I have been looking through the list of mysqli functions for the correct way to give errors but they all seem to be regarding the connection to the DB, not regarding success of an insert (and I can clearly see in my DB that it is not inserting.)

What would be the best way to debug? Which error handling shall I use for my insert?

I've tried using mysqli_sqlstate which gives a response of 42000 but I cannot see any syntax errors in my statement.

Add this just above your `$con = ...` line in `connectDN()` ~ `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);`. This makes the *mysqli* functions throw exceptions if they encounter any errors. You may also need to set `display_errors = On` and `error_reporting = E_ALL` in your `php.ini` file and then restart your web server — Phil, Oct 09 '14 at 21:23
Also, read this article on using prepared statements in *mysqli*. This will help you secure your queries from SQL injection vulnerabilities ~ http://php.net/manual/mysqli.quickstart.prepared-statements.php — Phil, Oct 09 '14 at 21:25

Phil · Accepted Answer · 2014-10-09T21:34:04.027

As mentioned in my comment, you would be better off using a prepared statement. For example...

$stmt = $con->prepare(
    'INSERT INTO users (name, email, password, isActivated) VALUES (?, ?, ?, 0)');
$stmt->bind_param('sss', $name, $email, $password);
$stmt->execute();

Using this, you don't have to worry about escaping values or providing quotes for string types.

All in all, prepared statements are much easier and much safer than attempting to interpolate values into an SQL string.

I'd also advise you to pass the $con variable into your function instead of creating it within. For example...

function newUserInsertDB(mysqli $con, $name, $email, $password) {
    // Prepare password
    $password = hashEncrypt($password);

    // functions that "echo" can cause unwanted side effects
    //echo $password . "<br />";

    // Perform queries 
    $stmt = $con->prepare(
        'INSERT INTO users (name, email, password, isActivated) VALUES (?, ?, ?, 0)');
    $stmt->bind_param('sss', $name, $email, $password);
    return $stmt->execute(); // returns TRUE or FALSE based on the success of the query
}

score 0 · Answer 2 · answered Oct 09 '14 at 21:24

The quotes are missing from the mysql statement from around the values. Also, you should escape the values before inserting them into the query. Do this way:

mysqli_query($con,"INSERT INTO users (name,email,password,isActivated) VALUES ('".
mysqli_real_escape_string($con,$name)."','".
mysqli_real_escape_string($con,$email)."','".
mysqli_real_escape_string($con,$password)."',0)");

Regards

MySQLi insert, successful database connection but not successfully inserted

2 Answers2