QnNLLu62

1""""
2SQL Injection Example
3This function is the only one you are permitted
4to modify for the lab assignment.
5
6Note: if you aren't familiar with str.format, here
7is a link to the docs:
8https://docs.python.org/3/library/stdtypes.html#str.format
9"""
10
11all_sql_queries = {'AND', 'OR', 'ALTER TABLE', 'AS', 'BETWEEN', 'CREATE DATABASE', 'CREATE TABLE',
12                   'CREATE INDEX', 'CREATE VIEW', 'DELETE', 'GRANT', 'REVOKE', 'COMMIT', 'ROLLBACK',
13                   'SAVEPOINT', 'DROP DATABASE', 'DROP INDEX', 'DROP TABLE', 'EXISTS', 'GROUP BY',
14                   'HAVING', 'IN', 'INSERT INTO', 'INNER JOIN', 'LEFT JOIN', 'RIGHT JOIN', 'FULL JOIN',
15                   'LIKE', 'ORDER BY', 'SELECT *', 'SELECT DISTINCT', 'SELECT INTO', 'SELECT TOP',
16                   'TRUNCATE TABLE', 'UNION', 'UNION ALL', 'UPDATE', 'WHERE'}
17
18
19def create_search_query(account_id: int, search_term: str) -> str:
20    """
21    Creation of SQL query that has injection vulnerability.
22    You should be able to
23        1) explain why this is vulnerable,
24        2) demonstrate how to exploit this vulnerability, and
25        3) modify this code to prevent SQL injection attack
26    :param account_id: int
27    :param search_term: str
28    :return: str (the query)
29    """
30
31    new_search_term = ''
32
33    # Filter out the characters that help inject SQL
34    search_term = search_term.replace('%', '')
35    search_term = search_term.replace('"','')
36    search_term = search_term.replace(';', '')
37
38    string_list = search_term.split(' ')
39
40    index = 0
41
42    # Until you see SQL SYNTAX which is denoted by the list above.
43    # Add the words of the string to our new filtered search term
44    while string_list[index] not in all_sql_queries:
45        new_search_term = new_search_term + string_list[index] + ' '
46        index = index + 1
47
48        if index > len(string_list)-1:
49            break
50
51    new_search_term = new_search_term[:-1]
52
53    # Never do this in the real world...
54    q = 'SELECT * FROM trnsaction ' \
55        'WHERE trnsaction.account_id = {} ' \
56        'AND ' \
57        'trnsaction.memo LIKE "%{}%"'.format(account_id, new_search_term)
58    return q
59