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!
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
Last edited by mrcoollion on Tue Jan 03, 2017 9:30 am, edited 2 times in total.
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.
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.)
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
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.
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.
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.
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
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......
.
