Section 3.41.1. AuthSelect

3.41.1. AuthSelect

This is an SQL select statement that will be used to find and fetch the password and possibly check items and reply items for the user who is attempting to log in. You can use the special formatting characters. %0 is replaced with the quoted and escaped user name. The first column returned is expected to be the password; the second is the check items (if any) and the third is the reply items (if any) (you can change this expectation with the AuthColumnDef parameter). Defaults to "select PASSWORD from SUBSCRIBERS where USERNAME=%0", which does not return any check or reply items. You can make arbitrarily complicated SQL statements so that you will only authenticate users for example whose account status is OK or who have not exceeded their download limit etc. For information about how check items and reply items are used, see Section 7. Check and reply items. If the password (or encrypted password) column for a user is NULL in the database, then any password will be accepted for that user.

The password column may be in any of the formats described in Section 7.1.1. User-Password, Password.

# Check user status is current. No reply items in DB
# Note: The entire statement must be on one line
AuthSelect select PW, CHECK from USERS where NAME=%0 and STATUS = 1

If AuthSelect is defined as an empty string, SQL will not attempt to authenticate at all.

If one or more AuthSelectParam parameters are specified, they will be used in order to replace any fields marked with a question mark in AuthSelect.

Tip

By default, many SQL servers do case-insensitive string comparison. This means that AuthBy SQL, AuthBy RADMIN etc. would match, for example mikem, MIKEM and MiKeM as being the same user. Some SQL databases allow you to force case-sensitive comparisons. For example, In the case of MySQL, the 'BINARY' keyword forces the following comparison to be case-sensitive. Therefore you could force case-sensitive user names in an AuthSQL for MySQL with something like:

AuthSelect select PASSWORD from SUBSCRIBERS where BINARY USERNAME=%0

Tip

You can improve the performance of AuthSelect queries executed by the SQL server with AuthSelectParam.

Note

In the event of a SQL timeout, Radiator will reconnect to the database and the AuthSelect query will be tried again. This means there may be up to 2 Timeout intervals before the entire AuthSelect query fails.