Nested Looping SQL Querys

[Fuzzydave]

I am back developing futher our Python/CGI based web application run by
a Postgres DB
and as per usual I am having some issues. It Involves a lot of Legacy
code. All the actual
SQL Querys are stored in the .py files and run in the .cgi files. I
have the problem that I
need to construct a row from two seprate SQL Querys, I have tried
combining the two
Querys but all that does is create a Query that returns nothing after a
long period running.

the first query results are delimited with [] and the second with {} I
want my result to
return [ val1 ] [ val2 ] [ val3 ] [ val4 ] { valA } { valB }
unfortunatly when i put my second
query in anywhere on the page its crashes and returns a Internal Server
Error.

the functions from the cmi file are below.



def creationSQL(pubID, productCode, description, suppNo1, all):

validQuery=0

if all:
all=int(all[0])
all = cromwell.toString(all)


sql='SELECT S.product_code, S.description, S.suppno1,
P.discount_factor, S.status, S.list_price, S.offer_price, P.page_no,
int8(P.oid), S.stock_qty '
sql=sql+'FROM (medusa.cmi_stockrec AS S INNER JOIN
medusa.cmi_auxstockrec AS A ON S.product_code=A.product_code) LEFT JOIN
medusa.cmi_pricing AS P ON S.product_code=P.product_code AND
P.pub_id='+pubID+' '
sql=sql+'WHERE '


if productCode!='':
sql=sql+cromwell.orSQL('S.product_code', productCode, 'ILIKE \'',
'%\'', 1)+' AND '
print '<div class="main">Product Code: <b>'+productCode+'</b></div>'
validQuery=1
if description!='':
sql=sql+' (upper(S.description) LIKE upper(\'%'+description+'%\'))
AND '
print '<div class="main">Description: <b>'+description+'</b></div>'
validQuery=1
if suppNo1!='':
sql=sql+' (upper(S.suppno1) LIKE upper(\'%'+suppNo1+'%\')) AND '
print '<div class="main">Part No: <b>'+suppNo1+'</b></div>'
validQuery=1
if all!=pubID:
sql=sql+' (P.product_code IS NULL) AND '

sql=sql[:-4]
sql=sql+' ORDER BY S.product_code'

print '<!-- SQL (Publication Creation):\n'
print sql
print '-->'

if validQuery==1:
return sql
else:
return ''

def creationPubSQL(pubID, productCode, description, suppNo1, all,
pubList, pubPageNo):

validQuery=0

if all:
all=int(all[0])
all = cromwell.toString(all)


sql='SELECT Pl.product_code, S.description, S.suppno1,
P.discount_factor, S.status, Pl.list_price, Pl.offer_price, P.page_no,
int8(P.oid), Pl.page_no, S.stock_qty '
sql=sql+'FROM ((medusa.cmi_pricing AS Pl INNER JOIN
medusa.cmi_stockrec AS S ON S.product_code=Pl.product_code) INNER JOIN
medusa.cmi_auxstockrec AS A ON S.product_code=A.product_code) LEFT JOIN
medusa.cmi_pricing AS P ON S.product_code=P.product_code AND
P.pub_id='+pubID+' '
sql=sql+'WHERE Pl.pub_id='+pubList+' AND '

if productCode!='':
sql=sql+cromwell.orSQL('Pl.product_code', productCode, 'ILIKE \'',
'%\'', 1)+' AND '
print '<div class="main">Product Code: <b>'+productCode+'</b></div>'
validQuery=1
if description!='':
sql=sql+' (upper(S.description) LIKE upper(\'%'+description+'%\'))
AND '
print '<div class="main">Description: <b>'+description+'</b></div>'
validQuery=1
if suppNo1!='':
sql=sql+' (upper(S.suppno1) LIKE upper(\'%'+suppNo1+'%\')) AND '
print '<div class="main">Part No: <b>'+suppNo1+'</b></div>'
validQuery=1
if pubPageNo!='':
sql=sql+cromwell.orSQL('Pl.page_no', pubPageNo, '=\'', '\'', 1)+' AND
'
print '<div class="main">Publication Page No:
<b>'+pubPageNo+'</b></div>'
validQuery=1
if all!=pubID:
sql=sql+' (P.product_code IS NULL) AND '

sql=sql[:-4]
sql=sql+' ORDER BY Pl.product_code'

print '<!-- SQL (Publication Creation):\n'
print sql
print '-->'

if validQuery==1:
return sql
else:
return ''


def stockdetailsSQL(productCode):

validQuery=0

sql="SELECT (stkphys - stkalloc) as free_stock, stk_qty_wk, stkalloc,
stkordq, r.fd_deliverydue "
sql=sql+'FROM charisma.sk_stklfl LEFT JOIN
progress.report_firstdelivery as r ON stkl_stockno = r.fd_sordstk '
sql=sql+'WHERE stkl_stockno = \''+productCode+'\' AND stkl_location =
\'081\' ORDER BY stkl_stockno'
validQuery=1

sql=sql[:-4]

print '<!-- SQL (stock details):\n'
print sql
print '-->'


if validQuery==1:
return sql
else:
return ''


The page code for the CGI file that genereates the tables

#!/usr/bin/python

# Creation Screen
# MeDuSa - Marketing Data System

# $Id: creation.cgi 54 2006-02-16 11:32:12Z
(e-mail address removed) $


print 'Content-Type: text/html\n\n'


import sys
sys.stderr = sys.stdout

from pyPgSQL import libpq
import cgi
import string
import os
import cmi
import cromwell

import hermes
conn = hermes.db()


# This will allow us to retrieve submitted form fields.
cgiForm=cgi.FieldStorage()



# Start assigning submitted form fields to variables.
submit=cgiForm.getvalue('submit')
pubID=cgiForm.getvalue('pubID')
pubName=cgiForm.getvalue('pubName','Unknown Publication')

sqlcheck1 = "SELECT pub_type FROM medusa.cmi_publication WHERE pub_id =
'"+pubID+"'"
overseas1 = conn.query(sqlcheck1)
pubType = cmi.fetch_rows(overseas1)

print pubType

# Check to find out which Search button was pressed ('Search' or
'Search ' )
# before assigning submitted form fields to variables.
if submit=='Search ':
productCode=cromwell.unhypCode(cgiForm.getvalue('productCode2', ''))
description=cgiForm.getvalue('description2','')
suppNo1=cgiForm.getvalue('suppNo12', '')
pageNo=cgiForm.getvalue('pageNo2', '')
pubList=cgiForm.getvalue('pubList2', '800')
pubPageNo=cgiForm.getvalue('pubPageNo2', '')

all=cgiForm.getvalue('all2')
if (all==None):
all=[]
elif not (type(all) is type([])):
all=[all]

else:
productCode=cromwell.unhypCode(cgiForm.getvalue('productCode', ''))
description=cgiForm.getvalue('description','')
suppNo1=cgiForm.getvalue('suppNo1', '')
pageNo=cgiForm.getvalue('pageNo', '')
pubList=cgiForm.getvalue('pubList', '800')
pubPageNo=cgiForm.getvalue('pubPageNo', '')

all=cgiForm.getvalue('all')
if (all==None):
all=[]
elif not (type(all) is type([])):
all=[all]



# Return list of checked product codes.
codes=cgiForm.getvalue('codes')
if (codes==None):
codes=[]
elif not (type(codes) is type([])):
codes=[codes]




# Perform a SELECT query to produce publication list.
result = conn.query('SELECT pub_name, pub_status, pub_id, pub_type FROM
cmi_publication WHERE (pub_status < 4) AND (pub_id > 0) ORDER BY
pub_status, pub_type, pub_name')
rows = cmi.fetch_rows(result)

pubs=[(800, 'Charisma')]


# Create a publication list array.
for row in rows:
listPubName=row[0]
listPubID=row[2]

pubs.append((listPubID, listPubName))



# Start printing the HTML page.
print '<html>'
print '<head>'
print '<title>MEDUSA</title>'
print '<LINK REL ="stylesheet" TYPE="text/css"
HREF="/styles/medusa.css" TITLE="Style">'
print '</head>'

print '<body link="#000080" alink="#000080" vlink="#000080"
topmargin=0>'
print '<form method=post>'



# Produce the search form at the top of the page and the publication
title below.
banner=cmi.printCreateHeader(1, 'creation', pubID, ['ProductCode',
'Description', 'PartNo', 'All'], pubName, productCode, description,
suppNo1, pageNo, pubList, all, pubs, pubPageNo)


# If a button other than Search was pressed then perform the related
query.
add_page=cgiForm.getvalue('add_page')
if (submit=='Add') or (add_page):

for row in codes:
list_code=row[0:11]
list_code = "\'" + list_code + "\'"

sql=cmi.addSQL(pubID, list_code, add_page)
if sql:
conn.query(sql)



pubListTmp = int(pubList)


# Perform the SELECT query to produce the page content based on whether
a publication has been selected or not.
if (pubListTmp==800):
sql=cmi.creationSQL(pubID, productCode, description, suppNo1, all)
else:
sql=cmi.creationPubSQL(pubID, productCode, description, suppNo1, all,
pubList, pubPageNo)



print '</p>'

# If a valid SELECT query has been created then display the results.
if sql:

# Execute the SELECT query.
result = conn.query(sql)
rows = cmi.fetch_rows(result)


# If the query has returned any results.
if rows:

# Create Edit form elements.
print '<p>Page No. <input type=text name=add_page size=5> <input
type=submit name=submit value=Add><hr></p>'


# Print key.
cmi.printCreateKey()


# Print structure table.
print '<table class=clear>'
print '<tr>'



# Print left hand column.
print '<td class=clear>'

print '<table cellpadding=3 cellspacing=1>'
print '<tr>'

# Print the table headers.
print '<th>Product<br>Code</th>'
print '<th>S</th>'
print '<th><input type=submit name=submit value=All ></th>'
print '<th>Description</th>'
print '<th>Supp.<br>Part No.</th>'
print '<th>Charisma<br>List</th>'
#print '<th>Charisma<br>Offer</th>'
print '<th>Last Cat<br>Discount</th>'
print '<th>Page<br>No</th>'
if (pubListTmp!=800):
print '<th>Pub<br>Page</th>'
print '<th>Stock Qty<br>Loc 81</th>'

print '</tr>'

matched=0
lastGroup=''


# Loop to print one line for each return from the database.
for row in rows:


# Assign the column values to named variables.
productCode=row[0]
description=row[1]
suppNo1=cromwell.notNone(row[2], '')
discount=row[3]
if discount==None:
discount='0'
else:
discount=cromwell.percentage(discount)
status=cromwell.notNone(row[4], '')
charList=cromwell.price(row[5], pubType)
charOffer=cromwell.price(row[6], pubType)
pageNo=cromwell.toString(row[7])
oid=cromwell.toString(row[8])
if (pubListTmp!=800):
pubPage=cromwell.toString(row[9])
stock=cromwell.toString(row[10])
else:
stock=cromwell.toString(row[9])



# Display a seperator between groups of product codes.
if lastGroup!=productCode[:6]:
if lastGroup!='':
print '<tr height=3><th colspan=8></th></tr>'
lastGroup=productCode[:6]



print '<tr>'

# Print a table row.
print '<td class='+cmi.pageStatusClass(pageNo, status)+'
align=left><a href="#"
onclick=\'javascript:window.open("http://ecatalogue.cromwell-tools.co.uk/details.php?product_code='+productCode+'&location=81","","scrollbars=yes,resizable=Yes,width=650,height=800")\'><b>'+cromwell.hypCode(productCode)+'</b></a></td>'
print '<td class='+cmi.pageStatusClass(pageNo, status)+'
align=left>'+status+'</td>'
print '<td class='+cmi.pageClass(pageNo)+' align=center><input
type=checkbox name=codes value='
print productCode+' '
if (cgiForm.getvalue('submit')=='All') or (productCode in codes):
print ' CHECKED',
print '></td>'
print '<td class='+cmi.pageClass(pageNo)+'
align=left>'+description+'</td>'
print '<td class='+cmi.pageClass(pageNo)+'
align=left>'+suppNo1+'</td>'
print '<td class='+cmi.pageClass(pageNo)+'
align=right>'+charList+'</td>'
#print '<td class='+cmi.pageClass(pageNo)+'
align=right>'+charOffer+'</td>'
print '<td class='+cmi.pageClass(pageNo)+'
align=right>'+discount+'%</td>'
print '<td class='+cmi.pageClass(pageNo)+'
align=right>'+pageNo+'</td>'
if (pubListTmp!=800):
print '<td class='+cmi.pageClass(pageNo)+'
align=right>'+pubPage+'</td>'

###################################################
#
# This is the section that generates the second sql string
#
####################################################


#sqlS=cmi.stockdetailsSQL(productCode)
#print sqlS
#rowsS = cmi.fetch_rows(sqlS)
for rowS in rowsS:
# freestock=cromwell.toString(rowS[0])
# stkqweeks=cromwell.toString(rowS[1])
# allocated=cromwell.toString(rowS[2])
# stkorderq=cromwell.toString(rowS[3])
# orderdate=cromwell.toString(rowS[4])

# print ''+freestock+''
# print ''+stkqweeks+''
# print ''+allocated+''
# print ''+stkorderq+''
# print ''+orderdate+''

#print '<td class='+cmi.pageClass(pageNo)+'
align=right>'+freestock+'</td>'
#print '<td class='+cmi.pageClass(pageNo)+'
align=right>'+stkqweeks+'</td>'
#print '<td class='+cmi.pageClass(pageNo)+'
align=right>'+allocated+'</td>'
#print '<td class='+cmi.pageClass(pageNo)+'
align=right>'+stkorderq+'</td>'
#print '<td class='+cmi.pageClass(pageNo)+'
align=right>'+orderdate+'</td>'

print '</tr>'

matched=matched+1

print '</table>'

print '<p><b>'+`matched`+'</b> items found</p>'


# End left hand column.
print '</td>'


# Print spacer column.
print '<td class=clear width=50>&nbsp</td>'


# Print right hand column.
print '<td class=clear valign=top>'

print '<b>Just added:</b><br>'
for code in codes:
print '&nbsp'*5, code, '<br>'

# End right hand column.
print '</td>'
print '</tr>'



# Close the table.
print '</table>'



# If no results are returned by the query.
else:
print '<p align="center"><font color=red><b>No items
found</b></font></p>'




# If a valid query has not been created (No search details entered).
else:

print '<table width="100%" height="500"
class="clear"><tr><td><h1>Creation Page</h1></td></tr></table>'



# Close HTML tags.
print '</form>'
print '</body>'
print '</html>'

 

[Bruno Desthuilliers]

Fuzzydave wrote:

(snip)
"""
pubID=cgiForm.getvalue('pubID')
pubName=cgiForm.getvalue('pubName','Unknown Publication')

sqlcheck1 = "SELECT pub_type FROM medusa.cmi_publication WHERE pub_id =
'"+pubID+"'"
overseas1 = conn.query(sqlcheck1)
pubType = cmi.fetch_rows(overseas1)
"""

May we have the url where we can see this application in action ? I know
some crackers that would be really pleased to mess with your production
database...

 

[Bruno Desthuilliers]

I am back developing futher our Python/CGI based web application run by
a Postgres DB
and as per usual I am having some issues. It Involves a lot of Legacy
code.

s/Legacy/Norwegian Blue/

 

[Steve Holden]

Fuzzydave wrote:

(snip)
"""
pubID=cgiForm.getvalue('pubID')
pubName=cgiForm.getvalue('pubName','Unknown Publication')

sqlcheck1 = "SELECT pub_type FROM medusa.cmi_publication WHERE pub_id =
'"+pubID+"'"
overseas1 = conn.query(sqlcheck1)
pubType = cmi.fetch_rows(overseas1)
"""

May we have the url where we can see this application in action ? I know
some crackers that would be really pleased to mess with your production
database...

In case Bruno's posting didn't make it obvious, your code is wide open
to SQL injection exploits. Google is your friend.

The correct way to remove such vulnerabilities is to use parameterized
queries, giving the parameters as a tuple second argument to
cursor.execute().

regards
Steve

 

[Dennis Lee Bieber]

sql='SELECT S.product_code, S.description, S.suppno1,
P.discount_factor, S.status, S.list_price, S.offer_price, P.page_no,
int8(P.oid), S.stock_qty '
sql=sql+'FROM (medusa.cmi_stockrec AS S INNER JOIN
medusa.cmi_auxstockrec AS A ON S.product_code=A.product_code) LEFT JOIN
medusa.cmi_pricing AS P ON S.product_code=P.product_code AND
P.pub_id='+pubID+' '

Well, already I see something to shudder at... Figure out how to use
the DB-API parameterized query syntax so the DB-API does the proper
quoting of arguments!

sql = """
select s.product_code, s.description, s.suppno1, p.discount_factor,
s.status, s.list_price, s.offer_price, p.page_no,
in8(p.oid) as poid, s.stock_qty
from medusa.cmi_stockrec as s
inner join medusa.cmi_auxstockrec as a
on s.product_code = p.product_code
left join medusa.cmi_pricing as p
on s.product_code = p.product_code and p.pub_id = %s
"""
params = [PubID]

# note: check on what your adapter uses for paramstyle; I'm used to
MySQL and the %s

where = ""
validQuery = False

if productCode!='':
sql=sql+cromwell.orSQL('S.product_code', productCode, 'ILIKE \'',
'%\'', 1)+' AND '

#I have no idea what the above is supposed to generate, so this is
just a guess

if productCode:
if where:
where = where + "and s.product_code like %s "
else:
where = where + "where s.product_code like %s "
params.append(productCode)
validQuery = True

print '<div class="main">Product Code: <b>'+productCode+'</b></div>'
validQuery=1
if description!='':
sql=sql+' (upper(S.description) LIKE upper(\'%'+description+'%\'))
AND '

if description:
if where:
where = where + "and upper(s.description) like %s "
else:
where = where + "where upper(s.description) like %s "
params.append(description)
validQuery = True

print '<div class="main">Description: <b>'+description+'</b></div>'
validQuery=1
if suppNo1!='':
sql=sql+' (upper(S.suppno1) LIKE upper(\'%'+suppNo1+'%\')) AND '

if suppNo1:
if where:
where = where + "and upper(s.suppno1) like %s "
else:
where = where + "where upper(s.suppno1) like %s "
params.append(suppNo1)
validQuery = True

print '<div class="main">Part No: <b>'+suppNo1+'</b></div>'
validQuery=1
if all!=pubID:
sql=sql+' (P.product_code IS NULL) AND '

if all != pubID:
if where:
where = where + "and p.product_code is null "
else:
where = where + "where p.product_code is null "
validQuery = True

sql=sql[:-4]
sql=sql+' ORDER BY S.product_code'

sql = sql + where + "order by s.product_code"

print '<!-- SQL (Publication Creation):\n'
print sql
print '-->'

if validQuery==1:
return sql
else:
return ''

if validQuery:
return sql, params
else:
return "", None

# Perform the SELECT query to produce the page content based on whether
a publication has been selected or not.
if (pubListTmp==800):
sql=cmi.creationSQL(pubID, productCode, description, suppNo1, all)

sql, params = cmi.creationSQL(...)
#basically ALL of the SQL generation code should be creating
parameterized queries AND returning a list/tuple of the parameters in
the order needed by the statement

# If a valid SELECT query has been created then display the results.
if sql:

# Execute the SELECT query.
result = conn.query(sql)

# not conn.execute() ? That's what all the DB-API compliant adapters
use

result = conn.execute(sql, params)

--
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/

 

[Steve Holden]

Dennis Lee Bieber wrote:
[...]

# not conn.execute() ? That's what all the DB-API compliant adapters
use

result = conn.execute(sql, params)

..execute() is a cursor method, not a connection method. Some DB API
modules do implement it as a connection method, but that makes it
impossible for several cursors to share the same connection (which is
allowed by some modules).

regards
Steve

 

[Dennis Lee Bieber]

.execute() is a cursor method, not a connection method. Some DB API
modules do implement it as a connection method, but that makes it
impossible for several cursors to share the same connection (which is
allowed by some modules).

It struck me that the original wasn't using a cursor just after the
messages posted.
--
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/

 

[Carsten Haese]

It struck me that the original wasn't using a cursor just after the
messages posted.

It doesn't look like the OP is using anything even remotely DB-API
compliant:

"""
import cmi
<snip>
import hermes
conn = hermes.db()
<snip>

pubID=cgiForm.getvalue('pubID')
pubName=cgiForm.getvalue('pubName','Unknown Publication')

sqlcheck1 = "SELECT pub_type FROM medusa.cmi_publication WHERE pub_id =
'"+pubID+"'"
overseas1 = conn.query(sqlcheck1)
pubType = cmi.fetch_rows(overseas1)
"""

hermes is apparently some wrapper that magically can connect to a
well-known database (via the argumentless db()) call. Who knows what
kind of animal the 'conn' object is that comes out of that db() call.
Apparently it's an object with a query() method that returns something
like a cursor that can be passed into cmi.fetch_rows(). At this point I
wonder why the responsibility of fetching rows is in a module separate
from the responsibility of establishing a database connection.

Legacy code, indeed.

-Carsten

 

[Steve Holden]

It doesn't look like the OP is using anything even remotely DB-API
compliant:

"""
import cmi
<snip>
import hermes
conn = hermes.db()
<snip>

pubID=cgiForm.getvalue('pubID')
pubName=cgiForm.getvalue('pubName','Unknown Publication')

sqlcheck1 = "SELECT pub_type FROM medusa.cmi_publication WHERE pub_id =
'"+pubID+"'"
overseas1 = conn.query(sqlcheck1)
pubType = cmi.fetch_rows(overseas1)
"""

hermes is apparently some wrapper that magically can connect to a
well-known database (via the argumentless db()) call. Who knows what
kind of animal the 'conn' object is that comes out of that db() call.
Apparently it's an object with a query() method that returns something
like a cursor that can be passed into cmi.fetch_rows(). At this point I
wonder why the responsibility of fetching rows is in a module separate
from the responsibility of establishing a database connection.

Legacy code, indeed.

Dog's breakfast, more like. I'm not surprised the OP is having trouble
refactoring the database govne the code that accesses it at the moment.

regards
Steve