Home Back to Tutorial Online Help

How to use database stored procedures for Xlight FTP Server

Xlight FTP Server can be configured to use the stored procedures in the database 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 a user's settings in the database. The primary table is the "acct_table", which 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 is referred to 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 logs into the 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 are 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 the stored procedure for the Xlight FTP Server

Many database systems support stored procedures. We use the MySQL database as an example. Before using 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 types of databases, make sure to upgrade to the latest ODBC Driver.

Before using database stored procedures, ODBC Data Source must be set up. 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 applications and it stores the user name, password and the number of posts etc. We also created the table "acct_param_table", which is one of the tables used by the 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 parameters, 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 stored procedure above, the password_in parameter for the test() accepts a clear-text password. You have to configure the virtual server to use a clear-text password for ODBC, as shown in the picture below.

The stored procedure above, test() will check database table account_info to authenticate the user, if the 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, and home_path values according to the "posts" number of this user in the "account_info" table. Finally, the test() procedure will return a result set to Xlight FTP Server with "SELECT home_path,home_perm,param_index;"

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

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

If you want to troubleshoot the problem, you can select the option "Show debug trace information in Error Log", you will then see SQL query errors in the Error Log.