\n", " $\"\"$ \n", " \"\"\" + w2 + w3;\n", " return tmp;" ] }, { "cell_type": "markdown", "metadata": { "id": "6A6KJESApcm4" }, "source": [ "The `partition()` method splits a string into three parts based on the given separator. It returns a tuple containing the part before the separator, the separator itself, and the part after the separator.\n", "\n", "For example, `\"hello world\".partition(\" \")` will return `(\"hello\", \" \", \"world\")`.\n" ] }, { "cell_type": "markdown", "metadata": { "id": "KJwuSFcFpcm4" }, "source": [ "### Warmup\n", "1. Provide a comment below and see how it shows up on the website. Run the following two blocks and refresh the website (http://localhost:5000)." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "rcFU6WyRpcm4" }, "outputs": [], "source": [ "comment = \"\" # Your answer (1. 2 marks)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "teWGELq1pcm4" }, "outputs": [], "source": [ "# Post your comment and refresh the website (http://localhost:5000).\n", "web = web_template;\n", "web = post(comment);\n", "\n", "if p:\n", " p.terminate()\n", " p.join()\n", "p = Process(target=app.run(host=\"0.0.0.0\", port=5000))\n", "p.start()" ] }, { "cell_type": "markdown", "metadata": { "id": "Qrojw71Spcm4" }, "source": [ "2. Construct a malicious input to comment that will inject an Javascript script of your choice." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "xtAxJUaXpcm4" }, "outputs": [], "source": [ "comment = # Your answer (2 - 8 marks) (Hint: Use the script tag and the alert() method in JavaScript.)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "YIIJ1vtJpcm4" }, "outputs": [], "source": [ "# Post your comment and refresh the website (http://localhost:5000).\n", "web = web_template;\n", "web = post(comment);\n", "\n", "if p:\n", " p.terminate()\n", " p.join()\n", "p = Process(target=app.run(host=\"0.0.0.0\", port=5000))\n", "p.start()" ] }, { "cell_type": "markdown", "metadata": { "id": "h6dt2ZGFpcm4" }, "source": [ "### Practice\n", "An important defense against XSS attacks is input sanitization. In this lab, we provide several attack scripts, and you will need to implement an sanitizer against them. Note that your sanitizer should allow normal comments to pass." ] }, { "cell_type": "markdown", "metadata": { "id": "-AnXnWjVpcm4" }, "source": [ "## Implement a sanitizer\n", "This part is the most important part of your assignment. Implement a sanitizer against the following XSS attack scripts. Note that your sanitizer should make all the malicious input fail but allow the honest input to be posted normally (which means you should not just output an empty string). You should think of the scenario where your sanitizer is used in a real-world website." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "cZfZp4fvpcm4" }, "outputs": [], "source": [ "def sanitizer(comment):\n", " # Your answer (3 - Avoid each attack 15 marks (60 marks in total))\n", " return comment;" ] }, { "cell_type": "markdown", "metadata": { "id": "2vZMyq6gpcm4" }, "source": [ "1. Normal input" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "JZ25L17xpcm4" }, "outputs": [], "source": [ "normal_comment = \"Here is my script.
Nobody told me I needed to bring my Web programming skills to SENG 4220!
\"" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "7SR49EbEpcm4" }, "outputs": [], "source": [ "# Post your comment and refresh the website (http://localhost:5000).\n", "web = web_template;\n", "comment = sanitizer(normal_comment);\n", "web = post(comment);\n", "\n", "if p:\n", " p.terminate()\n", " p.join()\n", "p = Process(target=app.run(host=\"0.0.0.0\", port=5000))\n", "p.start()" ] }, { "cell_type": "markdown", "metadata": { "id": "ed_IPujupcm5" }, "source": [ "2. XSS attack script A.
\n", "Note. Your sanitizer function must prevent this type of attack from occurring." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "BdrOBd_Qpcm5" }, "outputs": [], "source": [ "attack_comment = \"\" # attack comment" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "TOv36n2gpcm5" }, "outputs": [], "source": [ "# Post your comment and refresh the website (http://localhost:5000).\n", "web = web_template;\n", "comment = sanitizer(attack_comment);\n", "web = post(comment);\n", "\n", "if p:\n", " p.terminate()\n", " p.join()\n", "p = Process(target=app.run(host=\"0.0.0.0\", port=5000))\n", "p.start()" ] }, { "cell_type": "markdown", "metadata": { "id": "rHF7wBhPpcm5" }, "source": [ "3. XSS attack script B.
\n", "Note. Your sanitizer function also must prevent this type of attack from occurring." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "QYuxUJMMpcm5" }, "outputs": [], "source": [ "attack_comment = 'ipt>alert(\"XSS attack!\")ipt>' # attack comment" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "qpobGnXQpcm5" }, "outputs": [], "source": [ "# Post your comment and refresh the website (http://localhost:5000).\n", "web = web_template;\n", "comment = sanitizer(attack_comment);\n", "web = post(comment);\n", "\n", "if p:\n", " p.terminate()\n", " p.join()\n", "p = Process(target=app.run(host=\"0.0.0.0\", port=5000))\n", "p.start()" ] }, { "cell_type": "markdown", "metadata": { "id": "Dq1JHRGMpcm5" }, "source": [ "4. XSS attack script C.
\n", "Note. Your sanitizer function also must prevent this type of attack from occurring." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "VU43Awdlpcm5" }, "outputs": [], "source": [ "attack_comment = 'click me!' # attack comment" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "CIRa9_SRpcm5" }, "outputs": [], "source": [ "# Post your comment and refresh the website (http://localhost:5000).\n", "# To trigger the attack, you need to click the button.\n", "web = web_template;\n", "comment = sanitizer(attack_comment);\n", "web = post(comment);\n", "\n", "if p:\n", " p.terminate()\n", " p.join()\n", "p = Process(target=app.run(host=\"0.0.0.0\", port=5000))\n", "p.start()" ] }, { "cell_type": "markdown", "metadata": { "id": "uT7kSpy2pcm5" }, "source": [ "5. XSS attack script D." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "eM4_r2L9pcm5" }, "outputs": [], "source": [ "attack_comment = '\\x3cscript>alert(\"XSS attack!\")\\x3c/script>' # attack comment" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "5Mp0kHJjpcm5" }, "outputs": [], "source": [ "# Post your comment and refresh the website (http://localhost:5000).\n", "web = web_template;\n", "comment = sanitizer(attack_comment);\n", "web = post(comment);\n", "\n", "if p:\n", " p.terminate()\n", " p.join()\n", "p = Process(target=app.run(host=\"0.0.0.0\", port=5000))\n", "p.start()" ] }, { "cell_type": "markdown", "metadata": { "id": "gmCCaGREpcm9" }, "source": [ "### Finish\n", "Run the following block of code to stop the server once you are finished." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "YZgMiFo9pcm9" }, "outputs": [], "source": [ "p.terminate()\n", "p.join()" ] }, { "cell_type": "markdown", "metadata": { "id": "XbBIeJVFpcm9" }, "source": [ "## Section 2: A Simple SQL Injection Attack" ] }, { "cell_type": "markdown", "metadata": { "id": "096oMiEZpcm9" }, "source": [ "In this section, we will learn how to perform a SQL injection attack. SQL injection is a subset of code injection attacks, a class of attacks whose core idea is to convince the server to interpret an input as part of a SQL query. If the application is creating SQL strings by integrating user's input on the fly and then running them, we will see that it's straightforward to inject malicious SQL code. SQL injection attacks allow attackers to spoof one's identity or tamper with existing data.\n", "\n", "### Setup\n", "We first introduce how to build a database in python. Sqlite3 is a python module that provides a SQL interface to interact with a lightweight disk-based database. We will use this module to create the database and execute our SQL command." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "deletable": false, "editable": false, "id": "q9ctXpC8pcm9" }, "outputs": [], "source": [ "import sqlite3" ] }, { "cell_type": "markdown", "metadata": { "id": "mVdL_qpnpcm9" }, "source": [ "To use the module, we must first create a Connection object that represents the database. Usually, the data will be stored in the local file (for example, example.db). In this section, we use a special name :memory: to create a database in the memory. Once we have establish a Connection for the database, we can create a Cursor object and use it to execute SQL commands to insert data." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "amyBGPElpcm9" }, "outputs": [], "source": [ "# Create database\n", "# con = sqlite3.connect('example.db')\n", "con = sqlite3.connect(':memory:')\n", "# Create the Cursor\n", "cur = con.cursor()" ] }, { "cell_type": "markdown", "metadata": { "id": "SjOIi71Mpcm9" }, "source": [ "Now we call the execute() function to create two tables: the transcripts table that stores the grades of all the students, and the users table that stores passwords of all users.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "_G2Qu88xpcm9" }, "outputs": [], "source": [ "# Create tables\n", "cur.execute('''CREATE TABLE transcripts\n", " (studentID integer, name text, course text, grade text)''')\n", "cur.execute('''CREATE TABLE users\n", " (username text, password text)''')" ] }, { "cell_type": "markdown", "metadata": { "id": "CBS0q4tFpcm-" }, "source": [ "Below, we provide a function to execute multiple SQL commands in a single script. Using this function, we can insert data to both tables." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "HWS03xZhpcm-" }, "outputs": [], "source": [ "def exe_sql_script(query):\n", " (q1, q2, q3) = query.partition(';');\n", " res = [];\n", " while q2 != '':\n", " print(q1);\n", " try:\n", " for row in cur.execute(q1):\n", " res.append(row)\n", " except:\n", " print(\"Failed to execute the sql script!\")\n", " return []\n", " (q1, q2, q3) = q3.partition(';');\n", " print(q1);\n", " try:\n", " for row in cur.execute(q1):\n", " res.append(row)\n", " except:\n", " print(\"Failed to execute the sql script!\")\n", " return []\n", " return res" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "Yn_FIBnLpcm-" }, "outputs": [], "source": [ "exe_sql_script(\"\"\"\n", " INSERT INTO transcripts VALUES (1, 'Alfredo', 'SENG4220', 'A+');\n", " INSERT INTO transcripts VALUES (1, 'Alfredo', 'SENG4640', 'A-');\n", " INSERT INTO transcripts VALUES (2, 'Kyle', 'SENG4220', 'A-');\n", " INSERT INTO transcripts VALUES (2, 'Kyle', 'SENG3130', 'A');\n", " INSERT INTO transcripts VALUES (3, 'Brendan', 'SENG3130', 'B-');\n", " INSERT INTO transcripts VALUES (4, 'Brendan', 'SENG4220', 'B');\n", " INSERT INTO transcripts VALUES (4, 'Brendan', 'SENG3130', 'A');\n", " INSERT INTO transcripts VALUES (5, 'Adithya', 'SENG2110', 'A');\n", " INSERT INTO transcripts VALUES (6, 'Adithya', 'SENG3130', 'A');\n", " INSERT INTO transcripts VALUES (7, 'Ahana', 'SENG3130', 'C-');\n", " INSERT INTO transcripts VALUES (8, 'Domenick', 'SENG4220', 'A');\n", " INSERT INTO transcripts VALUES (8, 'Domenick', 'SENG3130', 'B');\n", " INSERT INTO transcripts VALUES (9, 'Trevor', 'SENG4220', 'A-');\n", " INSERT INTO transcripts VALUES (10, 'Kyle', 'SENG4640', 'B');\n", " INSERT INTO transcripts VALUES (11, 'Ahana', 'SENG4220', 'A');\n", " INSERT INTO transcripts VALUES (11, 'Ahana', 'SENG4640', 'A-');\n", " INSERT INTO transcripts VALUES (12, 'Trevor', 'SENG4640', 'B');\n", "\n", " INSERT INTO users VALUES ('Sina', '83@Ln^Yck_R@z2x#');\n", " INSERT INTO users VALUES ('manager', 'LbJHaRRm+98tF5P$');\n", " INSERT INTO users VALUES ('admin', 'cLzvCw9kCxYg?AdE');\n", " \"\"\");" ] }, { "cell_type": "markdown", "metadata": { "id": "LCcnZQUapcm-" }, "source": [ "Now we can collect data from the tables using the Select SQL command. Below, we select the records from the transcript table in the order of grades." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "TB1yUCiOpcm-" }, "outputs": [], "source": [ "res = exe_sql_script('SELECT * FROM transcripts ORDER BY grade');\n", "print(res)" ] }, { "cell_type": "markdown", "metadata": { "id": "liVioWo-pcm-" }, "source": [ "#### Warmup:\n", "1. Please provide a query that selects all records from the transcript table, ordered by course." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "6JcMrhkBpcm-" }, "outputs": [], "source": [ "query = ''; # Your answer (1 - 4 marks)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "auKsLWekpcm-" }, "outputs": [], "source": [ "# Test your answer\n", "res = exe_sql_script(query);\n", "print(res)" ] }, { "cell_type": "markdown", "metadata": { "id": "2s0oiFm2pcm-" }, "source": [ "2. Please provide a query that selects all grades from the transcript table where the course is SENG4220." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "cFmDa5d0pcm-" }, "outputs": [], "source": [ "query = \"\"; # Your answer (2 - 4 marks)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "FGnDOvm8pcm-" }, "outputs": [], "source": [ "# Test your answer\n", "res = exe_sql_script(query);\n", "print(res)" ] }, { "cell_type": "markdown", "metadata": { "id": "bhjvU8v1pcm_" }, "source": [ "## Practice\n", "In the above examples, users can provide the full query string for accessing the database. In the most of real-world applications, however, users usually can only provide a small part of query string, which will be concatenated with other strings provided by the application. However, this still allows users to perform an SQL injection attack.\n", "\n", "In the following questions, you are asked to fill in the input variable, which will be used to construct the query string later.\n", "### Question 1.\n", "Please fill in the input variable so that the SQL command will select all the records in the transcripts table where the course is SENG4220. You can execute the following code block to see if your answer is correct." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "RgyGHGmYpcm_" }, "outputs": [], "source": [ "input = '' # Your answer (3 - 2 marks)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "nPTm1_Yvpcm_" }, "outputs": [], "source": [ "# Test your answer\n", "query = \"SELECT * FROM transcripts WHERE course = '\" + input + \"'\";\n", "res = exe_sql_script(query);\n", "print(res)" ] }, { "cell_type": "markdown", "metadata": { "id": "M_XNe2MUpcm_" }, "source": [ "### Question 2.\n", "Now we can see how SQL injection works. It seems users can only specify the course name; however, some input strings may result in undesirable SQL commands.\n", "\n", "Please fill in the input variable so that the SQL command will select the password of admin from the users table. You can execute the following codes to see if your answer is correct." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "zTCMuLvdpcm_" }, "outputs": [], "source": [ "input = '' # Your answer (4 - 10 marks)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "pTV3Pzdbpcm_" }, "outputs": [], "source": [ "# Test your answer\n", "query = \"SELECT * FROM transcripts WHERE course = '\" + input + \"'\";\n", "res = exe_sql_script(query);\n", "print(res)" ] }, { "cell_type": "markdown", "metadata": { "id": "fVzHT5XTpcm_" }, "source": [ "### Question 3.\n", "Consider the following login function. Given the username and password, it searches the database for any matching records. If (len(select_res) > 0), the login attempt will succeed; otherwise, the login attempt will fail." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "LiRYo2qApcm_" }, "outputs": [], "source": [ "def login(username, password):\n", " try:\n", " select_res = exe_sql_script(\"SELECT username FROM users WHERE username = '\" + username + \"' AND password = '\" + password +\"'\");\n", " except:\n", " print(\"Failed to execute the sql script!\")\n", " if len(select_res) > 0:\n", " print(\"Login Successfully!\")\n", " print(\"user: \" + username)\n", " else:\n", " print(\"Login Failed!\")" ] }, { "cell_type": "markdown", "metadata": { "id": "sBR95J0ppcm_" }, "source": [ "Please provide values for the username and password variables to login as admin. You can find out the password of admin in the setup script or question 2. Execute the following login call to see the result." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "pEK-lnCSpcm_" }, "outputs": [], "source": [ "username = \"\" # Your answer (5 - 1 marks)\n", "password = \"\" # Your answer (6 - 1 marks)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "7uXTn5D5pcm_" }, "outputs": [], "source": [ "# Test your answer\n", "login(username, password)" ] }, { "cell_type": "markdown", "metadata": { "id": "SyeAS-lVpcm_" }, "source": [ "### Question 4.\n", "Suppose the attacker is not a user of this database and does not know the password of any users in the system. Please fill in the username and password variables to login without using passwords of any existing users in the setup script." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "unK1w04hpcm_" }, "outputs": [], "source": [ "username = \"\" # Your answer (7 - 4 marks)\n", "password = \"\" # Your answer (8 - 4 marks)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "rrf5jIlKpcm_" }, "outputs": [], "source": [ "# Test your answer\n", "login(username, password)" ] }, { "cell_type": "markdown", "metadata": { "id": "n3FCvDDKpcm_" }, "source": [ "### Question 5.\n", "Suppose the attacker wants to add their username and password to the database by leveraging the vulnerability in the login function. Please provide values for the username and password variables such that the attacker's credentials: (username) \"attacker\" and (password) \"password123\" are inserted into the users table. You can use the following login call to perform the injection and the second login call to see if the injection succeeds. Hint: the first login attempt might fail." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "ig8pxSN3pcm_" }, "outputs": [], "source": [ "username = \"\" # Your answer (9 - 5 marks)\n", "password = \"\" # Your answer (10 - 5 marks)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "AM_yAXY2pcm_" }, "outputs": [], "source": [ "# Perform the injection\n", "login(username, password)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "bxewEK_Wpcm_" }, "outputs": [], "source": [ "# Test your anwser\n", "login(\"attacker\", \"password123\")" ] }, { "cell_type": "markdown", "metadata": { "id": "SHUYPVO2pcm_" }, "source": [ "### Question 6.\n", "The application developer is trying to limit the number of SQL commands used in the login function as a potential defense. The new login function is given below. Please fill in the username and password variables to bypass this defense (Suppose the attacker does not know password of any existing user). Hint: think about how to start a comment in SQL." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "CxUpxMhRpcnA" }, "outputs": [], "source": [ "def login_defense_attempt1(username, password):\n", " select_res = []\n", " try:\n", " print(\"SELECT username FROM users WHERE username = '\" + username + \"' AND password = '\" + password +\"'\");\n", " for row in cur.execute(\"SELECT username FROM users WHERE username = '\" + username + \"' AND password = '\" + password +\"'\"):\n", " select_res.append(row)\n", " except:\n", " print(\"Failed to execute the sql script!\")\n", " if len(select_res) > 0:\n", " print(\"Login Successfully!\")\n", " print(\"user: \" + username)\n", " else:\n", " print(\"Login Failed!\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "ZRZ2EA5EpcnA" }, "outputs": [], "source": [ "username = \"\" # Your answer (11 - 5 marks)\n", "password = \"\" # Your answer (12 - 5 marks)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "YkkarJAwpcnA" }, "outputs": [], "source": [ "# Test your answer\n", "login_defense_attempt1(username, password)" ] }, { "cell_type": "markdown", "metadata": { "id": "yd-ThRfqpcnA" }, "source": [ "### Question 7.\n", "One way to defend against the attack in the question 6 is to sanitize the query string. For example, we may escape any potential input that could be used in an attack. Escaping a character means will treat characters as part of the string, not actual SQL syntax. Please implement a sanitizer in the following function to escape any potential symbol in the query string. You can test it using your answer in question 6." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "OlzZ_odrpcnA" }, "outputs": [], "source": [ "def login_defense_attempt2(username, password):\n", " query = \"SELECT username FROM users WHERE username = '\" + username + \"' AND password = '\" + password +\"'\";\n", " # -----------------------\n", " # Your answer here (13 - 30 marks)\n", "\n", "\n", " # ------------------------\n", " select_res = []\n", " try:\n", " print(query);\n", " for row in cur.execute(\"SELECT username FROM users WHERE username = '\" + username + \"' AND password = '\" + password +\"'\"):\n", " select_res.append(row)\n", " except:\n", " print(\"Failed to execute the sql script!\")\n", " if len(select_res) > 0:\n", " print(\"Login Successfully!\")\n", " print(\"user: \" + username)\n", " else:\n", " print(\"Login Failed!\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "NaliJt_WpcnA" }, "outputs": [], "source": [ "username = \"\" # Your answer in question 6\n", "password = \"\" # Your answer in question 6" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "dLa2e9X3pcnA" }, "outputs": [], "source": [ "# Test your answer; it is supposed to fail\n", "login_defense_attempt2(username, password)" ] }, { "cell_type": "markdown", "metadata": { "id": "3gCVLOvApcnA" }, "source": [ "## Finish\n", "Run the following block of code to stop the database once you are finished." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "fHDJx89opcnA" }, "outputs": [], "source": [ "cur.execute(\"DROP TABLE transcripts\")\n", "cur.execute(\"DROP TABLE users\")\n", "con.commit()\n", "con.close()" ] } ], "metadata": { "interpreter": { "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49" }, "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.11.8" }, "colab": { "provenance": [] } }, "nbformat": 4, "nbformat_minor": 0 }

Domenick Behnke following