U4-4232 - Umbraco.Core.Persistence.Querying issue with backslashes

Created by Stephan 14 Feb 2014, 09:12:22 Updated by Shannon Deminick 18 Feb 2014, 03:56:02

We have user logins such as DOMAIN\LOGIN and they were working fine in 4.7. When upgrading to 6.2 the backend does not support them anymore because in UserService.GetByUsername(string login) we do var query = Query.Builder.Where(x => x.Username.Equals(login)); and the Builder does ''not'' escape the backslash, so it ends up being sent to SQL as a single backslash, which SQL understands as an escaped char.

Just adding login = login.Replace("\", "\\"); in GetByUsername does fix the issue but...

Is this not a general issue with querying?

Comments

Shannon Deminick 17 Feb 2014, 03:27:51

I've just created a unit test for this which works, are you using MySql or ?


Stephan 17 Feb 2014, 07:32:27

Yes using MySql. So could it be that there's something in the MSSQL provider that automagically escapes strings?


Shannon Deminick 17 Feb 2014, 09:51:31

Righto, yup most likely a MySql thing. That should be easy to fix with our SQL syntax providers. MSSQL must deal with backslashes in sql arguments correctly I suppose. I do remember back when I used to use MySql alot I had to call a mysql escape method all the time.


Stephan 17 Feb 2014, 09:55:51

SQL... "what's good about standards is that everybody can have their own"...


Shannon Deminick 17 Feb 2014, 10:00:58

lol, looks like MySql would deal with it if these were query params but with the ModelToSqlExpressionHelper, this builds up SQL statements with values not with parameters. Would be nice to have the Query be able to build SQL up with params instead - better for everything really. So MSSQL must just deal with single backslash without issue as a value.


Shannon Deminick 17 Feb 2014, 10:35:33

I've streamlines how things are escaped, just gonna run some more tests, will push tomorrow.


Shannon Deminick 17 Feb 2014, 10:40:23

Actually I've pushed that up now, works in MSSQL, haven't yet tested in mysql, if you have time today let me know, otherwise I'll try tomorrow.


Shannon Deminick 18 Feb 2014, 03:55:53

Tested and fixed


Shannon Deminick 18 Feb 2014, 03:56:02

f1a439e8512b655728ea71d82ffdceabbddcf950


Priority: Normal

Type: Bug

State: Fixed

Assignee: Shannon Deminick

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions: 6.2.0

Due in version: 7.1.0, 6.2.0

Sprint:

Story Points:

Cycle: