SQL Injections and common countermeasures

D

Dirk

Hi,

Generally I am used to working in another programming environment and have
some questions on what dangers there are regarding SQL injections in MS
Access.
I have the following questions:

1) Using a DAO.QueryDef, a SQL statement with a parameters clause like
"PARAMETERS nAge Integer, sName Text; SELECT * FROM People WHERE Age = nAge
AND Name LIKE sName ORDER BY Name;" Upon filling the parameters with
QueryDef.Parameters("sName") = "Fred", do I still need to quote the string
and escape any quotes in it or does the type setting to Text in the
parameters instruction do this for me?
2) When building SQL statements directly, is there anything other that I
should be mindfull of besides quoting and escaping with strings and checking
validity of integers? Any other possible exploits that need to be catered for?

Regards,

Dirk Louwers
 
T

Tim Ferguson

1) Using a DAO.QueryDef, a SQL statement with a parameters clause like
PARAMETERS nAge Integer, sName Text;
SELECT *
FROM People
WHERE Age = nAge
AND Name LIKE sName
ORDER BY Name;

Upon filling the parameters
with QueryDef.Parameters("sName") = "Fred", do I still need to quote
the string and escape any quotes in it or does the type setting to
Text in the parameters instruction do this for me?
Click to expand...

No; you just stick in the value you want to find. One trial will show you
that! If you try cofusing the nage parameter with something like this

15 OR 1=1

then you'll just get a type conversion error. Ditto for the sname,
putting in

Fred" OR "a" = "a

will just fail to find any records.
2) When building SQL statements directly, is there anything other that
I should be mindful of besides quoting and escaping with strings and
checking validity of integers? Any other possible exploits that need
to be catered for?
Click to expand...

Don't forget formatting of dates: Jet is very specific and it's not
neccessarily what you expect but it's well documented in the help files.
Code injection is not a problem unless you allow the user to write his
own sql, which is never a good idea. Get particular values for particular
criteria and put them in one by one.

Hope that helps


Tim F
 
D

Dirk

Yes cheers, just the info I was looking for. Will look into the date
formatting shortly.
 
Top