Hi. This is a weird one that I spent the past 8 hours working on. I have an extremely stable project used for past 2.5 years in production. Have been converting and testing to run under SC9. and PHP7. Today I noticed that one of my main data entry forms was failing to save certain field values into the DB.
I use text type fields to have users put in regular names and data in the clear. For instance, first names are in {firstCLR}
I have always used the onBeforeInsert event to basically run selects with AES_encrypt to encrypt fields like {firstCLR} that I call in the clear fields. The value is stored in a field called {firstAES} that is a varbinary field in the database. Never had any issues.
Howeever, something in the form app must have changed from 8.1 to 9.1/9.2. When I try to save only certain things, it will fail to save the correct encrypted string. I used phpmyadmin to inspect and found that extra hex 5c values would be found in the varbinary fields. If I put in the name James it fails to read it back. That is because the hex encrypted string has an extra 5c hex. 5c hex is a slash . That happens to be the escape symbol. That is because if you look at the value as text the encryption created a ’ in the result. What is happening is that before 9.1 the {firstAES} field would just get saved. It appears that what is happening is that SC is now running an extra escape routine on all fields. Maybe the sc_sql_protect??? And that is inserting extra 5c symbols. These are getting saved into the varbianary and not being removed by mysql???
My work around right now is to onAfterInsert and onAfterUpdate to take all the fields like {firstCLR} and first protect myself with sc_sql_protect, then do an manual update of the data record. When I do that, my varbinary show hex code without the extra multple 5c values.
Again, I know it is due to changing to SC9.1/9.2 and PHP7 all at once. But what needs to happen in SC form settings - finer control of escaping in the field settings.
BTW I also tried passing encrypted values as global variables back to the field setting - database insert and update overrides. But these values are getting unneeded 5c added even as a forced defines value!!!
Perhaps because mysql escape string is depricated and gone in PHP7 they (SC) did some code changes???