Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2007
    Posts
    348

    Unanswered: Avoiding SQL Injection in Access

    If I need to use SQL to insert text from an unbound form or to do some quick comparisons, with the help of VBA, how can I avoid SQL injection?

    I'm copying the contents of emails into a memo field. If I do any SQL with this (like comparing that field to the stuff already in the table to check for dupes) I run a really good chance of capturing a ' or " which causes an unintentional form of SQL injection. Obviously Access has a way around this, as a person can query these fields and not encounter the same error.

    As I understand it, some folks would have you replace the ' or " with and Ascii value. It seems like this would mean I would have to do this coming and going. Meaning turn "can't" into "can" & chr(39) & "t" going to the table and change it back to "can't" bringing it back out for queries, forms or reports. I assume this would require using a classes' Set, Let and Get. I assume there is an easier/more native way to do this in Access.

    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I love questions like this!
    I'm posting partly because I want to subscribe and partly to give my 1 cent (I'll come up with something better later, no doubt ).

    Generally I have used the replace function to remove invalid characters / phrases. A handful of these are:
    • '
    • "
    • =
    • ;
    • *
    • / and \
    • DELETE
    • TRUNCATE
    • INSERT
    • UPDATE
    • SELECT
    • GO
    • LIKE
    • AND
    • OR

    Oh, one other thing - you may wish to escape single quotes with doubles
    (E.g. O'Rielly needs to become O''Reily)
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2007
    Posts
    348
    Delete, \ and / are great examples of things that would all show up legitimately in these strings, that would need to be able to save and retrieve.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Generally if you remove the semi-colon then other commands should not be able to be entered...

    Actually, I forgot to add ampersand (*) because as you mentioned, Chr() can be used!

    It is possibly overkill to remove all of the above, but there are certainly key ones!
    \ and / are escape characters in some languages, I don't think VBA has these
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2007
    Posts
    348
    Quote Originally Posted by georgev
    Generally if you remove the semi-colon then other commands should not be able to be entered...
    Except that the docmd.runsql method seems to not care either way if there is or is not a semicolon (Using Jet DB still). I'm gonna have to test that theory. It's an interesting question.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Not sure if this is any help, but I've used the: = chr(10) & me!MyTextfield & chr(10) (or was it chr(34) - can't remember as of right now) and I've also used the 3 " to help with syntax.

    strSQL = "Select * from MyCustomerTable where PKCustomerID = """ & PKID & """"

    Using the 3 " I've avoided some of the ', *, and other syntax problems like in O'Brien.
    Last edited by pkstormy; 08-02-07 at 13:33.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Feb 2007
    Posts
    348
    I tried out the triple quotes. It seems to work about the same as the single quote. Meaning, if you have any double quotes as a part of your text, it may bomb your SQL as well. I'd still be interested to understand how Access avoids these issues in it's own managing of data.
    However, in terms of fixing things. It seems that it is a lesser of two evils. A person could either error check for ' or for ". It seems more reasonable to search a string for " and replace it with ' than the other way around. I think that will be my fix for now. To use triple quotes and all other " become '. That is, unless someone else has an idea.

  8. #8
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    One methods that isn't mentioned, is to use stored procedures/stored queries, and utilize the parameters collection of the manipulation method one is using (DA0, ADO, ADO.Net).

    In addition to avoiding SQL injection attacks, it also internationalizes the app.

    Regardless of locale, you can pass a valid date contained in a variable of datatype Date to a parameter of datatype Date, without any casting, which is not exactly the case when doing dynamic SQL. Same with decimal numbers and strings containing whatever ... it's a bit more code, and you'll stuff the query category full of querie, but...

    Then, using ADO, you can do something like this
    Code:
        Dim cmd            As ADODB.Command
        Dim prm1           As ADODB.Parameter
        Dim prm2           As ADODB.Parameter
        Dim prm3           As ADODB.Parameter
        Dim strSql         As String
        
        strSql = "UPDATE justtesting SET myTest = ?, myDate = ? WHERE id = ?"
        
        Set cmd = New ADODB.Command
        With cmd
            Set .ActiveConnection = CurrentProject.Connection
            
            .CommandText = strSql
            .CommandType = adCmdText
            
            Set prm1 = .CreateParameter("mytext", adVarWChar, adParamInput, 35)
            .Parameters.Append prm1
            prm1.Value = "as""d''##'fasdf"
            
            Set prm2 = .CreateParameter("mytimestamp", adDate, adParamInput)
            .Parameters.Append prm2
            prm2.Value = Now
            
            Set prm3 = .CreateParameter("mylong", adInteger, adParamInput)
            .Parameters.Append prm3
            prm3.Value = 42
            
            .Execute , , adExecuteNoRecords
        End With
        Set cmd = Nothing
    Roy-Vidar

  9. #9
    Join Date
    Feb 2007
    Posts
    348
    Wow, that's waaay over my head. I'll spend the weekend trying to make it make sense in my little head. I'll touch base with you on this early next week roy, thanks for the suggestion.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What is the BE? If it is JET there is not that much you can inject (TMK) although I think there are a few DDL statements you could employ.

    The best way to mitigate SQL Injection is to design your app so it is impossible. This means parameterising all your interaction with the BE. I haven't seen Roy's exact method before but it is a variation on the same theme. I typically use stored procedures but you can also use sp_executesql. Removing characters is not sufficient defence. It is too easy to miss stuff - at the end of the day you are gambling pitting your knowledge against experts. At the absolute minimum, if you are not prepared to parameterise your input, you should only accept allowable input rather than attempt to exclude potentially suspicious input. Obviously, if you are concatenating names or something similarly "free" then you will struggle here.

    This is a nice example of SQL Injection.
    http://www.rockyh.net/AssemblyHijack...Hijacking.html
    Last edited by pootle flump; 08-03-07 at 08:35.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just remembered Jeff Smith had a (typically) good blog post on the topic. .
    http://weblogs.sqlteam.com/jeffs/arc.../21/10728.aspx
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2007
    Posts
    348
    Pootle,
    Thanks for the info. I'll have much reading to do.
    I am using Jet. I'm not worried about injection in the sense that someone will hack my DB, although it makes for an interesting test case. My greater concern is protecting my user from errors caused by unintentional injection. Meaning, without some protection, placing text in the application with " or ', depending on my approach, would cause an error and their updates wouldn't go through. An error kicks out and bedlam begins.

    At this point, I have used pkstormy's triple quotes technique. Then I scan each string for " and turn it to ' if I find it. It could be argued that this harms readability but I feel that it is infrequent enough of a case and that it is a close enough representation to get us by.
    I'm still planning on reading Roy and your articles because I'm sure it will come up again and, if my world goes right, then maybe I'll even have to worry about someone caring about hacking my DB. So it's good info for me. So, I'll almost certainly be back with questions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •