Don’t Panic! - CSCI E-151 (2024)

Introduction to Databases with SQL

Harvard Extension School
Fall 2024

Don’t Panic! - CSCI E-151 (1)
“An empty server room in an office at night, in the style of a detective noir,” generated by DALL·E 2

Problem to Solve

You’re a trained “pentester.” After your success in an earlier operation, a new company has hired you to perform a penetration test and report the vulnerabilities in their data system. This time, you suspect you can do better by writing a program in Python that automates your hack.

To succeed in this covert operation, you’ll need to…

  • Connect, via Python, to a SQLite database.
  • Alter, within your Python program, the administrator’s password.

If you don’t have experience with Python, not to worry! This problem will walk you through each step along the way.

Demo

Distribution Code

For this problem, you’ll need access to a database, a Python file, and a set of SQL statements to reset the database in case your hack fails the first time (not to worry if it does!).

Download the distribution code

Log into cs50.dev, click on your terminal window, and execute cd by itself. You should find that your terminal window’s prompt resembles the below:

$

Next execute

wget https://cdn.cs50.net/sql/2023/x/psets/6/dont-panic-python.zip

in order to download a ZIP called dont-panic-python.zip into your codespace.

Then execute

unzip dont-panic-python.zip

to create a folder called dont-panic-python. You no longer need the ZIP file, so you can execute

and respond with “y” followed by Enter at the prompt to remove the ZIP file you downloaded.

Now type

cd dont-panic-python

followed by Enter to move yourself into (i.e., open) that directory. Your prompt should now resemble the below.

dont-panic-python/ $

If all was successful, you should execute

ls

and see a database named dont-panic.db alongside a hack.py file and reset.sql file. If not, retrace your steps and see if you can determine where you went wrong!

Specification

In hack.py, write a Python program to achieve the following:

  • Connect, via Python, to a SQLite database.
  • Alter, within your Python program, the administrator’s password.

When your program in hack.py is run on a new instance of the database, it should produce the above results.

Clock’s ticking!

Walkthrough

If you’re new to Python (or to connecting Python with SQL!) this walkthrough will guide you through each of this problem’s steps.

Python

When you download the distribution code for this problem, you should notice a file named hack.py. You can tell this program is a Python program because it ends with .py. The .py extension identifies files as Python files much like how the .sql extension identifies files as a set of SQL statements.

At first, hack.py should only include a single line of Python code:

print("Hacked!")

To run this Python program, ensure that—when you type ls—you see hack.py among the files in your current directory. Then, execute the below in your terminal:

python hack.py

You should see “Hacked!” in your terminal window. Not quite a hack, but you’re on your way!

Connecting to a Database

Now that you’re able to run your Python program, the next step is to connect your program to dont-panic.db. To do so, you’ll need to make use of CS50’s library for Python. A library is a collection of code that someone else has written to solve a problem (and, importantly, which you can use in your own program!). In this case, one of the problems the CS50 library for Python helps you solve is the process of connecting to a SQLite database.

To use the CS50 library’s SQL functionality in your own program, replace print("Hacked!") with the below:

from cs50 import SQL

This line of Python code says that your program should grab (“import”) tools related to SQL from the CS50 library, called cs50.

With this library now included in your program, establishing a connection to dont-panic.db is as simple as one line of Python code:

from cs50 import SQLdb = SQL("sqlite:///dont-panic.db")

You can break this line of code into the following pieces:

  • db = SQL(...), which establishes a connection to the database given as an input, within the parentheses. This line of code also ensures you can refer to your database connection as db henceforth in your program.
  • sqlite:///dont-panic.db is a URL (similar to a website URL!) that identifies the database to which to connect (dont-panic.db) and which SQL dialect to use (sqlite, in this case, as opposed to mysql or postgres).

If you’re feeling more comfortable, you can learn more about this line of code in the documentation for CS50’s library for Python.

Try running your program now. You might not see anything happen and, if so, that’s a good sign!

Executing SQL Statements with Python

The CS50 library for Python’s SQL functionality comes with a method called execute. A method receives an input and produces an output. For instance, a method might take a SQL statement as input, execute that SQL statement on a database, and return to you the results of the SQL statement. In fact, that’s exactly what the execute method does!

from cs50 import SQLdb = SQL("sqlite:///dont-panic.db")db.execute( """ UPDATE "users" SET "password" = 'hacked!' WHERE "username" = 'admin'; """)

Notice that, inside the parentheses associated with the execute method, you’ve now written a fully-formed SQL query. When you run your Python program, the SQL query will be executed on the database.

After you run your program, try opening dont-panic.db with sqlite3. When you view the administrator’s password, you should find it is now “hacked!”

If at any point you’d like to reset dont-panic.db to its original state, recall that you can use reset.sql.

Prepared Statements

Imagine you wanted a user to determine the new administrative password as your Python program runs.

Recall from lecture that a prepared statement is a SQL query with placeholders for values that are inserted (“interpolated”) later. Since you don’t know what password your program’s user will choose, the best you can do is set a placeholder for the password and allow your program to interpolate the user-chosen password later. Using a prepared statement, then, can help!

The CS50 library for Python supports using prepared statements. First, modify your program to take input from the user:

from cs50 import SQLdb = SQL("sqlite:///dont-panic.db")password = input("Enter a password: ")db.execute( """ UPDATE "users" SET "password" = 'oops!' WHERE "username" = 'admin'; """)

Notice that if you now run your program, you’re prompted for a password. Whatever you enter is stored in the variable named password. A variable is a name for a value that can change.

Now, modify your SQL query to be a prepared statement. In CS50’s library, you can use a ? to represent a placeholder for a value you’ll supply later.

from cs50 import SQLdb = SQL("sqlite:///dont-panic.db")password = input("Enter a password: ")db.execute( """ UPDATE "users" SET "password" = ? WHERE "username" = 'admin'; """)

The last step, of course, is to tell the execute method which value it should substitute for the placeholder. To do that, you can add the value to substitute after the SQL query, separated by a comma:

from cs50 import SQLdb = SQL("sqlite:///dont-panic.db")password = input("Enter a password: ")db.execute( """ UPDATE "users" SET "password" = ? WHERE "username" = 'admin'; """, password)

Now, try running your program and viewing the changes in dont-panic.db!

Assessment

Given this problem’s alternative format, submission of hack.py is instead assessed only on correctness by check50.

check50

check50 cs50/problems/2024/sql/sentimental/dont-panic/python

How to Submit

After you submit, be sure to check your autograder results. If you see SUBMISSION ERROR: missing files (0.0/1.0), it means your file was not named exactly as prescribed (or you uploaded it to the wrong problem).

Correctness in submissions entails everything from reading the specification, writing code that is compliant with it, and submitting files with the correct name. If you see this error, you should resubmit right away, making sure your submission is fully compliant with the specification. The staff will not adjust your filenames for you after the fact!

  1. Download your hack.py file by control-clicking or right-clicking on the file in your codespace’s file browser and choosing Download.
  2. Go to CSCI E-151’s Gradescope page.
  3. Click Problem Set 6: Don’t Panic (Python).
  4. Drag and drop your hack.py file to the area that says Drag & Drop. Be sure it has that exact filename! If you upload a file with a different name, the autograder likely will fail when trying to run it, and ensuring you have uploaded files with the correct filenames is your responsibility!
  5. Click Upload.
Don’t Panic! - CSCI E-151 (2024)
Top Articles
Latest Posts
Recommended Articles
Article information

Author: Rob Wisoky

Last Updated:

Views: 5560

Rating: 4.8 / 5 (48 voted)

Reviews: 95% of readers found this page helpful

Author information

Name: Rob Wisoky

Birthday: 1994-09-30

Address: 5789 Michel Vista, West Domenic, OR 80464-9452

Phone: +97313824072371

Job: Education Orchestrator

Hobby: Lockpicking, Crocheting, Baton twirling, Video gaming, Jogging, Whittling, Model building

Introduction: My name is Rob Wisoky, I am a smiling, helpful, encouraging, zealous, energetic, faithful, fantastic person who loves writing and wants to share my knowledge and understanding with you.