-
[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]>
-
Re: Hashing Mysql example MySQL gives you. Like to know how its used
Quote:
Originally Posted by
sdowney1
That is encryption, not hashing. The two things are different.
Quote:
Originally Posted by
sdowney1
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
And what 'text' represents.
That is the text you are wanting to encrypt.
Quote:
Originally Posted by
sdowney1
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
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
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
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
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.
-
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.
-
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)
-
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:
-
Re: Hashing Mysql example MySQL gives you. Like to know how its used
Quote:
Originally Posted by
sdowney1
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
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.
-
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>
-
Re: Hashing Mysql example MySQL gives you. Like to know how its used
Quote:
Originally Posted by
sdowney1
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.
-
Re: Hashing Mysql example MySQL gives you. Like to know how its used
Quote:
Originally Posted by
PlausiblyDamp
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?
-
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]>
-
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>
-
Re: Hashing Mysql example MySQL gives you. Like to know how its used
Quote:
Originally Posted by
wqweto
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?
-
Re: Hashing Mysql example MySQL gives you. Like to know how its used
Quote:
Originally Posted by
sdowney1
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.
-
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)
-
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>
-
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.
-
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>
-
Re: [RESOLVED] Hashing Mysql example MySQL gives you. Like to know how its used
Quote:
Originally Posted by
wqweto
> 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]>
-
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>
-
Re: [RESOLVED] Hashing Mysql example MySQL gives you. Like to know how its used
Quote:
Originally Posted by
wqweto
> 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
-
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]>
-
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>
-
Re: [RESOLVED] Hashing Mysql example MySQL gives you. Like to know how its used
Quote:
Originally Posted by
wqweto
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]>
-
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]>
-
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>
-
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
-
Re: [RESOLVED] Hashing Mysql example MySQL gives you. Like to know how its used
Quote:
Originally Posted by
wqweto
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?
