ProFTPD module mod_sql_odbc


ODBC means Open Database Connectivity, and defines a standard way of connecting to a database server such as MySQL, Postgres, SQL Server, Oracle, etc. Many of the these databases also provide their own native APIs for connectivity, in addition to supporting ODBC. Other databases provide ODBC support, but no support for native connectivity.

The ProFTPD distribution includes the mod_sql_mysql module for talking to a MySQL database using the native MySQL API, and a mod_sql_postgres for connecting to Postgres databases using the native Postgres API. However, there was no support for databases other than these -- until now. The purpose of the mod_sql_odbc module is to allow ProFTPD's mod_sql module to connect to any database that supports ODBC. This module also allows proftpd to work with databases that may already provide APIs for native connectivity, but for which a specific mod_sql has not yet been developed.

This module is contained in the mod_sql_odbc.c file for ProFTPD 1.3.x, and is not compiled by default. Installation instructions are discussed here.

The most current version of mod_sql_odbc is distributed with ProFTPD.

Author

Please contact TJ Saunders <tj at castaglia.org> with any questions, concerns, or suggestions regarding this module.

Directives

SQLODBCVersion

Syntax: SQLODBCVersion version
Default: SQLODBCVersion ODBCv3
Context: server config, <VirtualHost>, <Global>
Module: mod_sql_odbc
Compatibility: 1.3.6rc2 and later

The SQLODBCVersion directive configures the ODBC API version that the ODBC driver should use/expect. The default is "ODBCv3", i.e. ODBC version 3. Some drivers may have issues with the default version; this can manifest as errors similar to the following in your SQLLogFile: 

  message: '[unixODBC][Driver Manager]Driver does not support the requested version'
When this happens, you might try using an older version of ODBC, via: 
  SQLODBCVersion ODBCv2

Installation

The mod_sql_odbc relies on one of the two following libraries for ODBC support: 
  http://www.iodbc.org
  http://www.unixodbc.org
One of these ODBC libraries must be installed prior to using mod_sql_odbc.

Follow the usual steps for using contrib modules in ProFTPD, making sure to list mod_sql. Note that you will need to use the LIBS environment variable for indicating which ODBC library to link against.

For example, if you wish to use the unixODBC library, you would use: 

  $ ./configure LIBS=-lodbc --with-modules=mod_sql:mod_sql_odbc ...
  $ make
  $ make install
On the other hand, for using the iODBC library, the invocation is slightly different, specifying a different library name: 
  $ ./configure LIBS=-liodbc --with-modules=mod_sql:mod_sql_odbc ...
  $ make
  $ make install

You may need to specify the location of the ODBC header and library files in your configure command, e.g.: 

  $ ./configure \
    LD_LIBRARY_PATH=/usr/local/odbc/lib \
    LDFLAGS=-L/usr/local/odbc/lib \
    LIBS=-lodbc \
    --with-modules=mod_sql:mod_sql_odbc \
    --with-includes=/usr/local/odbc/include \
    --with-libraries=/usr/local/odbc/lib

Usage

Note: some of the following information is taken from: 
  http://www.mysql.com/products/myodbc/manual.html#Introduction_to_ODBC

What is ODBC?
ODBC (Open Data Base Connectivity) provides a way for client programs to access a wide range of databases or data sources.

ODBC is a standardized API, developed according to the specifications of the SQL Access Group, that allows one to connect to SQL databases. It defines a set of function calls, error codes and data types that can be used to develop database-independent applications. ODBC is usually used when database independence, or simultaneous access to different data sources, is required.

For more information about ODBC, refer to: 

  http://www.microsoft.com/data/odbc/

When should mod_sql_odbc be used?
There are two cases where a site might need to use the mod_sql_odbc module: when the site's database does not provide a native connectivity API (e.g. FrontBase), and when the site's database does not have a corresponding mod_sql_db module for it. For example, ProFTPD does not have a mod_sql_oracle module. Using mod_sql_odbc and an Oracle-provided ODBC driver would allow a site to have proftpd communicate with their Oracle database.

How does ODBC work?
An ODBC driver is a dynamically loadable library that knows how to talk to a specific database. Many databases come bundled with an ODBC driver. The following lists where to find ODBC drivers for the popular MySQL and Postgres databases:

MySQL ODBC Driver (MyODBC)
http://www.mysql.com/products/myodbc/

Postgres ODBC Driver (psqlODBC)
https://odbc.postgresql.org/

The ODBC driver manager is a library that manages communication between the ODBC-aware application and ODBC drivers. Its main functionality includes:

The following lists some commonly used driver managers:
iODBC ODBC Driver Manager for Unix
http://www.iodbc.org

unixODBC Driver Manager for Unix
http://www.unixodbc.org

Microsoft Windows ODBC Driver Manager
http://www.microsoft.com/data/

Environment Variables
There are two environment variables that both the iODBC and unixODBC libraries use: LD_LIBRARY_PATH and ODBCINI. The ODBCINI environment variable specifies the configuration file used by the library; this file specifies the driver to use, and any database connection information that driver may need.The LD_LIBRARY_PATH environment variable is used to tell the Driver Manager where it may find the driver libraries. Note that on a Linux system, if the /etc/ld.so.conf contains the directory where the ODBC driver libraries live (and /sbin/ldconfig has been run), use of the LD_LIBRARY_PATH setting may not be necessary.

Configuring ODBC
The hard part about using the iODBC or unixODBC Unix driver managers is their configuration. Often in ODBC documentation you will mention of DSNs, or Data Source Names. A data source identifies a path to data that can include a network library, server, database, and other attributes. To connect to a data source, the driver manager checks for specific connection information. This specific connection information is recorded in a file usually called "odbc.ini".

Example iODBC odbc.ini file: 

  [ODBC Data Sources]
  mysql = MySQL ODBC Connector (MyODBC)

  [mysql]
  Driver = /usr/local/iodbc/lib/libmyodbc3.so
  Description = MySQL Driver
  Server = localhost
  Database = proftpd
  Port = 3306
  Socket = /tmp/mysql.sock

Example unixODBC odbc.ini file: 

  [mysql]
  Description = MySQL
  Driver = mysql
  Server = localhost
  Database = proftpd
  Port = 3306
  Socket = /tmp/mysql.sock
More details on the contents of odbc.ini files, and the different meanings of configuration options, can be found in the respective driver manager documentation.

Configuring mod_sql_odbc
For the most part, mod_sql_odbc requires little configuration. It relies on the ODBC driver managers, which in turn use the above environment variables. This means that ODBCINI and LD_LIBRARY_PATH can be set in the environment, before starting proftpd, and mod_sql_odbc would then work. The one required bit of information is the SQLConnectInfo directive. This directive needs to contain the name of the stanza in your odbc.ini file which holds the configuration. Using the example odbc.ini files above, you would need the following in your proftpd.conf: 

  # Tells mod_sql to use mod_sql_odbc
  SQLBackend odbc

  # Tells mod_sql_odbc which odbc.ini stanza to use
  SQLConnectInfo mysql

Starting with proftpd 1.2.10rc1, it is possible to use the SetEnv configuration directives to set the environment variables directly in the proftpd.conf file, which makes configuration more centralized.

Example configuration using SetEnv: 

  <IfModule mod_sql_odbc.c>
    SetEnv LD_LIBRARY_PATH /path/to/odbc/lib
    SetEnv ODBCINI /path/to/odbc.ini
  </IfModule>

Once you have your ODBC driver configured properly, you can follow the rest of the instructions in the SQL howto for creating the schema for user/group authentication.

Please contact the author directly with any questions or comments.

© Copyright 2003-2020 TJ Saunders
All Rights Reserved