SQL Injection on New Record Form (1 Viewer)

[camerontaylor]

I've got a form which a user can enter a new record's info on. I'm wondering if it is possible for SQL Injection to occur with the user input on this form. The new record in the datasheet is also updated to a subform on my main form. If it is possible, then can someone suggest a way for me to sanitize the user input on this form to remove or reduce the possibility of SQL injection. Thanks!

 

[theDBguy]

Hi. I believe SQL injection only occurs if you're executing a SQL statement with the input. Are you doing that? If so, can you please show it to us?

 

[camerontaylor]

Hi. I believe SQL injection only occurs if you're executing a SQL statement with the input. Are you doing that? If so, can you please show it to us?

I don't think so. It's purely just a form to create a new record. I'm not entirely sure of the inner workings behind it. I've attached an image. The save button closes the form, and runs a couple of queries using one of the date fields to create a string of characters and numbers.

 

[Pat Hartman]

The reason that SQL injection works is because SQL Server et al allow multiple action queries in a single string. Access SQL does not.

You can prove it to yourself. If you put an SQL string in a text control, the SQL string will end up as the column value in your table.

 

[theDBguy]

I don't think so.

You don't think SQL injection only happens when executing SQL statements, or you don't think you're executing SQL statements in your form?

What's behind the button "Save Project Details?" Is there any code behind it? If so, can you show it to us?

If you think you have a very simple form, what made you think about SQL injection that made you become concerned with it?

 

[Minty]

The short answer is no. Not on a bound form.
Not without some very convoluted code behind the scenes, which it doesn't sound as if you have.

 

[camerontaylor]

You don't think SQL injection only happens when executing SQL statements, or you don't think you're executing SQL statements in your form?

What's behind the button "Save Project Details?" Is there any code behind it? If so, can you show it to us?

If you think you have a very simple form, what made you think about SQL injection that made you become concerned with it?

Sorry, I was a bit ambiguous there. I don't think that I'm executing SQL statements in the form.

The Save Button has the following code behind it:

Private Sub saveProjectDetails_Click()

DoCmd.Close acForm, Me.Name, acSaveNo

incrementMonthNumAndCreateEP

End Sub

Private Sub incrementMonthNumAndCreateEP()
Dim strtDt As Date, EPNum$
Dim SQLcomm1$, SQLcomm2$
Dim monthNum As Integer, r As Recordset

monthNum = DCount("*", "formatDate", "[YM] = '" & Format(Nz(DLookup("StartDate", "ProjectList", DMax("ProjectID", "ProjectList") & "=[ProjectID]")), "yyyymm") & "'")

SQLcomm1 = "UPDATE ProjectList SET MonthCount = " & monthNum & " WHERE ProjectID =" & DMax("ProjectID", "ProjectList") & ";"

DoCmd.SetWarnings (False)
DoCmd.RunSQL SQLcomm1

strtDt = Nz(DLookup("StartDate", "ProjectList", DMax("ProjectID", "ProjectList") & "=[ProjectID]"), 0)

If (strtDt) = 0 Then
    EPNum = ""
Else
    EPNum = "EP" & Format(Nz(DLookup("StartDate", "ProjectList", DMax("ProjectID", "ProjectList") & "=[ProjectID]"), 0), "yymm") & "-" & Format(monthNum, "00")
End If

If EPNum = DLookup("EPNumber", "ProjectList") Then
    EPNum = "EP" & Format(Nz(DLookup("StartDate", "ProjectList", DMax("ProjectID", "ProjectList") & "=[ProjectID]"), 0), "yymm") & "-" & Format(monthNum + 1, "00")
End If

SQLcomm2 = "UPDATE ProjectList SET EPNumber ='" & EPNum & "'WHERE ProjectID = " & DMax("ProjectID", "ProjectList") & ";"

DoCmd.RunSQL SQLcomm2
DoCmd.SetWarnings (True)

End Sub

And the formatDate query looks like this: SELECT ProjectList.StartDate, Format(StartDate,"yyyymm") AS YM FROM ProjectList;

I am purely thinking about protecting against SQL injection because my supervisor has asked that it be implemented where necessary, and so I wanted to get confirmation of whether I need to be sanitizing or not.

 

[camerontaylor]

The reason that SQL injection works is because SQL Server et al allow multiple action queries in a single string. Access SQL does not.

You can prove it to yourself. If you put an SQL string in a text control, the SQL string will end up as the column value in your table.

So in this particular case, I don't have to worry about it?

 

[theDBguy]

I don't think that I'm executing SQL statements in the form.

Unfortunately, when you use code like this:

DoCmd.RunSQL SQLcomm1

You are executing a SQL statement.

However, as @Pat Hartman said earlier, you only need to be concerned if you were running this code in SQL Server.

 

[camerontaylor]

However, as @Pat Hartman said earlier, you only need to be concerned if you were running this code in SQL Server.

Ok, perfect. Thanks for the confirmation

 

[Gasman]

You might want to start Dimming your variables correctly as well.?
Each has to specified for Type?

 

[theDBguy]

Ok, perfect. Thanks for the confirmation

You're welcome. However, I'm still curious. What made you think of SQL injection in the first place? Did you read about it somewhere? Where? Thanks.

 

[isladogs]

Ah...little Bobby Tables again...bobby-tables.com: A guide to preventing SQL injection
Or perhaps the OP had read this article SQL Injection (w3schools.com)