SQL Injection Characters Cleaning Function

  • Thread starter Thread starter Yadda
  • Start date Start date
Y

Yadda

'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,
commentEnd

' replace single quote with two single quotes; also properly
formats legit possession and contractions
strUserInput = Replace(strUserInput, singleQuote, 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
 
Arvin said:
Your code is welcome, but Access MDB and ACCDB files are not subject to SQL
Injection attacks.
Click to expand...

Does JET db engine do it owns sql injection attack checking?
 
Your code is welcome, but Access MDB and ACCDB files are not
subject to SQL Injection attacks.
Click to expand...

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
place.

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 =>
http://stackoverflow.com/questions/512174/non-web-sql-injection/52238
2#522382

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

http://tinyurl.com/2bgo7ug =>
http://stackoverflow.com/questions/512174/non-web-sql-injection/51515
0#515150

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
injection.

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.
 
Does JET db engine do it owns sql injection attack checking?
Click to expand...

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).
 
'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)
Click to expand...

Not necessary:
semiColon = Chr(59)
Click to expand...

Not necessary:
doubleDash = Chr(45) & Chr(45)
Click to expand...

Not necessary:
commentStart = Chr(47) & Chr(42)
Click to expand...

Not necessary:
commentEnd = Chr(42) & Chr(47)

debug.print singleQuote, semicolon, doubleDash, commentStart,
commentEnd
Click to expand...

Doesn't work:
' replace single quote with two single quotes; also properly
formats legit possession and contractions
strUserInput = Replace(strUserInput, singleQuote,
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)
Click to expand...

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

cleanUserInput = Trim(strUserInput)

Debug.Print cleanUserInput

End Function
Click to expand...

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
WASTE OF TIME.

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.
 
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
WASTE OF TIME.

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.
Click to expand...

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.
 
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.
Click to expand...

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
queries.

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).
 
No. SQL-Injection requires multiple SQL statements to be executed.
Access is incapable of multiple SQL statements in the same query
procedure.
Click to expand...

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:

http://unixwiz.net/techtips/sql-injection.html

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
injection.

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
criteria.
 
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
queries.

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).
Click to expand...

Like firewall security multiple level defense is recommended for most
threats. Calling a cleaning function is very little overhead so what
does it hurt?
 
Like firewall security multiple level defense is recommended for
most threats. Calling a cleaning function is very little overhead
so what does it hurt?
Click to expand...

When it doesn't even clean out anything that makes a difference?
 
When it doesn't even clean out anything that makes a difference?
Click to expand...

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.
 
A lot of companies only use ODBC connections defined on servers.
Click to expand...

What does that have to do with Access?
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.
Click to expand...

It's a VBA function. What environments use VBA to manipulate Jet/ACE
data via ODBC?

Certainly not Access!
 
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!
Click to expand...

I use Access VBA to connect MS SQL Server (internal) and MySQL Server
(Internet SSL) via ODBC.
 
I use Access VBA to connect MS SQL Server (internal) and MySQL
Server (Internet SSL) via ODBC.
Click to expand...

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
purpose.

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.
 
Back
Top