login when pwdencrypt had been used where to start?

Question

I am trying to login on our database through my program but when I do I get Invalid Credentials so i am not sure where to start at hope anyone can help me with this as i haven't worked with pwdencryp and pwdcompare before and i am not sure what to do to get it to work

My code:

       protected String doInBackground(String... params) {
            if (userid.trim().equals("Developer")|| password.trim().equals("Dev!n_234"))
                isSuccess2=true;
            z = getString(R.string.login_succes);
            if(userid.trim().equals("")|| password.trim().equals(""))
                z = getString(R.string.indsæt_rigtigt_bruger);
            else
            {
                try {
                    Connection con = connectionClass.CONN();
                    if (con == null) {
                        z = getString(R.string.Forbindelses_fejl)+"L1)";

                    } else {
                        CallableStatement cs = null;
                        String query = "{call [system].[usp_validateUserLogin] (?,?,?,?,?)}";
                        Statement stmt = con.createStatement();
                        ResultSet rs = stmt.executeQuery(query);
                        CallableStatement ps = con.prepareCall(query);
                        ps.setString(1, userid);
                        ps.setString(2, password);
                        ps.setInt(3,72);
                        ps.setNull(4, Types.BOOLEAN);
                        ps.registerOutParameter(5, Types.VARCHAR);
                        cs.executeUpdate();
                        if(rs.next())
                        {

                            z = getString(R.string.login_succes);

                            isSuccess=true;
                        }
                        else
                        {
                            z = getString(R.string.Invalid_Credentials);
                            isSuccess = false;
                        }

                    }
                }
                catch (Exception ex)
                {
                    isSuccess = false;
                    z = getString(R.string.Exceptions)+"L2)";
                    Log.e("MYAPP", "exception", ex);
                }
            }
            return z;

        }
    }


}

} the procedure

ALTER PROCEDURE [system].[usp_validateUserLogin]
    @p_Login NVARCHAR ( 50 )
    , @p_Password NVARCHAR ( 32 )
    , @p_CompanyID INT
    , @p_OutDetails BIT = 1
    , @p_AuthenticationTicket VARCHAR(200) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @errNo INT
        , @recCount INT
        , @res INT

    SELECT u.*
        INTO #tmpLogin
    FROM system.[User] AS u WITH ( NOLOCK )
    WHERE ( u.Login = @p_Login )
        AND ( u.Company_ID = @p_CompanyID )
        AND ( pwdcompare ( @p_Password, u.Passwd ) = 1 )
        AND ( u.Status = 0 ) --Active

    SELECT @errNo = @@ERROR
        , @recCount = @@ROWCOUNT

    IF ( @errNo <> 0 )
    BEGIN
        RETURN 1010
    END

    IF ( @recCount = 1 )
    BEGIN
        DECLARE @userID INT
        SELECT @userID = ID
        FROM #tmpLogin

        EXEC @res = system.usp_renewAuthenticationTicket @p_DoerTicket = ''
                                                        , @p_AuthenticationTicket = @p_AuthenticationTicket OUTPUT
                                                        , @p_UserID = @userID
                                                        , @p_CompanyID = @p_CompanyID
        IF ( @res <> 0 )
            RETURN @res

    END
    --SET @p_AuthenticationTicket = 'TESTAUTHENTICATIONTICKET0123456789'

    IF ( @p_OutDetails = 1 )
    BEGIN
        SELECT *
        FROM #tmpLogin
    END

    RETURN 0
END

and here is the procedure for the user sign up

ALTER PROCEDURE [system].[usp_iudUser]
    @p_ID INT = NULL OUTPUT
    , @p_Login NVARCHAR ( 50 ) = NULL
    , @p_Password NVARCHAR ( 32 ) = NULL
    , @p_FullName NVARCHAR ( 100 ) = NULL
    --, @p_LastName NVARCHAR ( 50 ) = NULL
    , @p_EMail NVARCHAR ( 200 ) = NULL
    , @p_Status TINYINT = NULL
    , @p_Roles VARCHAR ( 200 ) = NULL
    , @p_DoerTicket VARCHAR ( 200 )
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @doerUserID INT
            , @doerCompanyID INT
    EXEC system.usp_validateAuthenticationTicket @p_Ticket = @p_DoerTicket
                                                , @p_UserID = @doerUserID OUTPUT
                                                , @p_CompanyID = @doerCompanyID OUTPUT


    MERGE INTO system.[User] AS target
    USING ( SELECT @p_ID
                , @doerCompanyID
                , @p_Login
                , @p_Password
                , @p_FullName
                , @p_Roles
                , @p_Status
                /*, @p_FirstName
                , @p_LastName*/
                , @p_EMail ) AS source ( ID
                                        , CompanyID
                                        , Login
                                        , Password
                                        , FullName
                                        , Roles
                                        , Status
                                        /*, FirstName
                                        , LastName*/
                                        , EMail )
    ON ( target.ID = source.ID )
        AND ( target.Company_ID = source.CompanyID )
    WHEN MATCHED THEN
        UPDATE SET 
            target.Login = CASE WHEN source.Status = 200 THEN target.Login + '_' + CAST ( source.ID AS VARCHAR ( 10 ) ) ELSE target.Login END --Login can not be changed
            , target.Passwd = ISNULL ( pwdencrypt ( source.Password ), target.Passwd )
            , target.FullName = ISNULL ( source.FullName, target.FullName )
            --, target.LastName = ISNULL ( source.LastName, target.LastName )
            , target.EMail = ISNULL ( source.EMail, target.EMail )
            , target.Roles = ISNULL ( source.Roles, target.Roles )
            , target.Status = ISNULL ( source.Status, target.Status )
    WHEN NOT MATCHED BY TARGET AND source.ID IS NULL THEN
        INSERT ( Company_ID
                , Login
                , Passwd
                , FullName
                , Roles
                , Status
                /*, FirstName
                , LastName*/
                , EMail )
            VALUES ( source.CompanyID
                    , source.Login
                    , pwdencrypt ( source.Password )
                    , source.FullName
                    , NULLIF ( RTRIM ( source.Roles ), '' )
                    , ISNULL ( source.Status, 0 )
                    /*, source.FirstName
                    , source.LastName*/
                    , NULLIF ( source.EMail, '' ) );

    IF ( @@ROWCOUNT <> 1 )
    BEGIN
        RETURN 1010
    END

    IF ( @p_Id IS NULL )
        SET @p_Id = SCOPE_IDENTITY ( )

    RETURN 0    
END

Answer 1

In the proc [usp_validateUserLogin] you use the parameter @p_OutDetails to determine if you return data to the caller and in the caller you use the fact that records have returned to determine if the process was a success.

The line ps.setString(4, null); is setting the value of the parameter @p_OutDetails in [usp_validateUserLogin] to null. This is different from not providing a parameter at all as SQL Server will use the value NULL instead of the default. If the parameter wasn't provided then it would use the default value (1). Additionally, the parameter type expected is BIT therefore it should be using something like ps.SetBoolean to set the value of BIT parameters to an explicit value of 1 (or true).

As an example to show using defaults versus supplying a value of NULL:

CREATE PROCEDURE TestProc
    @MyString   VARCHAR(10) = 'string'

AS
BEGIN
    SET NOCOUNT ON
    SELECT @MyString AS Param, ISNULL(@MyString, 'Was Null') AS ItsValue
END
GO


EXEC TestProc null
EXEC TestProc 'A Value'
EXEC TestProc   --no params at all

Running this proc returns the following for the 3 types of EXEC.

Param,ItsValue
NULL,Was Null

Param,ItsValue
A Value,A Value

Param,ItsValue
string,string

Your code is performing the first way therefore when it executes the following line, @p_OutDetails contains NULL and it skips this bit.

IF ( @p_OutDetails = 1 )
BEGIN
    SELECT *
    FROM #tmpLogin
END