Documentation for the poptasticDB database access functions.

The data source platforms supported by this release are as follows:
MySQL
Microsoft SQL Server
ODBC
SQLite

The ODBC version of the library has been tested with MySQL, MS SQL Server, MS JET(Access) and SQLite ODBC data sources.
Future ports of the library are planned for PostgreSQL and Oracle.

The library is distributed as a series of PHP files, each one containing the functions for one type of data source. These files are named in the format poptasticdb_datasource.php where datasource is the type of data source. For example, poptasticdb_mysql.php.
To use the functions, simply include the relevant file for your data source at the top of your PHP script. It is suggested that you rename the poptasticdb_datasourceX.php to poptasticdb.php and include that file. In this way if you migrate to another supported data source you need simply to overwrite poptasticdb.php with the relevant version, rather than editing the include statements in all your PHP files.

include ("poptasticdb.php");

You will also need the connection details of your of the data source. Just below the copyright and licence text at the top of the file are a series of data source specific variable definitions. The example below shows these taken from the MySQL version of the library. In this case you need a hostname, username, password and database name.

# MySQL connection settings
$tt_host="My_MySQL_server's_hostname_or_IP_address";
$tt_user="My_user_name";
$tt_password="My_password";
$tt_database="My_database";

Edit these variable definitions to the values appropriate for your database and save the file. The variable definitions for a MySQL server would now look something like this ficticious example:

# MySQL connection settings
$tt_host="192.168.1.42";
$tt_user="ttuser";
$tt_password="w23Hsx9A";
$tt_database="websitedata";

Sometimes different SQL dialects force an application to have different sets of SQL to do the same job on different servers. For example on a MySQL server to SELECT the first 5 records from a table you would run "SELECT a FROM b LIMIT 0,5" wheras on a Microsoft SQL server you would run "SELECT TOP 5 a FROM b". For this reason below the data source detail definitions there is the option to include an SQL dialect file called sql_dialect_datasource.php where datasource is your data source type. This file would typicaly return SQL strings as variables, or if it is required to return SQL based upon some piece of code it could contain functions that return SQL strings.

As has been mentioned above, this library was originally developed as part of one product. As a result is inherits some of the features of that product. In particular, the variables used all follow the convention that their names start with "tt_".
In addition, the functions all make reference to a global variable $tt_lang. The TT Web Site Manager was designed to be localised into more than one language. This was achieved by putting all text in a variable callled $tt_lang, defined in an include file. Thus all these functions can access a global variable called $tt_lang.

Once you have completed the steps above you should be able to use the functions from the library in your script. There now follows a description of each of the functions.

Escape a string for use in SQL.

string dequote(string SQL)

Example: $stuff_for_SQL = dequote($stuff_from_form);

This function tries to ensure that a strng is free of any characters which when included in a piece of SQL could cause that SQL to fall over. In this early version of the library the effectiveness of this function will vary between the different data source types so at the moment it does not guarantee to catch all the possible combinations of characters and data sources that could cause problems.

Establish a connnection with the data source.

resource connect_to_db(string SQL)

Example: $tt_link = connect_to_db();

This function establishes a connection with the data source and returns a link identifier. It should be called before calling any of the other functions.

Close a connnection with the data source.

close_db_connection(resource link_identifier)

Example: close_db_connection($tt_link);

This function closes an existing connection with the data source specified.

Execute a piece of PHP code for each member of a recordset returned by an SQL command.

array do_sql_wrapper (string SQL_code,string PHP_code)

Example:

<?
include ("poptasticdb.php");
$tt_link = connect_to_db();

$php = <<<EOD
echo $tt_row["name"] . "<br/>\n";
$tt_custom_data = "This is a piece of custom data generated by the PHP";
EOD;

$sql = 'select name from people';

$last_result = do_sql_wrapper ($sql,$php);
echo 'This query returned ' . $last_result["tt_query_num_rows"] . ' rows. \n';
echo $last_result["tt_custom_data"];
close_db_connection($tt_link);
?>

This function executes a piece of PHP code for each member of a recordset returned by an SQL command. The current line of the recordset is made available to the PHP code as an array called $tt_row, whose indeces are the names of the columns returned. The PHP code can place data in the array returned by the function by putting it in a variable called $tt_custom_data. This is indexed in the returned array as ["tt_custom_data"].
The function returns an array containing the number of rows in the recordset indexed as ["tt_query_num_rows"], the custom $tt_custom_data variable indexed as ["tt_custom_data"] and the last line of the recordset indexed by the names of the columns returned.

Execute a single piece of SQL

array do_single_sql (string SQL_code)

Example:

<?
include ("poptasticdb.php");
$tt_link = connect_to_db();

$sql = 'select name from people';

$last_result = do_single_sql ($sql);
echo $last_result["name"];
echo 'This query returned ' . $last_result["tt_query_num_rows"] . ' rows. \n';

close_db_connection($tt_link);
?>

This function executes a piece of SQL code. It returns an array containing the number of rows in the recordset indexed as ["tt_query_num_rows"] and the last line of the recordset indexed by the names of the columns returned.

Creates an HTML table containing the results of an SQL query

void do_sql_return_table (string SQL_code)

Example:

<?
include ("poptasticdb.php");
$tt_link = connect_to_db();

$sql = 'select name from people';

do_sql_return_table ($sql);

close_db_connection($tt_link);
?>

This function executes a piece of SQL code and outputs the resulting recordset as an HTML table to stdout. If no recordset is returned the HTML table contains the last message from the data source.

This library has been designed as far as possible for the differences between the different versions for different data sources to be invisible to the application calling it. The following sections deal mostly with the different setup requirements for the different versions.

The poptasticdb_mysql.php file has 4 variable definitions at the top of it for the database connection details, shown below. Edit them to the values that fit your server and save the file.

# MySQL connection settings
$tt_host="My_MySQL_server's_hostname_or_IP_address";
$tt_user="My_user_name";
$tt_password="My_password";
$tt_database="My_database";

The dequote function in poptasticdb_mysql.php is just a wrapper for PHP's mysql_escape_string() function.

The poptasticdb_mssql.php file has 4 variable definitions at the top of it for the database connection details, shown below. Just as you would for the MySQL version, edit them to the values that fit your server and save the file.

# MS SQL server connection settings
$tt_servername="The_name_of_your_MS_SQL_server";
$tt_serverusername="your_MS_SQL_server_username";
$tt_serverpassword="your_MS_SQL_server_password";
$tt_database="your_database_name";

There is no equivalent to mysql_escape_string() in PHP's MS SQL extension so the dequote function in poptasticdb_mssql.php tries to do some of the job by hand. The emphasis is not on catching and escaping all illegal character sequences but on making attacks on SQL queries through variables fed to scripts by HTTP GET or POST requests from browsers as difficult as possible. For an MS SQL Server, the most basic steps are to stop the ' string termination character and the -- SQL comment from being used. This should catch an attack such as " ' or 1=1 -- " and stop it.

There is only one configuration variable at the top of poptasticdb_sqlite.php, the path and filename of the SQLite database file relative to the php file. This can be anywhere within the file tree that the user under which PHP runs on your machine has read and write access to but great care has to be taken if it is in a web visible path because it can then be downloaded just like any other file, compromising the security of your database. If you cant put this file somewhere outside the web tree, at least put it in a directory protected by a .htaccess password to stop casual visitors taking it away.

# SQLite database relative filename.
# !!!WARNING!!! 
# If this file is in a web visible directory anyone could download it!
$tt_database="/relative/path/to/sqlite/filename";

The poptasticdb_odbc.php file has 3 variable definitions at the top of it for the data source details, shown below. Just as you would for the other versions, edit them to the values that fit your data source and save the file.

# ODBC connection settings
$tt_DSN="your_dsn_name";
$tt_user="your_user_name";
$tt_password="your_password";

In addition there is a further set of configuration lines in this file to select the yype of the ODBC data source. This selects which version of the dequote function and which dialect file are used. Simply uncomment the line that conforms to your data source.

# Select a data source type. Uncomment the line that conforms to your data source.
$tt_dsn_type = "transact-SQL"; #Microsoft data sources
#$tt_dsn_type = "SQLite"; #SQLite data sources.
#$tt_dsn_type = "MySQL"; #MySQL data sources.

This piece of software was written by John W. List with support from Carl Pickering.