SOLUTION IT360 Lab 11 Database Security SOLUTION

 

 

Part 1 Database Security: Granting Privileges

 

1.     Ask your partner to connect to cardhu using MySQL Workbench and then select all songs from your songs11 table: SELECT * FROM mxxxxxx.songs11; where xxxxxx is your alpha. What is the answer?

 

Answer: command denied, user does not have permission to select from that table

 

2.     Write a GRANT statement to grant SELECT privileges on your songs table to your partner (use the GRANT syntax given above).

 

Answer: GRANT SELECT ON mXXXXXX.song11 TO mYYYYYY;

If we also want to allow user mYYYYYY to be able to give the same permissions to others, we can use

GRANT SELECT ON mXXXXXX.song11 TO mYYYYYY WITH GRANT OPTION

 

3.     Ask your partner to run again the SELECT * FROM mxxxxxx.songs11; where xxxxxx is your alpha. What is the answer this time? If your partner gets the same answer as before, try to logout and log back in to MySQL Workbench.

 

Answer: The partner will be able to see the rows of the table now. The statement executes OK.

 

4.     What happens when your partner tries to INSERT a new song into your database by using the following statement: INSERT INTO mxxxxxx.songs11(Title, Artist) VALUES('Your Favorite Song', 'Your Favorite Artist')? Why?

Answer: command denied, user does not have permission to insert into that table. The user only has permission to select, not to insert.

 

5.     Now that you saw how you can grant privileges using the GRANT command, revoke the privileges that you granted to your partner: REVOKE SELECT ON mxxxxxx.songs FROM myyyyyy; where yyyyyy is your partner's alpha.

Answer: REVOKE SELECT ON mXXXXXX.songs11 FROM mYYYYYY;

 

Part 2 Application Security: Protecting Passwords

 

I have provided a basic userPage.php and user.inc.php in the zip file downloaded from the course calendar. The userPage.php is a basic login page. We will add to that functionality next.

 

SHA Hashing: 

 

  1. Modify userPage.php downloaded from the course calendar to add an “add user” button. When clicked, the “addUser” function in user.inc.php should be invoked with the appropriate parameters (similar with the add product in Lab 9).

 

Test your add user button by inserting a few users. Check that users were indeed inserted by using the MySQL Workbench, SELECT * FROM users11;

Added and modified code in userPage.php:

 

//process the add user

if (isset($_POST['addUSer'])) {

            $user = new User($_POST['user_ID'], $_POST['password']);

  $response = $user->addUser($db);

  if ($response){

            //everything OK, ask user to log in

     $page->content .=  "\n" . 'Thank you for registering. Please log in.';

  }

  else {

            //problems

     $page->content .=  "\n" . 'Sorry, but there was an error adding your user. Contact system administrator.';

  }

  unset($_POST['addUser']);

}

 

//create a form to get user name and password

$page->content .= '<h2>Authentication</h2>';

$page->content .= '<form action="userPage.php" method="post">';

$page->content .= '<p><label>Enter your user ID: <input type="text" name="user_ID"/></label></p>

    <p><label>Enter your password: <input type="password" name="password"/></label></p>

    <p><input type="submit" name = "login" value = "Login"/> <input type="submit" name = "addUser" value = "Add User"/></p>

    </form>';

 

  1. Add a line in addUser function in user.inc.php to encrypt the password received and insert the encrypted password into the database, instead of the plain text password. To encrypt a password, you can use the PHP sha1 function. For example,

           

            $encrypted = sha1("some string here");

returns the encrypted version of the "some string here".  

 

Insert a new user by invoking userPage.php from a browser and check your MySQL database and see that the passwords are now encrypted. 

 

In user.inc.php, for addUser function, replace $password = $this->password; with  $password = sha1($this->password);

 

8.     Now modify the “validate” function in user.inc.php to check against this new sha1 value. 

 

In user.inc.php, for validate function, replace $password = $this->password; with  $password = sha1($this->password);

 

9.     How does the encryption change the response to a user’s question: I forgot my password, can you tell me what it is?

 

The password cannot be returned to the user since it is encrypted using a one-way encryption function (so difficult to decrypt). The user must reset the password, or a temporary password is provided.

 

Part 3 Application Security: Session Variables


Remember the song website we started to create in class. With the new userPage.php in place, I would like to only allow valid users to access the songPage.php. The code currently in the userPage.php sets a session variable for the valid users and redirects them to songPage.php, so everything seems fine if users invoke userPage.php first. Only valid users should be able to see the list of songs, but try to access the songPage.php without logging in (without going to userPage.php first). 

 

  1. What happened?  Why?  Although you may not think that this is a big deal, what if it was a list of your accounts and account numbers instead of a bunch of songs? 

 

Answer: songPage.php does not do any types of checks, so anybody that knows the URL can run the code, even if not logged in. This is a security risk if the songPage displays sensitive information.

 

  1. Modify the file songPage.php to ensure that only valid users (ie. those who have logged in properly and have had the $_SESSION['name'] session variable set) will be able to see the page content.

 

Answer: Add this at top of page, after session_start() (even before connecting to the database):

//check if user logged in. If not, redirect to login page

if (!isset($_SESSION['name']) || empty($_SESSION['name'])){

            header('Location: userPage.php');

            exit;

}

 

Part 4 Application Security: SQL Injections

 

  1. a) Write a simple SQL Injection attack for any of the pages used for this lab. You can modify the provided PHP code to make it more susceptible to an attack if you want.

 

The quotes in the input string are not escaped, so all pages are vulnerable to various attacks.

Any single quote in the songPage title or artist will mess up the code

In the userPage, inserting a clever string such as blah’ OR 1 --   in the user id field, validates the user since the generated SQL query will be SELECT Type FROM users11 WHERE UserID = 'blah’ OR 1 --  ' and Password = '$password' Due to the OR 1, the first part of the condition is true, and because of the “— “ the last part of the condition is treated as a comment by SQL, so the password is not checked.

 

b) What are the effects of your attack? Anything that would change in any way the intended functionality of any of the pages is OK.

 

Answer: Any single quote will mess up the code so informative error messages are displayed. An attached will learn the names of the tables, and columns in the database.

Second attack will bypass the user authentication, allowing unauthorized users to use the system.

 

  1. How should the code be modified  for the page that was attacked in order to prevent future attacks of the same type? (you do not have to actually modify the code, just say what needs to be done).

 

Answer: You should definitely escape all quotes, which is done by using addslashes() function in PHP. Check the input string for weird characters or strings (such as SQL keywords or comment). Using “query” method in the mysqli class (as we did in class) to submit queries to the database is also preventing hackers to inject some string that would execute multiple queries.