Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SQL neni urcite spustitelny, psal sem TO ve vlaku v textaku .. je TO zapis myslenky
- CREATE PROCEDURE user_login_handler(IP VARCHAR(15), USERNAME VARCHAR(40), PASS VARCHAR(40))
- BEGIN
- DECLARE LOGIN_KEY VARCHAR(40);
- DECLARE SESSION_KEY VARCHAR(40);
- DECLARE userid INT(10);
- DECLARE userpass VARCHAR(40);
- SET userid = SELECT UID FROM users WHERE nick = USERNAME;
- SET userpass = SELECT passhash FROM users WHERE nick = USERNAME;
- IF (userid) THEN
- IF (userpass = sha1(PASS)) THEN
- SET LOGIN_KEY = SELECT KEYVALUE FROM security_keys WHERE SKID = 2;
- SET SESSION_KEY = SELECT KEYVALUE FROM security_keys WHERE SKID = 1;
- IF (LOGIN_KEY AND SESSION_KEY) THEN
- DECLARE LOGIN_KEY_HASH VARCHAR(40);
- DECLARE SESSION_KEY_HASH VARCHAR(40);
- SET LOGIN_KEY_HASH = sha1(CONCAT(PASS,TIME,LOGIN_KEY));
- SET SESSION_KEY_HASH = sha1(CONCAT(PASS,IP,TIME,SESSION_KEY));
- IF exist (SELECT UID FROM users_keys WHERE UID = userid) THEN
- UPDATE users_keys SET LOGIN_KEY = LOGIN_KEY_HASH WHERE UID = userid;
- UPDATE users_keys SET SESSION_KEY = SESSION_KEY_HASH WHERE UID = userid;
- ELSE
- INSERT INTO users_keys (UID ,LOGIN_KEY ,COOKIES_KEY ,PROTECTED)
- VALUES (userid,LOGIN_KEY_HASH,SESSION_KEY_HASH,0);
- END IF;
- RETURN CONCAT(1,LOGIN_KEY_HASH,SESSION_KEY_HASH);
- ELSE
- RETURN CONCAT(2,sha1(2),sha1(2));
- END IF;
- ELSE
- RETURN CONCAT(3,sha1(2),sha1(2));
- END IF;
- ELSE
- RETURN CONCAT(4,sha1(2),sha1(2));
- END IF;
- END
- --
- -- Struktura tabulky `security_keys`
- --
- CREATE TABLE IF NOT EXISTS `security_keys` (
- `SKID` INT(2) NOT NULL AUTO_INCREMENT COMMENT 'security key id',
- `KEYVALUE` VARCHAR(40) COLLATE utf8_bin NOT NULL COMMENT 'security key value',
- `DESCR` VARCHAR(40) COLLATE utf8_bin NOT NULL COMMENT 'decription',
- PRIMARY KEY (`SKID`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=5 ;
- --
- -- Vypisuji data pro tabulku `security_keys`
- --
- INSERT INTO `security_keys` (`SKID`, `KEYVALUE`, `DESCR`) VALUES
- (1, '9ecef4d8c37e772896c20384c6e4d4bbbbf4d968', 'users login key'),
- (2, 'd65107910e31a728fe31f958316ba48809d8e7c1', 'users session key'),
- (3, 'a5bca2ccdfa0cc1ea3423cde347b13f4955b4f30', 'administration access key'),
- (4, 'ae272fc89ab540bee64d920092ef0a34edc24377', 'master key for root');
- -- --------------------------------------------------------
- --
- -- Struktura tabulky `users`
- --
- CREATE TABLE IF NOT EXISTS `users` (
- `UID` INT(10) NOT NULL AUTO_INCREMENT,
- `passhash` VARCHAR(40) COLLATE utf8_bin NOT NULL,
- `nick` VARCHAR(40) COLLATE utf8_bin NOT NULL,
- PRIMARY KEY (`UID`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2 ;
- --
- -- Vypisuji data pro tabulku `users`
- --
- INSERT INTO `users` (`UID`, `passhash`, `nick`) VALUES
- (1, '7c4a8d09ca3762af61e59520943dc26494f8941b', 'tany');
- -- --------------------------------------------------------
- --
- -- Struktura tabulky `users_keys`
- --
- CREATE TABLE IF NOT EXISTS `users_keys` (
- `UID` INT(12) NOT NULL AUTO_INCREMENT COMMENT 'user id',
- `LOGIN_KEY` VARCHAR(40) COLLATE utf8_bin NOT NULL COMMENT 'key for login',
- `COOKIES_KEY` VARCHAR(40) COLLATE utf8_bin NOT NULL COMMENT 'key for remerber login ',
- `PROTECTED` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'bool readonly user (GM ...)',
- PRIMARY KEY (`UID`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2 ;
- --
- -- Vypisuji data pro tabulku `users_keys`
- --
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement