SQL Injection in Access

[David W. Fenton]

It's common to think that the dangers of SQL injection are limited
to web applications, but the fact is, they are not. I had never
given the possibility much thought until I saw this thread on
StackOverflow.com:

http://stackoverflow.com/questions/512174/non-web-sql-injection

I was skeptical about how dangerous it could be, since the biggest
dangers in SQL injection come from user input that manages to change
the SQL string to include multiple SQL statements (see the cartoon
about the kid whose name is "Robert'); DROP TABLE Students;"), but
there are still possible dangers from SELECT statements that could
be exploited to return all rows in a table. With sensitive data,
this could be a problem.

I wrote a long post considering the issues in Access. The whole
subject is worthy of significant consideration and discussion by
Access developers, I think.

 

[Michael Gramelspacher]

It's common to think that the dangers of SQL injection are limited
to web applications, but the fact is, they are not. I had never
given the possibility much thought until I saw this thread on
StackOverflow.com:

http://stackoverflow.com/questions/512174/non-web-sql-injection

I was skeptical about how dangerous it could be, since the biggest
dangers in SQL injection come from user input that manages to change
the SQL string to include multiple SQL statements (see the cartoon
about the kid whose name is "Robert'); DROP TABLE Students;"), but
there are still possible dangers from SELECT statements that could
be exploited to return all rows in a table. With sensitive data,
this could be a problem.

I wrote a long post considering the issues in Access. The whole
subject is worthy of significant consideration and discussion by
Access developers, I think.

Do you perhaps have an example of SQL injection against an Access table?

It seems that JET cannot run multiple SQL statements.

 

[Jeff Boyce]

David

Like Michael, I've looked into the possibility of concatenated SQL
statements (?SQL injection attack?) through an Access front-end. In my
testing, I was also unable to get Access to accept a multiple SQL statement
string and execute it.

Of course, I didn't test exhaustively, or I'd be ... exhausted.

Please provide a link to this dis-feature!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[David W. Fenton]

Do you perhaps have an example of SQL injection against an Access
table?

It seems that JET cannot run multiple SQL statements.

This is precisely the question I had. And, indeed, I asked exactly
that question in the discussion at the cited URL.

I suggest you actually read it before posting again.

 

[David W. Fenton]

Like Michael, I've looked into the possibility of concatenated SQL
statements (?SQL injection attack?) through an Access front-end.
In my testing, I was also unable to get Access to accept a
multiple SQL statement string and execute it.

Of course, I didn't test exhaustively, or I'd be ... exhausted.

Please provide a link to this dis-feature!

Read the discussion at the cited URL. This very question is
discussed (I was the one who brought it up!).

Geez, people. My whole point in posting was for you to go read the
discussion there. If you're not going to bother with that, why did I
bother posting?

 

[Albert D. Kallal]

Do you perhaps have an example of SQL injection against an Access table?

It seems that JET cannot run multiple SQL statements.

does not matter. Consider expressions like:

LastName = '(select id from table1 where id = shell("delete c:\*.*")

You have vba functions allowed in our sql, and some of it can be quite
nasty...

You might have a prompt like:

Enter account to delete

While "*" likely will not delete all of them, you might in place of

1235

enter:

id in (select id from tableCustomer)

So, in forms where you prompt the user, there is possible cases in which a
expressions, vba function such as eval(), or the shell command can be enter.
And, in addition my above example is a sub-query.

It is going to depend on how you setup the form + prompts for parameters to
a query, but since jet sql does allow most vba functions, this is a
legitimate topic and one would use additional caution if you were building
some type of public kiosk system etc....

Use of the "shell" command in some expressions could result in the user
having a command prompt window being launched, or as my above shell command
shows...deleing all files on a hard drive directory.

 

[David W. Fenton]

does not matter. Consider expressions like:

LastName = '(select id from table1 where id = shell("delete
c:\*.*")

Doesn't Jet 4 since SP 7 or so block that as an "unsafe expression?"

You have vba functions allowed in our sql, and some of it can be
quite nasty...

You might have a prompt like:

Enter account to delete

While "*" likely will not delete all of them, you might in place
of

1235

enter:

id in (select id from tableCustomer)

As I explained in the longer of the two posts of mine in the cited
URL, I would never use an unvalidated textbox to collect that kind
of user input. If I were asking a user to choose an account to
delete, I'd present a list of those accounts, and given them
absolutely no opportunity to type in arbitrary text (the InputBox()
function is a perfect example of such a vulnerable method for
collecting user input).

Would anybody with any sense do anything else? And not even to
prevent SQL injection, but simply because you want the user to be
choosing a valid account, so they don't waste their time.

So, in forms where you prompt the user, there is possible cases in
which a expressions, vba function such as eval(), or the shell
command can be enter.

Again, is it not the case that this would be prohibited by Jet?

And, in addition my above example is a sub-query.

I can't see how in a DELETE operation you would ever allow a user to
enter arbitrary text.

It is going to depend on how you setup the form + prompts for
parameters to a query, but since jet sql does allow most vba
functions, this is a legitimate topic and one would use additional
caution if you were building some type of public kiosk system
etc....

I don't think I've ever written an application that executes
INSERT/DELETE/UPDATE/APPEND operations in which I allowed the user
to input free text that would end up in the WHERE clause (which is,
I think, the only vulnerability).

I really do think that SELECT statements are the only place where
you risk exposing data to users that they should not see.

Use of the "shell" command in some expressions could result in the
user having a command prompt window being launched, or as my above
shell command shows...deleing all files on a hard drive directory.

Again, I think that "unsafe expressions" are now blocked, since Jet
4 SP7 on (we're now at SP9, no?).

I suggest that this discussion here in microsoft.public.access would
be much, much more profitable if those interested in posting here
would read the discussion at the cited URL in full. All three of the
followups so far are just recapitulating things that have already
been discussed there.

I really was hoping for a higher-quality discussion in this forum.

 

[Sylvain Lafontaine]

You speak a lot about validating all the data coming from the InputBox and
other textboxes. However, don't forget that many SQL Injection attacks have
used data already stored in a table. Many (?) people think about sanitizing
the data coming directly from an user through input boxes but many forgive
that often, you must do the same with data coming from tables.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[David W. Fenton]

You speak a lot about validating all the data coming from the
InputBox and other textboxes. However, don't forget that many SQL
Injection attacks have used data already stored in a table. Many
(?) people think about sanitizing the data coming directly from an
user through input boxes but many forgive that often, you must do
the same with data coming from tables.

This is an excellent point. I don't know that it's that essential
for an Access application (as opposed to a Web app), but I'm willing
to be convinced.

You should go over to StackOverflow.com and post that point.

 

[Sylvain Lafontaine]

I tried to go to StackOverflow.com and post that point but the site told me
that I've not an enough good reputation to do that. Very strange that you
can post questions and answers but no comment to an answer.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[David W. Fenton]

I tried to go to StackOverflow.com and post that point but the
site told me that I've not an enough good reputation to do that.
Very strange that you can post questions and answers but no
comment to an answer.

Well, until you can post comments, post it as a standalone answer.
Draw it out a bit, the way I did with my long post. It makes the
topic more complete, and it potentially builds reputation.

What do you think of the concept and implementation of
StackOverflow? I find it quite compelling as a model for this kind
of thing. It's a form of wiki, but with authorship (which wikis
don't have). I find it rather difficult to keep track of topics I'm
interested in (since the order of posts changes according to the
votes for them), but it's not as annoying now as it was to me at
first.

 

[MikeR]

It's common to think that the dangers of SQL injection are limited
to web applications, but the fact is, they are not. I had never
given the possibility much thought until I saw this thread on
StackOverflow.com:

http://stackoverflow.com/questions/512174/non-web-sql-injection

I was skeptical about how dangerous it could be, since the biggest
dangers in SQL injection come from user input that manages to change
the SQL string to include multiple SQL statements (see the cartoon
about the kid whose name is "Robert'); DROP TABLE Students;"), but
there are still possible dangers from SELECT statements that could
be exploited to return all rows in a table. With sensitive data,
this could be a problem.

I wrote a long post considering the issues in Access. The whole
subject is worthy of significant consideration and discussion by
Access developers, I think.

I'm so far from being an expert, I had to look up how to spell it, but wouldn't
parameterized queries defeat SQL injection?
Mike

 

[David W. Fenton]

I'm so far from being an expert, I had to look up how to spell it,
but wouldn't parameterized queries defeat SQL injection?

Yes -- that's a point made over and over in the cited discussion.
But parameter queries are not so easily used in Access in many
contexts. And they are not usable at al when you're seeking multiple
values.

 

[James A. Fortune]

Again, I think that "unsafe expressions" are now blocked, since Jet
4 SP7 on (we're now at SP9, no?).

I suggest that this discussion here in microsoft.public.access would
be much, much more profitable if those interested in posting here
would read the discussion at the cited URL in full. All three of the
followups so far are just recapitulating things that have already
been discussed there.

I really was hoping for a higher-quality discussion in this forum.

I read the cited URL. I trust this will not be a recapitulation.

"Given that Jet cannot execute multiple SQL statements separated by ";",
I'm having a hard time conceiving of any SQL injection threat with a Jet
back end. But perhaps that's because I'm just not as imaginative as
hackers." -- David Fenton

"If anyone can show an JET example in which the user can execute a sql
statement by injection, I am all ears as I don’t think it is possible
with dao + jet." -- Albert Kallal (Access MVP)

You just lack experience David, not imagination .

In (this newsgroup):

http://groups.google.com/group/microsoft.public.access/msg/db173c2291c1396f

I said (excuse the long sentence):

"Nearly all of the known SQL injection techniques simply don't apply to
Access forms, especially ones that do some data validation. Although I
know of one rather bizarre example of the possibility of SQL injection
in Access discovered by accident while testing some rather nonstandard
SQL syntax for a union query posted in another newsgroup, I think you
would be hard pressed to come up with an example of SQL injection on an
Access form in an MDE, even with SQL Server as the backend."

BTW, anyone who uses an InputBox to get unfiltered information for SQL
gets what they paid for . At the risk of having a low quality
discussion, I am loath to reveal any more than what I've stated, but
your and Albert's assumption about Access not being able to run multiple
SQL strings in one query isn't entirely correct. I sense that it may be
possible to open the crack in Access/JET SQL further. Just because you
don't know how it can be done doesn't mean it can't be done. Anyway,
the possibility of SQL injection in Access is much more limited than
with, say, SQL Server under ASP.NET on the web. It is quite easy to
safeguard against it.

James A. Fortune
(e-mail address removed)

 

[Jamie Collins]

parameter queries are not so easily used in Access in many
contexts. And they are not usable at al when you're seeking multiple
values.

FWIW an ADO Command object can be used for multiple paramters values.

Jamie.

--

 

[Jamie Collins]

I read the cited URL. I trust this will not be a recapitulation.

Just to clarify, I think discussion I think it is fair to say that the
sequence of events is that Fenton first thought that SQL injection wasn't
much of a problem for Access/Jet but retained an open mind and after some
discussion he 'saw the light' then took the time to post he thoughts at
length about how to avoid the problem in Access. Outstnading behaviour for
community member, I'd say

P.S. I'm loving StackOverflow. I'll never go back to newsgroups now ;-)

Jamie.

--

 

[David W. Fenton]

At the risk of having a low quality
discussion, I am loath to reveal any more than what I've stated,
but your and Albert's assumption about Access not being able to
run multiple SQL strings in one query isn't entirely correct. I
sense that it may be possible to open the crack in Access/JET SQL
further.

With Jet on the back end, it can't be done. Period.

The traditional point of the vulnerability with multiple SQL
statements is converting a SELECT statement with a WHERE clause in
which you accept user input for the value searched for. When the
user inputs data that terminates the original WHERE clause and then
issues another SQL statement, you've got the classic SQL injection
vulnerability.

Access/Jet will simply error out on such a SQL statement, in all
cases.

If you have evidence that there are situations where this is not the
case, I think you should tell us. Our apps are not out there in the
wild on the Internet, so it's not like this could lead to some kind
of major breach of Access/Jet integrity.

 

[James A. Fortune]

With Jet on the back end, it can't be done. Period.

The traditional point of the vulnerability with multiple SQL
statements is converting a SELECT statement with a WHERE clause in
which you accept user input for the value searched for. When the
user inputs data that terminates the original WHERE clause and then
issues another SQL statement, you've got the classic SQL injection
vulnerability.

Access/Jet will simply error out on such a SQL statement, in all
cases.

If you have evidence that there are situations where this is not the
case, I think you should tell us. Our apps are not out there in the
wild on the Internet, so it's not like this could lead to some kind
of major breach of Access/Jet integrity.

O.K., David. You've convinced me that it's not a big deal. Start here:

http://groups.google.com/group/comp.databases.ms-access/msg/a387a19df784d997

Note that without Jamie posting his syntax for reading JET data directly
from Excel we would likely have not known about that either. Thanks Jamie.

James A. Fortune
(e-mail address removed)