Skip to content

Commit 38c9e3d

Browse files
jeffsilvermjeffsilverm
committed
4th attempt to sync the repo
1 parent b07aa44 commit 38c9e3d

1 file changed

Lines changed: 247 additions & 0 deletions

File tree

‎week-04/sql_demo.py‎

Lines changed: 247 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,247 @@
1+
#! /usr/bin/env python
2+
#
3+
# An SQL demo. This builds a simple customer database
4+
# However, it will work with either sqlite3 or mysql
5+
# The hierarchy of objects is
6+
# business CONTAINS databases
7+
# databases CONTAINS tables
8+
# tables CONTAINS records
9+
# records CONTAINS fields
10+
11+
import datetime
12+
import sys
13+
import string
14+
15+
def print_users_by_state(state):
16+
cursor.execute("""SELECT first_name, last_name, city, state FROM customer where state="%s";"""%state)
17+
for row in cursor.fetchall():
18+
print row[0],row[1],row[2],row[3]
19+
20+
def print_db():
21+
cursor.execute("""SELECT first_name, last_name, city, state FROM customer;""")
22+
for row in cursor.fetchall():
23+
print row[0],row[1],row[2],row[3]
24+
25+
def get_credentials(db) :
26+
"""This function opens the credentials file, which is under the control of the system
27+
administrator. The software engineer cannot see it"""
28+
credentials_file = 'credentials_file.txt'
29+
try :
30+
f = open(credentials_file,'r')
31+
except IOError, e:
32+
print """Problems opening the credentials file %s - check file protection
33+
and EUID this database is running under"""
34+
sys.exit(1)
35+
credentials = f.readlines()
36+
lineno = 0
37+
for c in credentials :
38+
lineno += 1
39+
fields = c.split(":")
40+
if len(fields) != 4 :
41+
raise ValueError("Line %d of file %s has the wrong number of fields,\
42+
should be 4 actually is %d" % (lineno, credentials_file, len(fields) ))
43+
if fields[0] == db :
44+
fields[3] = string.strip( fields[3] )
45+
f.close()
46+
return fields[1:4]
47+
else :
48+
raise ValueError("The credentials file %s does not contain a host/user/password tuple\
49+
for database %s") % ( credentials, db)
50+
f.close()
51+
return
52+
53+
def populate_database() :
54+
"""This subroutine populates the database. Note that cursor and connection are passed globally"""
55+
today = datetime.date.today()
56+
57+
customers=[\
58+
("Jeff","Silverman","924 20th AVE E","","Seattle","WA","98112", today, "1"),
59+
("Robin","Finch","The Aviary","1100 Nowhere st","Utopia","KS","75024", today, "2"),
60+
("Felix","Felis","1103 SW 23rd st","","Chicago","IL","68123", today, "3"),
61+
("Jay","Inslee","Governors Mansion","Capitol Grounds", "Olympia", "WA", "98501", today, "4" ),
62+
]
63+
64+
try :
65+
for customer in customers:
66+
cursor.execute('''INSERT INTO customer (
67+
first_name, last_name, address_1, address_2, city, state, zipcode, signup_date, customer_number)
68+
VALUES ( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', %s )''' % customer )
69+
finally :
70+
connection.commit()
71+
72+
73+
def update_database( new_city, new_state, zipcode, customer_number ) :
74+
"""This subroutine updates the database. Note that cursor and connection are passed globally"""
75+
76+
77+
try :
78+
cursor.execute('''UPDATE customer SET city="%s", zipcode="%s", state="IL"
79+
WHERE customer_number=%s ''' % ( new_city, zipcode, customer_number ) )
80+
except sql.ProgrammingError,e :
81+
print "The update failed"
82+
raise
83+
else :
84+
print "The update succeeded"
85+
86+
87+
88+
def update_database_better ( new_city, new_state, zipcode, customer_number ) :
89+
"""This subroutine updates the database. Note that cursor and connection are passed globally
90+
This version is better because it sanitizes the input customer_number"""
91+
92+
93+
try :
94+
customer_number = int ( customer_number ) # Guarantees that the customer number will be an integer
95+
cursor.execute('''UPDATE customer SET city="%s", zipcode="%s", state="IL"
96+
WHERE customer_number=%s ''' % ( new_city, zipcode, customer_number ) )
97+
except ValueError, e :
98+
print "Converting the customer number to an integer caused a ValueError exception. %s" % \
99+
customer_number
100+
raise
101+
except sql.ProgrammingError,e :
102+
print "The update failed"
103+
raise
104+
else :
105+
print "The update succeeded"
106+
107+
def update_database_even_better ( new_city, new_state, zipcode, customer_number ) :
108+
"""This subroutine does an even better job of updating the database than update_database_better
109+
because it checks all arguments"""
110+
try :
111+
sql_str_arg_filter ( new_city )
112+
sql_str_arg_filter ( new_state )
113+
sql_zipcode_arg_filter ( zipcode )
114+
except ValueError, e :
115+
print "The check on the args caused a ValueError exception. %s" % \
116+
customer_number
117+
raise
118+
except sql.ProgrammingError,e :
119+
print "The update failed"
120+
raise
121+
else :
122+
print "The update succeeded"
123+
124+
125+
def sql_str_arg_filter( s ) :
126+
"""This subroutine verifies that string s contains only the characters
127+
[A-Za-z0-9]. In particular, the characters ", ', and ; are filtered out, however
128+
any effort to manipulate the database in an illicit way are prevented. If this
129+
subroutine detects an illegal character, then it raises ValueError. The subroutine
130+
always returns None"""
131+
import re
132+
illicit_match_set = """[^A-Za-z0-9]""" # This matches any char except legal ones
133+
r = re.search(illicit_match_set, s, flags=0)
134+
if r != None :
135+
raise ValueError
136+
137+
def sql_zipcode_arg_filter ( z ) :
138+
"""This function validates zipcodes. A zipcode can be a 5 digit number, or a 5 digit
139+
number, a hyphen, and a 4 digit number. If this subroutine detects an illegal character, then
140+
it raises ValueError. The subroutine always returns None"""
141+
142+
# match a 5 digit number with an optional hyphen followed by a 4 digit number
143+
licit_match_set = """([0-9]{5})(-([0-9]{4}))?"""
144+
r = re.search(licit_match_set, z, flags=0 )
145+
if r==None :
146+
raise ValueError
147+
148+
argv1 = str.lower(sys.argv[1])
149+
if argv1 == "sqlite3" :
150+
import sqlite3 as sql
151+
connection = sql.connect('business.db')
152+
elif argv1 == "mysql" :
153+
import MySQLdb as sql
154+
DB = 'business'
155+
(host, user, password ) = get_credentials(DB)
156+
connection = sql.connect(host=host, user=user, passwd=password, db=DB)
157+
else :
158+
print "Usage is \npython %s sqlite3\nor\npython %s mysql\n" % ( sys.argv[0],
159+
sys.argv[0] )
160+
sys.exit(1)
161+
162+
cursor = connection.cursor()
163+
164+
# Since we are starting from scratch, delete the table if it already exists.
165+
cursor.execute("""DROP TABLE IF EXISTS customer""")
166+
167+
cursor.execute("""CREATE TABLE customer (
168+
first_name VARCHAR(15) NOT NULL,
169+
last_name VARCHAR(15) NOT NULL,
170+
address_1 VARCHAR(30) NOT NULL,
171+
address_2 VARCHAR(30),
172+
city VARCHAR(20) NOT NULL,
173+
state CHAR(2) NOT NULL,
174+
zipcode CHAR(5) NOT NULL,
175+
signup_date DATE,
176+
customer_number INT ) """)
177+
178+
populate_database()
179+
180+
print_users_by_state("WA")
181+
182+
new_city="Cairo"
183+
new_state="IL"
184+
zipcode="62914"
185+
customer_number = "3"
186+
187+
update_database(new_city, new_state, zipcode, customer_number)
188+
189+
print_users_by_state("IL")
190+
191+
if len(sys.argv) == 3 and sys.argv[2]=="evil" :
192+
"""Let's do an SQL injection attack"""
193+
new_city="Aurora"
194+
new_state="IL"
195+
zipcode="60503"
196+
customer_number = "3"
197+
evil = " OR 'x'='x'"
198+
try :
199+
update_database ( new_city, new_state, zipcode, customer_number + evil )
200+
except sql.ProgrammingError,e :
201+
print "The SQL injection attack failed"
202+
else :
203+
print "The SQL injection attack succeeded"
204+
205+
print_users_by_state("IL")
206+
new_city="Miami"
207+
new_state="FL"
208+
zipcode="33101"
209+
customer_number = "3"
210+
try :
211+
update_database_better ( new_city, new_state, zipcode, customer_number + evil )
212+
except sql.ProgrammingError,e :
213+
print "The SQL injection attack failed by update_database_better"
214+
except ValueError,e :
215+
print "The SQL injection attack was prevented by update_database_better"
216+
else :
217+
print "The SQL injection attack succeeded by update_database_better"
218+
219+
print_users_by_state("FL")
220+
new_state = "ME"
221+
try :
222+
update_database_better ( new_city, new_state + evil, zipcode, customer_number )
223+
except sql.ProgrammingError,e :
224+
print "The SQL injection attack failed by update_database_better"
225+
except ValueError,e :
226+
print "The SQL injection attack was prevented by update_database_better"
227+
else :
228+
print "The SQL injection attack succeeded by update_database_better"
229+
230+
print_users_by_state("ME")
231+
232+
233+
new_state = "WY"
234+
try :
235+
update_database_even_better ( new_city, new_state + evil, zipcode, customer_number )
236+
except sql.ProgrammingError,e :
237+
print "The SQL injection attack failed by update_database_even_better"
238+
except ValueError,e :
239+
print "The SQL injection attack was prevented by update_database_even_better"
240+
else :
241+
print "The SQL injection attack succeeded by update_database_even_better"
242+
243+
print_db()
244+
245+
246+
connection.close()
247+

0 commit comments

Comments
 (0)