revDataFromQuery is not case sensitive ?

[mrcoollion]

It seems that the revDataFromQuery is not case sensitive .
I have data in the database with uppercase letters (user ID's) and with the below statement if there is a lowercase version of the user ID in the AndString I still get the user data. This should not happen in my case!

Code: Select all

put "SELECT "&FieldNames&" FROM "& TableName&" WHERE " & AndString & OrderByString into tSQL 
put revDataFromQuery(,,DatabaseID,tSQL) into tRec

Te be complete, the AndString is build in a loop with AND and LIKE .

Code: Select all

put AndString &" AND " & tFieldname &" LIKE '" & DataArrayKeyFields[tFieldname]  & "'" into AndString

Any way to solve this?
I do not want to use the pragma command (if at all possible with revDataFromQuery ) because the pragma command is specific to SQLite and is very unlikely to be compatible with any other SQL database engine.

Any idea's?

Regards,

Paul

[SparkOut]

You could try adding a COLLATE to the query to specify an overriding collation to use in the comparison, that is case sensitive, if you have it configured on the server (eg Latin1_general_cs).

put "SELECT" && FieldNames && "FROM" && TableName && "WHERE" && AndString && "COLLATE Latin1_general_cs" && OrderByString into tSQL

For utf data you might find that MySQL queries with the utf8_general_cs collation are not supported even if the collation is configured on the server, you could use
put "SELECT" && FieldNames && "FROM" && TableName && "WHERE" && AndString && "COLLATE utf8_bin" && OrderByString && "COLLATE utf8_general_ci" into tSQL

Comparing with binary collation as above should be case sensitive but ordering collation would be thrown out, hence the second COLLATE for the ORDER BY part of the query.

[mrcoollion]

Thanks SparkOut,

I will give it a try after the new year festivities.
Happy New Year to you and all LIVECODE forum users and specialists.

I will let you know what the results are.

Regards,

Paul (mrcoollion)

[mrcoollion]

Sadly this did not work as hoped for.

Both below lines gave the same non case sensitive result.
(With this I mean that the outcome of the UniqueSingleKeyField is as in the Database (case sensitive) however the comparison in the AndString is not case sensitive.)

Code: Select all

put "SELECT "&UniqueSingleKeyField&" FROM "& TableName&" WHERE " & AndString & " COLLATE Latin1_general_cs " & OrderByString into tSQL

put "SELECT "&UniqueSingleKeyField&" FROM "& TableName&" WHERE " & AndString &  " COLLATE utf8_bin "  & OrderByString & " COLLATE utf8_general_ci" into tSQL

I am working with a SQLite database

Maybe the answer lies in using GLOB ?
see: https://sqlite.org/optoverview.html

Regards,

Paul

[mrcoollion]

Tested GLOB in place of LIKE and that seems to do the trick.
The WHERE part is now case sensitive!

Code: Select all

put AndString &" AND " & tFieldname &" GLOB '" & DataArrayKeyFields[tFieldname]  & "'" into AndString

Be aware that working with wildcards is different when using GLOB.
The GLOB operator is similar to LIKE but uses the Unix file globbing syntax for its wildcards. Also, GLOB is case sensitive, unlike LIKE. Both GLOB and LIKE may be preceded by the NOT keyword to invert the sense of the test. The infix GLOB operator is implemented by calling the function glob(Y,X) and can be modified by overriding that function.
E.g. with LIKE % would be used as wildcard and with GLOB one would use * as wildcard. For more information See: https://www.sqlite.org/lang_expr.html

Regards,

Paul

[sphere]

Hoi Paul,

i'm having a likewise issue, i use php files with PDO statements as middleware between LC and the MariaDB (mysql) database because other users need to access the database too.
I noticed that entering 'password' or 'PaSsWorD' did not matter at all.

So i'll have to investigate a bit how this works.

Bedankt!
SPhere

[mrcoollion]

Success,

With me using GLOB works fine...

Groet,

Paul

[sphere]

Found another way.
When user logs in, in the program then a few items are downloaded, put in an variable and compared to what the user typed in.
i used partly this:

set the caseSensitive to true
set the wholeMatches to true
if not matchText(fld"pass", tPass) then

I'm not total sure if i needed the 2 lines completely, but it did not hurt
this helped me to get it respond correctly to upper and lower case differences.

[SparkOut]

Please please please get out of the habit of storing password data in your database. You should always hash the password and store only the hash. When the user types the password to log in, hash that again and compare the hashes.

[sphere]

Yes you're right.
i'm changing a database which was created by someone else and encrypt all data also passwords, as till now it is plain text.
Then also the login data will be encrypted, so not only hash.
Not that the data is now highly sensitive, but it is still personal data, which should be better protected.

I was hoping the new md5 was already in lc9dp6 but it is shifted to dp7, as it seems the md5 which is now in lc is not save enough.

i,m converting like this, encrypt --> Base64encode --> write to database
read out: read from database --> Base64decode --> decrypt
Tests run ok.
Also doing this with the user logins and after decrypt, compared.
Checking on case sensitivity, User name and password

[FourthWorld]

The sha1Digest function isn't quite as good as as sha2 or sha3 coming up soon, but it's here now and much stronger than md5.

[sphere]

Indeed i meant SHA1digest

[mrcoollion]

This might be an idea.

Use combination of sha1Digest and encrypt / decrypt

User gives a new to store password > encrypt with sha1Digest(passwrd) as password for the encript statement --> put result into database.
User gives password for check > get password-data from database > decrypt with sha1Digest(passwrd) as password for the decript statement > Check decripted password against given password.

This way there is no data in the database that links direct to the users password.
Also you do not need to have a decription password in the stack to decript the data from the database.
High safety level.

PS. I Did not try this yet....

Let me know the results... I do not have the time now to test......

Regards,

Paul

[sphere]

lc 9 dp7 released today with new SHA