Security Explain what security vulnerabilities were minimized by the project. Re
ID: 3670885 • Letter: S
Question
Security
Explain what security vulnerabilities were minimized by the project. Refer to the lines for the code whenever it helps to make clear your point. The more precisely you point to the code that helps to protect, the better.
Project
Introduction
Here is the introductory paragraph where you describe what the project is about. You also need to include a brief description of all the technologies needed for the project to run.
SQL Scripts
init.sql
-- init.sql
drop table if exists users cascade;
drop table if exists questions cascade;
drop table if exists evaluations cascade;
drop table if exists evaluation_questions cascade;
drop table if exists answers cascade;
create table users(
id serial,
username text unique,
password text,
salt text,
enc_pass text,
division text,
department text,
primary key (id)
);
create table questions(
id serial,
question_text text,
primary key (id)
);
create table evaluations(
id serial,
employee_id integer references users(id),
eval_title text,
eval_instructions text,
primary key (id)
);
create table evaluation_questions(
eval_id integer references evaluations(id),
question_id integer references questions(id)
);
create table answers(
id serial,
eval_id integer references evaluations(id),
question_id integer references questions(id),
num1 integer default 0,
num2 integer default 0,
num3 integer default 0,
num4 integer default 0,
num5 integer default 0,
primary key (id)
);
create view users_evaluations_view as
select users.id as uid, users.username,
users.division, users.department,
evaluations.id as eval_id, evaluations.employee_id,
evaluations.eval_title, evaluations.eval_instructions
from users join evaluations
on users.id=evaluations.employee_id
order by employee_id;
This script is used to create the tables used for the application. Lines 2-6 drop the tables so this script can be used to reset all the database tables so they are empty.
The view, "users_evaluations_view", is created on lines 50-57. The main purpose for this view, is to join the users and evaluations tables. This is needed to display the correct list of evaluations when an authenticated user logs in.
auth_funcs.sql
-- auth_funcs.sql
create or replace function do_hash(_mystring text)
returns text as
$func$
begin
return encode(digest(_mystring,'sha512'),'hex');
end;
$func$
language 'plpgsql';
create or replace function make_salt(_mystring text)
returns text as
$func$
begin
return do_hash(now() || _mystring);
end;
$func$
language 'plpgsql';
create or replace function make_enc_pass(_mystring text)
returns text as
$func$
begin
return do_hash(_mystring || make_salt(_mystring));
end;
$func$
language 'plpgsql';
insert_funcs.sql
-- insert_funcs.sql functions to aid inserting data
create or replace function add_user(_user text, _pass text,
_div text, _dept text)
returns integer as
$func$
begin
insert into users (username, salt, enc_pass, division,
department) values (_user, make_salt(_pass),
make_enc_pass(_pass), _div, _dept);
return currval('users_id_seq');
end;
$func$
language 'plpgsql';
create or replace function add_question(_qtext text)
returns integer as
$func$
begin
insert into questions(question_text) values
(_qtext);
return currval('questions_id_seq');
end;
$func$
language 'plpgsql';
create or replace function add_evaluation(_username text,
_title text, _instr text, _question_list text)
returns integer as
$func$
declare
_emp_id integer;
_eval_id integer;
_qArray text[];
rec record;
begin
select into rec * from users where username=_username;
if not found then
return -1;
else
_emp_id := rec.id;
end if;
insert into evaluations (employee_id, eval_title,
eval_instructions) values (_emp_id, _title, _instr);
_eval_id := currval('evaluations_id_seq');
_qArray := string_to_array(_question_list,',');
for i in 1 .. array_upper(_qArray,1)
loop
insert into evaluation_questions(eval_id, question_id)
values (_eval_id, cast(_qArray[i] as integer));
insert into answers (eval_id, question_id) values
(_eval_id, cast(_qArray[i] as integer));
end loop;
return currval('answers_id_seq');
end;
$func$
language 'plpgsql';
query_funcs.sql
-- query_funcs.sql
create or replace function check_user(_user text,
_pass text)
returns record as
$$
declare
_results record;
rec record;
begin
select into rec * from users where username=_user;
if not found then
_results := (-1, 'bad_user'::text,
'bad_user'::text);
else
if do_hash(_pass || rec.salt) = rec.enc_pass then
_results := (rec.id, rec.division,
rec.department);
else
_results := (-1, 'bad_pass'::text,
'bad_pass'::text);
end if;
end if;
return _results;
end;
$$
language 'plpgsql';
create or replace function get_dept(_dept text)
returns setof evaluations as
$$
declare
rec evaluations%rowtype;
begin
for rec in select * from evaluations
where employee_id in (select id from users
where department=_dept) order by employee_id
loop
return next rec;
end loop;
end;
$$
language 'plpgsql';
create or replace function get_div(_div text)
returns setof evaluations as
$$
declare
rec evaluations%rowtype;
begin
for rec in select * from evaluations
where employee_id in (select id from users
where division=_div) order by employee_id
loop
return next rec;
end loop;
end;
$$
language 'plpgsql';
The last two functions defined on lines 28-42 and lines 44-58 are not used because the users_evaluations_view defined in "init.sql" supercedes their usage. But, they illustrate how you might perform more complex queries using plpgsql.
The check_user() function defined on lines 2-26 is used to authenticate a user. This function first checks to see if the username exists in the users table. If the username is not in users, a -1 is returned for the id. If the user does exist in users but the password is wrong, a -1 is returned for the id. Only if the username exists and the supplied password is the correct one does this function return the user's id, division and department.
add_initial_data.sql
all.sql
i init.sql
i auth_funcs.sql
i insert_funcs.sql
i query_funcs.sql
i add_initial_data.sql
PHP Main Scripts
<?php
require_once('../../include/proj1/proj1.php');
$pdo = connect();
if (!$pdo) {
die('Could not connect');
}
$loggedIn = FALSE;
if (count($_POST > 0)) {
$username = $_POST['username'];
$password = $_post
}
if (empty($_POST['username']) || empty($_POST['password'] ||
$loggedIn = FALSE) {
$displayBlock = “ <form action=”login.php” “ .
“method=”post”> ”;
$displayBlock .= “ </form> ”;
}
?>
<html>
<body>
<?php echo $displayBlock; ?>
</body>
</html>
Explanation / Answer
The Security vulnerabilities minimized by the project were listed below:
The lines _results := (-1, 'bad_user'::text,
'bad_user'::text);
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.