Prepared.Statement

Function - What a program never does on the first run, seldom does on subsequent runs, and when it finally does, is a good indication that the program is obsolete.

Friday, February 24, 2006

ASP Prepared Statements

Finally, in the last part of this Prepared Statement series I will show you how to eliminate spaghetti SQL from your ASP applications:

Setting up your ASP Command Object:
Set dbCommand = Server.CreateObject("ADODB.Command")
Set dbCommand.ActiveConnection = dbConnection
dbCommand.CommandType = adCmdText
dbCommand.CommandText = "SELECT COUNT(username) FROM login WHERE username=? and pwd=?"

This code fragment basically sets up the ADODB objects in your ASP application. I assume that the connection object is already setup and is set to dbConnection. The Command object needs an active connection to operate upon, the command type is SQL text (adCmdText), and finally the actual SQL statement is placed in CommandText. Note that here I am using nameless parameters by order of question marks.
In the next fragment I will show you how to setup your SQL parameters in ASP:
Setting up Parameters in ASP:
dbCommand.Parameters.Append (dbCommand.CreateParameter("username", adChar, adParamInput, Len(username), username))
dbCommand.Parameters.Append (dbCommand.CreateParameter("pwd", adChar, adParamInput, Len(pwd), pwd))
Set rs = dbCommand.Execute

You can also use named parameters, just take a look at the C# example a couple of posts below for a similar example. Now here is the part that had me in a frenzy when I first learned of this technique many years ago...
Ideally we really want to setup the Command object and only use the parameters iteratively when inserting or updating several rows in a loop. In order to make all of this work, before the next iterator, we have to remove the old parameters. This can be done with calls to dbCommand.Parameters.Delete(0) as many times as you have parameters. This will essentially delete the parameter at the head of the list, until the list is no more. This is one way to tackle this issue.
So there you have it, writing prepared statements in 4 different programming environments. Check the blog back in a few and I will have some more interesting tips to write about...

Sunday, February 19, 2006

PHP Data Access - Prepared Statements

Probably the place where one would see alot of spaghetti SQL is in scripting or web application development. Generally when speaking of web applications you can almost assume all times that some type of database management system is involved, with scripts to access that data.

If you have ever inherited a web application from a 3rd party that was not using OO-style design you would probably have seen alot of problems with the SQL handling on the app. This kind of problem often leads to more problems, such as SQL attacks by injection of text, and others.

But there is a solution to this problem. In PHP and MySQL, which seem to go joined at the hip you can use PEAR, and the PEAR package provides some very nice interfaces for handling SQL OO-style. So here is how its done:
A simple query to authenticate a user against a database:
require_once("DB.php"); // require PEAR-DB

1:$dsn = "mysql://myusername:mypassword@localhost/mydatabase";
2:$db =& DB::connect($dsn, array());
3:if (DB::isError($db)) {
4:  die($db->getMessage());
5:}
6:
7:// setup a parameterized query
8:$sth = $db->prepare("SELECT COUNT(username) FROM login WHERE username=? and pwd=?");
9:$res = $db->execute($sth, array($username, $pwd));
10:
11:// loop through result set
12:$authenticated = false;
13:while(!DB::isError($res) && $row = $res->fetchRow()) {
14:  $authenticated = ($row[0] > 0);
15:  $res->free();
16:  break;
17:}

18:$db->disconnect(); // release database

What does it all mean?
Line 1: Setup the database connection string. Credentials and database to use.
Line 2: Attempt a connection via PEAR, a database is returned otherwise an error object.
Line 3: Check if the variable is a DB error object, if it is, the database connection failed.
Line 4: Die with an error message
Line 8: Setup a "statement" using the $sth variable. The SQL string contains question marks, each indicating where paramerters will be inserted. Note that it is not necessary to enclose the question marks with quotes if your parameters are text, PEAR will automatically sanitize the SQL statement based on the type of variable you use for each parameter
Line 9: Execute the statement. When using parameters in your SQL statement use the database's execute method. When doing simple queries that don't have parameters, skip line 8 and use the "query" method directly, which takes an SQL string as a parameter. The 2nd parameter passed into the execute method is an array of variables, each corresponding in order to parameters in your SQL statement.
Line 13: Loop through all the rows in the resultset. PEAR will create an array containing values for each column on the current row. Depending on how "fetchRow" is used, the array will be indexed numerically (order 0..Number-Of-Columns), or if fetchRow is used with "DB_FETCHMODE_ASSOC" then the array returned will be an associative array mapping column names to row values.

Here is the same example but accessing the data through an associative array:
Using fetchRow and DB_FETCHMODE_ASSOC:
8:$sth = $db->prepare("SELECT COUNT(username) as CNTUID FROM login WHERE username=? and pwd=?");
9:$res = $db->execute($sth, array($username, $pwd));
10:
11:// loop through result set
12:$authenticated = false;
13:while(!DB::isError($res) && $row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
14:  $authenticated = ($row["CNTUID"] > 0);
15:  $res->free();
16:  break;
17:}

And finally, here is an example of using a parameterless query with PEAR:
Using the query method:
$res = $db->query("SELECT COUNT(username) FROM login");

Note that on the above example, no parameters were needed because the query is simple, only returning the total number of rows in the login table. Although this could have been used with the prepare method and without any parameters, the query method provided a shortcut for parameterless queries.

In my opinion, PEAR provides a far superior way of handling MySQL than the C language based mysql_XXX calls.

Questions or comments, please click on the Comments link --->

Saturday, February 18, 2006

C# Data Access - Prepared Statements

In C# prepared statements (or parameterized statements) are built via the Command object. If your database management system is SQL Server, then you will be using the SqlCommand object (instead of your regular Command object that may be used with ODBC and Access).

The Command object allows you to add parameters to a parameter collection. In turn these parameters are matched by name and the underlying functionality handles escaping and any other necessary pre-processing of your SQL statement. Here is an example:
A simple query to authenticate a user against a database:
String sql = "SELECT count(username) FROM login where username=@USERNAME and pwd=@PWD";

The named parameters are @USERNAME and @PWD respectively.
The command object allows you to add SqlParameter objects to the Parameters collection. Here is one way of setting up your parameters:
SqlParameters example:

commandObj.Parameters.Add("@USERNAME", SqlDbType.VarChar, username.Length).Value = username;
commandObj.Parameters.Add("@PWD", SqlDbType.VarChar, pwd.Length).Value = pwd;

The SqlCommand (or Command) object may be used to build parameterized queries and updates. The parameter usage is exactly the same with both types of operations. Because the Parameters member is a collection, when re-using the same SqlCommand object in a looping operation you must empty the collection to avoid having a crash. This removal of old parameters is done with the collection's Clear method:
Clearing Parameters:
commandObj.Parameters.Clear();

Parameterized queries provide a better and cleaner approach to SQL queries in your applications. The SQL statements don't have to be sanitized in order to avoid quotation and other inconsistencies.
Next time: Parameterized queries for PHP using PEAR, then ASP.

Questions or comments --> Click on the comments link.

Friday, February 17, 2006

Java Data Access - Prepared Statements

If you just happen to be looking for, or currently working on any project, in any language, that contains data access code PLEASE do not write spaghetti SQL code. You will be doing the world of developers a favor. Here is what I refer to spaghetti code:

A simple query to authenticate a user against a database:
String sql = "SELECT count(username) FROM login where username='" + username + "' and pwd='" + pwd + "'";

The spaghetti SQL above is a really easy way to compose SQL statements before you execute them on your favorite SQL management system. Unfortunately, if you are not careful, a guy like O'Neal can come along and break your code by inserting a single quote on his username or password. The SQL has not been properly parsed.

The solution involves using parameterized command objects to handle this type of situation cleanly and efficiently:



A simple query to authenticate a user against a database in parameterized form:
PreparedStatement st = db.prepareStatement("SELECT count(username) FROM login WHERE username=? and pwd=?");
st.setString(1, username);
st.setString(2, pwd);

Voila! Problem solved. The Java PreparedStatement class handles escaping of your SQL string based on the type of the parameters. The question mark ordering corresponds to the position of each parameter. For example: 1 => username, 2 => password. Set the parameters, then execute the query.

This solution is also efficient if you have to run several statements in a loop. You can prepare the statement once, and then loop its execution with different parameters:



Inserting products into a database:
PreparedStatement st = db.prepareStatement("INSERT INTO products (name, sku, price) values (?,?,?)");
for(int j = 0; j < productsVector.size(); j++) {
   Product p = (Product)productsVector.get(j);
   st.clearParameters();
   st.setString(1, p.getName());
   st.setString(2, p.getSku());
   st.setDouble(3, p.getDouble());
   st.execute();
}

In this example the code loops through a vector of products, and reuses the prepared statement object several times to perform inserts in the database. Note that I purposedly left out several error checking code in order to keep this post small. Most of the data access calls in this examples raise SQL Exceptions.

A better and cleaner approach to handling SQL in your code.
Next time: Parameterized queries for C#, ASP, and PHP.

Questions or comments --> Click on the comments link.

My Alter Ego

Here is where my alter ego will have his memory dumps. I am a software developer working with education (during the day), and by night and weekends I do a lot of independent development for a variety of people all over the country, as time allows. Since I already have my education blog up and running here I figured I will start writing about my alter ego, the software developer/consultant in me.

It should come as no surprise to you the amount of pain that it comes when taking over a project that is not version 1.0. I don't know about you, but when I step into the role of main developer, lead, tester, and designer for my clients for a codebase that is not version 1.0 I always seem to find horrible problems. It seems that even though the app is running (for the most part), remember that the reason you were hired was to improve upon or fix some problem that prevents your client from conducting business. Sadly most times it is very painful for both parties to realize that the system can no longer be scaled. But why?

It has to do with economies and globalization actually. This is really a broad stroke at the heart of a simple problem: You get what you pay for (most of the times). During 2002-2004 I saw a lot of independent business sent overseas, not necessarily to good companies, but to individuals willing to work for $2 bucks and hour. Then this year I am seeing all of this business coming back to the US with clients griping that their products are delayed, do not work, and cannot be expanded. Reason: you get what you pay for.

So I am taking a memory dump of one of the biggest problems I see in the "independent" industry, with most of my clients being small businesses: the allure of cheap labor. It is one of my gripes in this area, especially when trying to help out a client from a tight spot created by someone inexperienced who calls themselves "developers". In reality, anyone can be a programmer, but it takes a lot more skill to be a developer. The difference is: one can design a complete system while the other can only write code.

Working in the education field has given me a great deal of experience with instructional technologies. The main reason this blog exists is to provide YOU, the reader, with some perls of wisdom that I have picked up along the way. I think that the blend of development and education will go over fairly well. It is my intend to post, and discuss development topics with you, in the hopes to advance your skills from "programmer" to "developer".