Python Tutorials: ADO and Python
Thursday, November 23, 2017
Chapter 3: What is a Parameter and Why Use It?
Parameters are a model that is used by several interfaces such as
DBI, BDE, ODBC etc. ADO also provides for using them. However novice programmers usually don't
know about them and never bother to use them at all. We will investigate several reasons why
they are such a good thing in this chapter.
Consider the following situation. You need to read several lines
from a text file and insert each one into a SQL table. A naive way to do this would be:
1. Open the text file.
A person who has dabbled with SQL before might note a problem with
the above approach. If the line read in step 2 contains a quote character ('), then the SQL
string in step 3 will not be formatted correctly. It will have similar problems if the line
contains other reserved characters such as %, ?, newlines etc. To prevent this, we will need
to rewrite step 3 as follows:
2. Read a line from the text file.
3. Prepare a SQL statement using string concatenation:
sql = "INSERT INTO tablename(column1) VALUES (' " + line + " ')"
4. Execute the SQL statement that we prepared above. The database engine validates our statement
as legal SQL, compiles it and then executes it.
5. Check if there are any more lines in the file. If there are any more lines, repeat from step 2.
3. Prepare a SQL statement using string functions to format the string properly:
where EscapeSpecialChars() is a function you write to escape (or remove) the characters that may cause
problems. Typically, this function should replace single quote (') with two single quotes ('')
and similarly handle %, ? etc. The trouble is that different database engines might have
different ideas of what characters are considered special and how they should be escaped.
sql = "INSERT INTO tablename(column1) VALUES (' " + EscapeSpecialChars(line) + " ')"
An additional problem with this approach is the fact that the SQL statement is being altered
every time a line is read in. The only change to the SQL statement is the value of the
variable "line", while the rest of it stays the same. The novice approach rebuilds the SQL string
from scratch each time, which takes up more time. First, you have the overhead of joining
strings together to form the SQL statement. Second, the database server will validate and
compile the statement each time before executing it. This is a wasteful approach since
the only thing that changes is the value of the line variable and there's no need to revalidate
or recompile the statement after the first time around. Thirdly, there is the possiblity of
SQL Injection, where a malicious person could run their own SQL statements on your server.
A better approach is to use the parameter facility. Modern database servers are capable of
taking a SQL statement with parameters and compiling it ahead of time. You can then pass a
parameter value to it and execute it each time for new parameter values. This saves time
because the database engine does not need to validate/recompile the statement each time.
Instead it has the statement validated and compiled and simply puts the parameter value into
it each time.
When using parameters, the same problem can be solved as follows:
1. Prepare a SQL statement using parameters and pass this to the database engine ahead of time.
The database engine validates the statement as valid SQL, compiles it into bytecode and returns
a handle which we can use to access this statement.
Since the database engine has already validated/compiled our statement once, it doesn't bother
to do it again for each new execution of the statement. When you have a lot of lines, this can
result in increased performance. As an additional bonus, step 4 automatically knows to
escape characters properly, so you don't have to write your own EscapeSpecialChars()
function to do so. This makes SQL injection attacks impossible.
2. Open the text file.
3. Read a line from the text file.
4. Execute the SQL statement via the handle we obtained in step 1 and pass the line as a
parameter value. The database engine already has the statement validated and compiled, so all
it does is substitute the parameter with the value that you pass to it.
5. Check if there are any more lines in the file. If there are any more lines, repeat from step 3.
It is possible to prepare a statement that takes multiple parameter arguments too. This allows
you to pass values for multiple columns in a statement. You can also assign default values to
parameters when creating them. Thus if you don't explicitly set the value of a parameter when
executing the SQL statement, it will use the default value for that parameter. This is very
handy if you need to insert multiple rows where the values of some columns only change
Parameters can also be used to supply values to a stored procedure object. This tutorial will
demonstrate usage of parameters to execute both SQL queries as well as stored procedures.
As you can see, the use of parameters has several advantages that make them essential for
any serious database programmer.
Copyright © 2004 Mayukh Bose. All rights reserved.
This work may be freely reproduced provided this copyright notice is preserved.
OPTIONAL: Please consider linking to this website (http://www.mayukhbose.com/
) as well.