On 23 Sep 2006 06:04:16 -0700, "(e-mail address removed)" <
[email protected]>
declaimed the following in comp.lang.python:
Answering bottom up...
Could this script be easily converted to Python? How would you execute
the Msql command line and direct the output to a variable for display
in the cgi script? Would it be possible to easily enhance this script
by allowing the user to pass in an SQL query parameter to the sql
script? I attempted this in Perl by substituting the string "p_1" in
the where clause of the sql code but I could not substitute this string
with the value in the cgi code (ie. $query =~ s/p_1/value_variable/
.
Perhaps it would be easier in Python?
Also, would the user supplied parameter be a security issue?
Unless you duplicate the type of checking a db-api module does for
parameterized queries -- indubitably...
I don't do PERL, but from what I see, they are essentially doing the
equivalent of a popen() call.
-=-=-=-=-=-=-=-=- script1.py
import os
USERID = "BestiariaCP" #this account is a read-only, no password
DBNAME = "bestiaria" #used for CherryTemplate page generation
# lacking a CGI interface, I'm just going to put in a few assignments
query = "script1.sql"
title = "This is demonstrably nonsense"
cmdline = "mysql -H -u %s %s < %s" % (USERID, DBNAME, query)
# I don't know what header and start_html() do, so just a dummy here
print """
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>%s</title>
</head>
<body>
""" % title
rpipe = os.popen(cmdline, "r")
results = rpipe.readlines()
rpipe.close()
print '<h3 align="center">%s</h3>' % title
print "".join(results)
print '<br><h4 align="center">This was a sample</h4>'
print "</body>\n</html>"
-=-=-=-=-=-=-=-=-=- script1.sql
select name, URL, description, occurs
from comics
order by name;
-=-=-=-=-=-=-=-=-=- script1.html (excluded middle)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>This is demonstrably nonsense</title>
</head>
<body>
<h3 align="center">This is demonstrably nonsense</h3>
<TABLE
BORDER=1><TR><TH>name</TH><TH>URL</TH><TH>description</TH><TH>occurs</TH></TR><TR><TD>A
Doemain of Our Own</TD><TD>
http://www.doemain.com/</TD><TD>The new
family on the block.</TD><TD>Typically Friday, though some Monday and
Wednesday
updates</TD></TR><TR><TD>Aford</TD><TD>
http://www.afordturtle.com/</TD><TD>A
turtle, a snake, and a robin; and their life in the
forest.</TD><TD>Daily</TD></TR>
<TR><TD>Virtual Comix</TD><TD>
http://comix.keenspace.com/</TD><TD>He's
the insane rabbit next door.</TD><TD>No updates since March of
2003</TD></TR><TR><TD>West Corner of the
Park</TD><TD>
http://www.graphxpress.com/</TD><TD>Inspired by FurryMUCK,
and done by Jim Groat, the creator of <i>Red
Shetland</i>.</TD><TD>Usually late Sunday, unless a Furry Convention
happens</TD></TR><TR><TD>Whatever
USA</TD><TD>
http://whateverusa.keenspace.com/</TD><TD>A precocious
porcupine pup and his pals.</TD><TD>Sporadic</TD></TR><TR><TD>Wild
Angels</TD><TD>
http://www.ottercomics.com/angels/</TD><TD>It'll put the
fur of God in you.</TD><TD>Irregular (between two and seven times each
month)</TD></TR><TR><TD>Wyldfire</TD><TD>
http://www.morgankeithstudios.com/projects_wyldfire.html</TD><TD>What
if the cat stuck up a tree <i>is</i> the fireman?</TD><TD>Archives
available</TD></TR></TABLE>
<br><h4 align="center">This was a sample</h4>
</body>
</html>
Seems like a lot of hassle to go through when a Python function can
generate similar results without losing the safety of parameterized
queries (I do hope the SQL files the CGI is specifying were pre-defined,
and not something the user uploads <G>)
-=-=-=-=-=-=-=- script2.py
import MySQLdb
USERID = "BestiariaCP" #this account is a read-only, no password
DBNAME = "bestiaria" #used for CherryTemplate page generation
# lacking a CGI interface, I'm just going to put in a few assignments
queryfile = "script1.sql"
title = "This is demonstrably nonsense"
cn = MySQLdb.connect(host="localhost", user=USERID, db=DBNAME)
cr = cn.cursor()
# I don't know what header and start_html() do, so just a dummy here
print """
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>%s</title>
</head>
<body>
""" % title
qin = open(queryfile, "r")
query = " ".join(qin.readlines())
qin.close()
cr.execute(query) #no parameters assumed
# THE FOLLOWING BLOCK OF CODE COULD EASILY BE MADE A
# FUNCTION IN SOME HTML REPORTING MODULE AS IT IS NOT
# DEPENDENT UPON THE ACTUAL QUERY TO KNOW WHAT TO PRODUCE
print '<h3 align="center">%s</h3>' % title
print '<table border="1">\n<tr>',
for fld in cr.description:
print "<th>%s</th>" % fld[0],
print "</tr>"
for rec in cr:
print "<tr>",
for fld in rec:
print "<td>%s</td>" % fld,
print "</tr>"
print "</table>"
# BACK TO ORIGINAL CONTENT
print '<br><h4 align="center">This was a sample</h4>'
print "</body>\n</html>"
cr.close()
cn.close()
-=-=-=-=-=- USES SAME SQL FILE
-=-=-=-=-=-=- script2.html (excluded middle)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>This is demonstrably nonsense</title>
</head>
<body>
<h3 align="center">This is demonstrably nonsense</h3>
<table border="1">
<tr> <th>name</th> <th>URL</th> <th>description</th> <th>occurs</th>
</tr>
<tr> <td>A Doemain of Our Own</td> <td>
http://www.doemain.com/</td>
<td>The new family on the block.</td> <td>Typically Friday, though some
Monday and Wednesday updates</td> </tr>
<tr> <td>Aford</td> <td>
http://www.afordturtle.com/</td> <td>A turtle, a
snake, and a robin; and their life in the forest.</td> <td>Daily</td>
</tr>
<tr> <td>Virtual Comix</td> <td>
http://comix.keenspace.com/</td>
<td>He's the insane rabbit next door.</td> <td>No updates since March of
2003</td> </tr>
<tr> <td>West Corner of the Park</td>
<td>
http://www.graphxpress.com/</td> <td>Inspired by FurryMUCK, and done
by Jim Groat, the creator of <i>Red Shetland</i>.</td> <td>Usually late
Sunday, unless a Furry Convention happens</td> </tr>
<tr> <td>Whatever USA</td> <td>
http://whateverusa.keenspace.com/</td>
<td>A precocious porcupine pup and his pals.</td> <td>Sporadic</td>
</tr>
<tr> <td>Wild Angels</td> <td>
http://www.ottercomics.com/angels/</td>
<td>It'll put the fur of God in you.</td> <td>Irregular (between two and
seven times each month)</td> </tr>
<tr> <td>Wyldfire</td>
<td>
http://www.morgankeithstudios.com/projects_wyldfire.html</td>
<td>What if the cat stuck up a tree <i>is</i> the fireman?</td>
<td>Archives available</td> </tr>
</table>
<br><h4 align="center">This was a sample</h4>
</body>
</html>
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/
