Skip to content

With MySQL use cast(okey as unsigned) instead of cast(okey as integer)

Eike Rathke requested to merge github/fork/erAck/mysql-cast into master

The autologin ("Save information") functionality in 2.3.1 is broken since

commit 52a41b37d554da11acc932eeec44c5fb1414a492
CommitDate: Fri Mar 23 18:01:32 2018 +0100

Rework autologin to use a token approach

Although a cookie serendipity[author_autologintoken] with correct expiration (one month) which random data content is present as value in the serendipity_options table with name autologin_Username and correct timestamp as okey and that is found with manually executing the SQL statement

SELECT name, value, okey FROM serendipity_options WHERE name = 'autologin_Username' AND okey > 1565801743 LIMIT 1

like done in include/functions_config.inc.php serendipity_checkAutologin(), the login is forgotten after 30 minutes or so. That was not the case with 2.1.5 where the login was valid for weeks.

Of

if (stristr($serendipity['dbType'], 'sqlite')) {
    $cast = "okey";
} else {
    // Adds explicits casting for mysql, postgresql and others.
    $cast = "cast(okey as integer)";
}

from which $cast then is used in the SQL statement instead of a plain okey; when doing that manually with

SELECT name, value, okey FROM serendipity_options WHERE name = 'autologin_Username' AND cast(okey as integer) > 1565801743 LIMIT 1

it produces the MySQL error

#1064 - 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 'integer) > 1565801743 LIMIT 1' at line 1

This also with $serendipity['dbType'] = 'mysqli' for the above code.

Indeed, cast(okey as integer) is invalid in MySQL and should be cast(okey as unsigned) instead which then also works manually, see https://stackoverflow.com/a/12127022 and https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html#function_cast

Same in serendipity_issueAutologin().

Changing those two places accordingly resolves the autologin not persistent problem.

Additionally, inspecting the serendipity_options table revealed loads of old serendipity[author_authorinformation] cookie information that was never deleted in serendipity_issueAutologin() with the

OR (okey LIKE 'l_%' AND $cast < " . (time() - 1814400) . ")")

expression producing a MySQL error. This has to be done manually once as also 2.3.1 will not delete it anymore.

Merge request reports