How to write Stored Procedure?

#1

I have a table named as employee containing all the details such as name, emp id, password, and so on. Can anyone help me to write Stored procedure to login using emp id and password which is stored in md5 format. I tried few but I am not getting where am I going wrong.

Any help would be great.

#2

Just show what you’ve got and where you expect the problem. Passwords should not be stored as md5 as it can be broken easily. You need at least some strong hash like bcrypt and a salt.

#3
CREATE DEFINER=`root`@`localhost` PROCEDURE `testing`(username varchar(10), password varchar(10), OUT isValid bool) BEGIN    if EXISTS(select * from emp_username_db where tgi = username and md5(pass) = password)   then    SET  isValid = 'true';   else	SET isValid = 'false';    end if; end
#4

Using AES for password would it work? Is it stronger than md5? @chorn

#5

password varchar(10) what should that be? It’s way to short for any serious secure password storage and doesn’t even catch up md5.

#6

Just have a look at PHPs default password hashing algorithms

https://www.php.net/manual/en/function.password-hash.php

#7

May I know where am I going wrong? As I am new to this and have never written stored procedure before.

#8

No! AES is encryption which means it can be De-crpted. The password should be hashed.
Just use password_hash and and password_verify and be done with it.

1 Like
#9

In my existing project they have used this format for generating password: password(‘password’).
So I can simply change this to password_hash(‘password’) it wont be harmful right? I am sorry for asking such silly questions but I need to know and learn more about security.

#10

Fatal error : Call to undefined function password_hash() in /var/www/html/TravelDashboard/testfile.php on line 57.

The php version used is 7.3.1

#11

the algorithm needs to be specified:

n.b.: a salt is automatically generated
the default cost of 10 is acceptable but 12-15 is better depending upon hardware

<?php
  $formPass = "Tuti.Shruti";
  $showHash = password_hash($formPass, PASSWORD_DEFAULT);
  echo $showHash;
?>

then compare a plain-text password using password_verify:
password_verify($plainTextPassword, $hashedPassFromDB)
.

edit: if the default algo(rithm) changes, it is currently BCrypt, then you have another block of code to implement: password_needs_rehash
https://php.net/manual/en/function.password-needs-rehash.php

#12

I am also working on a stored procedure but i have yet to figure out how to pass a parameter to it (username/password) using pdo.

if i can get mine working, then i will post here code.

#13

Oh that would be very nice for me. I can even get an idea on how to write and pass parameters. Thank you once again for helping me out. @johnphpnewb.

1 Like