Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- drop procedure if exists get_user_credentials;
- delimiter #
- create procedure get_user_credentials
- (
- in p_username varchar(32),
- in p_password varchar(32),
- in p_session_id char(32),
- in p_user_ip varchar(16)
- )
- proc_main:begin
- declare p_user_id int unsigned default null;
- declare p_status_id tinyint default -1;
- declare p_today datetime default now();
- declare p_reason varchar(512) default null;
- declare p_ban_id int default null;
- -- ban checks
- select u.user_id, u.status_id into p_user_id, p_status_id
- from users u where u.username = p_username and u.password = p_password and u.user_id > 1;
- select ban_id, reason into p_ban_id, p_reason
- from IP_ban where ip_num = inet_aton(p_user_ip) and p_today >= created_date and p_today <= expiry_date;
- if p_ban_id is not null then
- -- banned user login attempt
- call insert_user_log(5, p_user_id, p_session_id, p_user_ip);
- select 0 as user_id, 1 as err_id, p_reason as msg;
- leave proc_main;
- end if;
- select ban_id, reason into p_ban_id, p_reason
- from username_ban where username = p_username and p_today >= created_date and p_today <= expiry_date;
- if p_ban_id is not null then
- -- banned user login attempt
- call insert_user_log(5, p_user_id, p_session_id, p_user_ip);
- select 0 as user_id, 1 as err_id, p_reason as msg;
- leave proc_main;
- end if;
- if p_user_id is null then
- -- invalid user login
- call insert_user_log(3, p_user_id, p_session_id, p_user_ip);
- select 0 as user_id, e.* from error_msg e where e.err_id = 102;
- leave proc_main;
- end if;
- if p_status_id <= 0 then
- -- suspended, banned etc
- call insert_user_log(4, p_user_id, p_session_id, p_user_ip);
- call insert_user_ip(p_user_id, p_session_id, p_user_ip);
- select 0 as user_id, s.msg from user_status s where s.status_id = p_status_id;
- leave proc_main;
- end if;
- -- valid user let 'em in
- update users u set u.last_login_date = now() where u.user_id = p_user_id;
- call insert_user_log(1, p_user_id, p_session_id, p_user_ip);
- call insert_user_ip(p_user_id, p_session_id, p_user_ip);
- select
- u.user_id,u.username,u.role_id,u.role_name,u.status_id,
- u.status_name,u.theme_id,u.theme_name,game_id,
- u.last_login_date_fmt, warn_count
- from
- users_view u where u.user_id = p_user_id;
- end proc_main #
- delimiter ;
Add Comment
Please, Sign In to add comment