Solved Password Validation sub

[Pat Hartman]

Do not allow single or double quotes in passwords. For strings that do allow single or double quotes, you need to "escape" them to prevent errors.

 

[cheekybuddha]

For strings that do allow single or double quotes, you need to "escape" them to prevent errors.

Shouldn't be an issue if you use DAO.Parameters

 

[mikenyby]

Shouldn't be an issue if you use DAO.Parameters

@cheekybuddha your SP works perfectly in SQL Server, thank you! But there are some issues calling it in Access.

I'm having a little trouble implementing the Parameters. I've copied the code you have provided, adjusting the data types and variable names used to match my own, but I can't avoid a "Runtime Error 3265: Item not found in this collection" error highlighting the first .parameters line:

I can confirm the variables are passing properly:

This is my first time with querydefs.parameters so I'm assuming I'm having trouble defining the parameter names.

 

[Minty]

Make sure the parameters are explicitly defined in the source query.

 

[mikenyby]

Make sure the parameters are explicitly defined in the source query.

Is that possible in a pass-through query?

 

[cheekybuddha]

Minty is right.

Try amending:

Function PasswordValid(userID As Long, pw As String) As Integer

  Const SP As String = "PARAMETERS p1 dbLong, p2 dbText; validate_pw p1, p2;"
' ...

(Again, untested!)

 

[mikenyby]

Minty is right.

Try amending:

Function PasswordValid(userID As Long, pw As String) As Integer

  Const SP As String = "PARAMETERS p1 dbLong, p2 dbText; validate_pw p1, p2;"
' ...

(Again, untested!)

Still the same problem.
.Parameters("p1") highlighted with "item not found in this collection".

 

[mikenyby]

I adjusted this a bit to make it work, but it still may be vulnerable. Here is the new function:

Public Function PasswordValid(IntId As Long, strPassword As String) As Integer

  Dim SP As String
  SP = "validate_pw " & IntId & ", '" & strPassword & "'"
  Dim ret  As Integer

  With CurrentDb.QueryDefs("qryPassValid")
    .SQL = SP
    With .OpenRecordset
        ret = .Fields("valid")
        .Close
    End With
  End With
 
  PasswordValid = ret

End Function

It protects against @cheekybuddha 's original successful injection attack:

'),('1

because the SQL Query becomes

validate_pw [intID], ''),('1''

The additional quotes brick the code and I get a Runtime Error 3146: ODBC call failed error.

I tried adjusting the injection to prevent the quote doubling and escape the internal quote:

),(''1

This one got me my standard invalid password message.

I tried a DML injection to see if that would do anything. The resulting SQL string is:

validate_pw 6, 'xxx'; INSERT INTO Reasons (Reason) VALUES ('Test');

This was stopped by the With.OpenRecordset VBA line which caused an ODBC call failed error.

But I'm sure there must be other SQL injection methods that would work in this instance. With this method I think I would still have to disallow single and double quotes in passwords.

 

[cheekybuddha]

OK, since I haven't done any of this for a while it seems I missed (or more likely forgot ) that you can't use DAO parameters with a pass-through (See here on StackOverflow).

You have a couple of options:
1. Build the SQL string like you have above - if you do so, then use a Replace function on the strPassword variable so you don't get an error if the password contains a single quote. You **should** safe from SQL injection since you will be querying the db via Stored Proc that will only accept 2 parameters - BUT there are some clever hackers out there who still might be able to craft an input that will mess with your password_valid function.

Public Function PasswordValid(IntId As Long, strPassword As String) As Integer

  Dim SP As String
  SP = "validate_pw " & IntId & ", '" & Replace(strPassword, "'", "''") & "'"
' ...

2. Use an ADODB command so that you can pass the params to the SP. The tricky bit is getting your connection string correct!

Function PasswordValid(userID As Long, pw As String) As Integer

  Const SP As String = "validate_pw ?, ?;"
  Const adUseClient   As Integer = 3, _
        adVarChar     As Integer = 200, _
        adInteger     As Integer = 3, _
        adParamInput  As Integer = 1, _
        adStateOpen   As Integer = 1

  Dim cn As Object
  Dim pt As Object
  Dim cnString As String
  Dim ret  As Integer
 
  cnString = CurrentDb.QueryDefs("qryPassValid").Connect
  cnString = Replace(cnString, "ODBC;", "ODBC;PROVIDER=MSDASQL;")
' You will probably have to add on your username and password if you are not using a TrustedConnection - presumably this will all be compiled into an .accde before going in to production!
  cnString = cnString & "UID=" & sqlServerUserName & ";PWD=" & sqlServerPW &";"
  Set cn = CreateObject("ADODB.Connection")
  Set pt = CreateObject("ADODB.Command")
  
  With cn
    .ConnectionString = cnString
    .CursorLocation = adUseClient
    .Open
  End With

  With pt
    Set .ActiveConnection = cn
    .CommandText = SP
    .Parameters.Append .CreateParameter("p0", adInteger, adParamInput)
    .Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, 300)
    .Parameters(0) = userID
    .Parameters(1) = pw
    With .Execute
      If Not .EOF Then
        ret = .Fields(0)
      End If
      .Close
    End With
  End With
  
  If cn.State = adStateOpen Then cn.Close
  Set cn = Nothing
  Set pt = Nothing
 
  PasswordValid = ret

End Function

(The example is late-bound, so no need to set an ADODB reference.)

 

[mikenyby]

OK, since I haven't done any of this for a while it seems I missed (or more likely forgot ) that you can't use DAO parameters with a pass-through (See here on StackOverflow).

You have a couple of options:
1. Build the SQL string like you have above - if you do so, then use a Replace function on the strPassword variable so you don't get an error if the password contains a single quote. You **should** safe from SQL injection since you will be querying the db via Stored Proc that will only accept 2 parameters - BUT there are some clever hackers out there who still might be able to craft an input that will mess with your password_valid function.

Public Function PasswordValid(IntId As Long, strPassword As String) As Integer

  Dim SP As String
  SP = "validate_pw " & IntId & ", '" & Replace(strPassword, "'", "''") & "'"
' ...

2. Use an ADODB command so that you can pass the params to the SP. The tricky bit is getting your connection string correct!

Function PasswordValid(userID As Long, pw As String) As Integer

  Const SP As String = "validate_pw ?, ?;"
  Const adUseClient   As Integer = 3, _
        adVarChar     As Integer = 200, _
        adInteger     As Integer = 3, _
        adParamInput  As Integer = 1, _
        adStateOpen   As Integer = 1

  Dim cn As Object
  Dim pt As Object
  Dim cnString As String
  Dim ret  As Integer
 
  cnString = CurrentDb.QueryDefs("qryPassValid").Connect
  cnString = Replace(cnString, "ODBC;", "ODBC;PROVIDER=MSDASQL;")
' You will probably have to add on your username and password if you are not using a TrustedConnection - presumably this will all be compiled into an .accde before going in to production!
  cnString = cnString & "UID=" & sqlServerUserName & ";PWD=" & sqlServerPW &";"
  Set cn = CreateObject("ADODB.Connection")
  Set pt = CreateObject("ADODB.Command")
 
  With cn
    .ConnectionString = cnString
    .CursorLocation = adUseClient
    .Open
  End With

  With pt
    Set .ActiveConnection = cn
    .CommandText = SP
    .Parameters.Append .CreateParameter("p0", adInteger, adParamInput)
    .Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, 300)
    .Parameters(0) = userID
    .Parameters(1) = pw
    With .Execute
      If Not .EOF Then
        ret = .Fields(0)
      End If
      .Close
    End With
  End With
 
  If cn.State = adStateOpen Then cn.Close
  Set cn = Nothing
  Set pt = Nothing
 
  PasswordValid = ret

End Function

(The example is late-bound, so no need to set an ADODB reference.)

Thanks for this. I just got out of a meeting with management and now they are saying they want the whole thing on the cloud and a web CRUD app....

 

[cheekybuddha]

Thanks for this. I just got out of a meeting with management and now they are saying they want the whole thing on the cloud and a web CRUD app....

How are your web skills? Auth gets a lot more tricky when building a web app!

 

[mikenyby]

@cheekybuddha Frankly, not that great! This is after countless debriefings about how they're prioritizing storing all their data locally....