0

I am working on a project for the company I work for, designing a database to keep track of and create project numbers. I have it up and running, but my supervisor has asked that I include user-input sanitizing to escape special characters that could cause a problem for the existing code and SQL. I have a few different user-input forms, which are just bound text boxes which get entered into my table when the form is closed. I also have one Input Box, which asks for the project number which an employee would like to update the info for.

From my understanding, a local Access database on our company server is not going to be very prone to SQL injection, and MS Access has a way of handling injection which I do not really understand. However, I am looking for a list of characters which could potentially cause problems, where they could potentially cause problems, and the best way to deal with them.

I have tried inputting a few different special characters which I know to be problematic into the text boxes on the forms, but Access just parses them straight into the record, with no errors. I DO have one function written in which replaces single apostrophes with two apostrophes, and this is used on the InputBox.

Here is the code behind the InputBox:

    Private Sub btnOpenUpdate_Click()
Dim strInput$, safeInput$

strInput = InputBox("Enter the EP-Number for the project that you would like to update:", "Update Project")
safeInput = safeEntry(strInput) 'change all single apostrophes to double apostrophes '

If Len(safeInput & vbNullString) > 0 Then
    If DCount("EPNumber", "tblProjects", "EPNumber = '" & safeInput & "'") > 0 Then
        DoCmd.OpenForm "frmUpdateProject", , , "EPNumber = '" & safeInput & "'"
    Else
        MsgBox "Please enter a valid EP-Number.", vbInformation, "Error: Invalid EP-Number entered"
   End If
Else
    MsgBox "The field was left blank. Please enter a valid EP-number.", vbInformation, "Error: Empty field"
End If

End Sub

And here is the code behind the safeEntry function:

Public Function safeEntry(strEntry)
    safeEntry = Replace(Nz(strEntry), "'", "''")
End Function

Apologies for the somewhat lengthy summary of my situation, but any help and input would be very appreciated, as I am fairly new to the world of MS Access and SQL, and I am trying my best to learn how to protect the database.

camtaylor01
  • 1
  • 2
  • You can use my [CSql](https://stackoverflow.com/questions/43589800/syntax-error-in-insert-into-statement-whats-the-error/43591778#43591778) function. – Gustav May 31 '21 at 16:49
  • Thank you very much @Gustav. Just to confirm, can I replace my current safeEntry function with your CSql function, and it will do the same thing and more? And are there any risks with special characters used in form text boxes which are inserted into a table? – camtaylor01 May 31 '21 at 17:07
  • Does this answer your question? [Syntax error in insert into statement - what's the error?](https://stackoverflow.com/questions/43589800/syntax-error-in-insert-into-statement-whats-the-error) – June7 May 31 '21 at 17:30
  • What special characters aside from apostrophe and quote mark have you encountered issue with? If you simply set a field with value from data control (textbox, combobox, checkbox, etc) there is no issue: `Me.fieldname = Me.controlname`. Yes, use CSql() in place of safeEntry(). Review https://stackoverflow.com/questions/49509615/how-do-i-use-parameters-in-vba-in-the-different-contexts-in-microsoft-access – June7 May 31 '21 at 17:43
  • @June7 I haven't run into issues with any others, but I want to prepare for the worst. The links you sent were also very helpful, thank you! – camtaylor01 May 31 '21 at 18:04
  • Don't escape, but use parameters. See [How do I use parameters in VBA in the different contexts in Microsoft Access?](https://stackoverflow.com/q/49509615/7296893) – Erik A May 31 '21 at 18:48
  • It will work. How many "unsafe" characters can a user input for an EP number? @ErikA: Good advice, but this not a query but a filter for a form. – Gustav May 31 '21 at 20:16
  • @Gustav Form filters are in the answer on there, if you use `DoCmd.OpenForm` you set parameters with `DoCmd.SetParameter`, there's more details in the linked answer. For domain aggregates (the `DCount`), there's `TempVars`, which is a little more hacky. – Erik A May 31 '21 at 20:21
  • @Gustav if they're entering the number correctly, it will take the form EPyymm-00 where 0 represents any number 0-9. So in theory, there isn't anywhere where a user could input a problematic character if they're doing it properly, but we want to prepare for the worst case scenario. – camtaylor01 May 31 '21 at 21:32
  • Thanks @ErikA, I'll have to take a closer look into how parameters work in this scenario! – camtaylor01 May 31 '21 at 21:33
  • @ErikA: Oh, sorry, should have read more than the title. – Gustav Jun 01 '21 at 05:45

0 Answers0