Results 1 to 27 of 27

Thread: [RESOLVED] Hashing Mysql example MySQL gives you. Like to know how its used

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    710

    Resolved [RESOLVED] Hashing Mysql example MySQL gives you. Like to know how its used

    https://dev.mysql.com/doc/refman/8.0...on_aes-encrypt
    About the hashing Mysql example they give.

    I am trying to understand what 'My Secret Passphrase' represents.
    And what 'text' represents.
    And how to use this functionality of this DB functions.

    Code:
    mysql> SET block_encryption_mode = 'aes-256-cbc';
    mysql> SET @key_str = SHA2('My secret passphrase',512);
    mysql> SET @init_vector = RANDOM_BYTES(16);
    mysql> SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector);
    mysql> SELECT CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR);
    +-------------------------------------------------------------+
    | CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR) |
    +-------------------------------------------------------------+
    | text                                                        |
    +-

    I found out you can select what those example commands set
    And if you run those sets, but don't set 'text', then their select example returns a null


    Musing here
    it seems to me those 3 sets results have to be saved in the database
    then you use those to create 'text' and 'text' is what is the user's password
    'my secret passphrase' could be your own salt value.

    These 3 generated user values then can be used to bring back a value of 'text'
    So, if I ran these 2 code lines on another PC, I ought to always get 'text' as a result

    MariaDB [booksgood]> SET block_encryption_mode = 'aes-256-cbc';

    MariaDB [booksgood]> SELECT CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR);

    I should get 'text' returned.
    And 'text' and what the user's password claimed on a form input as 'text' will match and user is authorized.

    I have other PC running Mysql and can try that.
    It also should work for either any MySQL or MariaDB server as long as the version allows for this function.

    OR, it COULD be I don't have a clue for what I am thinking, and am way off base here.
    I just woke up and thought about it some and posted what I thought might be true.

    It also seems to me anyone with values read from the row could then return a value of 'text'
    Means a hacker could do that, and my idea is not correct.

    Code:
    MariaDB [booksgood]> SET block_encryption_mode = 'aes-256-cbc';
    Query OK, 0 rows affected (0.001 sec)
    
    MariaDB [booksgood]> SET @key_str = SHA2('My secret passphrase',512);
    Query OK, 0 rows affected (0.000 sec)
    
    MariaDB [booksgood]> SET @init_vector = RANDOM_BYTES(16);
    Query OK, 0 rows affected (0.001 sec)
    
    MariaDB [booksgood]> SELECT CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR);
    +-------------------------------------------------------------+
    | CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR) |
    +-------------------------------------------------------------+
    | NULL
        |
    +-------------------------------------------------------------+
    1 row in set (0.000 sec)
    
    
    
    
    MariaDB [booksgood]>  SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector);
    Query OK, 0 rows affected (0.000 sec)
    
    MariaDB [booksgood]>  SELECT CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR);
    +-------------------------------------------------------------+
    | CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR) |
    +-------------------------------------------------------------+
    | text
        |
    +-------------------------------------------------------------+
    1 row in set (0.000 sec)
    
    MariaDB [booksgood]> select @crypt_str;
    +------------------+
    | @crypt_str       |
    +------------------+
    | ?S?13N?????1?tv         |
    +------------------+
    1 row in set (0.000 sec)
    
    MariaDB [booksgood]> select @key_str;
    +----------------------------------------------------------------------------------------------------------------------------------+
    | @key_str
    
                   |
    +----------------------------------------------------------------------------------------------------------------------------------+
    | fb9958e2e897ef3fdb49067b51a24af645b3626eed2f9ea1dc7fd4dd71b7e38f9a68db2a3184f952382c783785f9d77bf923577108a88adaacae5c141b1576b0 |
    +----------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.000 sec)
    
    MariaDB [booksgood]> select @init_vector;
    +------------------+
    | @init_vector     |
    +------------------+
    | ?7#T|?q????d[       |
    +------------------+
    1 row in set (0.000 sec)
    
    MariaDB [booksgood]>

  2. #2
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,595

    Re: Hashing Mysql example MySQL gives you. Like to know how its used

    Quote Originally Posted by sdowney1 View Post
    https://dev.mysql.com/doc/refman/8.0...on_aes-encrypt
    About the hashing Mysql example they give.
    That is encryption, not hashing. The two things are different.

    Quote Originally Posted by sdowney1 View Post
    I am trying to understand what 'My Secret Passphrase' represents.
    That would be the password you are wanting to use to encrypt the data.

    Quote Originally Posted by sdowney1 View Post
    And what 'text' represents.
    That is the text you are wanting to encrypt.

    Quote Originally Posted by sdowney1 View Post
    And how to use this functionality of this DB functions.
    ....

    I found out you can select what those example commands set
    And if you run those sets, but don't set 'text', then their select example returns a null
    If you don't give it anything to encrypt, then it isn't going to return anything encrypted.

    Quote Originally Posted by sdowney1 View Post
    Musing here
    it seems to me those 3 sets results have to be saved in the database
    then you use those to create 'text' and 'text' is what is the user's password
    'my secret passphrase' could be your own salt value.
    No, only the encrypted result would be needed to be stored in the database. The other values are used as part of the generation of this encrypted value.

    The 'secret passphrase' wouldn't be a salt as such, because you are not hashing anything, you are encrypting the data - different things.

    Quote Originally Posted by sdowney1 View Post
    These 3 generated user values then can be used to bring back a value of 'text'
    So, if I ran these 2 code lines on another PC, I ought to always get 'text' as a result
    yes.


    Quote Originally Posted by sdowney1 View Post
    And 'text' and what the user's password claimed on a form input as 'text' will match and user is authorized.
    Yes, but this is doing it wrong, you should be storing a password hash. Then when a user enters a password you hash it and compare the hashes.

    Quote Originally Posted by sdowney1 View Post
    It also seems to me anyone with values read from the row could then return a value of 'text'
    Means a hacker could do that, and my idea is not correct.
    Which is why it should be a hash, not an encrypted value.

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    710

    Re: Hashing Mysql example MySQL gives you. Like to know how its used

    Then why does the site describe it like this?

    They mention hash and salt and kdf on that page in places. I agree I don't understand fully.

    Note
    Exploits for the MD5 and SHA-1 algorithms have become known. You may wish to consider using another one-way encryption function described in this section instead, such as SHA2().

    Caution
    Passwords or other sensitive values supplied as arguments to encryption functions are sent as cleartext to the MySQL server unless an SSL connection is used. Also, such values appear in any MySQL logs to which they are written. To avoid these types of exposure, applications can encrypt sensitive values on the client side before sending them to the server. The same considerations apply to encryption keys. To avoid exposing these, applications can use stored procedures to encrypt and decrypt values on the server side.

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    710

    Re: Hashing Mysql example MySQL gives you. Like to know how its used

    Their documentation is not fully clear to me.

    So, the 'my secret passphrase' is nothing supplied by a user then?
    Something the programmer supplies for AES encrypt?

    'text' would have been the thing to be encrypted? User supplied?

    What is @crypt string?
    looks like the AES encryption of 'text' with the modification including @key_str,@init_vector variables?


    mysql> SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector);

    MariaDB [booksgood]> select @crypt_str;
    +------------------+
    | @crypt_str |
    +------------------+
    | ?S?13N?????1?tv |
    +------------------+
    1 row in set (0.000 sec)

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    710

    Re: Hashing Mysql example MySQL gives you. Like to know how its used

    You can see why I am wondering if this is still some kind of hashing

    It mentions SHA2 in set command

    mysql> SET @key_str = SHA2('My secret passphrase',512);



    And like it says here SHA2 is for hashing. Can you use SHA2 and it not be hashing?

    https://www.comparitech.com/blog/inf...-does-it-work/

    The SHA-2 family of hashing algorithms are the most common hash functions in use. SHA-256 is particularly widespread. These hash functions are often involved in the underlying security mechanisms that help to protect our daily lives. You may have never noticed it, but SHA-2 is everywhere.

    To begin with, SHA-2 is involved in many of the security protocols that help to protect much of our technology:

  6. #6
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,595

    Re: Hashing Mysql example MySQL gives you. Like to know how its used

    Quote Originally Posted by sdowney1 View Post
    Their documentation is not fully clear to me.

    So, the 'my secret passphrase' is nothing supplied by a user then?
    Something the programmer supplies for AES encrypt?

    'text' would have been the thing to be encrypted? User supplied?
    The secret passphrase is used to encrypt the data, in this case 'text' is the data to be encrypted and would pressumably be the user supplied unencrypted password.. If you are allowing the users to specify and encryption phrase then this could be user supplied. In this case though you could be using one supplied by the program.

    Although, yet again, you shouldn't be encrypting passwords. You should be hashing the password and storing the hash.

    Quote Originally Posted by sdowney1 View Post
    What is @crypt string?
    looks like the AES encryption of 'text' with the modification including @key_str,@init_vector variables?


    mysql> SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector);
    The AES_ENCRYPT function returns the encrypted version of 'text' based on the key and init_vector parameters. Therefor crypt_str is the encrypted string.

  7. #7
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,298

    Re: Hashing Mysql example MySQL gives you. Like to know how its used

    A hint: always prefer CTR mode -- try 'aes-256-ctr' if you can because CBC mode is archaic and requires padding i.e. if you encrypt 2 bytes you get a full 16 bytes block. With CTR mode if you encrypt 2 bytes you get exact 2 bytes output *and* encryption can be done in parallel while with CBC each next block of 16 bytes depends on previous block output so these cannot be calculated in parallel.

    Another hint about nomenclature: the only hashing in your code is happening at the line SET @key_str = SHA2('My secret passphrase',512) -- this uses SHA2-512 hash function to hash a string to 64 bytes (512 bits) of which only 32 bytes are used as a key for AES-256 encryption (CBC mode is immaterial to the length of the key). The second 32 bytes are discarded instead of used as some other key material (can be used for IV).

    Another caveat here is that using raw SHA2 for key generation from passprase is usually frowned upon by crypto experts and everyone pretty much prefers to use (repeat) HMAC construct for derived key expansion i.e. HMAC-SHA2-512 in your case which allows using another system provided key (some call it pepper) and/or a context string for your key generation "scope" for even more flexibility.

    > Exploits for the MD5 and SHA-1 algorithms have become known

    Note that HMAC-SHA-1 (and even HMAC-MD5) is not broken i.e. the HMAC construct is very stable to attacks even if the underlying hash is already broken. For instance HMAC-SHA-1 is still used in non-deprecated ciphersuits in TLS.

    Every line w/ AES_ENCRYPT and AES_DECRYPT is not hashing -- let's call it encryption. Which means that Initialization vector (IV) parameter to these functions is not salt in the sense "salt" term is used for password hashing.

    cheers,
    </wqw>

  8. #8
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,595

    Re: Hashing Mysql example MySQL gives you. Like to know how its used

    Quote Originally Posted by sdowney1 View Post
    You can see why I am wondering if this is still some kind of hashing

    It mentions SHA2 in set command

    mysql> SET @key_str = SHA2('My secret passphrase',512);

    And like it says here SHA2 is for hashing. Can you use SHA2 and it not be hashing?

    https://www.comparitech.com/blog/inf...-does-it-work/

    The SHA-2 family of hashing algorithms are the most common hash functions in use. SHA-256 is particularly widespread. These hash functions are often involved in the underlying security mechanisms that help to protect our daily lives. You may have never noticed it, but SHA-2 is everywhere.

    To begin with, SHA-2 is involved in many of the security protocols that help to protect much of our technology:
    SHA2 is a hash function, that will return a hash of the string 'My secret passphrase', once that function has returned @key_str will contain the hash of the secret phrase. A hash is a one way trip, you cannot un-hash something to get the original data back.

    If you wanted to store a password (or pass phrase) you would take the hash of the password (often combined with a salt) and store the hash. That is all you would need to do. Hash the password and store the hash.


    The AES_ENCRYPT function is an encryption routine, it will generate an encrypted result that can later be decrypted back to the original data.

    The example from the documentation is taking a user supplied password, generating a hash from the password and then using the hash to encrypt the text, this resulting encrypted text could then be decrypted using the hash of the original password.

    If you want to store a password however, don't encrypt it - just hash it and store the hash.

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    710

    Re: Hashing Mysql example MySQL gives you. Like to know how its used

    Quote Originally Posted by PlausiblyDamp View Post
    SHA2 is a hash function, that will return a hash of the string 'My secret passphrase', once that function has returned @key_str will contain the hash of the secret phrase. A hash is a one way trip, you cannot un-hash something to get the original data back.

    If you wanted to store a password (or pass phrase) you would take the hash of the password (often combined with a salt) and store the hash. That is all you would need to do. Hash the password and store the hash.


    The AES_ENCRYPT function is an encryption routine, it will generate an encrypted result that can later be decrypted back to the original data.

    The example from the documentation is taking a user supplied password, generating a hash from the password and then using the hash to encrypt the text, this resulting encrypted text could then be decrypted using the hash of the original password.

    If you want to store a password however, don't encrypt it - just hash it and store the hash.
    So, some of those set commands can be used as a password hash?

    mysql> SET @key_str = SHA2('My secret passphrase',512);

    @key_str is the hash of My secret passphrase?
    Interesting

    Have to think about this, like so then you would run that line on a user supplied password, and compare to the stored hash @key_str?
    If they match, user is authorized?
    Last edited by sdowney1; Jun 10th, 2024 at 07:42 AM.

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    710

    Re: Hashing Mysql example MySQL gives you. Like to know how its used

    posting this and later will run the same set command on another PC running Mysql and see if the output matches

    If it does, then this is so easy, easier than what I had been doing with aesencrypt, aesdecrypt, or at least it is not harder.

    That aes_encrypt method in their example must be pretty secure as it uses SHA2 512 as part of the encryption
    for securing a text document.

    Code:
    MariaDB [booksgood]> SET @key_str = SHA2('My secret passphrase',512);
    Query OK, 0 rows affected (0.000 sec)
    
    MariaDB [booksgood]> select @key_str;
    
    +----------------------------------------------------------------------------------------------------------------------------------+
    | @key_str
                                     |
    +----------------------------------------------------------------------------------------------------------------------------------+
    | fb9958e2e897ef3fdb49067b51a24af645b3626eed2f9ea1dc7fd4dd71b7e38f9a68db2a3184f952382c783785f9d77bf923577108a88adaacae5c141b1576b0 |
    +----------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.000 sec)
    
    MariaDB [booksgood]>

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    710

    Re: Hashing Mysql example MySQL gives you. Like to know how its used

    yes looks like a match here it is on older ver 5.7

    Glad I pressed on with this. Will mark it solved

    Much thanks to PlausiblyDamp for the assistance in understanding this function.

    Code:
    Enter password: ****
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.7.44-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2023, Oracle and/or its affiliates.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> SET @key_str = SHA2('My secret passphrase',512);
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @key_str;
    +----------------------------------------------------------------------------------------------------------------------------------+
    | @key_str                                                                                                                         |
    +----------------------------------------------------------------------------------------------------------------------------------+
    | fb9958e2e897ef3fdb49067b51a24af645b3626eed2f9ea1dc7fd4dd71b7e38f9a68db2a3184f952382c783785f9d77bf923577108a88adaacae5c141b1576b0 |
    +----------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql>

  12. #12

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    710

    Re: Hashing Mysql example MySQL gives you. Like to know how its used

    Quote Originally Posted by wqweto View Post

    Another hint about nomenclature: the only hashing in your code is happening at the line SET @key_str = SHA2('My secret passphrase',512) -- this uses SHA2-512 hash function to hash a string to 64 bytes (512 bits) of which only 32 bytes are used as a key for AES-256 encryption (CBC mode is immaterial to the length of the key). The second 32 bytes are discarded instead of used as some other key material (can be used for IV).

    Another caveat here is that using raw SHA2 for key generation from passphrase is usually frowned upon by crypto experts and everyone pretty much prefers to use (repeat) HMAC construct for derived key expansion i.e. HMAC-SHA2-512 in your case which allows using another system provided key (some call it pepper) and/or a context string for your key generation "scope" for even more flexibility.


    </wqw>
    SET @key_str = SHA2('My secret passphrase',512);

    becomes ?

    SET @key_str = HMAC-SHA2('My secret passphrase', pepper, some string idea,512);

    or some such arrangement?

    Can you share the syntax to use?

  13. #13
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,595

    Re: Hashing Mysql example MySQL gives you. Like to know how its used

    Quote Originally Posted by sdowney1 View Post
    So, some of those set commands can be used as a password hash?

    mysql> SET @key_str = SHA2('My secret passphrase',512);

    @key_str is the hash of My secret passphrase?
    Interesting

    Have to think about this, like so then you would run that line on a user supplied password, and compare to the stored hash @key_str?
    If they match, user is authorized?
    When the user initially provides a password (or one is generated for them), you would run that code to generate the hash. Then you would store the hash in the database.

    When a user wants to login, you would indeed hash the provided password, compare that to the stored hash and if they match then the passwords were the same.

  14. #14

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    710

    Re: [RESOLVED] Hashing Mysql example MySQL gives you. Like to know how its used

    here's another syntax to generate a sha2 hash

    https://alphacodingskills.com/sql/no...-func-sha2.php

    Code:
    mysql> SELECT SHA2('xyz', 224);
    Result: '30e90f1cd0ceff8eb3dd6a540a605c0666f841d35de63c57e4dd2877'
    
    
    mysql> SELECT SHA2(NULL, 224);
    Result: NULL
    and that code matches my own server's results


    Code:
    mysql> SELECT SHA2('xyz', 224);
    +----------------------------------------------------------+
    | SHA2('xyz', 224)                                         |
    +----------------------------------------------------------+
    | 30e90f1cd0ceff8eb3dd6a540a605c0666f841d35de63c57e4dd2877 |
    +----------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT SHA2('xyz', 512);
    +----------------------------------------------------------------------------------------------------------------------------------+
    | SHA2('xyz', 512)                                                                                                                 |
    +----------------------------------------------------------------------------------------------------------------------------------+
    | 4a3ed8147e37876adc8f76328e5abcc1b470e6acfc18efea0135f983604953a58e183c1a6086e91ba3e821d926f5fdeb37761c7ca0328a963f5e92870675b728 |
    +----------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

  15. #15
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,298

    Re: [RESOLVED] Hashing Mysql example MySQL gives you. Like to know how its used

    https://mariadb.com/kb/en/kdf/

    It looks like MariaDB has dedicated function for HMAC and PBKDF2 -- the latter is the KDF you want to use for password hashing (w/ at least 100k iterations). PBKDF2 is based on HMAC, it just repeatedly calls HMAC to consume CPU/memory so that password hashes are hard to crack if (when) leaked to hackers.

    Password hashing is a separate discipline than generic topic of hashing. Use it to google search already implemented auth code for MySQL/MariaDB. There has to be some sample DB design with tables and functions in SQL or python or VBx to store user credentials.

    Edit: There are even better alternatives to PBKDF2 like scrypt and bcrypt which stress CPU/memory even more and are more flexible but probably missing from stock MySQL/MariaDB setup (need external DLLs).

    cheers,
    </wqw>

  16. #16

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    710

    Re: [RESOLVED] Hashing Mysql example MySQL gives you. Like to know how its used

    https://auth0.com/blog/adding-salt-t...ore-passwords/

    Some reasons to use unique salting of passwords

    it seems adding a unique salt to an individuals password is sufficiently secure for making a hash

    They store the salt unencrypted in same row as the hash.

  17. #17
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,298

    Re: [RESOLVED] Hashing Mysql example MySQL gives you. Like to know how its used

    > They store the salt unencrypted in same row as the hash.

    Yes, the idea of the salt is not to be another secret. It's main purpose is to slow down hash cracking when they are using so called rainbow tables.

    When passwords are salted they cannot use the same precomputed tables for all password but have to precompute for each password which ruins performance.

    cheers,
    </wqw>

  18. #18

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    710

    Re: [RESOLVED] Hashing Mysql example MySQL gives you. Like to know how its used

    Quote Originally Posted by wqweto View Post
    > They store the salt unencrypted in same row as the hash.

    Yes, the idea of the salt is not to be another secret. It's main purpose is to slow down hash cracking when they are using so called rainbow tables.

    When passwords are salted they cannot use the same precomputed tables for all password but have to precompute for each password which ruins performance.

    cheers,
    </wqw>
    and they keep hashes unique

    Saw this as an example.
    Do something similar then append it to the user password and hash them together
    Store the unique salt in the user's row, to be used for checking the user supplied password, as in append it, then hash it and compare the 2 hashes.

    Code:
    MariaDB [booksgood]> set @salt=SUBSTRING(MD5(RAND()), -10);
    Query OK, 0 rows affected (0.000 sec)
    
    MariaDB [booksgood]> select @salt;
    +------------+
    | @salt      |
    +------------+
    | 896dfdbde5 |
    +------------+
    1 row in set (0.000 sec)
    
    MariaDB [booksgood]>
    Last edited by sdowney1; Jun 10th, 2024 at 10:01 AM.

  19. #19
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,298

    Re: [RESOLVED] Hashing Mysql example MySQL gives you. Like to know how its used

    > and they keep hashes unique

    True. Otherwise two users with the same password would have the same hash in passwords table.

    If users cannot be renamed (i.e. not possible to change login name) then using login name + salt + password would prevent an attack by the sysadmin which can create a new user w/ known password (e.g. 1234) and copy/paste salt and hash from this user to some attacked user, then login to the system like the attacked user (which now has password 1234) then modify data in the system which leave audit trail done by attacked user and finally restore original salt and hash.

    cheers,
    </wqw>

  20. #20

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    710

    Re: [RESOLVED] Hashing Mysql example MySQL gives you. Like to know how its used

    Quote Originally Posted by wqweto View Post
    > and they keep hashes unique

    True. Otherwise two users with the same password would have the same hash in passwords table.

    If users cannot be renamed (i.e. not possible to change login name) then using login name + salt + password would prevent an attack by the sysadmin which can create a new user w/ known password (e.g. 1234) and copy/paste salt and hash from this user to some attacked user, then login to the system like the attacked user (which now has password 1234) then modify data in the system which leave audit trail done by attacked user and finally restore original salt and hash.

    cheers,
    </wqw>
    got that example from here
    Is a longer salt better?

    https://stackoverflow.com/questions/...rom%20salty%3B

  21. #21

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    710

    Re: [RESOLVED] Hashing Mysql example MySQL gives you. Like to know how its used

    or generate a 20 char long on like this


    Since to use @user vars, you have to append to your connection string this

    Code:
    Dim ConStrUserVar As String = frmlogonConnectstring & "Allow User Variables=True;"
    Code:
    MariaDB [booksgood]> SELECT SUBSTRING(MD5(RAND()), -20);
    +-----------------------------+
    | SUBSTRING(MD5(RAND()), -20) |
    +-----------------------------+
    | 9e09aa71d5b533eae6c0        |
    +-----------------------------+
    1 row in set (0.000 sec)
    
    MariaDB [booksgood]>

  22. #22
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,298

    Re: [RESOLVED] Hashing Mysql example MySQL gives you. Like to know how its used

    They use MD5 just to get random 16 bytes from not so random RAND function.

    Why do the hassle when you can just use cryptographically strong RANDOM and directly request 16 bytes or 20 bytes or whatever is needed.

    For salts it matters the least but 16 bytes is about as good as it gets -- you just have to decide if these are going to bloat your users/passwords table unnecessary. You can be stingy and get away with 8 bytes long salts for instance.

    Edit: Btw, you can find some wild security codes on the internet. Totally redundant and scarily wrong. My replies here included :-))

    cheers,
    </wqw>

  23. #23

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    710

    Re: [RESOLVED] Hashing Mysql example MySQL gives you. Like to know how its used

    Quote Originally Posted by wqweto View Post
    They use MD5 just to get random 16 bytes from not so random RAND function.

    Why do the hassle when you can just use cryptographically strong RANDOM and directly request 16 bytes or 20 bytes or whatever is needed.

    For salts it matters the least but 16 bytes is about as good as it gets -- you just have to decide if these are going to bloat your users/passwords table unnecessary. You can be stingy and get away with 8 bytes long salts for instance.

    Edit: Btw, you can find some wild security codes on the internet. Totally redundant and scarily wrong. My replies here included :-))

    cheers,
    </wqw>

    Ok , I looked for a while and found a way to use the function, here is 20, but can use 10

    As far as bloat, you should see my code! I have been converting a vb6 app, so I have lots of duplicate subs for trying different things.
    Someday it will get pared down.

    Anything I do, I try to at least get some understanding of it, not that I understand so much, but enough to make things work.

    Code:
    MariaDB [booksgood]> select HEX(RANDOM_BYTES(20));
    +------------------------------------------+
    | HEX(RANDOM_BYTES(20))                    |
    +------------------------------------------+
    | B87C5EC6133110A595568D43855BDF864B55D88F |
    +------------------------------------------+
    1 row in set (0.002 sec)
    
    MariaDB [booksgood]>

  24. #24

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    710

    Re: [RESOLVED] Hashing Mysql example MySQL gives you. Like to know how its used

    Looking at combining a MySQL function in the select to add the salt, it runs, created hash, but also gives a warning?

    What is the correct syntax to do this?

    It creates a different hash than just having 'New' so it has modified the hashed output.

    EDIT, I just realized, I can't do that with the salt.

    Still like to know my original question.

    Code:
    MariaDB [booksgood]> SELECT SHA2('New' + Hex(RANDOM_BYTES(10)), 512);
    +----------------------------------------------------------------------------------------------------------------------------------+
    | SHA2('New' + Hex(RANDOM_BYTES(10)), 512)
               |
    +----------------------------------------------------------------------------------------------------------------------------------+
    | 882714215509c4084d68f27dce978db9723768beea42517c93c63873e5cdcd9edaebf47b95dad12931a22615d92b9fd6dc1988034d84b51a9623cf85460e6041 |
    +----------------------------------------------------------------------------------------------------------------------------------+
    1 row in set, 2 warnings (0.001 sec)
    
    MariaDB [booksgood]>
    Last edited by sdowney1; Jun 11th, 2024 at 06:30 AM.

  25. #25
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,298

    Re: [RESOLVED] Hashing Mysql example MySQL gives you. Like to know how its used

    Obviously you have two different data-types at play -- strings (like 'New') and byte-arrays (like the result of RANDOM_BYTES).

    Try to cast everything to byte-arrays, don't HEX anything, concat byte-arrays, pass byte-arrays for keys and data.

    Using strings (or hex strings like '882714215509c4084d68f27dce978d') will be the root of all possible flaws you can introduce in your code.

    Edit: In MySQL/MariaDB there is so called hex literal like x'E764DF04463B55E9E2305934266227A1' or you can use a clutch like UNHEX('E764DF04463B55E9E2305934266227A1'). The latter is like using CLng("42") instead of just 42& in VBx.

    cheers,
    </wqw>

  26. #26

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    710

    Re: [RESOLVED] Hashing Mysql example MySQL gives you. Like to know how its used

    I wrote the code snippet this way, it runs. Makes the hashes collected into strings

    I realized I could not include the salt random bytes inside the hash create function as doing it like that, have no way to know what it is, and it needs to be saved in the row for use in matching the user inputted password hash create function.

    This part of the code is needed to make sure at least one person has all privileges to administer the program.
    Who knows if some rogue user deletes all admins. It is also needed on a new installation where no users exist.

    I test for that earlier in the sub, if none is found I have to make a 'New' user.
    So hashing snippet is to do that

    Code:
                        cmd1.CommandText = "Select Hex(RANDOM_BYTES(10))"
                        Using RDR = cmd1.ExecuteReader()
                            If RDR.Read Then
                                hashsalt = RDR.Item("Hex(RANDOM_BYTES(10))").ToString()
                            End If
                        End Using
    
                        'append salt to create a salted hash for password
                        saltedhash = "New" & hashsalt
    
                        cmd1.CommandText = "SELECT SHA2('" & saltedhash & "', 512)"
                        Using RDR = cmd1.ExecuteReader()
                            If RDR.Read Then hashpass = RDR.Item("SHA2('" & saltedhash & "', 512)").ToString()
                        End Using
    
                        'append salt to create a salted hash for priveliges
                        saltedhash = "A11111111111111111111" & hashsalt
    
                        cmd1.CommandText = "SELECT SHA2('" & saltedhash & "', 512)"
                        Using RDR = cmd1.ExecuteReader()
                            If RDR.Read Then hashpriv = RDR.Item("SHA2('" & saltedhash & "', 512)").ToString()
                        End Using

  27. #27

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    710

    Re: [RESOLVED] Hashing Mysql example MySQL gives you. Like to know how its used

    Quote Originally Posted by wqweto View Post
    Obviously you have two different data-types at play -- strings (like 'New') and byte-arrays (like the result of RANDOM_BYTES).

    Try to cast everything to byte-arrays, don't HEX anything, concat byte-arrays, pass byte-arrays for keys and data.

    Using strings (or hex strings like '882714215509c4084d68f27dce978d') will be the root of all possible flaws you can introduce in your code.

    Edit: In MySQL/MariaDB there is so called hex literal like x'E764DF04463B55E9E2305934266227A1' or you can use a clutch like UNHEX('E764DF04463B55E9E2305934266227A1'). The latter is like using CLng("42") instead of just 42& in VBx.

    cheers,
    </wqw>
    Interesting, 'New' is a string.

    Since it made a hash, maybe MySQL sort of figured out what you wanted, but complains.

    What data type is Hex(RANDOM_BYTES(10)), 512, a number? or a string, something else?
    So is the error due to a string and number being added? Is this what MySQL does not like?

    Maybe a way in the select to make it a string?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width