Parameterized ODBC book query

Last week we did a query that retrieved book information for a selected ISBN. This post shows an improved version of the query - a parameterized query. For a student database the prior query is OK. But for anything public or in production the query should be parameterized to prevent an SQL Injection attack. This is done by placing a ? in the SQL statement where the variable would go. Then in the cursor execute you follow the actual query with the parameters to be interpolated into the SQL statement, effectively replacing the ? placeholders. The script below illustrates this. The results are the same as last week's regular query. We may not always use these parameterized queries in this series of posts but this is how you would/should do it.

# retrieve book information using a parameterized ODBC query

# d d'urso ocdatabases laguna niguel, ca 92677

import pyodbc

import bookstore_connect_odbc as bookstore

def get_book_info(cnxn, isbn):

    """ Show key book properties for given isbn"""

    cursor = cnxn.cursor()

    sql = "select * from books where isbn = ?"

    cursor.execute(sql, isbn)

    info = cursor.fetchone()

    if info == None:

        print("isbn not found")

        return

    print("Isbn: " + info.isbn)

    print("Title: " + info.title)

    print("Author: " + info.author_name)

    print("Retail price: " + format(info.retail_price,'.2f'))

    print("Publication year: "+ str(info.publication_year))

# main program    

print("Welcome to ODBC Database Manipulation")

cnxn = bookstore.connect()

isbn = '0-125-3344-1'

print("Book Summary for isbn: " + isbn)

get_book_info(cnxn, isbn)

cnxn.close()

print("Database connection closed")