Servlet NewJavaServlet Test

CHAPTER 12 
■
 DATABASES AND JYTHON: OBJECT RELATIONAL MAPPING AND USING JDBC 
 
242 
 
string-based query. Listing 12-14 shows how to use string concatenation for dynamically forming a 
query and also substituting variables. 
Listing 12-14. String Concatenation for Dynamic Query Formation 
# Assume that the user selected a pull-down menu choice determining 
# what results to retrieve from the database, either continent or country name. 
# The selected choice is stored in the selectedChoice variable.  Let's also assume 
# that we are interested in all continents or countries beginning with the letter "A" 
 
>>> qry = "select " + selectedChoice + " from country where " + selectedChoice + " like 
'A%'" 
>>> cursor.execute(qry) 
>>> while cursor.next(): 
...     print cursor.fetchone() 
... 
(u'Albania',) 
(u'American Samoa',) 
... 
This technique works very well for creating dynamic queries, but it also has its share of issues. For 
instance, reading through concatenated strings of code can become troublesome on the eyes. 
Maintaining such code is a tedious task. Above that, string concatenation is not the safest way to 
construct a query as it opens an application up for a SQL injection attack. SQL injection is a technique 
that is used to pass undesirable SQL code into an application in such a way that it alters a query to 
perform unwanted tasks. If the user has the ability to type free text into a textfield and have that text 
passed into a string concatenated query, it is best to perform some other means of filtering to ensure 
certain keywords or commenting symbols are not contained in the value. A better way of getting around 
these issues is to make use of prepared statements. 
■
 Note Ideally, never construct a query statement directly from user data. SQL injection attacks employ such 
construction as their attack vector. Even when not malicious, user data will often contain characters, such as 
quotation marks, that can cause the query to fail if not properly escaped. In all cases, it’s important to scrub and 
then escape the user data before it’s used in the query. 
One other consideration is that such queries will generally consume more resources unless the database 
statement cache is able to match it (if at all). 
But there are two important exceptions to our recommendation: 
SQL statement requirements: Bind variables cannot be used everywhere. However, specifics will depend 
on the database. 
Ad hoc or unrepresentative queries: In databases like Oracle, the statement cache will cache the execution 
plan, without taking in account lopsided distributions of values that are indexed, but are known to the 
database if presented literally. In those cases, a more efficient execution plan will result if the value is put in 
the statement directly. 
www.it-ebooks.info

---

CHAPTER 12 
■ 
DATABASES AND JYTHON: OBJECT RELATIONAL MAPPING AND USING JDBC 
 
243 
 
However, even in these exceptional cases, it's imperative that any user data is fully scrubbed. A good solution is to 
use some sort of mapping table, either an internal dictionary or a mapping table driven from the database itself. In 
certain cases, a carefully constructed regular expression may also work. Be careful. 
Prepared Statements 
To get around using the string concatenation technique for substituting variables, we can use a 
technique known as prepared statements. Prepared statements allow one to use bind variables for data 
substitution, and they are generally safer to use because most security considerations are taken care of 
without developer interaction. However, it is always a good idea to filter input to help reduce the risk. 
Prepared statements in zxJDBC work the same as they do in JDBC, just a simpler syntax. In Listing 12-15, 
we will perform a query on the country table using a prepared statement. Note that the question marks 
are used as place holders for the substituted variables. It is also important to note that the executemany() 
method is invoked when using a prepared statement. Any substitution variables being passed into the 
prepared statement must be in the form of a tuple or list. 
Listing 12-15. Using Prepared Statements 
# Passing a string value into the query 
qry = "select continent from country where name = ?" 
>>> cursor.executemany(qry,['Austria']) 
>>> cursor.fetchall() 
[(u'Europe',)] 
 
# Passing some variables into the query 
>>> continent1 = 'Asia' 
>>> continent2 = 'Africa' 
>>> qry = "select name from country where continent in (?,?)" 
>>> cursor.executemany(qry, [continent1, continent2]) 
>>> cursor.fetchall() 
[(u'Afghanistan',), (u'Algeria',), (u'Angola',), (u'United Arab Emirates',), (u'Armenia',), 
(u'Azerbaijan',), 
... 
Resource Management 
You should always close connections and cursors. This is not only good practice but absolutely essential 
in a managed container so as to avoid exhausting the corresponding connection pool, which needs the 
connections returned as soon as they are no longer in use. The with statement makes it easy. See Listing 
12-16. 
Listing 12-16. Managing Connections Using With Statements 
from __future__ import with_statement 
from itertools import islice 
from com.ziclix.python.sql import zxJDBC 
 
# externalize 
jdbc_url =  "jdbc:oracle:thin:@host:port:sid" 
www.it-ebooks.info

---

CHAPTER 12 
■
 DATABASES AND JYTHON: OBJECT RELATIONAL MAPPING AND USING JDBC 
 
244 
 
username = "world" 
password = "world" 
driver = "oracle.jdbc.driver.OracleDriver" 
 
with zxJDBC.connect(jdbc_url, username, password, driver) as conn: 
    with conn: 
        with conn.cursor() as c: 
            c.execute("select * from emp") 
            for row in islice(c, 20): 
                print row # let's redo this w/ namedtuple momentarily... 
 
The older alternative is available. It’s more verbose, and similar to the Java code that would 
normally have to be written to ensure that the resource is closed. See Listing 12-17. 
Listing 12-17. Managing Connections Avoiding the With Statement 
try: 
    conn = zxJDBC.connect(jdbc_url, username, password, driver) 
    cursor = conn.cursor() 
    #do something with the cursor 
# Be sure to clean up by closing the connection (and cursor) 
finally: 
    if cursor: 
        cursor.close() 
    if conn: 
        conn.close() 
Metadata 
As mentioned previously in this chapter, it is possible to obtain metadata information via the use of 
certain attributes that are available to both connection and cursor objects. zxJDBC matches these 
attributes to the properties that are found in the JDBC java.sql.DatabaseMetaData object. Therefore, 
when one of these attributes is called, the JDBC DatabaseMetaData object is actually obtaining the 
information. 
Listing 12-18 shows how to retrieve metadata about a connection, cursor, or even a specific query. 
Note that whenever obtaining metadata about a cursor, you must fetch the data after setting up the 
attributes. 
Listing 12-18. Retrieving Metadata About a Connection, Cursor or Specific Query 
# Obtain information about the connection using connection attributes 
>>> conn.dbname 
'PostgreSQL' 
>>> conn.dbversion 
'8.4.0' 
>>> conn.drivername 
'PostgreSQL Native Driver' 
# Check for existing cursors 
>>> conn.__cursors__ 
[
] 
 
 
www.it-ebooks.info

---