With Oracle, and I would presume most (all?) of the commercial DBMS's, you
can give users specific permissions on specific tables - to SELECT, INSERT,
DELETE etc. You could, then, just not allow the user that the www users
login as access to SQL statement's except precisely where they need them.
Mike
Mike Lacey
www.tek-tips.com -- a friendly, flame free, environment for computer
professionals and students
Perl forum at:
http://www.tek-tips.com/gthreadminder.cfm/lev2/4/lev3/32/pid/219
----- Original Message -----
From: "Daniel Fisher" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, April 14, 2001 6:48 PM
Subject: Piggy backing SQL statements...
> There's something that I've always feared about interfacing with a
database
> through the web:
>
> Let's say, that I create a table named 'USER_TABLE' with the following
> columns:
> ID INTEGER PRIMARY KEY NOT NULL
> FIRST_NAME VARCHAR(128)
> LAST_NAME VARCHAR(128)
> EMAIL_ADDRESS VARCHAR(128)
>
> Let's also say that I made a cgi script that took a parameter named 'ID'
and
> displayed the user information appropriate for that ID by going into the
> database and performing the following statement:
>
> "SELECT * FROM USER_TABLE WHERE ID=".$query->param('ID')
>
> What I'm afraid of, is that in some databases, could the end user of the
> script wreak havoc on my database by "piggybacking" another SQL statement
> onto mine?
>
> Let's say this particular adversary places the following information as
the
> value for the 'ID' cgi parameter:
>
> ID=1; DELETE FROM USER_TABLE
>
> (url encoded of course)
>
> For an attack of this type, it would be fairly easy to verify that the ID
> being passed is an integer and does not contain any non-numeric
characters,
> *but* I'm sure that there are times that folks do want to select on
> alpha-numeric entries and then this attack could be used.
>
> This concern is just a hypothetical issue. I haven't empirically tested
what
> I've mentioned above. While I have a feeling that the semi-colon would
make
> most databases barf, I remember seeing something about an upcoming mysql
> feature that allowed for atomic statements (i.e. more than one statement
can
> be passed in at a time, but they'll
> be treated as one action)
>
> My questions are:
> 1) Is this a potential attack method?
> 2) If not, are there any attack methods that are in the same vein that I
> should be made aware of? Are there particular SQL functions that are
> vulnerable to 'piggybacking' or 'hijacking'?
> 3) Assuming that this is a potential security hole, are there any
databases
> that are more vulnerable to it?
> 4) Assuming that this is a potential security hole, are there any methods
to
> prevent an attack?
> 5) If this is not a potential attack method, could you tell me the
> safeguards that are in place to protect from this scenario?
>
> A potential answer to question 4 is to give the tables obscure names. (If
> the adversary couldn't guess the name 'USER_TABLE' then that table would
be
> [a little more at least] secure.) (This technique wouldn't protect tables
> that are created by default, though... Which happens in MS-SQL Server)
>
> Thank you for your time,
> Daniel Fisher
> [EMAIL PROTECTED]
>
