Fatal error: 1048:Column 'value' cannot be null :: insert into sessions (sesskey, ex

Here is more info to go by.

Apache 2.4
MySQL 5.6 - Mode: STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION
PHP 5.6.29
Zen Cart 1.5.5

[07-Jan-2017 23:05:19 America/Denver] Request URI: /***-c-24/***-c-24_29/***-p-371.html, IP address: ***
#1  trigger_error() called at [/***/***/public_html/includes/classes/db/mysql/query_factory.php:167]
#2  queryFactory->show_error() called at [/***/***/public_html/includes/classes/db/mysql/query_factory.php:139]
#3  queryFactory->set_error() called at [/***/***/public_html/includes/classes/db/mysql/query_factory.php:266]
#4  queryFactory->Execute() called at [/***/***/public_html/includes/functions/sessions.php:69]
#5  _sess_write()
#6  session_write_close() called at [/***/***/public_html/includes/application_bottom.php:16]
#7  require(/***/***/public_html/includes/application_bottom.php) called at [/***/***/public_html/index.php:105]

[07-Jan-2017 23:05:19 America/Denver] PHP Fatal error:  1048:Column 'value' cannot be null :: insert into zen_sessions (sesskey, expiry, `value`)
            values ('469db6ea8476d9b0150fc3551463e107', 1483856959, null)
            ON DUPLICATE KEY UPDATE `value`=null, expiry=1483856959 ==> (as called by) /***/***/public_html/includes/functions/sessions.php on line 69 <== in /***/***/public_html/includes/classes/db/mysql/query_factory.php on line 167

Although I do believe the above are default files, I'd like to mention that Dual Pricing module has been installed. Not sure which version exactly and it has been modified a bit by other coders.

Website Rob:

Here is more info to go by.

Apache 2.4
MySQL 5.6 - Mode: STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION
PHP 5.6.29
Zen Cart 1.5.5

[07-Jan-2017 23:05:19 America/Denver] Request URI: /-c-24/-c-24_29/-p-371.html, IP address: ***
#1 trigger_error() called at [///public_html/includes/classes/db/mysql/query_factory.php:167]
#2 queryFactory->show_error() called at [///public_html/includes/classes/db/mysql/query_factory.php:139]
#3 queryFactory->set_error() called at [///public_html/includes/classes/db/mysql/query_factory.php:266]
#4 queryFactory->Execute() called at [///public_html/includes/functions/sessions.php:69]
#5 _sess_write()
#6 session_write_close() called at [///public_html/includes/application_bottom.php:16]
#7 require(///public_html/includes/application_bottom.php) called at [//***/public_html/index.php:105]

[07-Jan-2017 23:05:19 America/Denver] PHP Fatal error: 1048:Column 'value' cannot be null :: insert into zen_sessions (sesskey, expiry, value)
values ('469db6ea8476d9b0150fc3551463e107', 1483856959, null)
ON DUPLICATE KEY UPDATE value=null, expiry=1483856959 ==> (as called by) ///public_html/includes/functions/sessions.php on line 69 <== in ///public_html/includes/classes/db/mysql/query_factory.php on line 167

> 
> Although I do believe the above are default files, I'd like to mention that Dual Pricing module has been installed. Not sure which version exactly and it has been modified a bit by other coders.

Requester information has been obscured. Please identify if was a "personal" search or if was a spider to the best of your determination.

Not sure what you mean by "Requester information has been obscured" as an error msg. is an error msg.

To answer your question though, I can confirm it was a Google Bot.

Website Rob:

Not sure what you mean by "Requester information has been obscured" as an error msg. is an error msg.

To answer your question though, I can confirm it was a Google Bot.

Not all error messages are created equally. :)

Having an error message identifies that there was an error. In this case, understanding the source/reason for the error message is important to working with it. Seems that there are several parts confirmed. Bot doesn't get $_SESSION data started, a session_id is generated and attempted to be stored even though there is no data to store with it. And it does appear that the default "result" of trying to feed an empty session is to push a string that is the result of base64_decode('null'); or there already exists something in the database that allows providing a stored value of 'null'. Either that or I've completely misunderstood how sessions and the sessions file work.

By the requestor information being obscured I was referring to what looked like would have been the ip address of the visitor. I can't say I see that error message much to know if it was to be the ip address of the store reporting the error or that of the one "causing" the error.

Regardless, at least for now it indicates that the problem isn't with session storage for cases when sessions work.

So presumably then the changes of zc 1.5.5a-d have not been applied and/or lat9s suggested modification as a result of not applying

mc12345678:

So presumably then the changes of zc 1.5.5a-d have not been applied and/or lat9s suggested modification as a result of not applying

Are you stating this particular problem has been corrected in 1.5.5d?

Website Rob:

Might want to flesh that out a bit. I tried your code with MySQL in STRICT_TRANS_TABLES mode and got a broken page with the error msg.

var-type undefined: stringIgnoreNull
That's because you're running ZC 1.5.5; the ***stringIgnoreNull ***value was added in Zen Cart 1.5.5a's /includes/classes/db/mysql/query_factory.php. You can safely use the ZC 1.5.5d version of that file on your 1.5.5 store.

Would like to point out some confusion though as this thread is about a "cannot be null" error in 1.5.5 and pretty close to what to I was experiencing. Was also not aware of the code change made in 1.5.5a till mentioned by mc12345678. I didn't see a Zen Version version number in your post on page 2 about using the "stringIgnoreNull" so not sure what version you were referring to for the change. Will try what you suggest though and see what results will be.

I also appreciate your help in the other, unrelated matter and glad you got it resolved so quickly.

Website Rob:

Are you stating this particular problem has been corrected in 1.5.5d?

No, the overall problem at least as of the version 1.5.5d released what the 28th of December (second reported release version) had not specifically resolved the issue. Versions a-d at least offered the datatype that would have resolved the issue of an incorrect datatype reported when attempting to apply it's use.

It would allow going down at least one path made available (applying the datatype of stringIgnoreNull) which would maintain the way session data has previously been handled. Previously the "session data" was stored in the database as a string containing the word null. This was instead of treating a string that is fully capitalized with the word NULL as a 'value' of null (rather than as a string).

Changes made to further the expected use of such strict settings may still have other areas within ZC to be so minorly modified to mitigate such issues, for example the second release of 1.5.5d was actually to remove one of the "features" added that unfortunately caused issue with modifying product via the admin screens. It didn't fully incorporate the possibility of a database being set with strict settings and the impact on the admin side.

Now as to the comment about whether the NULL session data should be stored or not, almost seems like a chicken-egg situation and the answer could be that in order to meet one set of "requirements" it must be stored as a string containing null so that when it is read and decoded, that the result of such decoding is a value of NULL (not a string), but then on the flip side, until that string containing null is put into the database, reading the session data would return an empty string not a value of NULL and therefore would indicate that the _sess_read function prior to the data being written to the database does not provide the same response as what would be fed to the _sess_write function... so in that light of "conflict", I go back to what is the benefit of writing the session_id to the database where the session will be set to NULL if stringIgnoreNull is used or is set to "" if the session was not previously stored. That said, and upon some additional thought to not store a session of NULL would also require not setting/storing a session = "" unless an attempt to restore a session with data = "" would provide an equivalent result as returning that the session = null.

Website Rob:

Would like to point out some confusion though as this thread is about a "cannot be null" error in 1.5.5 and pretty close to what to I was experiencing. Was also not aware of the code change made in 1.5.5a till mentioned by mc12345678. I didn't see a Zen Version version number in your post on page 2 about using the "stringIgnoreNull" so not sure what version you were referring to for the change. Will try what you suggest though and see what results will be.

I also appreciate your help in the other, unrelated matter and glad you got it resolved so quickly.
Rob, the issue is exactly what's being experienced on the site you manage (and you're welcome!).

Website Rob:

Not sure what you mean by "Requester information has been obscured" as an error msg. is an error msg.

To answer your question though, I can confirm it was a Google Bot.

Website Rob:

Would like to point out some confusion though as this thread is about a "cannot be null" error in 1.5.5 and pretty close to what to I was experiencing. Was also not aware of the code change made in 1.5.5a till mentioned by mc12345678. I didn't see a Zen Version version number in your post on page 2 about using the "stringIgnoreNull" so not sure what version you were referring to for the change. Will try what you suggest though and see what results will be.

I also appreciate your help in the other, unrelated matter and glad you got it resolved so quickly.

lat9:

Rob, the issue is exactly what's being experienced on the site you manage (and you're welcome!).

Which was confirmed to be on target with the "why" the problem was occurring by identifying the "visitor"/visitor type (item in the spiders.txt file causing the visitor to be identified as a spider and more than likely the default sessions values) that caused the error.

So yeah, on the same/right page/topic. Hopefully as a part of the discussion you've also now started looking at applying the fixes identified up to and including ZC 1.5.5d.

mc12345678:

So yeah, on the same/right page/topic. Hopefully as a part of the discussion you've also now started looking at applying the fixes identified up to and including ZC 1.5.5d.

It almost sounds like you are saying that using the most current version is a good thing. :P That should be a Slogan. :laugh:

Advice from all appreciated and Onward Ho! :D

Website Rob:

It almost sounds like you are saying that using the most current version is a good thing. :P That should be a Slogan. :laugh:

Advice from all appreciated and Onward Ho! :D

Can be (a good thing). :) at least on a test server it makes it easier to identify any possible issues ahead of time rather than just responding to what is identified by someone else. :)

If nothing else, offering some gentle reminders. :)

i have seen this problem on a few clients sites.... just to be clear, i have now updated:

includes/classes/db/mysql/query_factory.php

to the latest version. and have modified the _sess_write function in:

includes/functions/sessions.php

to such:

$sql = $db->bindVars($sql, ':zvalue', $val, 'stringIgnoreNull');

i'll see if this resolves the error.... i did see that the stringIgnoreNull as part of the bindVars in the query_factory.

best.

carlwhat:

i have seen this problem on a few clients sites.... just to be clear, i have now updated:

includes/classes/db/mysql/query_factory.php

to the latest version. and have modified the _sess_write function in:

includes/functions/sessions.php

to such:

$sql = $db->bindVars($sql, ':zvalue', $val, 'stringIgnoreNull');

> 
> i'll see if this resolves the error....  i did see that the stringIgnoreNull as part of the bindVars in the query_factory.
> 
> best.
That should correct the issue, @carlwhat, providing the same functionality (without the debug-log) as the original code.

**Update**:  I just had a drink of coffee and reread your post.   To which version of Zen Cart did you "back-port" the query_factory.php class-file?

My guidance to store owners receiving this error where their store's query_factory.php doesn't include the *stringIgnoreNull *handling**** would be to simply cast the value to a string prior to the database update rather than copying the entire class-file.

@lat9

re-reading my posts? sorry, i try to make things as clear and simple as possible...

my clients sites are at v1.5.5; i have not done any of the updates to a-b-c or d.... the differences in the class file were small; and in fact look solely related to the stringIgnoreNull situation. the date differences were as such:

- * @version $Id: Author: DrByte  Sun Feb 14 17:28:50 2016 -0500 Modified in v1.5.5 $
+ * @version $Id: Author: zcwuilt Fri Apr 15 Modified in v1.5.5 $

(not sure why the new versioning removed the year, the timestamp i can do without...)

frankly, it really was a minor annoyance; but i do like to address all debug logs. it's one of those things that the client never says anything about it.

so far so good.... if i see new errors related to this fix, i will post here in this thread.

thanks again.

@carlwhat, I was only saying that my coffee-level (or lack thereof) caused me to mis-read your post initially.

Migrating the most recent query_factory.php to an earlier ZC 1.5.5 releases should be A-OK; I was just concerned that moving the 1.5.5d version of the module to a site using an *earlier *Zen Cart version could be problematic.

I have recently begun running into this issue myself. I tried the solution suggested in post #34 above but that doesn't seen to be taking care of it. I'm posting one of the error logs below. I truncated the value of the session key because it was insanely long. I'm running version 1.5.5e of Zen Cart. Any advice is welcome.

[16-Oct-2017 14:41:43 America/New_York] Request URI: /catalog/metal-wine-racks-c-34_41/?zenid=2ebs3mmgmrqas9h520lq6enla1UNIONALLSELECTNULL--mXuy, IP address: 91.247.38.57
#1  trigger_error() called at [/XXX/XXX/XXX/public_html/XXX/catalog/includes/classes/db/mysql/query_factory.php:167]
#2  queryFactory->show_error() called at [/XXX/XXX/XXX/public_html/XXX/catalog/includes/classes/db/mysql/query_factory.php:139]
#3  queryFactory->set_error() called at [/XXX/XXX/XXX/public_html/XXX/catalog/includes/classes/db/mysql/query_factory.php:266]
#4  queryFactory->Execute() called at [/XXX/XXX/XXX/public_html/XXX/catalog/includes/functions/sessions.php:70]
#5  _sess_write()
#6  session_write_close() called at [/XXX/XXX/XXX/public_html/XXX/catalog/includes/application_bottom.php:16]
#7  require(/XXX/XXX/XXX/public_html/XXX/catalog/includes/application_bottom.php) called at [/XXX/XXX/XXX/public_html/XXX/catalog/index.php:105]

[16-Oct-2017 14:41:43 America/New_York] PHP Fatal error:  1048:Column 'sesskey' cannot be null :: insert into sessions (sesskey, expiry, `value`)
            values (null, 1508180743, 'c2VjdXJpdHlUb2tlbnxzOjMyOiJiMjYwOGQ4ZmIyNDgxMzhhMTE2Mjk3MGJkY2YwNzI1MyI7Y3VzdG9tZXJzX2hvc3RfYWRk')
            ON DUPLICATE KEY UPDATE `value`='c2VjdXJpdHlUb2tlbnxzOjMyOiJiMjYwOGQ4ZmIyNDgxMzhhMTE2Mjk3MGJkY2YwNzI1MyI7Y3VzdG9tZXJzX2hvc3RfYWRk', expiry=1508180743 ==> (as called by) /XXX/XXX/XXX/public_html/XXX/catalog/includes/functions/sessions.php on line 70 <== in /XXX/XXX/XXX/public_html/XXX/catalog/includes/classes/db/mysql/query_factory.php on line 167

This is what my includes\functions\sessions.php file looks like for the session write function. Did I miss something or do something incorrectly?

function _sess_write($key, $val) {
    global $db;
    if (!is_object($db)) return;
    $val = base64_encode($val);

    global $SESS_LIFE;
    $expiry = time() + $SESS_LIFE;

    $sql = "insert into " . TABLE_SESSIONS . " (sesskey, expiry, `value`)
            values (:zkey, :zexpiry, :zvalue)
            ON DUPLICATE KEY UPDATE `value`=:zvalue, expiry=:zexpiry";
    $sql = $db->bindVars($sql, ':zkey', $key, 'string');
    $sql = $db->bindVars($sql, ':zexpiry', $expiry, 'integer');
    //$sql = $db->bindVars($sql, ':zvalue', $val, 'string');
    $sql = $db->bindVars($sql, ':zvalue', $val, 'stringIgnoreNull');
    $result = $db->Execute($sql);

    return (!empty($result) && !empty($result->resource));
  }

Perhaps could identify how your store is different from a default store... plugins, customizations, etc... as well as the other information recommended in the posting tips that wasn't yet covered.

gwynwyffar:

I have recently begun running into this issue myself. I tried the solution suggested in post #34 above but that doesn't seen to be taking care of it. I'm posting one of the error logs below. I truncated the value of the session key because it was insanely long. I'm running version 1.5.5e of Zen Cart. Any advice is welcome.

[16-Oct-2017 14:41:43 America/New_York] Request URI: /catalog/metal-wine-racks-c-34_41/?zenid=2ebs3mmgmrqas9h520lq6enla1[B]UNIONALLSELECTNULL[/B]--mXuy, IP address: 91.247.38.57
#1 trigger_error() called at [/XXX/XXX/XXX/public_html/XXX/catalog/includes/classes/db/mysql/query_factory.php:167]
#2 queryFactory->show_error() called at [/XXX/XXX/XXX/public_html/XXX/catalog/includes/classes/db/mysql/query_factory.php:139]
#3 queryFactory->set_error() called at [/XXX/XXX/XXX/public_html/XXX/catalog/includes/classes/db/mysql/query_factory.php:266]
#4 queryFactory->Execute() called at [/XXX/XXX/XXX/public_html/XXX/catalog/includes/functions/sessions.php:70]
#5 _sess_write()
#6 session_write_close() called at [/XXX/XXX/XXX/public_html/XXX/catalog/includes/application_bottom.php:16]
#7 require(/XXX/XXX/XXX/public_html/XXX/catalog/includes/application_bottom.php) called at [/XXX/XXX/XXX/public_html/XXX/catalog/index.php:105]

[16-Oct-2017 14:41:43 America/New_York] PHP Fatal error: 1048:Column 'sesskey' cannot be null :: insert into sessions (sesskey, expiry, value)
values (null, 1508180743, 'c2VjdXJpdHlUb2tlbnxzOjMyOiJiMjYwOGQ4ZmIyNDgxMzhhMTE2Mjk3MGJkY2YwNzI1MyI7Y3VzdG9tZXJzX2hvc3RfYWRk')
ON DUPLICATE KEY UPDATE value='c2VjdXJpdHlUb2tlbnxzOjMyOiJiMjYwOGQ4ZmIyNDgxMzhhMTE2Mjk3MGJkY2YwNzI1MyI7Y3VzdG9tZXJzX2hvc3RfYWRk', expiry=1508180743 ==> (as called by) /XXX/XXX/XXX/public_html/XXX/catalog/includes/functions/sessions.php on line 70 <== in /XXX/XXX/XXX/public_html/XXX/catalog/includes/classes/db/mysql/query_factory.php on line 167

> 
> This is what my includes\functions\sessions.php file looks like for the session write function. Did I miss something or do something incorrectly?
> ```
function _sess_write($key, $val) {
    global $db;
    if (!is_object($db)) return;
    $val = base64_encode($val);

    global $SESS_LIFE;
    $expiry = time() + $SESS_LIFE;

    $sql = "insert into " . TABLE_SESSIONS . " (sesskey, expiry, `value`)
            values (:zkey, :zexpiry, :zvalue)
            ON DUPLICATE KEY UPDATE `value`=:zvalue, expiry=:zexpiry";
    $sql = $db->bindVars($sql, ':zkey', $key, 'string');
    $sql = $db->bindVars($sql, ':zexpiry', $expiry, 'integer');
    //$sql = $db->bindVars($sql, ':zvalue', $val, 'string');
    $sql = $db->bindVars($sql, ':zvalue', $val, 'stringIgnoreNull');
    $result = $db->Execute($sql);

    return (!empty($result) && !empty($result->resource));
  }

Did you edit that log, or was the portion I've highlighted above part of the log? If that was part of the log, then that's the root of the issue.

lat9:

Did you edit that log, or was the portion I've highlighted above part of the log? If that was part of the log, then that's the root of the issue.

WOW! great catch @lat9!

there is nothing the store owner can do to prevent this.

the store is in the NY state area. the IP address is from ukraine. it looks like someone is trying to do evil via a SQL injection.

the question exists whether this sort of attempt should generate a log in ZC or not. as there is nothing the store owner can do to prevent someone from manually manipulating the URL.

its funny, i have modified my function to handle issues like this. i did not post it earlier, as i can not remember why i did it. no doubt i did it to handle issues like this. here is my code. the section in red is the added part, and it certainly would address the error. feel free to use at your own discretion.

  function _sess_write($key, $val) {
    global $db;
    if (!is_object($db)) return;
    $val = base64_encode($val);

    global $SESS_LIFE;
    $expiry = time() + $SESS_LIFE;

    $sql = "insert into " . TABLE_SESSIONS . " (sesskey, expiry, `value`)
            values (:zkey, :zexpiry, :zvalue)
            ON DUPLICATE KEY UPDATE `value`=:zvalue, expiry=:zexpiry";
    $sql = $db->bindVars($sql, ':zkey', $key, 'string');
    $sql = $db->bindVars($sql, ':zexpiry', $expiry, 'integer');
    $sql = $db->bindVars($sql, ':zvalue', $val, 'stringIgnoreNull');
      if ($key != null) {
          $result = $db->Execute($sql);
        }
    return (!empty($result) && !empty($result->resource));
  }