MS Access db as backend

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

zz12 - 13 Sep 2007 23:13 GMT

Hello. Could anyone post or point me to somewhere that has a sample model
of a best practice for using .asp page to insert, select, delete, update to
an MS Access database? Stored Procedures I know are the way to go for SQL
Server but I heard parameterized Access queries (saved Access queries or sql
sommand code) were the equivalent and if so what does the code look like?

Thanks in advance.

Reply to this Message

Bob Barrows [MVP] - 14 Sep 2007 00:14 GMT

> Hello. Could anyone post or point me to somewhere that has a sample
> model of a best practice for using .asp page to insert, select,
[quoted text clipped - 3 lines]
> equivalent and if so what does the code look like?
> Thanks in advance.

There's not much to it:

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%4
0TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.402
0%40tk2msftngp13.phx.gbl

Signature

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"

Reply to this Message

zz12 - 14 Sep 2007 00:41 GMT

Are both link samples good enough to use in preventing sql injection?

Thanks for your speedy reply Bob. Much appreciated.

>> Hello. Could anyone post or point me to somewhere that has a sample
>> model of a best practice for using .asp page to insert, select,
[quoted text clipped - 9 lines]
>
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.402
0%40tk2msftngp13.phx.gbl

Reply to this Message

Bob Barrows [MVP] - 14 Sep 2007 00:53 GMT

Absolutely. Not a bit of dynamic sql in sight! :-)
(they use parameters - no concatenation to create sql statements)

> Are both link samples good enough to use in preventing sql injection?
>
[quoted text clipped - 19 lines]
>> I don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"

Signature

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"

Reply to this Message

zz12 - 14 Sep 2007 01:43 GMT

So basically after assigning the parameter variables it would look something
like this:

par1 = request.form("txtEmployeeName")
par2 = CDate(request.form("txtHireDate")

Connection.qryMSAccess_Insert par1,par2

... which makes it sql injection proof?

> Absolutely. Not a bit of dynamic sql in sight! :-)
> (they use parameters - no concatenation to create sql statements)
[quoted text clipped - 22 lines]
>>> I don't check it very often. If you must reply off-line, then remove
>>> the "NO SPAM"

Reply to this Message

Bob Barrows [MVP] - 14 Sep 2007 02:09 GMT

The fact that it's passing parameter values rather than using concatentation
to build a sql statement (dynamic sql).

SQL Injection depends on the use of dynamic sql. Without dynamic sql, sql
injection cannot take place. Try it. Create a table called tblTest
containing a Text column called txtcol and add 3 records containing 'a',
'b', and 'c' in the column. Then create a saved parameter query called qTest
with this sql:
select * from tstTable where txtcol=[p]

Then in asp try running this code:

s="a union select * from tstTable"
response.write "using dynamic sql;<BR>"
set rs=Connection.Execute(select * from tsttable where txtcol='" & _
s & "'"
response.write s.getstring(,,,"<BR>")
response.write "<BR>using saved parameter query: <BR>"
set rs=createobject("adodb.recordset")
Connection.qTest par1,rs
if rs.eof then response.write "no records returned"

> So basically after assigning the parameter variables it would look
> something like this:
[quoted text clipped - 37 lines]
>> I don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"

Signature

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"

Reply to this Message

zz12 - 14 Sep 2007 23:21 GMT

Thanks for your insightful and quick reply Bob. Much appreciated. Have a
good weekend.

Cheers :-)

> The fact that it's passing parameter values rather than using
> concatentation to build a sql statement (dynamic sql).
[quoted text clipped - 60 lines]
>>> I don't check it very often. If you must reply off-line, then remove
>>> the "NO SPAM"

Reply to this Message

Bob Barrows [MVP] - 14 Sep 2007 01:00 GMT

Actually, I should clarify that statement: this technique will prevent
"primary" sql injection. You still need to take precautions to prevent
"secondary" sql injection. These steps include:
- always validate user input in server-side code - this includes user input
that has been retrieved from a database
- never trust user inputs , no matter their source

You can read about secondary sql injection here:
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf

> Are both link samples good enough to use in preventing sql injection?
>
[quoted text clipped - 13 lines]
>>
>> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.402
0%40tk2msftngp13.phx.gbl

Signature

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"

Reply to this Message

Webmaster Forum / ASP / Database Access / September 2007

MS Access db as backend