Why won't my SQL passwordhashing-procedure run?

Question

I am trying to create a SQL procedure that hashes password inputs. This code won't run and I am not getting any useful response errors.

The first part creates the table, the second creates the procedure. When I call on my procedure in the third part it send the values into the procedure. There the password is supposed to be hashed using SHA2_512 and inserted into the table we made eralier.

I used online research to make this code, the parts I don't get is:

The N before my values

The SetNoCount

The @responsemessage

-- makes Admin table
CREATE TABLE IF NOT EXISTS `AdminUser` (
  `AdminID` smallint(6) NOT NULL AUTO_INCREMENT,
  `Username` char(15) NOT NULL,
  `PasswordHash` BINARY(64) NOT NULL,
  `Fornavn` char(30) NOT NULL,
  `Etternavn` char(40) NOT NULL,
  `Email` char(40) NOT NULL,
  PRIMARY KEY (`AdminID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Makes hashing procedure
CREATE OR REPLACE PROCEDURE vm_ski.addAdmin
    @pUsername NVARCHAR(50), 
    @pPassword NVARCHAR(50),
    @pFornavn NVARCHAR(30), 
    @pEtternavn NVARCHAR(40),
    @pEmail NVARCHAR(40),
    @responseMessage NVARCHAR(250)='' OUTPUT    
AS
BEGIN
    SET NOCOUNT ON

    BEGIN TRY

        INSERT INTO vm_ski.AdminUser (Username, PasswordHash, Fornavn, Etternavn, Email)
        VALUES(@pUsername, HASHBYTES('SHA2_512', @pPassword), @pFornavn, @pEtternavn, @pEmail)

        SET @responseMessage='Success'

    END TRY
    BEGIN CATCH
        SET @responseMessage=ERROR_MESSAGE() 
    END CATCH

END;

-- Admin example
DECLARE @responseMessage NVARCHAR(250)

EXECUTE vm_ski.addAdmin
          @pUsername = N'sondre',
          @pPassword = N'example'
          @pFornavn = N'Sondre'
          @pEtternavn = N'Morgendal'
          @pEmail = N'sondre.example@gmail.com'
          ;

Because `HASHBYTES('SHA2_512', @pPassword)` isn't a MySQL function. in MySQL you need to use `SHA2(@pPassword, 512)` ... And you shouldn't be using SHA512 hash because it's a fast hashing algorithm and there are GB's of existing rainbow tables out on the internet that can reverse enigeer a SHA512 hash to the password within seconds without using a salt with in the SHA512 hash... — Raymond Nijland, Apr 11 '18 at 11:38
I understand, but I am currently learning so I just need to make it work before I change my hashing method. It still doesn't work, I fear there is another thing making the errors — Aune, Apr 11 '18 at 11:44
"I change my hashing method. It still doesn't work," Yes that because `CREATE OR REPLACE PROCEDURE` also is SQL-server Syntax.. MySQL doesn't support REPLACE clause in there.. MySQL also doesn't support `OUTPUT` clause with `CREATE PROCEDURE` statements... `N'sondre.example@gmail.com'` the N infront is also SQL-server syntax.. `ERROR_MESSAGE()` is also A SQL-server function... — Raymond Nijland, Apr 11 '18 at 12:09
You don't understand. You're still using SQL Server's specific functions with MySQL, you're still trying to hash using the database. If you don't understand that you can't randomly mix SQL server functions with MySQL, then how can you understand anything? This problem with hashing the passwords has been solved over and over and over and over - and you even have google, stackoverflow, a computer with keyboard. Use it to find what other people did, you'll find plenty of explanation and reasoning. Your way - bad. — N.B., Apr 11 '18 at 12:11
With respect, @Aune, you would not learn to repair engines on a airplane. Inventing your own password validation is **dangerous** on the modern internet crawling with cybercriminals. Read this if you're not sure why. https://HaveIbeenPwned.com/ . Read this for an explanation of good practices. http://php.net/manual/en/faq.passwords.php — O. Jones, Apr 11 '18 at 12:16
You're probably wondering why you're getting so much flak. Why? Many people have been burned by doing what you're doing. — O. Jones, Apr 11 '18 at 12:21
Haha, I am used to people being critized when asking for help online, no problem. I know most of you mean well. I didn't realise mutiple commands I was using was not compatible with MySQL. Also, I followed the advice below and hashed in PHP for better security. Thank you for teaching me :) — Aune, Apr 11 '18 at 16:57

Martin · Accepted Answer · 2018-04-11T12:01:33.727

3

This is not a direct answer to the question; this is a security note on the methodology of the question

Do NOT hash passwords in MySQL. The data given to MySQL is plaintext, and easily intercepted by MySQL processing logs as well as possibly numerous other places before being dumped in the database (such as if message packets sent to the database are non-localhost and are non-TLS). ( Why? )

When hashing passwords you want to be doing so as early in the process as possible. This typically means using PHP password_hash and simply dumping only the hashed data in the MySQL.

If you do not use PHP to interact with your SQL then you can use other server methods such as Argon2 or Libsodium.

Also as a side point you should be using the mb4 UTF-8 charset and collations - principly utf8mb4_general_ci ( Why? )

edited Apr 11 '18 at 12:01

answered Apr 11 '18 at 11:44

Martin

22,212
11
70
132

I see. I will hash in PHP then. Thank you for valuable information. – Aune Apr 11 '18 at 11:50
" I will hash in PHP then." if you will hash in PHP instead off the database then this question is solved? right? @Aune then you should accept this answer as the solution -> https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – Raymond Nijland Apr 11 '18 at 12:12

Why won't my SQL passwordhashing-procedure run?

1 Answers1