@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.
I adjusted this a bit to make it work, but it still may be vulnerable. Here is the new function:
Code:
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:
SQL:
'),('1
because the SQL Query becomes
SQL:
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:
SQL:
),(''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:
SQL:
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.
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.
Code:
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!
Code:
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.)
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.
Code:
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!
Code:
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.)
) that you can't use DAO parameters with a pass-through (
