Prev   Next

Back to the top of the FAQ

Q. I've moved on from the Standard and Custom ODBC Select statement and I am in the process of setting up with the "Advanced" ODBC string. Tell me more about this.
A. While the Standard and Custom options are useful to get AuthentiX working quickly and easily, the Advanced option is useful for database experts who want complete flexibility and power.

When you use the "Use string to validate (empty rowset indicates failure)" option a simple macro substitution is made at run time, replacing values such as $USERNAME$ with their runtime values.. Then the statement is executed using the ODBC SQLExecDirect call. You need to make sure the statement you use should make sense to the ODBC driver and database you are using. If the call results in an empty rowset access is denied, otherwise access is granted, and the username and password combination are stored in the AuthentiX ODBC username/password cache.

The other two Advanced Options ("Use Standard Select to validate, execute ODBC string on success." and "Use Custom Select to validate, execute ODBC string on success.") only calls the Advanced ODBC string if they succeed. This can be useful if you want to log successful logins for example. In this case the $VERIFY$ macro subsitution indicates whether this is an initial login, or a verification against the database, in accordance with the operation of the ODBC cache.

Here is an example string:

EXEC sp_Login '$USERNAME$', 
	'$PASSWORD$', '$IPADDRESS$', 
	'$USERAGENT$', '$VERIFY$'
And another, used in the SQL sample below
EXEC sp_Login '$USERNAME$', 
	'$PASSWORD$', 'c:\inetpub\wwwroot\members"

Here is an example SQL Stored procedure:

CREATE PROCEDURE VerifyUser
  @UserName VarChar(50), /* THIS IS THE USERNAME PARAMETER */
  @Password VarChar(15), /* THIS IS THE PASSWORD PARAMETER */
  @DirName VarChar(50) /* THIS IS THE DIRECTORY NAME PARAMETER */
AS
  /* THIS SELECT RETURNS A NON-EMPTY RESULTSET IF */
  /*  THE USER IS A MEMBER OF A GROUP THAT HAS ACCESS TO THE */
  /*  REQUESTED DIRECTORY AND IF THE USER HAS A VALID PASSWORD */
  SELECT @UserName, @Password, @DirName FROM 
    WebUsers w, UserRelations u,  GroupRelations g, GroupDirs d
    WHERE w.UserName=@UserName
    AND w.Password = @Password
    AND w.UserID = u.UserID
    AND u.GroupID = g.GroupID
    AND g.DirID = d.DirID  
    AND d.DirName =@DirName
Also, "Alexandre Volpim" (volpim@camerasurf.com.br) shows us how to create a stored procedure with multiple selects.
set nocount on
declare @loginCheck varchar(100)

select @loginCheck=login from clients where login=@login and
password=@password
if (@loginCheck<>'') then
begin
    insert into log (login,date) values (@loginCheck,getdate())
end
select * from clientes where login=@loginCheck

The result of this stored-procedure will be the result of the last Select because all other statements (select and insert) don't return data. This SP is not usefull, but my ideia is to tranform the IP of the form xxx.xxx.xxx.xxx to a int before the select statement. The code to transform the IP didn't return data, but the SP doesn't works. Actually I call another SP (valIP) in the authentication SP:
CREATE PROCEDURE valIP

@ip char(15),
@resultado numeric(15) output
AS

DECLARE
@octeto int,
@pos int,
@posant int,
@contador int,
@valor numeric(15)

select @posant=1
select @valor=0
select @contador=0
select @pos=CHARINDEX('.',@ip)
while (@pos<>0)
begin
  select @octeto=SUBSTRING(@ip,@posant,@pos-@posant)
  select @valor=@octeto+@valor*256
  select @contador=@contador+1
  select @posant=@pos+1
  select @pos=CHARINDEX('.',@ip,@posant)

end
select @octeto=SUBSTRING(@ip,@posant,Len(@ip)-@posant+1)
select @valor=@octeto+@valor*256

select @resultado=@valor

Back to the top of the FAQ

Prev   Next