modifying html input date for mysql, reg ex or string interpolation?

[Kun]

I have an html form that takes dates and inserts them into a mysql file.
Currently, users have to type in dates in the yyyy-mm-dd format. As
of now, this process works with the sql. However, I would like to make
this process easier by:

1) providing drop down menus for year, month, and date respectively.


in a failed attempt, i tried made 3 drop down lists (dateyear,
datemonth, dateday) respectively and then used string interpolation to
tie them together into a yyyy-mm-dd string called 'date'. I then tried
to use this 'date' string in my original sql query and it no longer worked.

Is this because the new 'date' value is a string instead of an int? How
can I go about solving this problem and making the new 'date' string
work with my old sql query?


Attached is my code:


#!/usr/bin/env python

import cgi
print "Content-type: text/html"
print

form = cgi.FieldStorage()
print form.keys()


#gets value for each input
price = form["price"]
price = price.value
purchasetype = form["purchasetype"]
purchasetype = purchasetype.value
date = form["date"]
date = date.value
comment = form["comment"]
comment = comment.value


dateyear = form["dateyear"]
dateyear = dateyear.value
datemonth = form["datemonth"]
datemonth = datemonth.value
dateday = form["dateday"]
dateday = dateday.value


#string interpolation for date
date = "%d-%d-%d" % (dateyear, datemonth, dateday)
print "<br>"



for x in form.keys():
print "%s=%s" % (x, form[x].value) + "<br>"


# make connection to MySQL
import MySQLdb
import re
import urllib
import sys

try:
connection = MySQLdb.connect(host="localhost", user="xxxx",
passwd="xxxx", db ="xxxx")

except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
sys.exit (1)

#take data and put it into table that you have created.
cursor = connection.cursor()
mysqlstatement = ""
mysqlstatement = "INSERT INTO dir (date, purchasetype, price, comment)
VALUES ('"+ date +"','"+ purchasetype +"','"+ price +"','"+ comment +"' )"
print mysqlstatement
cursor.execute (mysqlstatement)

 

[John J. Lee]

mysqlstatement = "INSERT INTO dir (date, purchasetype, price, comment)
VALUES ('"+ date +"','"+ purchasetype +"','"+ price +"','"+ comment +"' )"

[...]

Haven't read your post carefully, but the first thing that jumps out
at me is that you should be using SQL parameter interpolation, not
Python string formatting.

sql = ("INSERT INTO dir (date, purchasetype, price, comment) "
"VALUES (%s, %s, %s, %s)")
cursor.execute(sql, (date, purchasetype, price, comment))


Google for "SQL injection" to see why this is a nasty security issue,
not just a matter of practical coding convenience.


John

 

[John J. Lee]

for x in form.keys():
print "%s=%s" % (x, form[x].value) + "<br>"

[...]

....and the second thing that jumps out at me is that you're neglecting
to HTML-quote the data you're inserting into your web page here (for
example, using xml.sax.saxutils.escape()), which is another common
cause of security holes, very similar to those with failing to use
proper SQL parameter quoting.

Google for "cross-site scripting".


John