In the previous blog we saw how to

1. Create and delete tables when the modules are installed or uninstalled

2. Updating a table once the module is in use. This allows for applying patches easily

In this blog we will see two other aspects of the Drupal database layer

1. Writing secure SQL to avoid SQL injection

2. Reading from a database and iterating over results

1. Writing secure SQL:

What is SQL injection attack? 

It is a way to modify the request URL in a way to force the server to execute unintended SQL query. If the application is not performing any sanitization and constructs SQL statements on the fly, SQL injections can be easy to manufacture. For example if you type a query:

"SELECT * from node where type = '$type'";

And $type is a parameter that coming from the URL:

http:yourSite.com/q=story (for Story)

Now all a user has to do is modify the URL and type 

http:yourSite.com/q=story' OR type = 'page'

This will return back all the nodes of type story and page. Think of all the conditions a user can enter in the where clause and retrieve different information from your site.

A simple fix for this is to use placeholders in the query and escaping the inputing. So instead of typing the query as:

$result = db_query( "SELECT * from node where type = '$type'");

you would type it as 

$sql = "SELECT * from node where type = '%s';

$result = db_query($sql, $type);

%s converts the $type to a string so if the user modified the URL as shown above to story' OR type = 'page' the SQL will be converted to 

"SELECT * from node where type = 'story/' OR type = 'page/'; There are no content types that contain / at the end which is basically escaping the single quote. Thus the query would fail and the attack would be useless.

2. Reading data from database

First look at a database statement returning a single value:

So if you had a query:

$sql = "SELECT molecule_name from {molecule} where id = '%s'";

$result = db_query($sql, id);

then a convenience function db_result will will return the result. So to return a single value the code would be:

return db_result($result);

And that's it.

If however the query returns multiple rows then the following while loop is required:

while ($res = db_fetch_object($result)){

  do something with $res2

}

db_fetch_object will parse and iterate over the $result.

These handy trips can be quite useful. Don't forget to refer to http://api.drupal.org for more details. In the next blog, we will cover reading specific number of records from the database and unique table names in a shared hosting environment.