Erratic results with mysqli insert query

Question

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!

You're using incorrect identifiers in your column declaration. Add `or die(mysqli_error($link))` to `mysqli_query()` and you will see the syntax error being made. Also make sure `$session_id` isn't a string being passed. — Funk Forty Niner, Apr 05 '15 at 18:35
I see you remove the quotes around `updated_at)` - You had `'updated_at')` earlier. That's why I said that. — Funk Forty Niner, Apr 05 '15 at 18:40
Yes. That was a typo. It isn't in my actual code. Sorry about that. — AndroidDev, Apr 05 '15 at 18:41
Where is `$session_id` coming from? Are you using sessions? Is that variable's value being passed as an integer or a (mixed) string? — Funk Forty Niner, Apr 05 '15 at 18:49
It's coming from an ios mobile app as an `NSNumber`. It's just a randomly generated integer between 0 and MAX_INT (I'm not using sessions for this). I tried ignoring it by modifying my script to include a line `$session_temp = 1234` and updating the mysqli_query line to use that variable, but I still get an error. — AndroidDev, Apr 05 '15 at 19:14
What is the exact error, or are you using a custom `echo "Error";` message? Consider using error reporting if you're not already doing so. Try to add error reporting to the top of your file(s) right after your opening PHP tag for example ` — Funk Forty Niner, Apr 05 '15 at 19:15
I'm having great difficulty exposing the `mysqli_error` message since this echoes back to the mobile app. For some reason, I can't get the app to print out this error. I need to wrap it in a json object, and the decoding fails on the app side. Auggh! I appreciate all of your help, however. — AndroidDev, Apr 05 '15 at 19:23
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. — AndroidDev, Apr 05 '15 at 19:28
I suggest you use `mysqli_real_escape_string()` for all your variables going into the INSERT then. That will most likely fix it. Do you know how to use that function? And/or try `VALUES ('$session_id',` if it's related to the first variable, using quotes for it also. Seems like it to me. — Funk Forty Niner, Apr 05 '15 at 19:29
You will also need to modify your `session_id` column to be a `varchar` and long enough to accomodate that long string. That's if that column's value `<´§y˙'T]î‘Á gô, EÈöJÀÊ∫Xû¶T¢ÑÖ◊` is the one going into `session_id`. — Funk Forty Niner, Apr 05 '15 at 19:37
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! — AndroidDev, Apr 05 '15 at 19:44
That's great Randall and you're quite welcome, I'm glad it was finally resolved. I hope the answer given below is satisfactory. *Cheers* — Funk Forty Niner, Apr 05 '15 at 19:48

score 1 · Accepted Answer · edited May 23 '17 at 10:24

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.

@Randall You're welcome Randall. It was the 2147483647 causing the duplicate error after all. I thought that number rang a distant bell lol! I've added that to my answer, should you not have seen it already; *cheers!* — Funk Forty Niner, Apr 05 '15 at 19:52
Yeah, am glad this one was resolved! *Afternoon Sam!* - @JayBlanchard — Funk Forty Niner, Apr 06 '15 at 20:30

score 0 · Answer 2 · answered Apr 05 '15 at 18:26

0

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

answered Apr 05 '15 at 18:26

anurag

560
6
13

I hadn't, but it isn't solving anything. Thanks for the suggestion. – AndroidDev Apr 05 '15 at 18:34

Erratic results with mysqli insert query

2 Answers2