View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0004963||OXID eShop (all versions)||4.01. Database handling||public||2013-02-25 14:04||2015-05-04 14:55|
|Product Version||4.7.1 / 5.0.1 revision 52468|
|Target Version||4.7.10 / 5.0.10||Fixed in Version||4.7.10 / 5.0.10|
|Summary||0004963: Login doesn't work with Mysql 5.5 and iUtfMode=1|
|Description||I think the problem from Ticket 4878 (https://bugs.oxid-esales.com/view.php?id=4878) is not realy solved.|
It could not be that i have to manually change the character set of oxpassword and oxpasssalt for each installation/setup with mysql >5.5.
The real problem is that the return value of MD5 and other functions has changed:
"Some encryption functions return strings of ASCII characters: MD5(), OLD_PASSWORD(), PASSWORD(), SHA(), SHA1(). As of MySQL 5.5.3, their return value is a nonbinary string that has a character set and collation determined by the character_set_connection and collation_connection system variables. Before 5.5.3, these functions return binary strings. The same change was made for SHA2() in MySQL 5.5.6. "
I suggest a solution like changing _getLoginQuery()-method in oxuser:
In case of iUtfMode=1 you should use "oxuser.oxpassword = BINARY MD5( CONCAT(..." instead of "oxuser.oxpassword = MD5( CONCAT(..."
|Steps To Reproduce||Set up database with MySql >5.5 and set iUtfMode in config-file to "1"|
try to login and you'll get
0001267 - Illegal mix of collations (latin1_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
|Tags||No tags attached.|
|parent of||0004878||resolved||After switch to MySQL 5.5, login not possible any longer|
|related to||0004173||resolved||jurate.baseviciene||Remove password-generation/hashing from database to php|
Reminder sent to: manuel
Can you give as more details: do you have this problem on pure installation, during which you select create utf shop? and on first login to shop with user created during setup shop is broken?
||I've installed a fresh version of oxid 5.0.1 locally and deployed it to a system with an oxid-database from version 4.6.4 which has been updated to 5.0.1 schema and mysql 5.5. I've set up the iUtfMode in config.inc.php to 1 because the old shop on the system already ran in this mode.|
Reminder sent to: manuel
problem actually is clear (mix up with collations etc.), but i cant reproduce it with user table, can you attache db dump in which you get this error.
Fixed as it was suggested:
"I suggest a solution like changing _getLoginQuery()-method in oxuser:
In case of iUtfMode=1 you should use "oxuser.oxpassword = BINARY MD5( CONCAT(..." instead of "oxuser.oxpassword = MD5( CONCAT(...""
|Problem occurred because MySQL 5.1 refer password field as latin1. MySQL 5.5 in other hand refer password field as utf8. This happened because password field was not specified which will change in next major version.|