Different between numbers and Chars

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
ace16vitamine
Posts: 130
Joined: Fri Apr 13, 2018 1:53 pm

Different between numbers and Chars

Post by ace16vitamine » Wed Feb 05, 2020 10:51 pm

Dear all,

it is really strange...

I have a Maria DB (UTF-8). In the Database i have stored a simple table (T_USER) with test datas in a text Field:

This is working with my simple script:
UserID: 1234
Passwort: 1234

And this is not working:
UserID: 1234
Passwort: 1234a

Code: Select all

   
   
   ### Create SELECT to get the password
   put "SELECT passwort from T_USER where UserID like " & username_client into sql_daten_User
   put revDataFromQuery(tab,return,dbid,sql_daten_User) into sql_daten_User_ergebnis
   
   ### Check if Password (sql_daten_User_ergebnis ) is the same as the var username_passwort
   
   switch
      case sql_daten_User_ergebnis is username_passwort
         put "user_valid" into user_validation
         break
      case sql_daten_User_ergebnis is not  username_passwort
         put "user_invalid" into user_validation
         break
   end switch 
   
   return user_validation

If I check step by step the results are:
put username_passwort -> 1234 -> OK
put "1234a" into sql_daten_User_ergebnis instead the SQL Select -> OK

It is only the happens that I get user_invalid when a char is in the password. But why????


Regards
Stef

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: Different between numbers and Chars

Post by AxWald » Thu Feb 06, 2020 12:00 pm

Hi,
ace16vitamine wrote:
Wed Feb 05, 2020 10:51 pm
This is working with my simple script:
UserID: 1234
Passwort: 1234

And this is not working:
UserID: 1234
Passwort: 1234a
So, your script works if "passwort" is a number, and fails if it's a string.

What's the difference between numbers & strings when thrown at a DB as data? Right, strings must be quoted. Let's have a look at your code:
ace16vitamine wrote:
Wed Feb 05, 2020 10:51 pm

Code: Select all

   put "SELECT passwort from T_USER where UserID like " & username_client into sql_daten_User
You see? No quote. The db thinks you give it a number, but actually it's a string. The db chokes.

Would you do what's recommended over and over (the dreaded error checking!) you could see it actually. Modify your switch:

Code: Select all

   switch
      case sql_daten_User_ergebnis begins with "revdberr"
         answer error "The DB returned an error:" & CR & sql_daten_User_ergebnis & \
               CR & CR & "Your query string was:" & CR & sql_daten_User
         exit to top
         break
      case sql_daten_User_ergebnis is username_passwort
         put "user_valid" into user_validation
         break
      case sql_daten_User_ergebnis is not  username_passwort
         put "user_invalid" into user_validation
         break
   end switch
Add this to each & every database query! You must know when a query fails, and you must know why. You need to know the reason the DB gives (often enough not really helpful ...) and you need to know what string was actually thrown at the DB. Only then do you have a chance to correct mistakes.

In this case ("passwort" is a string) your statement should look like this:

Code: Select all

SELECT passwort from T_USER where UserID = '1234a';
You compare passwords for exact matches (=), not for partial matches (LIKE). And when you want a LIKE to work, you use placeholders (LIKE '123$').
And: assuming "username_client" really contains the password, not what the name suggests ...
To construct it, you do:

Code: Select all

   put "SELECT passwort from T_USER where UserID = " & swote(username_client) into sql_daten_User
For this above to work, and for saving much hard-to-read concatenations, you always have 2 functions in your message path (stack script, library stack ...):

Code: Select all

function swote what  --  what => 'what'
   return "'" & what & "'"
end swote

function kwote what  --  what => "what"
   return quote & what & quote
end kwote
Hope this helps. Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

SparkOut
Posts: 2834
Joined: Sun Sep 23, 2007 4:58 pm

Re: Different between numbers and Chars

Post by SparkOut » Thu Feb 06, 2020 6:59 pm

What AxWald said, very eloquently.

But also, please do NOT EVER store a user's password in plaintext. You should always hash the password given when the user signs up, and store the hash.

Then when the password is given to log in, hash it again and compare the hashes, not the password.

Note MD5 and SHA1 are not secure, the password should be hashed with SHA256 at the very least.

ace16vitamine
Posts: 130
Joined: Fri Apr 13, 2018 1:53 pm

Re: Different between numbers and Chars

Post by ace16vitamine » Thu Feb 06, 2020 11:18 pm

@SparkOut, sure, this is stored cleartext only for testing.

@AxWald, seems that there was another problem. My DB select was right, it was a problem with the variable username_passwort... I dont know why (because I received this from another function and I have checked this multiple times) put if i put it manually in front of my select
  • put "1234a" into username_passwort
It is working without any problems. Seems that I have to check the function which creates username_passwort.

Anyway... Thank you for your time and for helping me to identify this. I have changed my switch with your Idea, many thanks!!

Post Reply

Return to “Databases”