Habilis
New member
Day 4 2/2: SQL security work takes form
I moved forward with the SQL security work
So basically here is the main idea of how It works
and here are some pieces of my SQL code
DECLARE _ragDBWebUserViewer VARCHAR(32);
DECLARE _ragDBWebUserViewerPass VARCHAR(32);
SET _ragDBWebUserViewer = 'ragdbwebviewer';
SET _ragDBWebUserViewerPass = 'huji'
ALTER TABLE login ADD COLUMN emailverrif VARCHAR(32) NOT NULL DEFAULT '' AFTER pincode_change;
DELIMITER //
CREATE PROCEDURE WebRegisterNewRagPlayerOne
(
IN login VARCHAR(23)
, IN email VARCHAR(39)
, IN password VARCHAR(32)
, IN sex ENUM('M','F')
, IN emailverrifhash VARCHAR(32)
, OUT returnparam INT(1)
)
proc_webreg:BEGIN
-- -1 Unhandled error
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET returnparam = -1;
SELECT userid
INTO @loginalreadyexists
FROM login
WHERE userid = login;
SELECT email
INTO @emailalreadyexists
FROM login
WHERE email = email;
IF @loginalreadyexists NOT NULL THEN
-- -2 Login already exists
SET returnparam = -2;
LEAVE proc_webreg;
END IF;
IF @emailalreadyexists NOT NULL THEN
-- -3 Email already exists
SET returnparam = -3;
LEAVE proc_webreg;
END IF;
-- 4085943947 (2099-06-24)
INSERT INTO login
(userid, email, user_pass, sex, emailverrif, unban_time)
VALUES
(login, email, password, sex, emailverrif, 4085943947);
END //
DELIMITER ;
GRANT EXECUTE ON PROCEDURE ragdb.WebRegisterNewRagPlayerOne TO _ragDBWebUserViewer@'localhost';
DELIMITER //
CREATE PROCEDURE WebRegisterNewRagPlayerTwo
(
IN emailverrifhash VARCHAR(32)
, OUT returnparam INT(1)
)
BEGIN
-- Hey it's not a tutorial
END //
DELIMITER ;
GRANT EXECUTE ON PROCEDURE ragdb.WebRegisterNewRagPlayerTwo TO _ragDBWebUserViewer@'localhost';
DELIMITER //
CREATE PROCEDURE RsetPasswordRagPlayerOne
(
IN login VARCHAR(23)
, IN emailverrifhash VARCHAR(32)
, OUT email VARCHAR(39)
)
BEGIN
-- Hey it's not a tutorial
END //
DELIMITER ;
GRANT EXECUTE ON PROCEDURE ragdb.RsetPasswordRagPlayerOne TO _ragDBWebUserViewer@'localhost';
DELIMITER //
CREATE PROCEDURE RsetPasswordRagPlayerTwo
(
IN password VARCHAR(32)
, IN emailverrifhash VARCHAR(32)
, OUT returnparam INT(1)
)
BEGIN
-- Hey it's not a tutorial
END //
DELIMITER ;
GRANT EXECUTE ON PROCEDURE ragdb.RsetPasswordRagPlayerTwo TO _ragDBWebUserViewer@'localhost';
So basically as you can see there is ragdbwebviewer that has access only to the stored procedures and I will add some views
it will have access to
like
Create View AS SELECT nicnake, level,
left join job....
whatever (note I'm not doing select * nor selecting login, email, passwords)
The password reset part is a security weakness, but if my website is hacked and
ragdbwebviewer credentials obtained, hacker will have to know account login to reset the password... in theory
in real life there will be some more layers of security...
So basically here is the main idea of how It works
and here are some pieces of my SQL code
DECLARE _ragDBWebUserViewer VARCHAR(32);
DECLARE _ragDBWebUserViewerPass VARCHAR(32);
SET _ragDBWebUserViewer = 'ragdbwebviewer';
SET _ragDBWebUserViewerPass = 'huji'
ALTER TABLE login ADD COLUMN emailverrif VARCHAR(32) NOT NULL DEFAULT '' AFTER pincode_change;
DELIMITER //
CREATE PROCEDURE WebRegisterNewRagPlayerOne
(
IN login VARCHAR(23)
, IN email VARCHAR(39)
, IN password VARCHAR(32)
, IN sex ENUM('M','F')
, IN emailverrifhash VARCHAR(32)
, OUT returnparam INT(1)
)
proc_webreg:BEGIN
-- -1 Unhandled error
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET returnparam = -1;
SELECT userid
INTO @loginalreadyexists
FROM login
WHERE userid = login;
SELECT email
INTO @emailalreadyexists
FROM login
WHERE email = email;
IF @loginalreadyexists NOT NULL THEN
-- -2 Login already exists
SET returnparam = -2;
LEAVE proc_webreg;
END IF;
IF @emailalreadyexists NOT NULL THEN
-- -3 Email already exists
SET returnparam = -3;
LEAVE proc_webreg;
END IF;
-- 4085943947 (2099-06-24)
INSERT INTO login
(userid, email, user_pass, sex, emailverrif, unban_time)
VALUES
(login, email, password, sex, emailverrif, 4085943947);
END //
DELIMITER ;
GRANT EXECUTE ON PROCEDURE ragdb.WebRegisterNewRagPlayerOne TO _ragDBWebUserViewer@'localhost';
DELIMITER //
CREATE PROCEDURE WebRegisterNewRagPlayerTwo
(
IN emailverrifhash VARCHAR(32)
, OUT returnparam INT(1)
)
BEGIN
-- Hey it's not a tutorial
END //
DELIMITER ;
GRANT EXECUTE ON PROCEDURE ragdb.WebRegisterNewRagPlayerTwo TO _ragDBWebUserViewer@'localhost';
DELIMITER //
CREATE PROCEDURE RsetPasswordRagPlayerOne
(
IN login VARCHAR(23)
, IN emailverrifhash VARCHAR(32)
, OUT email VARCHAR(39)
)
BEGIN
-- Hey it's not a tutorial
END //
DELIMITER ;
GRANT EXECUTE ON PROCEDURE ragdb.RsetPasswordRagPlayerOne TO _ragDBWebUserViewer@'localhost';
DELIMITER //
CREATE PROCEDURE RsetPasswordRagPlayerTwo
(
IN password VARCHAR(32)
, IN emailverrifhash VARCHAR(32)
, OUT returnparam INT(1)
)
BEGIN
-- Hey it's not a tutorial
END //
DELIMITER ;
GRANT EXECUTE ON PROCEDURE ragdb.RsetPasswordRagPlayerTwo TO _ragDBWebUserViewer@'localhost';
So basically as you can see there is ragdbwebviewer that has access only to the stored procedures and I will add some views
it will have access to
like
Create View AS SELECT nicnake, level,
left join job....
whatever (note I'm not doing select * nor selecting login, email, passwords)
The password reset part is a security weakness, but if my website is hacked and
ragdbwebviewer credentials obtained, hacker will have to know account login to reset the password... in theory
in real life there will be some more layers of security...
Last edited by a moderator:
h34r: 
