MS Access db as backend

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.

Re: MS Access db as backend

zz12 wrote:
> 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.

There's not much to it:

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

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

--
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"

Re: MS Access db as backend

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

Thanks for your speedy reply Bob. Much appreciated.


"Bob Barrows [MVP]" wrote in message
news:%23ByCBvl9HHA.5948@TK2MSFTNGP04.phx.gbl...
> zz12 wrote:
>> 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.
>
> There's not much to it:
>
> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
>
> --
> 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"
>

Re: MS Access db as backend

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

zz12 wrote:
> Are both link samples good enough to use in preventing sql injection?
>
> Thanks for your speedy reply Bob. Much appreciated.
>
>
> "Bob Barrows [MVP]" wrote in message
> news:%23ByCBvl9HHA.5948@TK2MSFTNGP04.phx.gbl...
>> zz12 wrote:
>>> 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.
>>
>> There's not much to it:
>>
>> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>>
>> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
>>
>> --
>> 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"

--
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"

Re: MS Access db as backend

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

zz12 wrote:
> Are both link samples good enough to use in preventing sql injection?
>
> Thanks for your speedy reply Bob. Much appreciated.
>
>
> "Bob Barrows [MVP]" wrote in message
> news:%23ByCBvl9HHA.5948@TK2MSFTNGP04.phx.gbl...
>> zz12 wrote:
>>> 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.
>>
>> There's not much to it:
>>
>> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>>
>> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
>>

--
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"

Re: MS Access db as backend

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?




"Bob Barrows [MVP]" wrote in message
news:%23TE8AFm9HHA.1208@TK2MSFTNGP03.phx.gbl...
> Absolutely. Not a bit of dynamic sql in sight! :-)
> (they use parameters - no concatenation to create sql statements)
>
> zz12 wrote:
>> Are both link samples good enough to use in preventing sql injection?
>>
>> Thanks for your speedy reply Bob. Much appreciated.
>>
>>
>> "Bob Barrows [MVP]" wrote in message
>> news:%23ByCBvl9HHA.5948@TK2MSFTNGP04.phx.gbl...
>>> zz12 wrote:
>>>> 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.
>>>
>>> There's not much to it:
>>>
>>> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>>>
>>> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
>>>
>>> --
>>> 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"
>
> --
> 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"
>

Re: MS Access db as backend

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;
"
set rs=Connection.Execute(select * from tsttable where txtcol='" & _
s & "'"
response.write s.getstring(,,,"
")
response.write "
using saved parameter query:
"
set rs=createobject("adodb.recordset")
Connection.qTest par1,rs
if rs.eof then response.write "no records returned"

zz12 wrote:
> 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?
>
>
>
>
> "Bob Barrows [MVP]" wrote in message
> news:%23TE8AFm9HHA.1208@TK2MSFTNGP03.phx.gbl...
>> Absolutely. Not a bit of dynamic sql in sight! :-)
>> (they use parameters - no concatenation to create sql statements)
>>
>> zz12 wrote:
>>> Are both link samples good enough to use in preventing sql
>>> injection? Thanks for your speedy reply Bob. Much appreciated.
>>>
>>>
>>> "Bob Barrows [MVP]" wrote in message
>>> news:%23ByCBvl9HHA.5948@TK2MSFTNGP04.phx.gbl...
>>>> zz12 wrote:
>>>>> 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.
>>>>
>>>> There's not much to it:
>>>>
>>>> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>>>>
>>>> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
>>>>
>>>> --
>>>> 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"
>>
>> --
>> 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"

--
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"

Re: MS Access db as backend

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

Cheers :-)



"Bob Barrows [MVP]" wrote in message
news:ekfyYvm9HHA.4584@TK2MSFTNGP03.phx.gbl...
> 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;
"
> set rs=Connection.Execute(select * from tsttable where txtcol='" & _
> s & "'"
> response.write s.getstring(,,,"
")
> response.write "
using saved parameter query:
"
> set rs=createobject("adodb.recordset")
> Connection.qTest par1,rs
> if rs.eof then response.write "no records returned"
>
> zz12 wrote:
>> 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?
>>
>>
>>
>>
>> "Bob Barrows [MVP]" wrote in message
>> news:%23TE8AFm9HHA.1208@TK2MSFTNGP03.phx.gbl...
>>> Absolutely. Not a bit of dynamic sql in sight! :-)
>>> (they use parameters - no concatenation to create sql statements)
>>>
>>> zz12 wrote:
>>>> Are both link samples good enough to use in preventing sql
>>>> injection? Thanks for your speedy reply Bob. Much appreciated.
>>>>
>>>>
>>>> "Bob Barrows [MVP]" wrote in message
>>>> news:%23ByCBvl9HHA.5948@TK2MSFTNGP04.phx.gbl...
>>>>> zz12 wrote:
>>>>>> 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.
>>>>>
>>>>> There's not much to it:
>>>>>
>>>>> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>>>>>
>>>>> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
>>>>>
>>>>> --
>>>>> 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"
>>>
>>> --
>>> 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"
>
> --
> 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"
>