CYBR 7100 Purdue University Global Securing Application Development Worksheet
Instructions This assignment will continue the work in Assignment 1. You will learn how to project your SQL quires. You need to apply all the learn techniques in your program and submit your work by using Assignment 3 Template. Crafting Safe Query Parameters In Assignment 1, you saw how an intruder can exploit your system and gain admin permissions by using a carefully crafted string. The issue was that you allowed the value passed from the client to be executed directly to the database, without performing any sort of check or validation. SQL injections rely on this type of vulnerability.Before we introduce the solutions, we create another non-admin user. Open the SQL shell and repeat Step 3 in Assignment 1 to login database. Step 3.1 use the following command to add user “test”, and check if you successfully added. INSERT INTO users (username, admin) VALUES (‘test’, true);The result should be like follows.Any time user input is used in a database query, there’s a possible vulnerability for SQL injection. The key to preventing Python SQL injection is to make sure the value is being used as the developer intended. In the previous example, you intended for username to be used as a string. In reality, it was used as a raw SQL statement.Step 3.2 Make your is_admin() method safeTo make sure values are used as they’re intended, you need to escape the value. For example, to prevent intruders from injecting raw SQL in the place of a string argument, you can escape quotation marks:>>> # BAD EXAMPLE. DON'T DO THIS! >>> username = username.replace("'", "''") This is just one example. There are a lot of special characters and scenarios to think about when trying to prevent Python SQL injection. Lucky for you, modern database adapters, come with built-in tools for preventing Python SQL injection by using query parameters. These are used instead of plain string interpolation to compose a query with parameters.Note:Different adapters, databases, and programming languages refer to query parameters by different names. Common names include bind variables, replacement variables, and substitution variables.Now that you have a better understanding of the vulnerability, you’re ready to rewrite the function using query parameters instead of string interpolation:def is_admin(username: str) -> bool: with connection.cursor() as cursor: cursor.execute(""" SELECT admin FROM users WHERE username = %(username)s """,{ 'username':username }) result = cursor.fetchone() if result is None: #User does not exist return False else: admin, = result return adminHere’s what’s different in this example: In line 9, you used a named parameter username to indicate where the username should go. Notice how the parameter username is no longer surrounded by single quotation marks.In line 11, you passed the value of username as the second argument to cursor.execute(). The connection will use the type and value of username when executing the query in the database. To test this function, try some valid and invalid values, including the dangerous string from before:>>> >>> is_admin('test') False >>> is_admin("'; select true; --") False Or use the code in IDE:print(is_admin('test'))print(is_admin("'; select true; --"))Amazing! The function returned the expected result for all values. What’s more, the dangerous string no longer works. To understand why, you can inspect the query generated by execute():Now, it is your turn to make your program secure. By following the example, apply the secure coding practice to your program. You need to submit a screen shot of your code and your output.This is my output: Passing Safe Query Parameters Database adapters usually offer several ways to pass query parameters. Named placeholders are usually the best for readability, but some implementations might benefit from using other options.Let’s take a quick look at some of the right and wrong ways to use query parameters. The following code block shows the types of queries you’ll want to avoid:# BAD EXAMPLES. DON'T DO THIS! cursor.execute("SELECT admin FROM users WHERE username = '" + username + '"); cursor.execute("SELECT admin FROM users WHERE username = '%s' % username); cursor.execute("SELECT admin FROM users WHERE username = '{}'".format(username)); cursor.execute(f"SELECT admin FROM users WHERE username = '{username}'"); Each of these statements passes username from the client directly to the database, without performing any sort of check or validation. This sort of code is ripe for inviting Python SQL injection. In contrast, these types of queries should be safe for you to execute:# SAFE EXAMPLES. DO THIS! cursor.execute("SELECT admin FROM users WHERE username = %s'", (username, )); cursor.execute("SELECT admin FROM users WHERE username = %(username)s", {'username': username}); In these statements, username is passed as a named parameter. Now, the database will use the specified type and value of username when executing the query, offering protection from Python SQL injection. Step 3.3 Create a secure count_rows() methodDon’t forget to add from psycopg2 import sql Using SQL Composition So far you’ve used parameters for literals. Literals are values such as numbers, strings, and dates. But what if you have a use case that requires composing a different query—one where the parameter is something else, like a table or column name?Inspired by the previous example, let’s implement a function that accepts the name of a table and returns the number of rows in that table:# BAD EXAMPLE. DON'T DO THIS! def count_rows(table_name: str) -> int: with connection.cursor() as cursor: cursor.execute(""" SELECT count(*) FROM %(table_name)s """, { 'table_name': table_name, }) result = cursor.fetchone() rowcount, = result return rowcount Try to execute the function on your users table:>>> Traceback (most recent call last): File "<stdin>", line 1, in <module> File "<stdin>", line 9, in count_rows psycopg2.errors.SyntaxError: syntax error at or near "'users'" LINE 5: 'users' ^ The command failed to generate the SQL. As you’ve seen already, the database adapter treats the variable as a string or a literal. A table name, however, is not a plain string. This is where SQL composition comes in.You already know it’s not safe to use string interpolation to compose SQL. Luckily, Psycopg provides a module called psycopg.sql to help you safely compose SQL queries. Let’s rewrite the function using psycopg.sql.SQL():from psycopg2 import sql def count_rows(table_name: str) -> int: with connection.cursor() as cursor: stmt = sql.SQL(""" SELECT count(*) FROM {table_name} """).format( table_name = sql.Identifier(table_name), ) cursor.execute(stmt) result = cursor.fetchone() rowcount, = result return rowcount There are two differences in this implementation. First, you used sql.SQL() to compose the query. Then, you used sql.Identifier() to annotate the argument value table_name. (An identifier is a column or table name.)Note:Users of the popular package django-debug-toolbar might get an error in the SQL panel for queries composed with psycopg.sql.SQL(). A fix is expected for release in version 2.0.Now, try executing the function on the users table:>>> >>> count_rows('users') 2 Great! Next, let’s see what happens when the table does not exist:>>> >>> count_rows('foo') Traceback (most recent call last): File "<stdin>", line 1, in <module> File "<stdin>", line 11, in count_rows psycopg2.errors.UndefinedTable: relation "foo" does not exist LINE 5: "foo" ^ The function throws the UndefinedTable exception. In the following steps, you’ll use this exception as an indication that your function is safe from a Python SQL injection attack.Note:The exception UndefinedTablewas added in psycopg2 version 2.8. If you’re working with an earlier version of Psycopg, then you’ll get a different exception.To put it all together, add an option to count rows in the table up to a certain limit. This feature might be useful for very large tables. To implement this, add a LIMIT clause to the query, along with query parameters for the limit’s value:from psycopg2 import sql def count_rows(table_name: str, limit: int) -> int: with connection.cursor() as cursor: stmt = sql.SQL(""" SELECT COUNT(*) FROM ( SELECT 1 FROM {table_name} LIMIT {limit} ) AS limit_query """).format( table_name = sql.Identifier(table_name), limit=sql.Literal(limit), ) cursor.execute(stmt) result = cursor.fetchone() rowcount, = result return rowcount In this code block, you annotated limit using sql.Literal(). As in the previous example, psycopg will bind all query parameters as literals when using the simple approach. However, when using sql.SQL(), you need to explicitly annotate each parameter using either sql.Identifier() or sql.Literal().Note:Unfortunately, the Python API specification does not address the binding of identifiers, only literals. Psycopg is the only popular adapter that added the ability to safely compose SQL with both literals and identifiers. This fact makes it even more important to pay close attention when binding identifiers.Execute the function to make sure that it works:>>> >>> count_rows('users', 1) 1 >>> count_rows('users', 10) 2 Now that you see the function is working, make sure it’s also safe:>>> >>> count_rows("(select 1) as foo; update users set admin = true where name = 'haki'; --", 1) Traceback (most recent call last): File "<stdin>", line 1, in <module> File "<stdin>", line 18, in count_rows psycopg2.errors.UndefinedTable: relation "(select 1) as foo; update users set admin = true where name = '" does not exist LINE 8: "(select 1) as foo; update users set adm... ^ This traceback shows that psycopg escaped the value, and the database treated it as a table name. Since a table with this name doesn’t exist, an UndefinedTable exception was raised and you were not hacked!add the secure count_rows() method to your program and execute from command line>>> count_rows('users', 1)1>>> count_rows('users', 10)2>>> count_rows("(select 1) as foo; update users set admin = true where name = 'haki'; --", 1)Traceback (most recent call last): File "<stdin>", line 1, in <module> File "<stdin>", line 18, in count_rowspsycopg2.errors.UndefinedTable: relation "(select 1) as foo; update users set admin = true where name = '" does not existLINE 8: "(select 1) as foo; update users set adm... ^Or execute in IDE by using print(count_rows('users', 1))print(count_rows('users', 10))print(count_rows("(select 1) as foo; update users set admin = true where name = 'yan'; --", 1))Then the result should looks like: Attachments Assignment 3 Submission Template.docx(15.54 KB)
