deleting DB & tables via webforms

[Harag]

Hi all

I heard that my DB tables can be truncated, deleted & dropped via uses
using my forms on my site

when a form asks for something like "name & passoword" IE a login form
this is where users/hackers can do nasty things.

instead of typing in the username they type something like:

' ; DROP DB

I'm not sure what they can type or how as you need things like DB &
table names.

My question is basically - how do you people stop people doing things
like this???

I'm using W2k Pro IIS5, ASP 3.0, and SQL Server. I'm going to have
quite a lot of forms when the projects completed and would like to
solve this issue asap.

thanks for any help.

Al

[Bob Barrows]

See the SQL Injection FAQ at [url]www.sqlsecurity.com[/url].

Basically, you can stop all of this by using stored procedures instedad of
dynamic SQL.

Bob Barrows

Harag wrote:

> Hi all
>
> I heard that my DB tables can be truncated, deleted & dropped via uses
> using my forms on my site
>
> when a form asks for something like "name & passoword" IE a login form
> this is where users/hackers can do nasty things.
>
> instead of typing in the username they type something like:
>
> ' ; DROP DB
>
> I'm not sure what they can type or how as you need things like DB &
> table names.
>
> My question is basically - how do you people stop people doing things
> like this???
>
> I'm using W2k Pro IIS5, ASP 3.0, and SQL Server. I'm going to have
> quite a lot of forms when the projects completed and would like to
> solve this issue asap.
>
> thanks for any help.
>
> Al

[Bob Barrows]

Nicely done, except for this:

Daniel Bush wrote:

> On Sun, 27 Jul 2003 19:04:01 +0100, Harag <harag@softhome.net> wrote:
>
> It should be something like this
> set cmd=server.createobject("adodb.command")
> with cmd
> .activeconnection=conn
> .cmdtype=adCmdText
> .commandtext="insert into tablea(a,b,c) values (?,?,?)"
> end with
> set rs=server.createobject("adodb.recordset")
> set rs=cmd.execute(lngRecs, _
>
> array(request.form("varA"),request.form("varB"),re quest.form("varC"))
>
> (or alternatively, you can create and populate the parameters in the
> with cmd block above and use the syntax
>
> rs.open cmd,,adOpenStatic,adLockOptimistic
> )
>

Why in the world would you advocate using a recordset object to run an
action query? A recordset is (should be) used for receiving results from a
records-returning query or procedure. It is a waste of resources and time to
create and open a recordset when you don't expect results from your query.
Much better would be:
cmd.execute lngRecs, _
array(request.form("varA"),request.form("varB"),re quest.form("varC"), _
adCmdText + adExecuteNoRecords

or, if you manually create the parameters collection:
cmd.execute lngRecs, , adCmdText + adExecuteNoRecords

Bob Barrows




The key is using the adExecuteNoRecords constant to tell the Command not to
bother creating a recordset object

[Daniel Bush]

Please allow me to modify these rules - Bob caught me with an
embarrasing error:

>
>Dan's Rules for Web Based ASP Database Programming
>
>1. Use Stored Procedures where possible. You can require this - go
>ahead and do so if you can.
>
>2. Do NOT allow the use of the "sa" username to access the database
>via your web site - create a web user account for each web site to
>access their database(s). When creating the UserName, give only the
>rights necessary (in SQL Server, datareader and if necessary,
>datawriter) to the databases required. So, even if a spurious "db
>drop" or other input is used, the UserId won't have permissions to do
>it anyway.
>
>3. REQUIRE - that the programmers use a DSNless connection to the
>database. I require that they put the ADO ConnectionString, UserId,
>and Password into Application variables in the global.asa. That way
>moving a web site to another box is easy and the administrators of the
>box don't have to worry about managing DSN's (which is an outdated way
>of connecting to databases anyway, if a programmer insists on this
>method, you have probably got an ignorant, inexperienced or lazy one
>anyway) (or all three).
>
>4. If the programmer needs to use Dynamic SQL instead of stored
>procedures, make sure that they use the ? placeholder in their SQL
>calls and use the ADO Command object ALWAYS when they use Dynamic SQL
>(you avoid the user being able to put in additional SQL into the SQL
>call). Require them to use Command Objects even for Recordset calls.
>If you see a programmer using a recordset object to do an UPDATE or
>INSERT statement, fire them and get someone who knows what they are
>doing. If you see code like this, hurl the programmer immediately out
>the nearest window:
>
>set rs=server.createobject("adodb.recordset")
>rs.open "insert into tablea (a,b,c) values('" & request.form("varA") &
>"', '" & request.form("varB") & "', '" & request.form("varC") & "')",
>conn,1,1
>
>It should be something like this
>set cmd=server.createobject("adodb.command")
>with cmd
> .activeconnection=conn
> .cmdtype=adCmdText
> .commandtext="insert into tablea(a,b,c) values (?,?,?)"
>end with

WRONG - this is what I should have written:
cmd.execute lngRecs, _
array(request.form("varA"),request.form("varB"),re quest.form("varC")

If you need to execute SQL to retrieve information back from the SQL
Server, again, use a command object but use a recordset to hold your
output
set cmd=server.createobject("adodb.command")
set rs=server.createobject("adodb.recordset")
with cmd
.activeconnection=conn
.cmdtype=adcmdtext
.commandtext="select from tableA where a=? or b=? or c=?"
end with
set rs=cmd.execute(lngRecs, _
array(request.form("varA"),request.form("varB"),re quest.form("varC"))

>
>(or alternatively, you can create and populate the parameters in the
>with cmd block above and use the syntax
>
>rs.open cmd,,adOpenStatic,adLockOptimistic
>)
>

Sorry for the confusion. Later in the post I was trying (badly as it
turns out) to point out this exact point - DO NOT use a recordset
object unless you have output comming back from the database.

Thanks Bob for pointing out my error(s),

Dan Bush
[email]me@R3M0V3.danbush.com[/email]

[Daniel Bush]

On Mon, 28 Jul 2003 08:08:50 -0400, "Bob Barrows"
<reb_01501@yahoo.com> wrote:

>Nicely done, except for this:
>
>Daniel Bush wrote:

>> On Sun, 27 Jul 2003 19:04:01 +0100, Harag <harag@softhome.net> wrote:
>>
>> It should be something like this
>> set cmd=server.createobject("adodb.command")
>> with cmd
>> .activeconnection=conn
>> .cmdtype=adCmdText
>> .commandtext="insert into tablea(a,b,c) values (?,?,?)"
>> end with
>> set rs=server.createobject("adodb.recordset")
>> set rs=cmd.execute(lngRecs, _
>>
>> array(request.form("varA"),request.form("varB"),re quest.form("varC"))
>>
>> (or alternatively, you can create and populate the parameters in the
>> with cmd block above and use the syntax
>>
>> rs.open cmd,,adOpenStatic,adLockOptimistic
>> )
>>

>Why in the world would you advocate using a recordset object to run an
>action query? A recordset is (should be) used for receiving results from a
>records-returning query or procedure. It is a waste of resources and time to
>create and open a recordset when you don't expect results from your query.
>Much better would be:
>cmd.execute lngRecs, _
>array(request.form("varA"),request.form("varB"),r equest.form("varC"), _
>adCmdText + adExecuteNoRecords
>
>or, if you manually create the parameters collection:
>cmd.execute lngRecs, , adCmdText + adExecuteNoRecords
>
>Bob Barrows
>
>
>
>
>The key is using the adExecuteNoRecords constant to tell the Command not to
>bother creating a recordset object
>

ARRGGGG!

Thanks for pointing out my mistake - I am definately NOT advocating
using a recordset object for an action query. I was trying to say the
exact opposite (and I tried to say so again in the last paragraph),
but instead of putting the SQL I meant to put in the command object, I
put in the "insert" statement. The SQL statement should have been
something like this:

>> .commandtext="insert into tablea(a,b,c) values (?,?,?)"

..commandtext="select a,b,c from tableA where a=? or b=? or c=?"

Anyway, I deserve this for being so sure of myself. One always gets
humbled when you try to put out "Dan's Rules for ..." anything <red
faced grin>.

My point in the last couple of threads was to try and show people that
this SQL Injection problem (and many others) can be eliminated just by
using the COMMAND object with placeholders. When this is used, it is
impossible for the input to be regarded as anything other than data -
and cannot be mistaken for CommandText.

I do agree that Stored Procedures are the best way to go, but
sometimes due to lack of DBA resources or other reasons, we, as
programmers, must use dynamic SQL - and if you do, you can avoid many
of the pitfalls if you just use the command object for all dynamic
SQL.

Dan Bush
[email]me@R3M0V3.danbush.com[/email]

[Harag]

Thanks for the help & tips guys.

Al.

On Sun, 27 Jul 2003 19:04:01 +0100, Harag <harag@softhome.net> wrote:

>Hi all
>
>I heard that my DB tables can be truncated, deleted & dropped via uses
>using my forms on my site
>
>when a form asks for something like "name & passoword" IE a login form
>this is where users/hackers can do nasty things.
>
>instead of typing in the username they type something like:
>
>' ; DROP DB
>
>I'm not sure what they can type or how as you need things like DB &
>table names.
>
>My question is basically - how do you people stop people doing things
>like this???
>
>I'm using W2k Pro IIS5, ASP 3.0, and SQL Server. I'm going to have
>quite a lot of forms when the projects completed and would like to
>solve this issue asap.
>
>thanks for any help.
>
>Al