By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,704 Members | 1,076 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,704 IT Pros & Developers. It's quick & easy.

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

P: n/a
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)
Apr 11 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Kun <ne*******@gmail.com> writes:
[...]
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

Apr 11 '06 #2

P: n/a
Kun <ne*******@gmail.com> writes:
[...]
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

Apr 12 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.