CODEWITHSUNDEEP
phpmysqlmysqli
AndroidDev
AndroidDev

Reputation: 21237

Erratic results with mysqli insert query

I'm running the same mysqli insert query using the exact same parameters. It is successful about 1/3 of the time, and I cannot figure out what is going wrong.

<?php
    //...
    $decrypted_session_key = 'unavailable'; // initialize
    $res = openssl_get_privatekey($priv_key, $passphrase);
    $result = openssl_private_decrypt($encrypted_session_key, $decrypted_session_key, $res, OPENSSL_PKCS1_OAEP_PADDING);

    if ($decrypted_session_key == 'unavailable') {
        mysqli_close($link);
        echo json_encode(array('result' => 'failed', 'message' => 'failed to decrypt the session key'));
        die();
    }

    if (!$decrypted_session_key) {
        echo json_encode(array('result'=>'failed', 'message'=>'decrypted session key has failed'));
        die();
    }

    $updated_at = date("Y-m-d H:i:s");

    // save this record to the database
    $result = mysqli_query($link, "INSERT INTO Session_Keys (session_id, session_key, iv, updated_at)
                                   VALUES ($session_id, '$decrypted_session_key', '$iv', '$updated_at')");

    if (!$result) { 
        $param_check = $session_id . " " . base64_encode($iv) . " " . base64_encode($decrypted_session_key) . " " . $updated_at;
        echo json_encode(array('result'=>'failed', 'message'=>$param_check));
        die();
    }

    // ...
}

Whenever this fails, I get the last echo statement returned. My suspicion was that the php decryption routine was failing, but it's not. All of the parameters are perfect, including the decryption value. Is the problem that my insert statement is wrong? I've tried various combination of quoting the fields, but nothing consistent results.

The table structure is like this:

'session_id' int(11)
'session_key' tinyblob
'iv' tinyblob
'updated_at' datetime

enter image description here I don't understand why my results are so inconsistent. If it fails, why not fail every time? If it works, why doesn't it work every time? Very confused. Any help is appreciated. Thanks!

Upvotes: 1

Views: 95

Answers (2)

Funk Forty Niner
Funk Forty Niner

Reputation: 74217

Having suggested to the OP in comments to use or die(mysqli_error($link)) to mysqli_query() led them to find the reason why their query was failing.

Their comments:

"I finally got at the error message here it is: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<´§y?'T]î‘Á gô, EÈöJÀÊ?Xû¶T¢ÑÖ?, 2015-04-05 19:26:00)' at line 2 Must be something to do with the bytes that I am trying to store."

and

"Now I understand the problem. Your suggestion about looking at mysqli_error is what led me to the solution (after lots of difficulty on my end to gain access to it!). The real error (after I correctly quoted my fields) is Duplicate entry '2147483647' for key 'PRIMARY'. My ios app is randomly assigning an unsigned integer value (0 - 4294967294) but an int(11) field maxex out at 2147483647. So, half of my values were received as duplicates, thereby causing an error. Problem solved. Thanks for your help!"

Pulled from:

https://stackoverflow.com/a/17783287/

2147483647 is the largest int value for mysql. Just change the type from int to bigint.

  • which explains the duplicate error.

Upvotes: 1

anurag
anurag

Reputation: 630

Did you try committing the transaction?

/* commit transaction */
if (!$mysqli->commit()) {
    print("Transaction commit failed\n");
    exit();
}

http://php.net/manual/en/mysqli.commit.php

Upvotes: 0

Related Questions