SQL Injection Characters Cleaning Function

Discussion in 'Access Security' started by Yadda, Aug 2, 2010.

  1. Yadda

    Yadda Guest

    'Clean troublesome characters used in SQL INJECTION attacks.
    Function cleanUserInput(strUserInput As String) As String

    Dim cleanChar As String
    Dim singleQuote As String
    Dim semiColon As String
    Dim doubleDash As String
    Dim commentStart As String
    Dim commentEnd As String

    cleanChar = Chr(32) 'space character which the SQL parser ignores
    singleQuote = Chr(39)
    semiColon = Chr(59)
    doubleDash = Chr(45) & Chr(45)
    commentStart = Chr(47) & Chr(42)
    commentEnd = Chr(42) & Chr(47)

    debug.print singleQuote, semicolon, doubleDash, commentStart,

    ' replace single quote with two single quotes; also properly
    formats legit possession and contractions
    strUserInput = Replace(strUserInput, singleQuote, singleQuote &

    ' remove semicolon command delimiter
    strUserInput = Replace(strUserInput, semiColon, cleanChar)

    ' remove double dash comment
    strUserInput = Replace(strUserInput, doubleDash, cleanChar)

    ' remove slash begin comment
    strUserInput = Replace(strUserInput, commentStart, cleanChar)

    ' remove slash end comment
    strUserInput = Replace(strUserInput, commentEnd, cleanChar)

    'remove xp_ external commands
    strUserInput = Replace(strUserInput, "xp_", cleanChar)

    cleanUserInput = Trim(strUserInput)

    Debug.Print cleanUserInput

    End Function
    Yadda, Aug 2, 2010
    1. Advertisements

  2. Yadda

    Arvin Meyer Guest

    Arvin Meyer, Aug 2, 2010
    1. Advertisements

  3. Yadda

    LightBulb Guest

    Does JET db engine do it owns sql injection attack checking?
    LightBulb, Aug 2, 2010
  4. That's not entirely true, Arvin. Traditionally, people think of the
    "; DROP TABLE tblInventory;" type of SQL injection, or injecting DDL
    statements. That definitely *is* impossible in Access. Likewise, you
    can't inject additional DML statements, such as "; DELETE * FROM
    tblInventory;", but you're not home free with that, either, since
    WHERE clauses are the main vulnerability.

    But attacks that reveal data structure and that can return more data
    (manipulating the WHERE clause) can be "injected." The former can
    become the basis of an attack (and is valid only for Jet/ACE-based
    websites) while the latter can reveal data that is not supposed to
    be accessible. The most damaging potential vulnerability is if you
    are collecting user input for a DELETE (or any other DML statement)
    -- in that case, it's possible to convert a single-row delete into
    something that deletes all the records in a table.

    Of course, it's very uncommon for a delete operation in an Access
    application to be launched from a user interface that allows the
    user to input free text, mostly since the vast majority of deletes
    in an Access database don't involve a SQL statement in the first

    For more on this, see a StackOverflow post of mine from some time
    ago considering the issue (I was doubtful there was any risk, too,
    but in the course of studying the issue, changed my mind):

    http://tinyurl.com/2docw6h =>

    The post by "onedaywhen" referred to in there is this one:

    http://tinyurl.com/2bgo7ug =>

    It's worth a read as it draws out how this is done.

    Keep in mind that it's pretty easy to avoid any of these risks in
    Access simply by limiting the user's input choices. If they can only
    choose existing values and there's no place for them to type in free
    text as criteria, there's no possibility for any form of SQL

    Also keep in mind that my faith in Application.BuildCriteria was
    misplaced, as it manages to process some of the injection methods
    without mangling them so that they break.
    David W. Fenton, Aug 2, 2010
  5. What do you mean by "SQL Injection"?

    Jet/ACE is not vulnerable to the most common type of SQL injection
    because it can't process multiple SQL statements.

    It is vulnerable to two types of injection risks:

    1. probing for database structure, which can then be used for
    further attackes.

    2. manipulation of the WHERE clause to return excess data in SELECTs
    and to apply UPDATEs/INSERTs/DELETEs to more records than intended.

    Most Access applications don't have much in the way of risk because
    free-text user input is not requested for querying purposes.
    Likewise, unlike a web page with a GET or POST, there are no
    discoverable parameters that can be manipulated.

    From outside Access, there are only the same risks as outlined
    above. But parsing the SQL string has never been considered a
    reliable way to protect against SQL injection. The real way to
    protect against it in Jet/ACE is by using parameterized SQL
    statements (as it is with every other database engine).
    David W. Fenton, Aug 2, 2010
  6. Not necessary:
    Not necessary:
    Not necessary:
    Not necessary:
    Doesn't work:
    Not necessary:
    Do you know anything at all about Jet/ACE SQL? Apparently you don't,
    as you've just posted a function that "fixes" things that don't
    exist in Jet/ACE SQL. All of the things you're trapping for (with
    the exception of single quotes) would be rejected by the Jet/ACE SQL
    parser as invalid SQL.

    And the doubling up of the quotes doesn't work (though it may be
    that if you're using Jet/ACE via ODBC or OLEDB that things get
    converted somewhere along the line).

    You should test your code in Access to see if it works before
    posting. And for something like this, you should test the
    un-scrubbed SQL to see if Jet/ACE parses it or not. Had you done
    these things, you'd have discovered that your posts is A COMPLETE

    And, of course, it's the wrong way to approach protecting from SQL
    injection, anyway -- the only reliable method for that is to use
    parameterized SQL. If you do that, you don't have to worry about any
    of these other issues at all.
    David W. Fenton, Aug 2, 2010
  7. Yadda

    Yadda Guest

    I was looking for comments and should have said that. The function was
    not assuming the database back-end as ACCESS can use ODBC to connect to
    and SQL server too. I was not sure of JET DB as it is not a an SQL
    Server db.
    Yadda, Aug 3, 2010
  8. Yadda

    Arvin Meyer Guest

    Arvin Meyer, Aug 3, 2010
  9. ODBC translates things from Jet's SQL dialect to the back end's
    dialect, so if you're using ODBC, the back end SQL dialect is
    unimportant, unless you're using passthrough queries (the only type
    that are sent directly to the server without the intervention of
    either Jet/ACE or ODBC).

    It's still the WRONG way to address SQL injection threats -- the
    only reliable way to insure against it is to use parameterized

    But really, the only environment in which there's any risk of SQL
    injection with Access is if you're using a Jet/ACE data file behind
    a website. When using Access itself, you have the front-end
    MDB/ACCDB accessible to you and so you can figure out the table
    structures and write your own queries that delete data or drop
    tables or whatever. There is very little in the Access UI that would
    allow any form of SQL injection.

    The only scenario I can think of is if you've created a
    query-by-form interface and allowed the user to type free-form text
    as criteria. The risk there is avoided by using parameterized SQL,
    just as it is elsewhere, rather than trying to parse out characters
    that might or might not be used for attempting SQL injection.

    Frankly, it's a very, very small risk.

    Given that your function is the wrong way to address the problem,
    it's really of no use whatsoever (even if any of it applied to
    Jet/ACE/ODBC SQL executed in Access).
    David W. Fenton, Aug 3, 2010
  10. Arvin, that's not the definition of SQL injection at all, just the
    most common risk.

    Jet/ACE *is* susceptible to schema probing, outlined in
    non-Access-specific terms here, for instance:


    It's also susceptible to manipulation of the WHERE clause to return
    more rows than requested in SELECT statements (of the 1=1 variety),
    and of applying DELETEs/UPDATEs to all rows instead of a subset
    (using the same trick).

    All of these count as SQL injection, and these latter two types are
    both possible with Jet/ACE.

    They are unlikely for two reasons:

    1. schema probing is really only relevant to a Jet/ACE database used
    as the back end of a website, and that's mostly not an appropriate
    use of Jet/ACE in the first place. It's irrelevant for standard
    Access apps, because the schema is much more easily accessible via
    other methods.

    2. SQL injection is most relevant for applications that do all their
    editing with SQL. While that would apply to a website backed by a
    Jet/ACE database (see #1), it doesn't apply to a standard Access
    app, which doesn't use SQL to edit bound data. That is, most of the
    data editing in an Access application is via bound forms rather than
    through SQL, so it's not really going to be susceptible to SQL

    But that doesn't mean we shouldn't think about the risks and take
    appropriate action. If you have any free-text fields in a
    query-by-form interface, you should look at it carefully,
    particularly if you are driving updates/deletes with that interface.

    Otherwise, it's entirely a non-issue.

    And scrubbing SQL strings is not the best way to address it at all
    -- you avoid it entirely by always parameterizing your query
    David W. Fenton, Aug 3, 2010
  11. David W. Fenton, Aug 3, 2010
  12. Yadda

    Yadda Guest

    Like firewall security multiple level defense is recommended for most
    threats. Calling a cleaning function is very little overhead so what
    does it hurt?
    Yadda, Aug 4, 2010
  13. When it doesn't even clean out anything that makes a difference?
    David W. Fenton, Aug 4, 2010
  14. Yadda

    Yadda Guest

    A lot of companies only use ODBC connections defined on servers. What
    if the developer of the GUI end doesn't know the details of the ODBC
    connection and/or if the database administration dept have changed out
    the db back-end? You will notice the function is only passed single
    string and makes no assumptions of the database.
    Yadda, Aug 5, 2010
  15. What does that have to do with Access?
    It's a VBA function. What environments use VBA to manipulate Jet/ACE
    data via ODBC?

    Certainly not Access!
    David W. Fenton, Aug 5, 2010
  16. Yadda

    Yadda Guest

    I use Access VBA to connect MS SQL Server (internal) and MySQL Server
    (Internet SSL) via ODBC.
    Yadda, Aug 6, 2010
  17. But unless you're using ADO or passthroughs, it's still Jet/ACE's
    SQL dialect within Access, so that your function serves no actual

    And that's all the more reason why you should be using parameterized
    server-side objects instead of depending on scrubbing the SQL. The
    former will always protect you, while the latter will only catch the
    exploits you know to check for.
    David W. Fenton, Aug 6, 2010
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.