Database security

[Steve]

I am planning on deploying a fairly unsophisticated web application
using asp. The app will read a user's record from an Access database
stored on the web server (the database file will not be anywhere
within wwwroot), display the contents of the record (in a pre-filled
out form) to the user, allow the user to makes any changes in the
form, and then submit the form information, which would then be
written to a new database.

My question goes to security. My plan is for each record in the
database to have a 4-digit PIN, so that only a user who can present
the right combination of account number (stored in a separate field)
and PIN can access their respective record. This seems too simple to
provide effective security. Am I right?

Also, what are the possible exploits a hacker could use to gain access
to the database. OK, that's a real open-ended and vague question, but
there it is anyway.

Finally, does using SQL server (as opposed to a simple DSN-less
connection to an Access database) provide better security from
unauthorized access to my data, or does it just multiply the
opportunities for intrusion?

[Brynn]

If you are going to have more than 8 - 10 users conecting to your
Access Database at one time, then I would not use Access ... it will
most likely not work very well for you.

As far as the data is concerned ... I am not sure that having your
Access DB on your server in a non-web directory will be any less
secure then having a SQL database ... although many times the SQL
database is on a seperate server all together .... this alone may
provide a little more security, but as long as permissions are setup
properly, you can secure about any file just as tight IMO.

I have heard many people say that you should store your connection
string in the global.asa file for security reasons ... and I do that.

I would be interested in hearing from anyone that has information on
whether or not DSN or DSN-less provides any type of security
difference ... that is as long as permissions to the database and DSN
were setup properly. DSN / DSN-less I thought was always just a
performance issue.

Also, if you insist on using Access ... I would suggest looking into
using Access stored queries (similar to stored procedures ... but more
limited). I think that stardeveloper.com has a tutorial still on that
subject ... at least they did.

Good luck,

Brynn
[url]www.coolpier.com[/url]

On 7 Jan 2004 09:04:27 -0800, [email]ursinho@yahoo.com[/email] (Steve) wrote:

>I am planning on deploying a fairly unsophisticated web application
>using asp. The app will read a user's record from an Access database
>stored on the web server (the database file will not be anywhere
>within wwwroot), display the contents of the record (in a pre-filled
>out form) to the user, allow the user to makes any changes in the
>form, and then submit the form information, which would then be
>written to a new database.
>
>My question goes to security. My plan is for each record in the
>database to have a 4-digit PIN, so that only a user who can present
>the right combination of account number (stored in a separate field)
>and PIN can access their respective record. This seems too simple to
>provide effective security. Am I right?
>
>Also, what are the possible exploits a hacker could use to gain access
>to the database. OK, that's a real open-ended and vague question, but
>there it is anyway.
>
>Finally, does using SQL server (as opposed to a simple DSN-less
>connection to an Access database) provide better security from
>unauthorized access to my data, or does it just multiply the
>opportunities for intrusion?

[Jeff Cochran]

On 7 Jan 2004 09:04:27 -0800, [email]ursinho@yahoo.com[/email] (Steve) wrote:

>I am planning on deploying a fairly unsophisticated web application
>using asp. The app will read a user's record from an Access database
>stored on the web server (the database file will not be anywhere
>within wwwroot), display the contents of the record (in a pre-filled
>out form) to the user, allow the user to makes any changes in the
>form, and then submit the form information, which would then be
>written to a new database.
>
>My question goes to security. My plan is for each record in the
>database to have a 4-digit PIN, so that only a user who can present
>the right combination of account number (stored in a separate field)
>and PIN can access their respective record. This seems too simple to
>provide effective security. Am I right?
>
>Also, what are the possible exploits a hacker could use to gain access
>to the database. OK, that's a real open-ended and vague question, but
>there it is anyway.

Easiest would be [url]http://www.yourdomain.com/yourdatabase.mdb[/url] which
would prompt them to download it. :)

Store the database outside the web folder structure.

>Finally, does using SQL server (as opposed to a simple DSN-less
>connection to an Access database) provide better security from
>unauthorized access to my data, or does it just multiply the
>opportunities for intrusion?

SQL is *always* better than Access, but you can easily break it's
security as well. SQL Injection is the most common technique, but
anything that allows a user to run a query might have issues. Stored
procedures and views can help.

Jeff

[Brynn]

On that note, here is a pretty good article describing SQL Injection

[url]http://www.sitepoint.com/article/794[/url]

Just found it

Brynn
[url]www.coolpier.com[/url]


On Wed, 07 Jan 2004 18:22:14 GMT, [email]jcochran.nospam@naplesgov.com[/email] (Jeff
Cochran) wrote:

>On 7 Jan 2004 09:04:27 -0800, [email]ursinho@yahoo.com[/email] (Steve) wrote:
>

>>I am planning on deploying a fairly unsophisticated web application
>>using asp. The app will read a user's record from an Access database
>>stored on the web server (the database file will not be anywhere
>>within wwwroot), display the contents of the record (in a pre-filled
>>out form) to the user, allow the user to makes any changes in the
>>form, and then submit the form information, which would then be
>>written to a new database.
>>
>>My question goes to security. My plan is for each record in the
>>database to have a 4-digit PIN, so that only a user who can present
>>the right combination of account number (stored in a separate field)
>>and PIN can access their respective record. This seems too simple to
>>provide effective security. Am I right?
>>
>>Also, what are the possible exploits a hacker could use to gain access
>>to the database. OK, that's a real open-ended and vague question, but
>>there it is anyway.

>
>Easiest would be [url]http://www.yourdomain.com/yourdatabase.mdb[/url] which
>would prompt them to download it. :)
>
>Store the database outside the web folder structure.
>

>>Finally, does using SQL server (as opposed to a simple DSN-less
>>connection to an Access database) provide better security from
>>unauthorized access to my data, or does it just multiply the
>>opportunities for intrusion?

>
>SQL is *always* better than Access, but you can easily break it's
>security as well. SQL Injection is the most common technique, but
>anything that allows a user to run a query might have issues. Stored
>procedures and views can help.
>
>Jeff

[Darren]

Hi,

On my personal site (url below), I have rolled my own simple blog
application. In it I allow people to post their own comments. My issue is on
how to restrict what exactly people enter into it.

My question therefore, is how do I restrict people, and what should I
restrict them entering when posting their comments into my database.

The database is MS Access.

Thanks for any and all help and advice.

--
Web Design in Northern Ireland
[url]www.yourdesignz.co.uk[/url]

Personal Site
[url]www.darren-craig.co.uk[/url]

[Aaron [SQL Server MVP]]

> On my personal site (url below), I have rolled my own simple blog

> application. In it I allow people to post their own comments. My issue is

on

> how to restrict what exactly people enter into it.
>
> My question therefore, is how do I restrict people, and what should I
> restrict them entering when posting their comments into my database.

??????????

Did you read your question? I have no idea where to begin because I have no
clue what you're talking about. What exactly are you trying to restrict?

[Darren]

Sorry, I should have made myself more clear.

I believe that people can post certain scripts into my database that can
comprimise security and I would like to know how to prevent this happening.

HTH


"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:evN2ZplfEHA.3888@TK2MSFTNGP09.phx.gbl...

> > On my personal site (url below), I have rolled my own simple blog
> > application. In it I allow people to post their own comments. My issue

is

> on

> > how to restrict what exactly people enter into it.
> >
> > My question therefore, is how do I restrict people, and what should I
> > restrict them entering when posting their comments into my database.

>
> ??????????
>
> Did you read your question? I have no idea where to begin because I have

no

> clue what you're talking about. What exactly are you trying to restrict?
>
>

[Bob Barrows [MVP]]

Maybe you are talking about SQL Injection, to which Accesss isn't really
vulnerable, but, look at this:


[url]http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23[/url]
[url]http://www.nextgenss.com/papers/advanced_sql_injection.pdf[/url]
[url]http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf[/url]

Bob Barrows

Darren wrote:

> Sorry, I should have made myself more clear.
>
> I believe that people can post certain scripts into my database that
> can comprimise security and I would like to know how to prevent this
> happening.
>
> HTH
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
> news:evN2ZplfEHA.3888@TK2MSFTNGP09.phx.gbl...

>>> On my personal site (url below), I have rolled my own simple blog
>>> application. In it I allow people to post their own comments. My
>>> issue is on how to restrict what exactly people enter into it.
>>>
>>> My question therefore, is how do I restrict people, and what should
>>> I restrict them entering when posting their comments into my
>>> database.

>>
>> ??????????
>>
>> Did you read your question? I have no idea where to begin because I
>> have no clue what you're talking about. What exactly are you trying
>> to restrict?

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Chris Hohmann]

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:Oksl32lfEHA.3916@TK2MSFTNGP11.phx.gbl...

> Maybe you are talking about SQL Injection, to which Accesss isn't really
> vulnerable, but, look at this:
>
>
> [url]http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23[/url]
> [url]http://www.nextgenss.com/papers/advanced_sql_injection.pdf[/url]
> [url]http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf[/url]

Color me confused. I was operating under the impression that Access was
vulnerable to SQL injection. Can you elaborate?

[Bob Barrows [MVP]]

Chris Hohmann wrote:

> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:Oksl32lfEHA.3916@TK2MSFTNGP11.phx.gbl...

>> Maybe you are talking about SQL Injection, to which Accesss isn't
>> really vulnerable, but, look at this:
>>
>>
>> [url]http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23[/url]
>> [url]http://www.nextgenss.com/papers/advanced_sql_injection.pdf[/url]
>> [url]http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf[/url]

>
> Color me confused. I was operating under the impression that Access
> was vulnerable to SQL injection. Can you elaborate?

SQL Injection works due to SQL Server's ability to use comment characters in
T-SQL statements, execute multiple batched statements, and use system and
external stored procedures to perform activities outside of the database.

Access
1. has no comment characters that can be used in sql statements
2. does not have the ability to batch statements: ie, run multiple sql
statements in a single command
3. does not allow user-defined VBA functions to be called in sql statements
executed from external applications.

The most a hacker can do is cause unwanted data to be added to your
database, but normal validation code will prevent most of that.

However, don't take the lack of susceptibility to SQL Injection as an
endorsement for the use of Access databases in web applications. The very
features that cause SQL Server (and other server-based databases) to be
vulnerable to SQL Injection are the features that make it a better choice
for a backend database for web applications.

If the developer never uses dynamic sql, his applications will not be
vulnerable to SQL Injection, and his web applications will be easier to code
(since delimiter problems will disappear).

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows [MVP]]

Chris Hohmann wrote:

> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:Oksl32lfEHA.3916@TK2MSFTNGP11.phx.gbl...

>> Maybe you are talking about SQL Injection, to which Accesss isn't
>> really vulnerable, but, look at this:
>>
>>
>> [url]http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23[/url]
>> [url]http://www.nextgenss.com/papers/advanced_sql_injection.pdf[/url]
>> [url]http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf[/url]

>
> Color me confused. I was operating under the impression that Access
> was vulnerable to SQL injection. Can you elaborate?

Correction:

There is an exploit that can use injected sql against Access if the hacker
can guess what the last criterion in a WHERE clause is:

sql = " ... WHERE ... somefield = " & value
If the hacker can cause this data to be posted to the value variable:

1 OR 1 > 0

then he can cause the query to return records that were not intended to be
returned. This is pretty much the only exploit that can be done against
Access.

Again, the avoidance of dynamic sql will halt this exploit in its tracks.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Jeff Cochran]

On Mon, 9 Aug 2004 17:42:42 -0700, "Chris Hohmann"
<nospam@thankyou.com> wrote:

>"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
>news:Oksl32lfEHA.3916@TK2MSFTNGP11.phx.gbl...

>> Maybe you are talking about SQL Injection, to which Accesss isn't really
>> vulnerable, but, look at this:
>>
>>
>> [url]http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23[/url]
>> [url]http://www.nextgenss.com/papers/advanced_sql_injection.pdf[/url]
>> [url]http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf[/url]

>
>Color me confused. I was operating under the impression that Access was
>vulnerable to SQL injection. Can you elaborate?

Access isn't vulnerable to a traditional SQL injection attack, but
what you may be mistaking is that a user could post a blog entry that
contained malicious code. That's any database vulnerability, the
entering of undesriable data. It could be as simple as the poster
entering pornographic descriptions or malicious links and so on.

Check the code for some of the free forums and blog scripts. Most do
an adequate job of filtering input, though anywhere a user can post
HTML they could still post a malicious link. Badword filters, HTML
Encoding the text, escaping quotes and so on should be standard in any
input checking. And none keep a user from posting:

"Hey, here's the link to the XP SP2 download:
http://www.badsite.com/badprogram.html/"

Some user will still plug that in their browser and blame you. :)

One popular option is registration to post. Using a password emailed
to a valid email account helps keep people honest. When you do
encounter a porblem, block the poster or IP. Having a moderated blog
also would help.

Jeff

[Chris Hohmann]

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OMSBaHofEHA.3944@tk2msftngp13.phx.gbl...

> Chris Hohmann wrote:

> > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> > news:Oksl32lfEHA.3916@TK2MSFTNGP11.phx.gbl...

> >> Maybe you are talking about SQL Injection, to which Accesss isn't
> >> really vulnerable, but, look at this:
> >>
> >>
> >> [url]http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23[/url]
> >> [url]http://www.nextgenss.com/papers/advanced_sql_injection.pdf[/url]
> >> [url]http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf[/url]

> >
> > Color me confused. I was operating under the impression that Access
> > was vulnerable to SQL injection. Can you elaborate?

>
> Correction:
>
> There is an exploit that can use injected sql against Access if the hacker
> can guess what the last criterion in a WHERE clause is:
>
> sql = " ... WHERE ... somefield = " & value
> If the hacker can cause this data to be posted to the value variable:
>
> 1 OR 1 > 0
>
> then he can cause the query to return records that were not intended to be
> returned. This is pretty much the only exploit that can be done against
> Access.
>
> Again, the avoidance of dynamic sql will halt this exploit in its tracks.

One could also use a UNION statement:

Dim cn,rs,sql
....
sql = "SELECT last_name,first_name FROM Employees WHERE last_name LIKE '%" &
Request.Querystring("FirstLetter") & "%'"
Set rs = cn.Execute sql
....

You would pass in the following as FirstLetter:

' UNION ALL SELECT username, password FROM Users WHERE username LIKE '

This method can also be extended to DML (INSERT, UPDATE, DELETE) statements
that are generated dynamically. In fact, any dynamically generated SQL is
potentially susceptible to SQL injection regardless of the platform, which I
guess was my original point.

[Darren]

Thank you all for your input.

>>Aaron - thank you, I did, read my post, but obviously it did make complete

sense to me. On re-reading it, I could and should have explained myself with
a little more clarity.

>>Bob - SQL injection was, I see now, what I was referring to. Thank you for

the very detailed explanation.

>> Jeff - Thank you also for the input. I will investigate some of the free

blog scripts to see how they filter user input.

Thank you all for taking the time to reply to my post.

Regards
Darren

--
Web Design in Northern Ireland
[url]www.yourdesignz.co.uk[/url]
Personal Site
[url]www.darren-craig.co.uk[/url]

[Glen T]

Working on completing my first CF application. Small website with some
restricted access pages and calendar/booking system.

I am using MS Access, largely because I have it, and the usage of this website
will be extremely low.

My question is, in order to secure the DB, do a) use the MS Access security to
set passwords on the tables, or b) put the DB in a folder with a username and
password on the server, or c) something else?

When I constructed my DB access code using DreamWeaver, I noticed that you
could enter a username and password into those forms when the queries are
created. What do those usernames and passwords hook up to? Thanks.

[NicTunney]

Make sure you keep your mdb off of the web root so it is not browsable. I'm
not sure about the Access security as it has been awhile since I used Access,
so I'll defer that part! In general, Access is not a secure option for a web
database, but I wouldn't say never use it. If cost is a factor, look towards
PostgreSQL or even MySQL.

[ctrl+alt+delete]

Consider as an option using the free version of MS SQL server... MSDE. It
will open up a whole new set of tools and methods for handling data that
Access doesn't.

"NicTunney" <webforumsuser@macromedia.com> wrote in message
news:dgl8gp$452$1@forums.macromedia.com...

> Make sure you keep your mdb off of the web root so it is not browsable.
> I'm
> not sure about the Access security as it has been awhile since I used
> Access,
> so I'll defer that part! In general, Access is not a secure option for a
> web
> database, but I wouldn't say never use it. If cost is a factor, look
> towards
> PostgreSQL or even MySQL.
>

[Glen T]

Currently, the site owner's ISP does not support MySQL and is "recommending" MS Access.

[Glen T]

It turns out that this ISP locates databases in a folder located above web root, so it shoud be relatively safe.