This lab was designed to be completed during lab-time for cs250. This lab is not graded for correctness. However, you should work through all of the problems, as it will help you on assignments and exams. If you cannot complete the lab in the alloted time, finish at home.
This particular lab was designed to be completed on the Mac lab machines in GS 930
Recall the banking example that was used in lecture this week.
I wrote a python program named deposit.py with the following code:
import sys
import sqlite3
print('Welcome to the banking app!')
print('Enter a account ID to deposit money into:')
acct_id = sys.stdin.readline().strip()
print('Enter amount to deposit:')
dep = sys.stdin.readline().strip()
conn = sqlite3.connect('bankdb')
query = "UPDATE account SET balance = balance + " + dep + " WHERE acct_id == " + acct_id + ";"
res = conn.executescript(query)
conn.commit()
conn.close()
This program connects to a sqlite3 database named bankdb, which has the following schema:
CREATE TABLE customer (
first_name TEXT,
last_name TEXT,
uid INT PRIMARY KEY);
CREATE TABLE account (
type TEXT,
balance FLOAT,
owner_uid INT,
acct_id INT PRIMARY KEY,
FOREIGN KEY (owner_uid) REFERENCES customer(uid));
(A)
Come up with at least 10 total insert statements.
Insert at least 3 rows into customer and at least 7 into account.
(B)
Run deposit.py a few times, and change the balances of a multiple accounts.
(C) Use SQL Injection to add $1,000,000 to one of the accounts.
(D)
Use SQL Injection to delete one of the accounts from the account table.
(E) Use SQL Injection to change the first/last name of one of the existing customers to Jiminy Cricket.
(F)
What would happen if a user typed 1 OR acct_id != 1 ; -- as the first input (acct_id) to deposit.py?
First, come up with what you think would happen in your head.
Then, try it out and take a peek at the database afterward.
(G) Fix this script so that it is no longer vulnerable to SQL injection. Once you’ve made the fix, try out the above SQL injection “attacks” to make sure they no longer work.
This neat website has links to hundreds of interesting and unique CSV data sets. Use this site for this problem.
(A) Browse through the aforementioned site and find a data set that looks interesting to you. Once found, click the “CSV” link to download the data as CSV. Open the file up in a text editor, such as atom. Make sure that the first row of the CSV file has the names of the columns. If not, add them in manually.
(B)
Create a new (empty) SQLite database named lab12-2.
Using the techniques discussed in lecture this week, load this data file into a new table.
(C)
Type .schema.
You’ll probably notice that all of the columns in the table are of type TEXT.
Depending on the data set you chose, this probably not what you want, because some of them would likely be integer or float values.
Delete the table (using DROP TABLE).
(D) Open up the CSV file and get rid of the first row with the names of the columns. Then go back to your SQL session, manually create a more accurate schema for the data, and re-load the file.
For this problem, download the Diamond CSV data set. You will first load this data into a table in a SQLite database, and then you will use the aggregate functions discussed in lecture to compute some values.
(A)
diamonddb.CREATE TABLE ... statement to create a table named diamond that this CSV file will be loaded into.
Make sure to give each column an appropriate type.Diamond.csv (the row with the column titles).import to load this CSV file into the diamond table(B)
Write a SELECT statement that computes the average price of all diamonds in the table.
(C)
Write a SELECT statement that returns the cheapest diamond in the whole table.
(D)
Write a SELECT statement that returns the largest diamond (the most carats) whose color is 'E' and clarity is IF.
(E)
Write a SELECT statement that counts the number of diamonds that cost less than 3000 dollars.
(F)
Write a SELECT statement that computes the average size (in carats) of diamonds costing more than 7000 dollars.