Hacker prevention

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MikeR - 12 Nov 2008 13:00 GMT

Due to a crash, I lost all my links to the various info about defeating SQL/script
injection.

I need the advice again. Is there a site that summarizes it? Or some separate
links/wisdom?

Thanks, Mike

Reply to this Message

Bob Barrows - 12 Nov 2008 13:34 GMT

> Due to a crash, I lost all my links to the various info about
> defeating SQL/script injection.
>
> I need the advice again. Is there a site that summarizes it? Or some
> separate links/wisdom?

Here is what I have:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf

Signature

HTH,
Bob Barrows

Reply to this Message

MikeR - 12 Nov 2008 22:10 GMT

>> Due to a crash, I lost all my links to the various info about
>> defeating SQL/script injection.
[quoted text clipped - 7 lines]
> http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
> http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf

Thanks Bob -

In your spare time, how about writing a best practices tutorial, with all the good
stuff in one place? ;->

The last link gets changed to
http://www.hp.com/spidynamics/papers/SQLInjectionWhitePaper.pdf
and I can't get to it. Huh?

I'm working with an Access 2000 db and I'm going to use parameterized queries.
Is there a way, on an update, to avoid blanking a field if it already contains a
value, and the user doesn't enter anything for that field in the input form?

For instance my stored query might be:

INSERT tbl1 set name = [P1], phone = [P2], category = [P3] where ID = [P4]

and the user didn't put in a phone on the input form.

I call the proc using

cn.nameproc SanName, SanPhone, SanCat, SanUID and SanPhone is empty

Reply to this Message

Bob Barrows [MVP] - 12 Nov 2008 23:38 GMT

>>> Due to a crash, I lost all my links to the various info about
>>> defeating SQL/script injection.
[quoted text clipped - 5 lines]
> In your spare time, how about writing a best practices tutorial, with
> all the good stuff in one place? ;->

Sure, as soon as I get a round tuit. All I've got are square ones ;-)

> The last link gets changed to
> http://www.hp.com/spidynamics/papers/SQLInjectionWhitePaper.pdf
> and I can't get to it. Huh?

Try using the Wayback Machine at www.archive.org ... or search the hp site

> I'm working with an Access 2000 db and I'm going to use parameterized
> queries. Is there a way, on an update, to avoid blanking a field if it
[quoted text clipped - 4 lines]
>
> INSERT

I presume you mean UPDATE ... :-)

> tbl1 set name = [P1], phone = [P2], category = [P3] where ID =
> [P4]
[quoted text clipped - 3 lines]
>
> cn.nameproc SanName, SanPhone, SanCat, SanUID and SanPhone is empty

You have no alternative but to change your query to handle the situation
where the parameter is blank, using iif().

set phone=iif([P20="",[phone],[P2])

Signature

Microsoft MVP - ASP/ASP.NET - 2004-2007
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

MikeR - 13 Nov 2008 02:02 GMT

>>>> Due to a crash, I lost all my links to the various info about
>>>> defeating SQL/script injection.
[quoted text clipped - 7 lines]
>
> Sure, as soon as I get a round tuit. All I've got are square ones ;-)

Dang! I had a spare one, but I can't find it.

>> The last link gets changed to
>> http://www.hp.com/spidynamics/papers/SQLInjectionWhitePaper.pdf
>> and I can't get to it. Huh?
>
> Try using the Wayback Machine at www.archive.org ... or search the hp site

That worked.

>> I'm working with an Access 2000 db and I'm going to use parameterized
>> queries. Is there a way, on an update, to avoid blanking a field if it
[quoted text clipped - 6 lines]
>
> I presume you mean UPDATE ... :-)

Ooops.... yep.

>> tbl1 set name = [P1], phone = [P2], category = [P3] where ID =
>> [P4]
[quoted text clipped - 8 lines]
>
> set phone=iif([P20="",[phone],[P2])

Neat.

What's your take on the need to sanitize the input before sending it to the query? I
got mixed signals from those papers.

Reply to this Message

Bob Barrows [MVP] - 13 Nov 2008 02:31 GMT

> What's your take on the need to sanitize the input before sending it
> to the query? I got mixed signals from those papers.

It's mildly controversial. Some people take the stand that since sql
injection is impossible given the use of parameters, then in-depth
sanitation, beyond the obvious task of preventing errors by making sure the
supplied data is of the proper datatypes is nothing but a waste of time.

My thinking is that security should consist of several layers, the first
being to validate the data to make sure it does not contain attempts to
breach your security. True, validation is not 100% secure, so the fall-back
layer is the use of parameters rather than dynamic sql. Some people
recommend inconveniencing the hacker when detected: for example, redirecting
him to a page that looks like what he would get if his hack was successful,
but displaying a perpetual progress bar so that his time is wasted. At the
very least, detected attempts should be logged so you are alerted about
them.

Signature

Microsoft MVP - ASP/ASP.NET - 2004-2007
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

MikeR - 14 Nov 2008 00:06 GMT

>> What's your take on the need to sanitize the input before sending it
>> to the query? I got mixed signals from those papers.
[quoted text clipped - 13 lines]
> very least, detected attempts should be logged so you are alerted about
> them.

Very reasoned. Thanks.
Mike

Reply to this Message

MikeR - 14 Nov 2008 00:31 GMT

>>>> Due to a crash, I lost all my links to the various info about
>>>> defeating SQL/script injection.
[quoted text clipped - 37 lines]
>
> set phone=iif([P20="",[phone],[P2])

UPDATE Stations SET rigs = [RIG], AMPS = iif([P2] = "" , [AMPS],[P2])
WHERE call=[UCALL];

Running this in the query designer blanks the column AMPS if P2 is left blank. If
it's filled, it works as expected.

Reply to this Message

Bob Barrows - 14 Nov 2008 14:31 GMT

> UPDATE Stations SET rigs = [RIG], AMPS = iif([P2] = "" , [AMPS],[P2])
> WHERE call=[UCALL];
>
> Running this in the query designer blanks the column AMPS if P2 is
> left blank. If it's filled, it works as expected.

Are you making sure to pass an empty string if the user leaves it blank?

Signature

HTH,
Bob Barrows

Reply to this Message

MikeR - 14 Nov 2008 16:28 GMT

>> UPDATE Stations SET rigs = [RIG], AMPS = iif([P2] = "" , [AMPS],[P2])
>> WHERE call=[UCALL];
[quoted text clipped - 3 lines]
>
> Are you making sure to pass an empty string if the user leaves it blank?

<slapforehead strength="hard">DOH!</slapforehead> Nope, I was just clicking OK on
the dialog box in Access. Entering "" worked.

Reply to this Message

Bob Barrows - 14 Nov 2008 16:37 GMT

>>> UPDATE Stations SET rigs = [RIG], AMPS = iif([P2] = "" ,
>>> [AMPS],[P2]) WHERE call=[UCALL];
[quoted text clipped - 7 lines]
> <slapforehead strength="hard">DOH!</slapforehead> Nope, I was just
> clicking OK on the dialog box in Access. Entering "" worked.

In that case, you would want to check for Null instead of an empty
string:
iif([P2] Is Null, ...

Signature

HTH,
Bob Barrows

Reply to this Message

MikeR - 17 Nov 2008 12:34 GMT

>>>> UPDATE Stations SET rigs = [RIG], AMPS = iif([P2] = "" ,
>>>> [AMPS],[P2]) WHERE call=[UCALL];
[quoted text clipped - 10 lines]
> string:
> iif([P2] Is Null, ...

Thank you sir!

Reply to this Message

Webmaster Forum / ASP / Database Access / November 2008

Hacker prevention