Preventing SQL injection without annoying users? (1 Viewer)

[Zedster]

For some time I have been designing all my database with unbound forms that input and output data on a SQL server using VBA. To protect against SQL injection on all my forms for adding and editing records I use a simple function I created:

Public Function MakeSqlSafe(varInput As Variant) As String

   Dim strInput As String: strInput = CStr(varInput)

   If InStr(strInput, ";") > 0 Then
       strInput = Replace(strInput, ";", ",")
   End If
   If InStr(strInput, "drop") > 0 Then
       strInput = Replace(strInput, "drop", "droop")
   End If
   If InStr(strInput, "'") > 0 Then
       strInput = Replace(strInput, "'", "''")
   End If
   If InStr(strInput, "--") > 0 Then
       strInput = Replace(strInput, "--", " - - ")
   End If
   If InStr(strInput, "/*") > 0 Then
       strInput = Replace(strInput, "/*", "/ *")
   End If
   If InStr(strInput, "*/") > 0 Then
       strInput = Replace(strInput, "*/", "* /")
   End If
   If InStr(strInput, "xp_") > 0 Then
       strInput = Replace(strInput, "xp_", "x p _")
   End If
    
   MakeSqlSafe = strInput


End Function

It works by replacing potentially harmful characters/words in the form with less harmful ones so the SQL doesn't see the harmful ones. But at the same time it actually changes what the user inputs, which can cause upset. I have a couple of questions:

• Is there any way to protect against SQL Injection without being so intrusive?
• Have I covered everything?

 

[Minty]

If you use bound forms then SQL Injection is almost unheard of.
I'm intrigued why you would use unbound forms for most database tasks?

Even allowing for all, that if you use parameterised queries to perform your updates and inserts, I also think that a SQL injection problem is almost 99% negated?

 

[The_Doc_Man]

I'm with Minty here. Allowing direct formulation of SQL is an incredibly rough strategy to police. What I did was to decide ahead of time what actions would be allowed and then generate some basic queries where I could "fill in the blanks" of a where clause that allowed specific actions to occur. The combo boxes on the forms selected things that could be done - like setting the state code in the records selected by other combo boxes. There was no way they could inject anything. Letting a user have direct access to SQL is a nightmare because that language has really complex abilities. If you happened to have missed a keyword, then you are skating on really thin ice.

 

[theDBguy]

Hi. Just curious, inputting data into SQL Server is not the same as executing SQL statements on the server. Are you doing the latter?

 

[Zedster]

I'm intrigued why you would use unbound forms for most database tasks?

There are two main reasons:

• Over the years I have had a number of occasions when inexperienced uses would modify/delete data and then close the form not realising they had done so. So I started to use unbound forms to have more control over users. Generally the forms I display have view permission only. If a user wishes to make an addition they click an edit button and the fields become editable, the edit button changes caption to save changes and should changes be required the necessary SQL is fired across to the SQL server. If they make an accidental error and close down the form, the changes are not committed, they have to actively decide to click the "save button"
• A previous IT manager had concerns (rightly or wrongly) regarding Access pulling data across the server and i went this route to minimise network traffic, all queries are filtered on the SQL server.

I have been working this way for so long now, it has become the standard way I think when it comes to Access.

 

[Zedster]

Hi. Just curious, inputting data into SQL Server is not the same as executing SQL statements on the server. Are you doing the latter?

I run all queries that pull or push data on the SQL server itself. Some of my databases don't have any tables, not even linked.

 

[theDBguy]

I run all queries that pull or push data on the SQL server itself. Some of my databases don't have any tables, not even linked.

Ah, that makes sense why you would be concerned about SQL injections. As was already mentioned, the best practice approach for preventing SQL injections is to use parameterized queries. Cheers!

 

[Isaac]

For some time I have been designing all my database with unbound forms that input and output data on a SQL server using VBA. To protect against SQL injection on all my forms for adding and editing records I use a simple function I created:

Public Function MakeSqlSafe(varInput As Variant) As String

   Dim strInput As String: strInput = CStr(varInput)

   If InStr(strInput, ";") > 0 Then
       strInput = Replace(strInput, ";", ",")
   End If
   If InStr(strInput, "drop") > 0 Then
       strInput = Replace(strInput, "drop", "droop")
   End If
   If InStr(strInput, "'") > 0 Then
       strInput = Replace(strInput, "'", "''")
   End If
   If InStr(strInput, "--") > 0 Then
       strInput = Replace(strInput, "--", " - - ")
   End If
   If InStr(strInput, "/*") > 0 Then
       strInput = Replace(strInput, "/*", "/ *")
   End If
   If InStr(strInput, "*/") > 0 Then
       strInput = Replace(strInput, "*/", "* /")
   End If
   If InStr(strInput, "xp_") > 0 Then
       strInput = Replace(strInput, "xp_", "x p _")
   End If
  
   MakeSqlSafe = strInput


End Function

It works by replacing potentially harmful characters/words in the form with less harmful ones so the SQL doesn't see the harmful ones. But at the same time it actually changes what the user inputs, which can cause upset. I have a couple of questions:

• Is there any way to protect against SQL Injection without being so intrusive?
• Have I covered everything?

I would be very surprised if worrying about SQL Injection, within the context of an Access database whether bound or unbound form, has any significant value.

But to directly answer your question: You could replace the so-called "harmful" characters with something very unique, like:

97F217ED-7694-4C41-6F95-7C7E44D08412

.... and then, right after the record is Inserted, you could run an update to change the text back.

I really don't think any of this is needed.

 

[Pat Hartman]

Over the years I have had a number of occasions when inexperienced uses would modify/delete data and then close the form not realising they had done so.

Not possible if you understand how bound forms work and use the appropriate events to block all disallowed actions and prevent the entry of bad or incomplete data.

Maybe it's time to learn the "Access way" so you can take advantage of the power of Access which is bound forms.

 

[arnelgp]

Who is injecting? What?
It is your program so it is the one injecting?
Even if some would, they will never pass it to your function (makesqlsafe).
Injection will come from external of your access or even outside sql server.
The question is, what is the worth?
And who would care to hack?
Too much Mission Impossible movie.
my suspicion is that the chinese are involved?