Home Back to Tutorial Online Help

How to use database stored procedures for Xlight FTP Server

Xlight FTP Server can be configured to use database stored procedures to authenticate users. It provides the flexibility of integrating Xlight FTP Server with other types of database applications.

Before we start the stored procedures tutorial, we will explain a little bit about how the ODBC function works internally in the Xlight FTP Server.

Xlight FTP Server uses two database tables to store user's settings in the database. The primary table is the "acct_table", which it stores the basic settings of a user, such as host_id or host_port, username, password and his group, home path and home path permission etc. The second table is the "acct_param_table", which it is referred by the "param_index" column in the "acct_table". It stores additional settings of a user. These settings can be used as a parameter template for all users.

When a user logins into FTP server, Xlight FTP Server will send two SQL queries to the database to verify this user's password and get his settings. The username and password is used by the first SQL query to find a matched record in the database, and get basic parameters, such as "home_path", "home_perm" etc. The "param_index" value returned by the first SQL query will be used as the index for the second SQL query. If the first SQL query won't return "param_index", the second SQL query will not be sent. The second SQL query will query the database table "acct_param_table" to get more parameters for this user.

Use stored procedure for Xlight FTP Server

Many database system supports stored procedures. We use MySQL database as an example. Before use MySQL, you must download the latest version of MySQL ODBC Driver - 3.51.16 or above. Otherwise because of MySQL Bug #27544, the second SQL query will fail. For other type of database, make sure to upgrade to latest ODBC Driver.

Before using database stored procedures, ODBC Data Source must be setup. You can refer here for the setup procedure.

In this example, we had a table "account_info" in the database. This table is from other database application and it stores user name, password and number of posts etc. We also created the table "acct_param_table", which is one of tables used by Xlight FTP Server for additional parameters of a user. We didn't create Xlight FTP Server's primary database table "acct_table", because we will use stored procedures to emulate its functionality.

The following code is a sample MySQL stored procedure test(), it accepts two parameter, username_in and password_in.

DELIMITER $$
DROP PROCEDURE IF EXISTS test $$
CREATE PROCEDURE test(username_in VARCHAR(65), password_in VARCHAR(65))
main: BEGIN
  DECLARE user_name VARCHAR(65);
  DECLARE home_path VARCHAR(255);
  DECLARE home_perm VARCHAR(10);
  DECLARE num_posts, param_index INT(10);

  SELECT `account`, `Posts`
    INTO user_name, num_posts
    FROM account_info
    WHERE account = username_in AND password = password_in;

  IF user_name != username_in THEN
    LEAVE main;
  END IF;

  SET home_perm = 'LRS';

  IF num_posts > 100 THEN
    SET param_index = 1;
    SET home_path = 'c:\\folder_1';
  ELSEIF num_posts > 200 THEN
    SET param_index = 2;
    SET home_path = 'c:\\folder_2';
  ELSE
    SET param_index = 0;
    SET home_path = 'c:\\folder_0';
  END IF;

  SELECT home_path,home_perm,param_index;

END $$
DELIMITER ;

In the above stored procedure, password_in parameter for test() accepts clear text password. You have to configure the virtual server to use clear text password for ODBC, as showed in the picture below.

The above stored procedure test() will check database table account_info to authenticate user, if user name or password is not matched, the stored procedure will not return any result set to Xlight FTP Server and user authentication will fail.

After successful authentication, the stored procedure will return different param_index, home_path values according to the posts number of this user in the "account_info" table. Finally the test() procedure will return result set to Xlight FTP Server with "SELECT home_path,home_perm,param_index;"

Because the test() procedure replace the functionality of "acct_table", you should put "call test('$USER','$PASS');" in the "Statement to replace the first SQL query", as showed in the picture below.

$USER and $PASS are pre-defined parameters, which Xlight FTP Server will automatically replace them with real login username and password. For the list of supported pre-defined paramters, please refer to the [Global Options]->[Advanced] section of Xlight FTP Server's help document.

If you want to troubleshoot problem, you can select the option "Log SQL query error to System Error Log", you will then see SQL query errors in the Error Log.