Getting to grips with ADODB

Getting to grips with ADODB

concrete5 implements ADODB for all its database operations. This article will get you started.


Article by Ollie / / Comments / Difficulty 
Getting to grips with ADODB

concrete5 implements ADODB for all its database operations, and if you're developing a block or single page application the chances are you'll end up writing code which uses ADODB to get and set data.

ADODB is a database abstraction library. It makes working with databases much simpler and more semantic. There are other benefits too, such as being able to connect to different database types with no changes to your application syntax, and automatic sanitising of query input via prepared statements. More on this later.

First, lets query some data.

To load our ADODB library and get a database connection we typically need to do the following:

$db = loader::db();

We then need an sql statement to execute against a database table or tables. So lets assume we have a 'users' table (not the concrete5 users table because we wouldn't query that directly with SQL, we'd use the 'user' and 'userinfo' objects, wouldn't we), our SQL might look like this:

$sql = "select * from users;";

We then need to execute this SQL statement. One common mistake made by new users is over-reliance on ADODB's built in $db->execute() method.  For example, you can obtain and process a recordset using the following perfectly valid operations (though a crude example):

$rS = $db->execute($sql);
while (!$rS->EOF) {
  echo $rS->fields['userName']
  $rS->MoveNext();
}

However, ADODB offers a number of additional methods which can further abstract the process of dealing with recordsets. In this scenario you might instead use $db->getAll() which will return a 2 dimensional array which can be iterated through with a simple foreach loop, with the recordset being discarded, as per this example:

$results = $db->getAll($sql);
foreach ($results as $result) {
  echo result['userName'];
}

There are other methods which are worth checking such as $db->getRow(), and $db->getOne() both of which are useful in some scenarios. To summarise you'll most often use $db->execute() for insert, update and delete operations.

Another, advantage of using concrete5's ADODB implementation is, as previously mentioned, you can rely on automatic sanitising of query input, via prepared statements. This is important to prevent both typical sql injection type attacks, but also for ensuring only valid and safe input actually gets stored away in your database.

There are other advantages, which will become obvious, so let's tale a look. You might construct your SQL query like this:

$userName = myPreferredMethodForMakingInputSafe($userName);
$userID = myPreferredMethodForMakingInputSafe($userID);
$sql = "update users set userName = '" . $userName .
          "' where userID = " . $userID . ";";
$result = $db->execute($sql);

Now, we should sanitise the variables $userName and $userID - see the dummy methods? There are a number of approaches for sanitising variable input - that I'm not going to cover here - suffice to say it's something that needs doing if we construct our SQL this way.

On top of the sanitisation issue, the line of PHP which sets the $sql variable above is pretty fiddly to construct, even in this very simple update query, so, as it gets more complex, there's a reasonably good chance you'll miss a single quote or make another mistake that causes it to bomb. Yes?

So what can ADODB do for us here? Consider the same query, but rewritten to take advantage of ADODB's prepared statement functionality:

$sql = "update users set userName = ? where userID = ?";
$result = $db->execute($sql, array($userName, $userID));

So, to summarise, we use '?' variable placeholder tokens in our SQL statement and pass our variables as an array into the $db->execute() method as an additional argument. It's less code, we don't need any separate sanitisation routines - ADODB does it for us - and our SQL query is so much easier to construct and modify. Much better.

If you found this useful, or have other handy concrete5 ADODB tips to contribute why not do it in the comments.

Thanks for reading.

Join the conversation

comments powered by Disqus