View Issue Details

IDProjectCategoryView StatusLast Update
0004963OXID eShop (all versions)4.01. Database handlingpublic2015-05-04 14:55
Reportermanuel Assigned To 
PriorityhighSeveritycrashReproducibilityalways
Status resolvedResolutionfixed 
Product Version4.7.1 / 5.0.1 revision 52468 
Target Version4.7.10 / 5.0.10Fixed in Version4.7.10 / 5.0.10 
Summary0004963: Login doesn't work with Mysql 5.5 and iUtfMode=1
DescriptionI 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:
https://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html#function_md5
"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 ReproduceSet 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 '='
TagsNo tags attached.
ThemeBoth
BrowserAll
PHP Version5.3
Database Version5.5

Relationships

parent of 0004878 resolved After switch to MySQL 5.5, login not possible any longer 
related to 0004173 resolvedjurate.baseviciene Remove password-generation/hashing from database to php 

Activities

Linas Kukulskis

2013-02-25 17:39

reporter   ~0008453

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?

manuel

2013-02-26 09:07

reporter   ~0008458

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.

Linas Kukulskis

2013-03-04 15:52

reporter   ~0008468

Reminder sent to: manuel

hi,

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.

saulius.stasiukaitis

2013-11-21 14:45

reporter   ~0009291

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(...""

saulius.stasiukaitis

2013-11-21 17:11

reporter   ~0009292

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.